DBMS_SQL PACKAGE
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: DBMS_SQL PACKAGE

  1. #1
    Join Date
    Mar 2001
    Posts
    6
    Hi,
    I created a procedure to create a table.
    Compile it No error found.
    Execute my procedure. Successfully.

    But there is no table creation.

    Can someone tell me What's wrong ?
    Should I grant some thing for my self ?

    Please help

    Thanks
    DJ

    CREATE OR REPLACE PROCEDURE create_tables AS

    vCursor NUMBER;
    vTableDescription VARCHAR2(4000);
    vCreateString VARCHAR2(100);
    vNumber INTEGER;
    BEGIN
    /*Open the cursor for processing */
    vCursor := DBMS_SQL.OPEN_CURSOR;

    /* First create column for the table */
    vTableDescription := '( c1 VARCHAR2(4000), c2 CHAR(255), c2 NCHAR(256),
    c4 LONG, c5 NUMBER, c6 INTEGER, c7 DATE, c8 BLOB,
    C9 CLOB)';

    /* Create a table statement and parse vTableName */
    vCreateString := 'CREATE TABLE TEST_DJ ' || vTableDescription;
    DBMS_SQL.PARSE(vCursor, vCreateString, DBMS_SQL.V7);

    vNumber := DBMS_SQL.EXECUTE(vCursor);

    /* Close the cursor */
    DBMS_SQL.CLOSE_CURSOR(vCursor);
    EXCEPTION
    WHEN OTHERS THEN
    /* Close the cursor first, then raise the error */
    DBMS_SQL.CLOSE_CURSOR(vCursor);

    END create_tables;


  2. #2
    Join Date
    Dec 2000
    Posts
    10
    Hello DJ,

    Could you remove exception block?
    Like this I think the next time you execute your procedure a beautiful error message will appear!!!
    Tell us the error message you get.

    Bye,
    Steph.

  3. #3
    Join Date
    Feb 2001
    Posts
    184
    hI,

    In your code you have Exception which is being read, so tha table is not created successfully.
    Here are the Errors.
    vCreateString VARCHAR2(100);
    The length is not enough to handle the Statement, Increase this length may be 500 etc.

    Second you have two columns with same name... See C2, C2

    Change these one, remove the exception, or put a message in exception, so you know which part is executed.

    May be like this


    EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('This Procedure is in Error');
    /* Close the cursor first, then raise the error */
    DBMS_SQL.CLOSE_CURSOR(vCursor);

    You be fine, Thanks.


  4. #4
    Join Date
    Mar 2001
    Posts
    6
    Thanks for your help.
    I used SQLCODE and SQLERMM and I found few problem.

    SQL> exec create_tables;
    ORA-01031: insufficient privileges

    I grant CREATE TABLE.
    It works now.

    DJ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width