Dynamic SQL

From: Skurský Michal <skursky_at_brn.pvt.cz>
Date: Wed, 17 Jan 2001 15:57:24 +0100
Message-Id: <10744.126884@fatcity.com>

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;
/ Received on Wed Jan 17 2001 - 08:57:24 CST