do we have if/then/else in sqlplus?

[thg]

do we have if/then/else or other conditional statements in sqlplus(NOT PL/SQL!)?

[Victoria]

Use the decode function.

[thg]

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

[pando]

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

[thg]

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

[thg]

in the above patch.sql, if i removed the line that starts with ALTER, then the patch run successfully.

[rbouissou]

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!!).

[kmesser]

http://www.dbasupport.com/forums/sho...?threadid=9482

[miritika]

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;

[thg]

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;