RE: Dynamic SQL

From: larry elkins <elkinsl_at_flash.net>
Date: Wed, 17 Jan 2001 18:44:40 -0600
Message-Id: <10744.126946@fatcity.com>

For what it is worth you don't really need DBMS_SQL in your particular case. The only thing that is "dynamic" is the name of the table. This could easily be passed in as a parameter. Following is an example using an explicit cursor (you could do the same thing with an implicit cursor and checking for the no_data_found exception).

SQL> CREATE OR REPLACE PROCEDURE cc1(source IN VARCHAR2) is   2 Cursor C1 Is
  3 Select 'x'
  4 From user_Tables
  5 Where table_name = upper(source);   6 primary_dummy varchar2(1);
  7 Begin
  8 Open C1;
  9 Fetch C1 Into primary_dummy;
 10 If C1%Found Then
 11 DBMS_OUTPUT.PUT_LINE('Table exists ');  12 Else
 13 DBMS_OUTPUT.PUT_LINE('Table does not exist ');  14 End If;
 15 Close C1;
 16 End;
 17 /

Procedure created.

SQL> set serveroutput on
SQL> execute cc1('EMP')
Table exists

PL/SQL procedure successfully completed.

SQL> execute cc1('ABCDEFGH')
Table does not exist

Just an alternative to consider.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Skurský Michal Sent: Wednesday, January 17, 2001 10:08 AM To: Multiple recipients of list ORACLE-L Subject: Dynamic SQL

Hello,
can anybody help me to understand my mistake? I tried to create procedure with 'table_name' parameter, which writes result of select -> Table exists or not. Procedure doesn't work properly - existing table is not found.
Thanks

CREATE OR REPLACE PROCEDURE cc1(source IN VARCHAR2) is

  name               VARCHAR2(30);
  source_cursor      INTEGER;
  ignore             INTEGER;

BEGIN
  source_cursor := dbms_sql.open_cursor;   DBMS_SQL.PARSE(source_cursor,

       'SELECT table_name INTO name FROM user_tables where table_name = source' ,DBMS_SQL.native);
  ignore := DBMS_SQL.EXECUTE(source_cursor);   DBMS_OUTPUT.PUT_LINE('Table exists ');   DBMS_SQL.CLOSE_CURSOR(source_cursor);

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Table does not exist ');     IF DBMS_SQL.IS_OPEN(source_cursor) THEN       DBMS_SQL.CLOSE_CURSOR(source_cursor); Received on Wed Jan 17 2001 - 18:44:40 CST