DBMS_SQL PACKAGE

[djpham]

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;

[SCORBET]

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.

[Oracledba8]

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.

[djpham]

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