Re: variablize from clause in cursor declaration

A copy of this was sent to cubical8_at_my-deja.com (if that email address didn't require changing) On Fri, 17 Sep 1999 14:42:37 GMT, you wrote:

>I was trying to write an oracle procedure with two cursors. The frist
>is opened against a table which provides information such as a table
>name to be used by the second cursor. In theory what I am trying to do
>is variablize the name of the table in the from clause of the second
>cursor declaration. I have talked this over with Oracle support and
>they say that you can't really do this but I know some folks may have
>already had to do something very similar. I need your ideas on
>possible solutions or work arounds.
>

You need to use dynamic sql to do this. In Oracle8.0 and before this is done via the DBMS_SQL package.

In Oracle8i, release 8.1 this could look like this:

declare

   l_cnt;
begin

   for x in ( select table_name from user_tables ) loop

      execute immediate 'select count(*) from ' || x.table_name INTO l_cnt;
      dbms_output.put_line('The count for ' || x.table_name || ' is ' || l_cnt

   end loop;
end;
/

>Thanks
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Sep 17 1999 - 10:03:19 CDT