Re: Couple of PL/SQL questions

From: <mark_fredericks_at_my-dejanews.com>
Date: Thu, 15 Apr 1999 19:10:20 GMT
Message-ID: <7f5dik$1b2$1@nnrp1.dejanews.com>

Ian

Here is an example of a function using dynamic that can access a table by passed name and where clause. It is not as general as select * , performing only a record count here but you can see the sytax and get some ideas.

HTH Mark

CREATE OR REPLACE function count_table_records (in_table in varchar2, in_clause in varchar2 default null) return integer is /* Purpose: function to count records in any table using dynamic sql usage: emp_count := count_table_records ('emp'); clerk_count := count_table_records ('emp', 'where job = upper(''CLERK'')' ); chi_count := count_table_records (in_table => 'scott.emp', in_clause => 'where deptno in (select deptno from dept where location = upper(''chicago'')' ); */ temp_count integer; exec_feedback integer; cursor_handle integer := dbms_sql.open_cursor ; sql_str varchar2(32000) := 'select nvl(count(*),0) recs from ' || in_table || ' ' || in_clause; begin

dbms_sql.parse(cursor_handle,sql_str,dbms_sql.native); 
dbms_sql.define_column(cursor_handle,1,temp_count);  exec_feedback :=
dbms_sql.execute_and_fetch (cursor_handle); 
dbms_sql.column_value(cursor_handle,1,temp_count); 
dbms_sql.close_cursor(cursor_handle);  return (temp_count); exception  when

others then dbms_sql.close_cursor(cursor_handle); dbms_output.enable(999999); dbms_output.put_line(substr('err count_table_records'|| ' '||sql_str,1,80)); dbms_output.put_line(sqlerrm); return(-1); -- return ilogiacal count if error encountered end count_table_records;

In article <01be864e$b1202ec0$3a64a8c0_at_jan-hein>,   "jbiekens" <jbiekens_at_gironet.nl> wrote:
> You have to use the DBMS_SQL package to be able to use dynamic sql. In
> ...
> Iancrozier <iancrozier_at_aol.com> schreef in artikel
> <19990409101529.27675.00000553_at_ng-fq1.aol.com>...
> > Is it possible to pass an argument to a PL/SQL program like you can to
> > procedure?
> >
> > e.g. ( variable_in in number)
> >
> > When passing an argument to a procedure, can you use the argument to
> specify
> > which schema's
> > tables you want to read e.g.
> >
> > select * from variable_in.table1;
> >
> > where variable_in is passed to the procedure.
> >
> > I have tried, but the procedure compiles with errors.
> >
> >
> > Thanks in advance
> >
> > Ian Crozier
> >

>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Apr 15 1999 - 14:10:20 CDT