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

    Exclamation Unanswered: error message while not executing the actual error

    When I run the following script, everything works fine. Afterwards the stored procedure ish.applsp_ChangeWidthColumnChar does not exists. When I run this script for the second time sqlplusw says ish.applsp_ChangeWidthColumnChar is not declared. This is correct. But in the beginning I put a check, so the second time only CALL ish.applsp_DropProcedure('applsp_ChangeWidthColumn Char'); should be executed. Why I get errormessage on lines that are not executed? Can I catch these errors?
    -----------------------------

    -- BEGIN SAPPSQL '06.00.00.03' PART 1
    BEGIN
    IF NOT ish.global_pkg.sSAPPSQL < '06.00.00.03' THEN
    RAISE NO_DATA_FOUND;
    END IF;


    ish.global_pkg.sStatement :=
    'CREATE OR REPLACE PROCEDURE ish.applsp_ChangeWidthColumnChar
    (
    cTable IN VARCHAR2, -- Name of table to add column to.
    cColumn IN VARCHAR2, -- Name of column to add.
    nWidth IN NUMBER DEFAULT 0, -- Width of column.
    ) AS
    nCurrentWidth NUMBER;
    BEGIN
    IF cTable IS NOT NULL AND cColumn IS NOT NULL THEN
    EXECUTE IMMEDIATE ''ALTER TABLE ISH.'' || ish.applfn_FixName( cTable ) || '' MODIFY '' || ish.applfn_FixName( cColumn ) || '' VARCHAR2('' || TO_CHAR(nWidth) || '')'';
    END IF;
    END;
    ';
    EXECUTE IMMEDIATE ish.global_pkg.sStatement;

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


    -- BEGIN SAPPSQL '06.00.00.03' PART 2
    BEGIN
    IF NOT ish.global_pkg.sSAPPSQL < '06.00.00.03' THEN
    RAISE NO_DATA_FOUND;
    END IF;

    ish.applsp_ChangeWidthColumnChar('SYSTEM', 'SDEFEXPLORERIFACE', 'SDEIDESTTABLE', 30 );

    UPDATE ISH.SAPPLIC SET SAPPSQL = '06.00.00.03';
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('SECTION 06.00.00.03 PART 2 ALREADY DONE');
    END;
    /

    CALL ish.applsp_DropProcedure('applsp_ChangeWidthColumn Char');

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Sorry, but I've scratched my head over this code for 5 minutes and I still don't understand what you are trying to do here. It is probably the first time I have ever seen "nested dynamic PL/SQL"! Why do you want to keep creating and dropping the same procedure anyway? Why do you create the procedure to take 3 parameters but then call it with 4? Why don't you just do something like this?:
    Code:
    BEGIN
      IF NOT ish.global_pkg.sSAPPSQL < '06.00.00.03' THEN
        RAISE NO_DATA_FOUND;
      END IF;
    
      EXECUTE IMMEDIATE 'ALTER TABLE ISH.SDEFEXPLORERIFACE MODIFY SDEIDESTTABLE VARCHAR2(30)';
    
      UPDATE ISH.SAPPLIC SET SAPPSQL = '06.00.00.03';
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('SECTION 06.00.00.03 PART 2 ALREADY DONE');
    END;
    /
    Perhaps if you cut and paste the results from actualy running this it will make more sense.

  3. #3
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Arrow just as an example

    The sqlcode I put in my message was just an example. I delete some lines from the original, to make it not to complicated. It comes done to this:
    --------------
    BEGIN
    IF (FALSE ) THEN
    ish.applsp_addcolumn('tablename', 'columnname');
    END IF;
    END;
    ---------------
    When I run the script above, it won't execute applsp_addcolumn. If applsp_addcolumn does not exist, then I still get an errormessage that applsp_addcolumn is not declared. Can I catch this error?

    Isn't it strange for sqlplusw to complain about lines that won't be executed? Syntactical I can imagine, but functional no.

    Sorry for the complicated example of my previous post.

    beforehand thanx
    Coen Dunnink
    The Netherlands

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    That was the simple version? Good grief

    Now I see what you mean, though. It isn't sqlplusw that is complaining, it is the PL/SQL interpreter/compiler, which you are asking to compile a piece of code that references a non-existent procedure. It isn't a run-time error, it is a compile-time error.

    So why do you keep creating and dropping that procedure anyway? Why not just leave it there permanently?

    The alternative (which I fear will appeal to you!) is to use yet more dynamic code:
    Code:
    BEGIN
      IF (FALSE ) THEN
        EXECUTE IMMEDIATE 'ish.applsp_addcolumn(''tablename'', ''columnname'')';
      END IF;
    END;

  5. #5
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Exclamation clear

    Ok thanx,

    That's clear. I don't know why the procedure was dropped. I didn't made that part of the script. But the script has ran at customers, so when they update again the script should be flawless. It's also possible to always create the stored procedure. So I have to think about the sollution. But thanx!

    greetings
    Coen Dunnink
    The Netherlands

Posting Permissions

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