Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Unanswered: Need clarification on dynamic sql

    Hello guys,

    I am a bit confused about the need of dynamic sql in one of my stored procedures. I am new to PL/SQL.

    There are two store procedures in the attachment below.
    The first does not require dynamic sql to function properly (as intended), but the second does.

    Can someone explain to me why?

    Thanks!
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >but the second does
    I see no reason why EXECUTE IMMEDIATE is used in the 2nd procedure.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Need clarification on dynamic sql

    You only need dynamic SQL if the names of the tables and/or columns used in the query can change at runtime. That is not the case here, so neither procedure needs dynamic SQL.

  4. #4
    Join Date
    Oct 2003
    Posts
    4
    Actually, without dynamic sql the second one does not work properly.

    I have to mention that one of the column of the table in the second procedure has a nested table of objects.

    Without dynamic sql, only the first row is affect no matter what primary key you pass in.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    In that case, it is probably because the version of PL/SQL you have doesn't support the new feature that SQL supports. With any Oracle version prior to 10G, SQL is always ahead of PL/SQL with new features.

  6. #6
    Join Date
    Oct 2003
    Posts
    4
    I am using Oracle9i R2.

    Will my program behave differently with 10G?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •