Re: PL/SQL Problem

From: Ken Denny <kendenny_at_bnr.ca>
Date: 1997/09/23
Message-ID: <3428222F.5AF6@bnr.ca>#1/1

Gary England wrote:
>
> DBMS_SQL. That was the name I was trying to remember. How is it setup
> and called from PL/SQL?
>
> Thanks,

DECLARE
   dysql INTEGER;
   dontcare INTEGER;
   sql_cmd VARCHAR2(20);
BEGIN
   sql_cmd := 'OPEN_CURSOR (1)';
   dysql := DBMS_SQL.OPEN_CURSOR;
   sql_cmd := 'PARSE (1)';
   DBMS_SQL.PARSE(dysql,"DROP TABLE TABLE_NAME",DBMS_SQL.NATIVE);    sql_cmd := 'EXECUTE (1)';
   dontcare := DBMS_SQL.EXECUTE(dysql);
/*
|| I don't know if the cursor needs to be closed and reopened before || executing another command, but it can't hurt. */

   sql_cmd := 'CLOSE_CURSOR (1)';
   DBMS_SQL.CLOSE_CURSOR(dysql);
   sql_cmd := 'OPEN_CURSOR (2)';
   dysql := DBMS_SQL.OPEN_CURSOR;
   sql_cmd := 'PARSE (2)';
   DBMS_SQL.PARSE(dysql,"CREATE TABLE TABLE_NAME",DBMS_SQL.NATIVE);    sql_cmd := 'EXECUTE (2)';
   dontcare := DBMS_SQL.EXECUTE(dysql);
   sql_cmd := 'CLOSE_CURSOR (2)';
   DBMS_SQL.CLOSE_CURSOR(dysql);
EXCEPTION
   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE('ERROR '||TO_CHAR(SQLCODE)||' OCCURRED ON '
         ||sql_cmd;

END; Hope this helps
Ken Denny
kendenny_at_bnr.ca (work)
kdenny_at_interpath.com (home) Received on Tue Sep 23 1997 - 00:00:00 CDT