-
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;
-
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.
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|