Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Location
    Sydney
    Posts
    1

    Unhappy Unanswered: PLS-00103: Encountered the symbol "SELECT" when expecting one of

    This piece of code is not working, can some pls help me, I'm very new to this, the environment is Oracle 9i


    CREATE PROCEDURE SP_BLARCDAILY AS
    SortedFlag INT;
    SELECT SortedFlag = (SELECT RUNNING FROM INSTANCE WHERE INSTNAME = 'Sorted');

    --/******************************** Updating records with WMODE = 2 ****************************/
    IF SortedFlag = 0
    BEGIN
    PROMPT "Updating TRANSGRPTRANS.WMODE to 2...";
    UPDATE TRANSGRPTRANS
    SET WMODE = 2
    FROM TRANSGRP TRANSGRP, TRANSGRPTRANS TRANSGRPTRANS
    WHERE TRANSGRP.TRANSGRPID = TRANSGRPTRANS.TRANSGRPID
    AND TRANSGRP.WMODE = 2;

    PROMPT "Updating TRANS.WMODE to 2...";
    UPDATE TRANS
    SET WMODE = 2
    FROM TRANSGRPTRANS TRANSGRPTRANS, TRANS TRANS
    WHERE TRANSGRPTRANS.TRANSID = TRANS.TRANSID
    AND TRANSGRPTRANS.WMODE = 2;

    PROMPT "Updating FILETBL.WMODE to 2...";
    UPDATE FILETBL
    SET WMODE = 2
    WHERE FILEID NOT IN
    (SELECT FILEID
    FROM TRANSGRP
    WHERE CURRENTSTATE IN (0,1,2,3,4,5,22,11,14,16,17,19,25) AND WMODE = -1);

    --/******************************* Inserting records with WMODE = 2 *****************************/
    PROMPT "Moving TRANSGRPTRANS to WTRANSGRPTRANS where WMODE = 2...";
    INSERT INTO WTRANSGRPTRANS
    SELECT * FROM TRANSGRPTRANS
    WHERE WMODE = 2;

    PROMPT "Moving TRANSGRP to WTRANSGRP where WMODE = 2...";
    INSERT INTO WTRANSGRP
    SELECT * FROM TRANSGRP
    WHERE WMODE = 2;

    PROMPT "Moving TRANS to WTRANS where WMODE = 2...";
    INSERT INTO WTRANS
    SELECT * FROM TRANS
    WHERE WMODE = 2;

    PROMPT "Moving FILETBL to WFILETBL where WMODE = 2...";
    INSERT INTO WFILETBL
    SELECT * FROM FILETBL
    WHERE WMODE = 2;

    --/******************************** Removing records with WMODE = 2 ******************************/
    PROMPT "Deleting TRANSGRPTRANS where WMODE = 2...";
    DELETE
    FROM TRANSGRPTRANS
    WHERE WMODE = 2
    AND TRANSGRPID IN (SELECT TRANSGRPID FROM WTRANSGRPTRANS)
    AND TRANSID IN (SELECT TRANSID FROM WTRANSGRPTRANS);

    PROMPT "Deleting TRANS where WMODE = 2...";
    DELETE
    FROM TRANS
    WHERE WMODE = 2
    AND TRANSID IN (SELECT TRANSID FROM WTRANS);

    PROMPT "Deleting TRANSGRP where WMODE = 2..."
    DELETE
    FROM TRANSGRP
    WHERE WMODE = 2
    AND TRANSGRPID IN (SELECT TRANSGRPID FROM WTRANSGRP);

    PROMPT "Deleting FILETBL where WMODE = 2...";
    DELETE
    FROM FILETBL
    WHERE WMODE = 2
    AND FILEID IN (SELECT FILEID FROM WFILETBL);
    END;
    commit;


    THANX IN ADVANCE

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, there are several things that prevent your procedure to work properly.
    First, syntax should be this:
    Code:
    CREATE OR REPLACE PROCEDURE prc_name (parameters) AS
      variable declarations;
    BEGIN
      your code here;
    END prc_name;
    Your procedure doesn't have BEGIN-END block at all. There is a BEGIN inside of the IF statement, but, then again, IF lacks in THEN keyword as well as END IF.

    Next, you can't use PROMPT within a PL/SQL procedure. To view output from it, use DBMS_OUTPUT.PUT_LINE.

    Check UPDATE statement syntax; shortly, it is
    Code:
    UPDATE your_table SET
      your_column = your_value
      WHERE condition;
    and not the way you put it.

    I'm afraid you'll have to rewrite it completely ... and, do read a manual. It will help you not to make such a trivial mistakes as the ones you made.

Posting Permissions

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