execute immediate

[Ervin Rodriguez]

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.

[Boris Milrud]

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.

[Ervin Rodriguez]

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.
>

[Boris Milrud]

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.
>>
>