Error(30,11): PLS-00103: Encountered the symbol UPDATE error

Question

While creating below SP I am getting the error as

Error(30,11): PLS-00103: Encountered the symbol "UPDATE" when expecting one of the following: ( - + case mod new not null select with continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe

Here is my SP

Procedure Update_Link_Details (
 P_UDLINKID NVARCHAR2,
 P_FOLDERSTRUCTURE NVARCHAR2,
 TBL_UPD OUT SYS_REFCURSOR        
)
 AS
 BEGIN
    OPEN TBL_UPD FOR 
         Update Tb_Link_Print 
          set status='Start' 
          where LINKPRINTID= P_UDLINKID 
          and upper(FOLDERSTRUCTURE) LIKE '%' || upper(P_FOLDERSTRUCTURE) || %';    
  NULL;
END Update_Link_Details;

are you trying to query data or change it? your cursor should be a SELECT, not an update. or, it shouldn't be a cursor to begin with. — thatjeffsmith
@thatjeffsmith: I just want to update the table with that status, i dont want select or something like that — hud
You have a single quote missing around the last % and you should look at the RETURNING INTO clause to see if that will cover what you want to return. — BriteSponge

APC APC · Accepted Answer · 2017-04-12T14:30:23

"I just want to update the table with that status ...Also I want to return the updated record"

That's two things, so it's two statements:

Procedure Update_Link_Details (
 P_UDLINKID NVARCHAR2,
 P_FOLDERSTRUCTURE NVARCHAR2,
 TBL_UPD OUT SYS_REFCURSOR        
)
 AS
 BEGIN
     Update Tb_Link_Print 
      set status='Start' 
      where LINKPRINTID= P_UDLINKID 
      and upper(FOLDERSTRUCTURE) LIKE '%' || upper(P_FOLDERSTRUCTURE) || '%';    
  OPEN TBL_UPD FOR 
   select * from  Tb_Link_Print 
   where LINKPRINTID= P_UDLINKID 
      and upper(FOLDERSTRUCTURE) LIKE '%' || upper(P_FOLDERSTRUCTURE) || '%';    
END Update_Link_Details;

This is a trifle clunky. Here is a version which engineers away the duplication:

Procedure Update_Link_Details (
 P_UDLINKID NVARCHAR2,
 P_FOLDERSTRUCTURE NVARCHAR2,
 TBL_UPD OUT SYS_REFCURSOR        
)
 AS
      upd_rowids sys.dbms_debug.vc2coll;
 BEGIN
     Update Tb_Link_Print 
      set status='Start' 
      where LINKPRINTID= P_UDLINKID 
      and upper(FOLDERSTRUCTURE) LIKE '%' || upper(P_FOLDERSTRUCTURE) || '%'
      returning rowidtochar(rowid) bulk collect into upd_rowids;    

  OPEN TBL_UPD FOR 
   select p.* 
   from  Tb_Link_Print p
   join table(upd_rowids) u
      on p.rowid = chartorowid(u.column_value) 
   ;    
END Update_Link_Details;

It uses the RETURNING clause to capture the ROWIDs of the affected rows, then opens the Ref Cursor using those ROWIDs to restrict the result set.

Error(30,11): PLS-00103: Encountered the symbol UPDATE error

1 Answers