Re: Passing table name to stored procedure?

From: <pcucciolo_at_hotmail.com>
Date: Thu, 17 Dec 1998 15:48:22 GMT
Message-ID: <75b946$qi0$1@nnrp1.dejanews.com>

In article <7553mn$8ea$1_at_mawar.singnet.com.sg>,   "radzi" <mradzi_at_pc.jaring.my> wrote:
> Hi,
>
> I am writing a stored procedure which should take in tablename as parameter.
> I tried several ways but it did not compile. Could anybody who can help me
> on this? Or is it this the limitation which I have to live with the stored
> procedure?
>
> Many thanks.
>
> Radzi.
> IMS Asia (1989) Pte. Ltd.
> Singapore.
>

Try :

create or replace procedure test(tab1 in varchar2,tab2 in varchar2,campo in varchar2) as c1 integer; rc integer; error integer; conti varchar2(20); contati varchar2(20); begin c1:=dbms_sql.open_cursor; dbms_sql.parse(c1,'select count(*) from '||tab1,dbms_sql.V7); dbms_sql.define_column_char(c1,1,conti,20); rc:=dbms_sql.execute(c1); error:=dbms_sql.fetch_rows(c1); dbms_sql.column_value_char(c1,1,conti);

dbms_output.put_line('in '||tab1||' has '||conti||' rows');
dbms_sql.close_cursor(c1); c1:=dbms_sql.open_cursor;
dbms_sql.parse(c1,'select count(*) from  '||tab1||' a,'||tab2||' b where

a.'||campo||' = b.'||campo,dbms_sql.V7); dbms_sql.define_column_char(c1,1,contati,20); rc:=dbms_sql.execute(c1); error:=dbms_sql.fetch_rows(c1); dbms_sql.column_value_char(c1,1,contati); dbms_output.put_line('in '||tab1||' in join mediante '||campo||' whit '||tab2||' has '||contati||' rows'); dbms_sql.close_cursor(c1); end; /

bye Pino
Italy

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Dec 17 1998 - 09:48:22 CST