CODEWITHSUNDEEP
plsql
RLearner
RLearner

Reputation: 65

Update in PL/SQL Block giving PLS-00103 error

I am trying to create a PL/SQL block to update a empty column, is_true, in a new table created_table on the basis of another table, table1. The id fields on the tables match and there are no rows in created_table that doesn't have a corresponding row in table1. I'm basing the update on the status column of table1, but instead of 'Yes' and 'No', I need it reflected as 'Y' and 'N': 

created_table:                         expected results:
id | is_true | other columns           id | is_true | other columns
---|---------|--------------           ---|---------|--------------
 1 | null    | ...                      1 | 'Y'     | ...          
 2 | null    | ...                      2 | 'N'     | ...          

table1:
id | status | other columns
---|--------|--------------
 1 | 'Yes'  | ...          
 2 | 'No'   | ...

Since created_table is very large, I'm trying to update it using a PL/SQL procedure, so that in case of failure midway, I'll still have updated rows. The next run of the procedure can then pick up where it previously failed without processing already processed rows.

I've tried testing with this code block:

DECLARE
  is_true varchar2 (5) created_table.is_true%type; 
BEGIN
  FOR status IN (SELECT a.status 
                 from table1 a 
                 left join created_table b
                 where and a.id=b.id ) 
  LOOP
        IF  status = 'Yes' THEN
            UPDATE created_table SET is_true= 'Y'
        ELSE
            UPDATE created_table SET is_true= 'N'
                WHERE ROWNUM := status.ROWNUM
        END IF;
        DBMS_OUTPUT.PUT_LINE('Done');
    END LOOP;
END;

But it's giving me errors:

PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   * & = - + ; < / > at in is mod remainder not rem

What can I do to make it work?

Upvotes: 2

Views: 218

Answers (1)

Scratte
Scratte

Reputation: 3166

Your code had multiple errors:

DECLARE
--  is_true varchar2 (5) created_table.is_true%type; -- PLS-00103: Encountered the symbol "CREATED_TABLE" when expecting one of the following:
  is_true created_table.is_true%type;
BEGIN
  FOR status IN (SELECT a.status 
                 from table1 a 
--                 left join created_table b  -- ORA-00905: missing keyword
--                 where and a.id=b.id ) 
                 left join created_table b on a.id = b.id)
  LOOP
--        IF  status = 'Yes' THEN -- PLS-00306: wrong number or types of arguments in call to '='
        IF  status.status = 'Yes' THEN
--            UPDATE created_table SET is_true= 'Y' -- ORA-00933: SQL command not properly ended
            UPDATE created_table SET is_true= 'Y';
        ELSE
            UPDATE created_table SET is_true= 'N'
--                WHERE ROWNUM := status.ROWNUM  -- ORA-00920: invalid relational operator and ORA-00933: SQL command not properly ended
                WHERE ROWNUM = status.ROWNUM;
        END IF;
        DBMS_OUTPUT.PUT_LINE('Done');
    END LOOP;
END;  -- PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
/

The PLS-00103 is really just telling you that a / is missing. After that the remaining error is PLS-00302: component 'ROWNUM' must be declared. From "Oracle Database Online Documentation": rownum is a pseudocolumn.

Your SQL would update every row in the table setting is_true to 'Y' on the first encounter of status being 'Yes', since you didn't give it a WHERE clause. I'm assuming that is not your intent.

There was also no COMMIT in your PL/SQL block, so in effect it would have been the same as running a normal SQL.

Based on the situation you've described, I made some changes to the code block. This will provide with a limit for how many rows can be processed prior to a COMMIT. I set the limit to 5. You should change that to something appropriate. It will not pick up any rows that have empty values for is_true, so in effect it will only work on non-processed rows:

DECLARE
  commit_counter PLS_INTEGER := 1; -- count commits
  commit_limit   PLS_INTEGER := 5; -- rows for commit limit
  counter        PLS_INTEGER := commit_limit;
BEGIN
  FOR rec IN (SELECT a.status, a.id 
                FROM created_table b
                JOIN table1 a ON a.id = b.id
               WHERE b.is_true IS NULL) -- do not pick up processed rows
  LOOP
        IF  rec.status = 'Yes' THEN
            UPDATE created_table SET is_true = 'Y'
             WHERE id = rec.id;
        ELSE
            UPDATE created_table SET is_true = 'N'
             WHERE id = rec.id;
        END IF;

        counter := counter - 1;

        IF counter < 1 THEN
          counter := commit_limit; --reset counter
          commit_counter := commit_counter + 1;
          COMMIT;
        END IF;
  END LOOP;

  COMMIT; -- all rows are processed;

  DBMS_OUTPUT.PUT_LINE(commit_counter || ' COMMITS');
END;
/

This will update all the rows in one go, still only updating the "empty" rows:

UPDATE created_table b
   SET is_true = (SELECT CASE a.status WHEN 'Yes' THEN 'Y'
                                       ELSE 'N'
                         END 
                    FROM table1 a
                   WHERE a.id = b.id)
 WHERE b.is_true IS NULL;

Upvotes: 1

Related Questions