Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

MERGE INTO, clobs and bind variables

433851Dec 2 2004 — edited Dec 2 2004
I'm useing a MERGE statement and it should insert/update into a CLOB field. This MERGE statement is working without the clob part, but when I add the "returning xxx into" it gives me:
ORA-00905: missing keyword
or
ORA-00933: SQL command not properly ended

The insert and update statements work independently without the MERGE INTO.

What is wrong?
MERGE INTO SessionTbl2 thistbl
USING 
  (SELECT '6c20c1f2b67d6cf9ac348cc1177c9e3d' sessionid
   FROM dual) thistbl2
ON (thistbl2.sessionid = thistbl.sessionid AND
    thistbl.varname=:varname)
WHEN MATCHED THEN
  UPDATE SET thistbl.varvalue=:variablevalue,
           thistbl.only_on_page=:onlyonpage,
           thistbl.expire_tstamp=:expire_tstamp,
           thistbl.inactivity_timeout=:inactivity_timeout,
           thistbl.readonly=:readonly,
           thistbl.varvalue2=EMPTY_CLOB()
  returning thistbl.varvalue2 into :the_clob
WHEN NOT MATCHED THEN
INSERT (sessionid,varname,varvalue,tstamp,
        only_on_page,expire_tstamp,
        inactivity_timeout,readonly,varvalue2)
       VALUES
       (:cacheidval,:varname,:variablevalue,
       :nowts,:onlyonpage,:expire_tstamp,
       :inactivity_timeout,
       :readonly,EMPTY_CLOB())
     returning varvalue2 into :the_clob
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 30 2004
Added on Dec 2 2004
2 comments
1,845 views