DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

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

  1. #11
    Join Date
    Jul 2000
    Posts
    296
    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', '','','','');
    ...


    ...



  2. #12
    Join Date
    Jan 2001
    Posts
    59

    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;
    .
    /













  3. #13
    Join Date
    Jan 2001
    Posts
    59

    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>

  4. #14
    Join Date
    Jan 2001
    Posts
    59

    I fixed the problem again. please disregard the above message. Thanks!


  5. #15
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    If you are using 8i then you can use CASE statements.

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

  7. #17
    Join Date
    Jan 2001
    Posts
    59
    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?








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

  9. #19
    Join Date
    Jan 2001
    Posts
    59

    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
  •  


Click Here to Expand Forum to Full Width