-
To get the error try in SQL*Plus after running your script:
SHOW ERRORS
or
SELECT * FROM user_errors
WHERE name = ' EXECUTEDDL' ;
Probably a / after the procedure will help:
and remove exec before executeddl in your PL/SQL block.
...
CREATE OR REPLACE PROCEDURE executeDDL(STRING IN varchar2) AS
...
END;
/
DECLARE
...
If you use Oracle 8, you can use the procedure DBMS_UTILITY.EXEC_DDL_STATEMENT
...
:cur_version := :target_version;
DBMS_UTILITY.EXEC_DDL_STATEMENT('ALTER TABLE TEST ADD (FFF VARCHAR2 (50))');
INSERT INTO TEST (AAA,BBB,CCC,DDD) VALUES('UPDATED', '','','','');
...
In 8i you can use EXECUTE IMMEDIATE:
...
:cur_version := :target_version;
EXECUET IMMEDIATE 'ALTER TABLE TEST ADD (FFF VARCHAR2 (50))';
INSERT INTO TEST (AAA,BBB,CCC,DDD) VALUES('UPDATED', '','','','');
...
...
-
ignore the last message. I fixed that problem, but a new problem arises too. I always get deadlocks whenever I execute my patch.sql to update the database.
here is the result of the two runs
SQL> start patch
Task is complete, old version = 6
111111111111111111111111111
22222222222222222222222222222
Closing cursor, err code = -4020, err = ORA-04020: deadlock detected while trying to lock object PPDBA.TEST
cursor closed
Task is complete, new version = 7.1
PL/SQL procedure successfully completed.
7.1
7.1
SQL> start patch
Task is complete, old version = 6
111111111111111111111111111
22222222222222222222222222222
33333333333333333333333333333
44444444444444444444444444444
55555555555555555555555555555
Task is complete, new version = 7.1
PL/SQL procedure successfully completed.
7.1
7.1
SQL>
SQL>
You can see that in the first run, deadlock occurs when it try to execute the line:
DBMS_SQL.PARSE(cursor_name, ppstatement, DBMS_SQL.native);
but the second run went successfully. I don't know why there is always a deadlock for the first run. I dont' have any other sqlplus.exe open that locks the TEST table.
here is my script for creating the procedure:
CREATE OR REPLACE PROCEDURE executeDDL(ppstatement IN varchar2) AS
cursor_name INTEGER;
ret INTEGER;
BEGIN
DBMS_OUTPUT.PUT_LINE('1111111111111111111111111111');
cursor_name := DBMS_SQL.OPEN_CURSOR;
DBMS_OUTPUT.PUT_LINE('22222222222222222222222222222');
DBMS_SQL.PARSE(cursor_name, ppstatement, DBMS_SQL.native);
DBMS_OUTPUT.PUT_LINE('33333333333333333333333333333');
ret := DBMS_SQL.EXECUTE(cursor_name);
DBMS_OUTPUT.PUT_LINE('44444444444444444444444444444');
DBMS_SQL.CLOSE_CURSOR(cursor_name);
DBMS_OUTPUT.PUT_LINE('55555555555555555555555555555');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Closing cursor, err code = ' || sqlcode || ', err = ' || sqlerrm);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
DBMS_OUTPUT.PUT_LINE('cursor closed');
END;
/
--------- here is the patch.sql ---------
SET SERVEROUTPUT ON
VARIABLE cur_version VARCHAR2 (10)
VARIABLE target_version VARCHAR2 (10)
DECLARE
version VARCHAR2 (10);
BEGIN
SELECT VALUE INTO version FROM SYSFIELD WHERE SYSKEY='DB_VERS_LOAN';
:cur_version := version;
:target_version := '7.1';
IF :cur_version < :target_version THEN
DBMS_OUTPUT.PUT_LINE('Task is complete, old version = ' || :cur_version);
:cur_version := :target_version;
executeDDL('ALTER TABLE TEST ADD (QQQ VARCHAR2 (50))');
INSERT INTO TEST (AAA,BBB,CCC,DDD, EEE) VALUES('UPDATED', '','','','');
COMMIT;
DBMS_OUTPUT.PUT_LINE('Task is complete, new version = ' || :cur_version);
ELSE
INSERT INTO TEST (AAA,BBB,CCC,DDD,EEE) VALUES('NOUPDATE', '','','','');
DBMS_OUTPUT.PUT_LINE('Task is complete, current db version = ' || :cur_version || ' is already up to date');
END IF;
END;
.
/
-
akkerend,
I changed the line to
DBMS_UTILITY.EXEC_DDL_STATEMENT('ALTER TABLE TEST ADD (QQQ VARCHAR2 (50))');
but i still get the deadlock error for the first run, second run is fine.
SQL> start patch
Task is complete, old version = 6
DECLARE
*
ERROR at line 1:
ORA-04020: deadlock detected while trying to lock object PPDBA.TEST
ORA-06512: at "SYS.DBMS_SYS_SQL", line 487
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "SYS.DBMS_UTILITY", line 332
ORA-06512: at line 10
SQL> start patch
Task is complete, old version = 6
Task is complete, new version = 7.1
PL/SQL procedure successfully completed.
7.1
7.1
SQL>
-
I fixed the problem again. please disregard the above message. Thanks!
-
If you are using 8i then you can use CASE statements.
-
please remove exec from ur procedure thats it and it will work exactly fine
exec executeDDL('ALTER TABLE TEST ADD (FFF VARCHAR2 (50))');
and instead write
executeDDL('ALTER TABLE TEST ADD (FFF VARCHAR2 (50))');
Thats it and it will work absolutely fine.
Please do let me know if it works fine.
-
SET ECHO ON
SET SERVEROUTPUT ON
SET FEEDBACK ON
SET AUTOP OFF
CREATE OR REPLACE PROCEDURE executeDDL(ppstatement IN varchar2) AS
cursor_name INTEGER;
ret INTEGER;
BEGIN
cursor_name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_name, ppstatement, DBMS_SQL.native);
ret := DBMS_SQL.EXECUTE(cursor_name);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Closing cursor, err code = ' || sqlcode || ', err = ' || sqlerrm);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
DBMS_OUTPUT.PUT_LINE('cursor closed');
END;
/
hmm..... everything works fine now, but I found a new problem: When I passed the ALTER TABLE statement to the executeDDL procedure, it works great. but it fails when I trying to execute the CREATE TABLE STATEMENT using the procedure.
here is the output:
SQL> exec executeDDL('create table test3 (aaa varchar2 (50))');
Closing cursor, err code = -1031, err = ORA-01031: insufficient privileges
cursor closed
PL/SQL procedure successfully completed.
SQL>
I don't understand why it says "insufficient privilege" since I am login as a dba and the procedure was ran by me. If I copy and paste the create statement to the command line, then it works fine,
SQL>
SQL> create table test3 (aaa varchar2 (50));
Table created.
SQL>
This is weird. does anyone know what's happening?
-
if u connect as scott then it gives u an error insufficient priveleges.
So u just need to login as sys and issue this command
grant create table to scott;
But if u r logged in as sys user then it gives no problem. It has been tested.
-
miritika,
Thanks, that works!
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
|