Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Unanswered: not only syntax check

    I have to followinf plsq-block in my sql-script:

    BEGIN
    IF NOT ish.global_pkg.sSAPPSQL < '06.12.00.05' THEN
    RAISE NO_DATA_FOUND;
    END IF;

    --CREATE COLUMNB
    --COPY DATA FROM COLUMNA TO COLUMNB
    --REMOVE COLUMNA

    DBMS_OUTPUT.PUT_LINE('BEGIN SECTION 06.12.00.05 AT ' || TO_CHAR(SYSDATE, 'DD-MM-YYYY HH24:MIS') );
    ish.applsp_AddMessageToUCT('UMM','1737', 1, 'Job is geen onderdeel van een reeks', 'Job is no part of recurrent pattern');

    ish.global_pkg.sSAPPSQL := '06.12.00.05';

    DBMS_OUTPUT.PUT_LINE('END SECTION 06.12.00.05 AT ' || TO_CHAR(SYSDATE, 'DD-MM-YYYY HH24:MIS') );
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('SECTION 06.12.00.05 ALREADY DONE');
    END;
    /

    It's about:

    --CREATE COLUMNB
    --COPY DATA FROM COLUMNA TO COLUMNB
    --REMOVE COLUMNA

    When I run the script for the second time in sqlplusw errors are raised. Because columna does not exists anymore. This means there is more checking then syntax only. When I want to run the script multiple times without erros, what can I do? I tried execute immediate.

    beforehand tahnx

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What really lies behind the commented lines? "CREATE columnb", "REMOVE columna" - how do you do that? I'm unfamiliar with this commands ...

    OK; errors you got are obvious - you already said that "columna" doesn't exist and you can't copy data from it into "columnb". I'd say you can't avoid raising the error unless you comment those lines (which is, really, not a solution), or - at the beginning of the script - add "CREATE columna" command. However, you'll have to handle the exception that'll raise when trying to add a columnn with the name that already exists (ORA-01430).

    To avoid that, you might try using a query such as
    Code:
    DECLARE
      it_exists VARCHAR2(1);
    BEGIN
      SELECT 'x' INTO it_exists   
      FROM user_tab_columns 
      WHERE table_name = 'YOUR_TABLE_NAME' 
         AND column_name = 'COLUMNA';
    
      NULL; /* do nothing; "columna" already exists in the table */
    
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      CREATE columna;
    END;
    /

Posting Permissions

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