do we have if/then/else or other conditional statements in sqlplus(NOT PL/SQL!)?
Printable View
do we have if/then/else or other conditional statements in sqlplus(NOT PL/SQL!)?
Use the decode function.
can you give me the syntax? where can I find the decode function?
The reason I asked this is because I want to execute some alter table statement in my script base on the result of a database query.
let's say the binding variable version is the result from the db query, then I would do things like this
if (version < 4) then
--execute some db update querys
else
-- do nothing
end
I dont think that kind of logic is not possible with SQL, SQLPLUS is only the client for executing SQL, PL/SQL so it's kind of irrelevant here
DECODE is used to decode data in rows, for example if you have an employee with no commission in the EMP (famous emp table huh) instead of getting blank nulls you can make these nulls a zero
select decode(comm, '', 0, comm) from emp
in PL/SQL, you can only do select, insert, update statement, but not alter, create statements. I tried that and it gave me this err message.
--------------
SQL> start patch
ALTER TABLE TEST ADD(EEE VARCHAR2 (50));
*
ERROR at line 11:
ORA-06550: line 11, column 6:
PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:
begin declare else elsif end exit for goto if loop mod null
pragma raise return select update while
<<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall
SQL>
--------------
and here is my patch.sql
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;
ALTER TABLE TEST ADD(EEE VARCHAR2 (50));
INSERT INTO TEST VALUES('NOUPDATE', '','','');
DBMS_OUTPUT.PUT_LINE('Task is complete, new version = ' || :cur_version);
ELSE
INSERT INTO TEST VALUES('NOUPDATE', '','','');
DBMS_OUTPUT.PUT_LINE('Task is complete, current db version = ' || :cur_version || ' is already up to date');
END IF;
END;
.
/
print cur_version
in the above patch.sql, if i removed the line that starts with ALTER, then the patch run successfully.
In order to use DDL in a PL/SQL block as the ALTER command, it is necessary to use dynamic SQL throught the specific package (i don't remember exactly the name!!).
in order to write ddl statements in a pl/sql block we make use of dbms_sql package , the example is given below, hope this solves ur problem.
here in the example below i have taken a parameter string where in u can pass the ddl statement upon the execution of the pl/ sql block.
like, exec proc_name('alter table tab_name add col1 datatype');
CREATE OR REPLACE PROCEDURE proc_name(STRING IN varchar2) AS
cursor_name INTEGER;
ret INTEGER;
BEGIN
cursor_name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_name, string, DBMS_SQL.native);
ret := DBMS_SQL.EXECUTE(cursor_name);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
thanks miritika!
I am new to store procedure. I put the block of code you have in front of my patch.sql script, and I got this warning message. Could you tell me where I should place the code for create the procedures?
SQL> start patch
Warning: Procedure created with compilation errors.
SQL>
Here is the new patch.sql i have:
VARIABLE cur_version VARCHAR2 (10)
VARIABLE target_version VARCHAR2 (10)
CREATE OR REPLACE PROCEDURE executeDDL(STRING IN varchar2) AS
cursor_name INTEGER;
ret INTEGER;
BEGIN
cursor_name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_name, string, DBMS_SQL.native);
ret := DBMS_SQL.EXECUTE(cursor_name);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
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;
exec executeDDL('ALTER TABLE TEST ADD (FFF VARCHAR2 (50))');
INSERT INTO TEST (AAA,BBB,CCC,DDD) VALUES('UPDATED', '','','','');
DBMS_OUTPUT.PUT_LINE('Task is complete, new version = ' || :cur_version);
ELSE
INSERT INTO TEST (AAA,BBB,CCC,DDD) VALUES('NOUPDATE', '','','','');
DBMS_OUTPUT.PUT_LINE('Task is complete, current db version = ' || :cur_version || ' is already up to date');
END IF;
END;
.
/
print cur_version
and here is the script that I used to create my test table.
DROP TABLE TEST;
CREATE TABLE TEST (
AAA varchar2 (50) NULL,
BBB varchar2 (50) NULL,
CCC varchar2 (50) NULL
)
TABLESPACE dynamic_data;
GRANT select, insert, update, delete on TEST to ppuser;
DROP PUBLIC SYNONYM TEST ;
CREATE PUBLIC SYNONYM TEST FOR TEST;
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!