?
Solved

Invalid SQL statement with bind variables, XMLType, REF Cursors, & Pipelined function

Posted on 2007-10-11
2
Medium Priority
?
1,676 Views
Last Modified: 2013-12-19
I have a stored procedure I'm trying to get the bugs out of. I keep getting this invalid SQL statement error in this one stored proc. For the life of me,  I can't figure out why. Here's the proc and related procs. First, the questions I have:

Q1. Why is the SQL statement hinky?
Q2. Can you actually use pipelined functions of rec with XMLType columns with REF CURSORS?
Q3. Do you think this should work?

----------------------
TYPES
----------------------
TYPE xml_rec IS RECORD(elem XMLType);
TYPE tbl_xmlElements IS TABLE OF xml_rec;
TYPE xmlCursor_type IS REF CURSOR RETURN xml_rec;

--------------------------------------------------------
STORED PROCEDURE You call from SQLPLUS
--------------------------------------------------------
PROCEDURE insertDatatypesFromFile(xmlFile IN VARCHAR2, schemaNm IN VARCHAR2)
      IS
            xml BFILE;
            xType XMLType;
            cmd VARCHAR2(4000);
      BEGIN
            xml := BFILENAME('XML_DATA', xmlFile);
            xType := XMLType(xml, 0, schemaNm, 1, 0);
            DBMS_OUTPUT.put_line('PROCEDURE: idb_xml.insertDatatypesFromFile');
            DBMS_OUTPUT.put_line('parent xml:');
            DBMS_OUTPUT.put(xType.GETSTRINGVAL());
            insertXMLElementsIntoXMLView('idb_xml.extractDatatypes', xType, 'OVXML_SUPPORTED_DATATYPES');
      EXCEPTION
            WHEN OTHERS THEN
                  DBMS_OUTPUT.put_line('Error processing idb_xml.insertDatatypesFromFile.');
                  IDB_MANAGEMENT.outputErrorStack;
                  IF DBMS_LOB.ISOPEN(xml) = 1
                  THEN
                        DBMS_LOB.CLOSE(xml);
                  END IF;
      END;

-----------------------------------------
It Calls This Function
-----------------------------------------
--The extractFx name must include the package specifier. EX: 'idb_xml.extractDatatypes'
      PROCEDURE insertXMLElementsIntoXMLView(extractFx IN VARCHAR2, parentXML IN XMLType, viewNm IN VARCHAR2, viewOwner IN VARCHAR2)
      IS
            curElements xmlCursor_type;
            rec xml_rec;
            sel VARCHAR2(4000);
      BEGIN
            sel := 'OPEN :cur FOR SELECT * FROM TABLE(CAST(' || extractFx || '(:xml) AS tbl_xmlElements))';
            DBMS_OUTPUT.put_line('idb_xml.insertXMLElementsIntoXMLView - about to open cursor.');
            DBMS_OUTPUT.put_line('cursor command = ' || sel);
            EXECUTE IMMEDIATE sel USING curElements, extractFx, parentXML;
            
            IF curElements%ISOPEN
            THEN
                  DBMS_OUTPUT.put_line('cursor is open. rowcount = ' || curElements%ROWCOUNT);
            END IF;
            
            LOOP
                  FETCH curElements INTO rec;
                  EXIT WHEN curElements%NOTFOUND;
                  
                  sel := 'INSERT INTO ' || viewOwner || '.' || viewNm || ' VALUES (:elem)';
                  DBMS_OUTPUT.put_line('About to insert XMLType.');
                  DBMS_OUTPUT.put_line('sql = ' || sel);
                  EXECUTE IMMEDIATE sel USING rec.elem;
            END LOOP;
            
            CLOSE curElements;
      EXCEPTION
            WHEN OTHERS THEN
            DBMS_OUTPUT.put_line('error processing idb_xml.insertXMLElementsIntoXMLView');
            IDB_MANAGEMENT.outputErrorStack;
            IF curElements%ISOPEN
            THEN
                  CLOSE curElements;
            END IF;
      END;

********************************************************************
It is this function call which is crapping out. Here's my server output.
********************************************************************
idb_xml.insertXMLElementsIntoXMLView - about to open cursor.
cursor command = OPEN :cur FOR SELECT * FROM TABLE(CAST(idb_xml.extractDatatypes(:xml) AS tbl_xmlElements))
error processing idb_xml.insertXMLElementsIntoXMLView
Oracle Error Stack:    ORA-00900: invalid SQL statement
********************************************************************

----------------------------------------------------------------------------------
The function which is crapping out should eventually call this one.
----------------------------------------------------------------------------------
FUNCTION extractDatatypes(elements IN XMLType) RETURN tbl_xmlElements
      PIPELINED
      AS
            dtXML XMLType;
            i binary_integer := 1;
            elements2 XMLType := elements;
            rec xml_rec;
      BEGIN
            LOOP
                  dtXML := elements2.extract('//idb:Datatypes/idb:XM_SUPPORTED_DATATYPE[' || i || ']');
                  EXIT WHEN dtXML IS NULL;
                  
                  rec.elem := dtXML;
                  PIPE ROW (rec);
                  i := i+1;
            END LOOP;
            RETURN;
      END;

Thank you very much,
Julie
Oracle 10.2.0.1
0
Comment
Question by:jtittler
2 Comments
 

Accepted Solution

by:
jtittler earned 0 total points
ID: 20073269
Having tried rewriting this so it's not a dynamic query, just to test. It turns out that the object type the table function is referencing can't have members with datatypes like XMLType. You can only using basic sql datatypes as listed here: http://www.techonthenet.com/oracle/datatypes.php.

There are still problems, but that fixes this particular error message.

Thanks.
Julie
0
 
LVL 1

Expert Comment

by:Vee_Mod
ID: 20073394
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month8 days, 22 hours left to enroll

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question