john1953
asked on
ORA-00904 Error (need help)
I am receiving a PL/SQL: ORA-00904: "DOCREF": invalid identifier am not sure why. Can someone provide guidance?
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----
This is my source code -
CREATE OR REPLACE PROCEDURE Sp_Sel_Xmldoc(iNODEID IN NUMBER, iDOCTYPE IN VARCHAR2, iDOCREF IN NUMBER,
oxmldata OUT sys.XMLTYPE)
AS
V_NODEID NUMBER(10);
V_DOCTYPE VARCHAR2(20);
V_DOCREF NUMBER(10);
V_XMLDATA sys.XMLTYPE;
BEGIN
V_NODEID := iNODEID;
V_DOCTYPE := iDOCTYPE;
V_DOCREF := iDOCREF;
SELECT XMLDOC INTO (V_XMLDATA)
FROM NODALXML
WHERE nodeid = V_NODEID
AND doctype = V_DOCTYPE
AND docref = V_DOCREF;
END;
/
The oracle table looks like this -
CREATE TABLE PRISM.NODALXML
(
NODALXML_PK NUMBER(10),
NODEID NUMBER(10),
DOCTYPE VARCHAR2(20 BYTE),
DOCREF NUMBER(10),
XMLDOC SYS.XMLTYPE
)
Thanks.
--------------------------
This is my source code -
CREATE OR REPLACE PROCEDURE Sp_Sel_Xmldoc(iNODEID IN NUMBER, iDOCTYPE IN VARCHAR2, iDOCREF IN NUMBER,
oxmldata OUT sys.XMLTYPE)
AS
V_NODEID NUMBER(10);
V_DOCTYPE VARCHAR2(20);
V_DOCREF NUMBER(10);
V_XMLDATA sys.XMLTYPE;
BEGIN
V_NODEID := iNODEID;
V_DOCTYPE := iDOCTYPE;
V_DOCREF := iDOCREF;
SELECT XMLDOC INTO (V_XMLDATA)
FROM NODALXML
WHERE nodeid = V_NODEID
AND doctype = V_DOCTYPE
AND docref = V_DOCREF;
END;
/
The oracle table looks like this -
CREATE TABLE PRISM.NODALXML
(
NODALXML_PK NUMBER(10),
NODEID NUMBER(10),
DOCTYPE VARCHAR2(20 BYTE),
DOCREF NUMBER(10),
XMLDOC SYS.XMLTYPE
)
Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you should use the oxmldata OUT sys.XML%TYPE
the '%' is needed for a referencial datatype
the '%' is needed for a referencial datatype
is the "CREATE TABLE PRISM.NODALXL" statement correct?
DOCTYPE VARCHAR2(20 BYTE) ????
I would guess this is supposed to be "DOCTYPE VARCHAR2(20)"...