1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Dynamic Sql

Discussion in 'SQL PL/SQL' started by mdevendh, Aug 9, 2011.

  1. mdevendh

    mdevendh Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    Can anyone tell me the dynamic sql concept with simple example
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I presume you're asking about using execute immediate with dynamic strings. It's a complex topic and there are numerous examples that can be given. Basically dynamic sql is used when one needs to create a statement 'on the fly' usually (but not limited to) creating select statements when the table name is not known beforehand:

    Code (SQL):
    SQL> DECLARE
      2          sql_stmt varchar2(4000);
      3          tabnm   varchar2(35):='EMP';
      4          tabrec  emp%rowtype;
      5  BEGIN
      6          sql_stmt := 'select * from '||tabnm||' where rownum = 1';
      7
      8          EXECUTE immediate sql_stmt INTO tabrec;
      9
     10          dbms_output.put_line(tabrec.empno||' ' ||tabrec.ename||' '||tabrec.sal||' '||tabrec.hiredate);
     11
     12  END;
     13  /
    7369 SMITH 800 17-DEC-80
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    Remember that execute immediate involves a 'context switch' and the data returned from the dynamic select will not be displayed in the PL/SQL 'window' and must be returned to a variable (as illustrated).
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    This link may hepl you to get an overview.