execute immediate


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: execute immediate

  1. #1
    Ervin Rodriguez Guest

    execute immediate


    Hello there, I need some help here.
    I have a table that contains sql queries(select statements). I created a
    cursor so that i can retrieve those queries and execute them one by one.
    I am trying to use execute immediate but it does not work. What I want to
    do is to retrieve the query from the table and execute them. each query
    will return a row. I want to take the value return by the query and store
    them in variables. I was trying to use the execute immediate statement and
    i got stuck. can you please help? how can i do this? thank you.

  2. #2
    Boris Milrud Guest

    Re: execute immediate


    Ervin,
    Here is the code example that could be useful in your case:

    drop table Test;

    create table Test
    (
    Id integer,
    SQLString varchar2(4000)
    );

    insert into Test values (1, 'select count(*) from Emp');
    insert into Test values (2, 'select count(*) from Dept');
    insert into Test values (3, 'select count(*) from User_Tables');

    commit;

    Now, actual PL/SQL block:

    declare

    vCount integer;

    cursor csrSQLToExecute (pId Test.Id%type)
    is
    select *
    from Test
    where Id > pId;

    begin

    for rec in csrSQLToExecute(0)
    loop
    execute immediate rec.SQLString into vCount;
    dbms_output.put_line('String: ' || rec.SQLString ||
    '. Result: ' || to_char(vCount));
    end loop;

    end;
    /

    Is that what you need? Let me know if you have any questions.

    Boris.


    "Ervin Rodriguez" <ervin06@netscape.net> wrote:
    >
    >Hello there, I need some help here.
    >I have a table that contains sql queries(select statements). I created

    a
    >cursor so that i can retrieve those queries and execute them one by one.
    >I am trying to use execute immediate but it does not work. What I want

    to
    >do is to retrieve the query from the table and execute them. each query
    >will return a row. I want to take the value return by the query and store
    >them in variables. I was trying to use the execute immediate statement

    and
    >i got stuck. can you please help? how can i do this? thank you.



  3. #3
    Ervin Rodriguez Guest

    Re: execute immediate


    Thank you for your help, Mr. Milrud. This really helps. Now, what if SQLString
    column is of type CLOB, how can I convert to a varchar so that i can execute
    it using a cursor? thanks

    "Boris Milrud" <milrud@hotmail.com> wrote:
    >
    >Ervin,
    >Here is the code example that could be useful in your case:
    >
    >drop table Test;
    >
    >create table Test
    >(
    > Id integer,
    > SQLString varchar2(4000)
    >);
    >
    >insert into Test values (1, 'select count(*) from Emp');
    >insert into Test values (2, 'select count(*) from Dept');
    >insert into Test values (3, 'select count(*) from User_Tables');
    >
    >commit;
    >
    >Now, actual PL/SQL block:
    >
    >declare
    >
    > vCount integer;
    >
    > cursor csrSQLToExecute (pId Test.Id%type)
    > is
    > select *
    > from Test
    > where Id > pId;
    >
    >begin
    >
    > for rec in csrSQLToExecute(0)
    > loop
    > execute immediate rec.SQLString into vCount;
    > dbms_output.put_line('String: ' || rec.SQLString ||
    > '. Result: ' || to_char(vCount));
    > end loop;
    >
    >end;
    >/
    >
    >Is that what you need? Let me know if you have any questions.
    >
    >Boris.
    >
    >
    >"Ervin Rodriguez" <ervin06@netscape.net> wrote:
    >>
    >>Hello there, I need some help here.
    >>I have a table that contains sql queries(select statements). I created

    >a
    >>cursor so that i can retrieve those queries and execute them one by one.
    >>I am trying to use execute immediate but it does not work. What I want

    >to
    >>do is to retrieve the query from the table and execute them. each query
    >>will return a row. I want to take the value return by the query and store
    >>them in variables. I was trying to use the execute immediate statement

    >and
    >>i got stuck. can you please help? how can i do this? thank you.

    >



  4. #4
    Boris Milrud Guest

    Re: execute immediate


    Ervin,

    If the SQL string is stored as CLOB, then you have to:
    - get CLOB locator
    - read the CLOB into the string using DBMS_LOB package
    - then execute this string using EXECUTE IMMEDIATE command.

    The limit of the varchar2 string in PL/SQL is 32767 characters. Therefore,
    if your CLOB string is longer than that, you have to add the code to read
    it in chunks (of 32767 or less) and load it into the table (index-by or nested)
    of strings. Otherwise, the code is pretty straightforward:


    drop table Test2;

    create table Test2
    (
    id integer,
    SQLString clob
    );

    insert into Test2 values (1, 'select count(*) from Emp');
    insert into Test2 values (2, 'select count(*) from Dept');
    insert into Test2 values (3, 'select count(*) from User_Tables');

    commit;

    Now, actual PL/SQL block:

    declare
    vCount integer;

    cursor csrSQLToExecute
    is
    select *
    from Test2;

    vSQLString varchar2(32767);
    vAmount pls_integer := 0;

    begin
    for rec in csrSQLToExecute
    loop
    if (rec.SQLString is not null)
    then

    vAmount := dbms_lob.getLength(rec.SQLString);

    -- Read the whole clob but do not exceed 32767 limit.
    vAmount := least(vAmount, 32767);

    dbms_lob.read(rec.SQLString, vAmount, 1, vSQLString);

    execute immediate vSQLString into vCount;
    dbms_output.put_line('String: ' || vSQLString ||
    '. Result: ' || to_char(vCount));

    end if;

    end loop;
    end;
    /

    Let me know if it works for you.

    Boris.


    "Ervin Rodriguez" <ervin06@netscape.net> wrote:
    >
    >Thank you for your help, Mr. Milrud. This really helps. Now, what if SQLString
    >column is of type CLOB, how can I convert to a varchar so that i can execute
    >it using a cursor? thanks
    >
    >"Boris Milrud" <milrud@hotmail.com> wrote:
    >>
    >>Ervin,
    >>Here is the code example that could be useful in your case:
    >>
    >>drop table Test;
    >>
    >>create table Test
    >>(
    >> Id integer,
    >> SQLString varchar2(4000)
    >>);
    >>
    >>insert into Test values (1, 'select count(*) from Emp');
    >>insert into Test values (2, 'select count(*) from Dept');
    >>insert into Test values (3, 'select count(*) from User_Tables');
    >>
    >>commit;
    >>
    >>Now, actual PL/SQL block:
    >>
    >>declare
    >>
    >> vCount integer;
    >>
    >> cursor csrSQLToExecute (pId Test.Id%type)
    >> is
    >> select *
    >> from Test
    >> where Id > pId;
    >>
    >>begin
    >>
    >> for rec in csrSQLToExecute(0)
    >> loop
    >> execute immediate rec.SQLString into vCount;
    >> dbms_output.put_line('String: ' || rec.SQLString ||
    >> '. Result: ' || to_char(vCount));
    >> end loop;
    >>
    >>end;
    >>/
    >>
    >>Is that what you need? Let me know if you have any questions.
    >>
    >>Boris.
    >>
    >>
    >>"Ervin Rodriguez" <ervin06@netscape.net> wrote:
    >>>
    >>>Hello there, I need some help here.
    >>>I have a table that contains sql queries(select statements). I created

    >>a
    >>>cursor so that i can retrieve those queries and execute them one by one.
    >>>I am trying to use execute immediate but it does not work. What I want

    >>to
    >>>do is to retrieve the query from the table and execute them. each query
    >>>will return a row. I want to take the value return by the query and store
    >>>them in variables. I was trying to use the execute immediate statement

    >>and
    >>>i got stuck. can you please help? how can i do this? thank you.

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center