RE: Dynamic SQL

Hey Larry,

You amaze as always by the kind of rationale you have.

• larry elkins <elkinsl_at_flash.net> wrote:
  > 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-----
  > Sent: Wednesday, January 17, 2001 10:08 AM
  > To: Multiple recipients of list ORACLE-L
  >
  >
  > 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);
  > END IF;
  > END;
  > /
  >
  > Execute cc1('xxxx')
  >
  > --
  > Please see the official ORACLE-L FAQ:
  > http://www.orafaq.com
  > --
  > Author: larry elkins
  > INET: elkinsl_at_flash.net
  >
  > Fat City Network Services -- (858) 538-5051 FAX:
  > (858) 538-5051
  > San Diego, California -- Public Internet
  > access / Mailing Lists
  >

  ---

  
  > To REMOVE yourself from this mailing list, send an
  > E-Mail message
  > to: ListGuru_at_fatcity.com (note EXACT spelling of
  > 'ListGuru') and in
  > the message BODY, include a line containing: UNSUB
  > ORACLE-L
  > (or the name of mailing list you want to be removed
  > from). You may
  > also send the HELP command for other information
  > (like subscribing).
  

  ---