If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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

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: