• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2069
  • Last Modified:

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.
0
john1953
Asked:
john1953
3 Solutions
 
cjjcliffordCommented:
hi,

is the "CREATE TABLE PRISM.NODALXL" statement correct?

DOCTYPE VARCHAR2(20 BYTE) ????

I would guess this is supposed to be "DOCTYPE VARCHAR2(20)"...
0
 
cjjcliffordCommented:
sorry, that CREATE TABLE looks ok...
0
 
Helena Markováprogrammer-analystCommented:
Maybe you can try this:

CREATE OR REPLACE PROCEDURE Sp_Sel_Xmldoc(iNODEID IN NUMBER, iDOCTYPE IN VARCHAR2, iDOCREF IN NUMBER,
         oxmldata OUT sys.XMLTYPE)
AS

 p_1   NODALXML.XMLDOC%TYPE;

BEGIN

SELECT p.XMLDOC INTO p_1
FROM NODALXML p
WHERE p.nodeid =iNODEID AND p.doctype =iDOCTYPE AND docref = iDOCREF;

END;
/
0
 
BobMcCommented:
Seems to work fine for me (9204) - is the procedure in the same schema as the table?
0
 
Aaron ShiloChief Database ArchitectCommented:
you should use the oxmldata OUT sys.XML%TYPE

the '%' is needed for a referencial datatype
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now