Need help - how to update table data using variables

[john9]

I would like some guidance on this problem. Below is a Oracle stored procedure that will update a selected table row based on pass variables. The variable key fields sent to the procedure are used in a select comparison.

I am getting a missing "FROM" syntax error when compiling the procedure.

Thanks in advance.
--------------------------------------------
CREATE OR REPLACE PROCEDURE prism.Sp_Upd_Xmldoc(NODEID IN VARCHAR2, DOCTYPE IN VARCHAR2, DOCREF IN VARCHAR2,
xmldata IN SYS.XMLTYPE)
AS
BEGIN


UPDATE PRISM.NODALXML X

SET (X.XMLDOC) =

(SELECT XMLDATA

WHERE X.NODEID = NODEID AND X.DOCTYPE = DOCTYPE AND X.DOCREF = DOCREF);

END;
/

 

[john9]

Hi folks -

Just a little clarification to the question.

Regarding the previous comments there is no source table where the updated table will get it's information from.
In other words, the key information sent to this procedure comes from a calling JAVA application. I used the supplied keys
variables (NODEID, DOCTYPE, DOCREF) to select a specific row in table NODALXML and update the column XMLDOC using the variable XMLDATA.

Hope this clarification helps.

 

[SantaMufasa]

No, John, actually the "clarification" muddied up the water. What do you mean, "...there is no source table where the updated table will get it's information from"? SQL will certainly complain if you have a "SELECT" without a "FROM" as in your case above.

Perhaps you could restate the objective of your "UPDATE" statement, above. If there is no table, why are you trying to update from it? Specifically, what do you expect the value of "XMLDATA" to be if "X.NODEID = NODEID AND X.DOCTYPE = DOCTYPE AND X.DOCREF = DOCREF"?

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:38 (02Sep04) UTC (aka "GMT" and "Zulu"), 09:38 (02Sep04) Mountain Time)