Oracle SQL RETURN INTO Fails within CFQUERY (ORA-06550/PLS-00103)

Question

I'm trying to update a record at the same time I select it. In Oracle SQL Developer, the below query works. However, when I execute it from within a CFQUERY tag in ColdFusion, I get an error (see below). I found this stack overflow (ORA-06550 and PLS-00103) but wasn't any help.

Ideally, I'd also like to return the whole record, not just the ID of the affected record. So, I have two questions.

1. Why is the below record failing from within a ColdFusion CFC's CFQUERY?

2. How to I rewrite the query to return the affected record rather than just that record's id?

   DECLARE
     record_id scpricequeue.scpricequeueid%TYPE;
   
   BEGIN
   update scpricequeue
   set islocked = 1, datelocked = sysdate
   where scpricequeueid = (
     select scpricequeueid
     from (
       select scpricequeueid
       from scpricequeue
       where islocked = 0 and completed = 0
       order by dateadded asc
     )
     where rownum < = 1
   )
   RETURNING scpricequeueid INTO record_id;
   DBMS_OUTPUT.put_line('Locked Record: ' || record_id);
   
   END;
   

ERROR RECEIVED when executed as CFQUERY:

ORA-06550: line 1, column 8: PLS-00103: Encountered the symbol "" when
expecting one of the following: begin function package pragma procedure
subtype type use <an identifier> <a double-quoted delimited-identifier>
form current cursor The symbol "" was ignored.*

Answer 1

Do two queries, this should answer your question: https://stackoverflow.com/a/1883117/3112803 Hope that helps.

Since you are only doing one update and one select then the cftransaction tags are not needed. But if you were doing multiple add/update/deletes then you would want the cftransaction tag so if a error happens they would all roll back.

Answer 2

There are some db engines with which you can use a semi-colon. These include ms-sql and mySql. These do not include oracle.

This code runs without error:

<cfquery name="x" datasource="burns">
select 1 record 
from dual
</cfquery>

But, if you add a semi-colon,

<cfquery name="x" datasource="burns">
select 1 record 
from dual;
</cfquery>

You get

Error Executing Database Query.  
[Macromedia][Oracle JDBC Driver][Oracle]ORA-00911: invalid character  

The error occurred in D:\DW\dwtest\Dan\abc.cfm: line 106

104 :  --->
105 : 
106 : <cfquery name="x" datasource="burns">
107 : select 1 record 
108 : from dual;

Basically you need another approach. You move your code to a stored procedure and call that from ColdFusion, or you can follow @gfrobenius's advice and use more than one cfquery. Or you can do something else.

Answer 3

EDIT:
you can see here that, after the semicolon in declare block, CF pushed the query to the db layer, and oracle in turn replied back stating, I see a space instead of a begin keyword! Sure we have to push the block completely, and this should work.

<cfset variables.plsql = "
DECLARE
  record_id scpricequeue.scpricequeueid%TYPE;

BEGIN
update scpricequeue
set islocked = 1, datelocked = sysdate
where scpricequeueid = (
  select scpricequeueid
  from (
    select scpricequeueid
    from scpricequeue
    where islocked = 0 and completed = 0
    order by dateadded asc
  )
  where rownum < = 1
)
RETURNING scpricequeueid INTO record_id;
DBMS_OUTPUT.put_line('Locked Record: ' || record_id);

END;
"
>
<cfquery name="q" datasource="yourDSN">
    #variables.plsql#
</cfquery>

Set the query string as a variable, while executing anonymous pl/sql blocks!

Courtesy: http://awads.net/wp/2005/07/25/oracle-plsql-in-cfquery/