Help with DBMS_SQL

From: jon clarke <jonathan.clarke_at_uk.sun.com>
Date: Thu, 28 Jan 1999 11:43:54 +0000
Message-ID: <36B04D7A.34BA9906@uk.sun.com>

Folks,

I am trying to dynamically drop and create sequences. The dropping is working fine, but the creating keeps coming back with errors.

Errors
sType CREATE sSeqName ACIT_ID_SEQ
Called CREATE
sStatement is CREATE SEQUENCE ACIT_ID_SEQ Created cursor
pr_build_dynamic:Something went wrong. closing cursor ORA-01403: no data found
sequence_install:Problem creating sequences ORA-01403: no data found

Code is

DBMS_OUTPUT.put_line('Called CREATE');

       
       
       sStatement := 'CREATE SEQUENCE '||sSeqname;  


       --START WITH '||TO_CHAR(nStartValue);


       DBMS_OUTPUT.put_line('sStatement is '||sStatement);
       
       cursor_name := DBMS_SQL.open_cursor;
       DBMS_OUTPUT.put_line('Created cursor');
       
              -- Create the statement you want parsed
       DBMS_SQL.parse(cursor_name
                     , sStatement
                     , DBMS_SQL.v7);
       DBMS_OUTPUT.put_line('Parsed statement');
            


       --  Execute


       rows_processed := DBMS_SQL.execute(cursor_name);
       DBMS_OUTPUT.put_line('Executed');
       


       --Close after successful completion


       DBMS_SQL.close_cursor(cursor_name); 
       DBMS_OUTPUT.put_line('Cursor closed normally');

   END IF;
EXCEPTION
    WHEN OTHERS
    THEN
       DBMS_OUTPUT.put_line('pr_build_dynamic:Something went wrong. closing cursor '|| SQLERRM(100));

       DBMS_SQL.close_cursor(cursor_name);
       RAISE;

any suggestions

Cheers

Jon Received on Thu Jan 28 1999 - 05:43:54 CST