do we have if/then/else in sqlplus?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: do we have if/then/else in sqlplus?

  1. #1
    Join Date
    Jan 2001
    Posts
    59

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

  2. #2
    Join Date
    May 2000
    Posts
    58
    Use the decode function.

  3. #3
    Join Date
    Jan 2001
    Posts
    59

    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




  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  5. #5
    Join Date
    Jan 2001
    Posts
    59

  6. #6
    Join Date
    Jan 2001
    Posts
    59

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

  7. #7
    Join Date
    Jun 2000
    Location
    French Polynesia
    Posts
    16

    Alter in PL/SQL

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

  8. #8
    Join Date
    Aug 2000
    Posts
    462

  9. #9
    Join Date
    Apr 2001
    Posts
    51
    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;

  10. #10
    Join Date
    Jan 2001
    Posts
    59

    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;




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width