CODEWITHSUNDEEP
oracle-databasestored-procedures
HEEN
HEEN

Reputation: 4727

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

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;

Upvotes: 1

Views: 320

Answers (1)

APC
APC

Reputation: 146349

"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.

Upvotes: 1

Related Questions