Re: Passing Table name as an argument

From: Arlette BROSSARD <abray_at_club-internet.fr>
Date: 14 Apr 1999 15:26:56 GMT
Message-ID: <01be8693$5b0f5ac0$LocalHost@ntdv2113>

Your problem is not to pass a table_name as a argument to a stored procedure
but to use a variable after FROM in a DML.

You need use Dynamic SQL (DBMS_SQL) and for that : OPEN_CURSOR,
PARSE,
DEFINE_COLUMN,
EXECUTE_AND_FETCH,
COLUMN_VALUE,
CLOSE CURSOR EXAMPLE: CREATE OR REPLACE PROCEDURE prc_count (stable_name IN VARCHAR2)

IS

icursor     INTEGER;
squery      VARCHAR2(2000) := 'SELECT COUNT(*) FROM ';
imode       INTEGER := DBMS_SQL.NATIVE;
irows       INTEGER(1);

rows_count INTEGER;

BEGIN icursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE (icursor,squery||stable_name,imode); DBMS_SQL.DEFINE_COLUMN (icursor,1,rows_count); irows := DBMS_SQL.EXECUTE_AND_FETCH (icursor); IF irows > 0 THEN

   DBMS_SQL.COLUMN_VALUE (icursor,1,rows_count); END IF;
DBMS_SQL.CLOSE_CURSOR (icursor);

DBMS_OUTPUT.PUT_LINE ('rows : '||rows_count);

EXCEPTION
   WHEN cefit.tooldiv.sortie_application THEN

      IF DBMS_SQL.IS_OPEN (icursor) THEN
         DBMS_SQL.CLOSE_CURSOR (icursor);
      END IF;
      DBMS_OUTPUT.PUT_LINE ('ERROR : '||SQLERRM);
      

END;
/

This example gives

SQL> EXECUTE prc_count ('all_tables')
rows : 222

Procédure PL/SQL terminée avec succès.

vragha_at_my-dejanews.com a écrit dans l'article <7f0og3$1kd$1_at_nnrp1.dejanews.com>...
> Hello!
>
> I want to pass a table name as an argument to a stored procedure? Is it
> possible at all or am I dreaming?
>
> Iwas trying it with a very simple stored procedure e.g
>
> #######################PL/SQL script#####################################
> create or replace procedure sp_test(table_name in varchar2(100))
> is
> test_num number;
> selecttable varchar2(100);
> begin
> select count(*) into test_num from table_name;
> end;
> #######################End PL/SQL

script#####################################

>
> I tried out various combinations using this, but without luck. Can
anybody
> suggest how to go about this.
>
> Thanks.
>
>
> --
> Vijay R
> vragha_at_my-dejanews.com
>
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

>
Received on Wed Apr 14 1999 - 10:26:56 CDT