Re: DBMS_SQL like package

A copy of this was sent to Jiri.Felcman_at_noofs.abb.no (if that email address didn't require changing) On Tue, 21 Sep 1999 06:56:17 GMT, you wrote:

>Does anybody know a package offering a function processing dynamic SQL
>Select with pragma WNDS. I want to use a dynamic SELECT in a view
>definition. Function returns a column value.
>

You could write an extproc in 8.0 to do this

In Oracle8i, release 8.1, this is supported with native dynamic sql. It would look like this:

tkyte_at_8i> create or replace function count_rows( p_tname in varchar2 ) return number
  2 as
  3 l_cnt number;
  4 begin
  5 execute immediate 'select count(*) from ' || p_tname INTO l_cnt;   6
  7 return l_cnt;
  8 end;
  9 /

Function created.

tkyte_at_8i>
tkyte_at_8i> select count_rows( 'emp' ) from dual   2 /

COUNT_ROWS('EMP')

               14

>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 Tue Sep 21 1999 - 07:55:52 CDT