Hi,
I am having problems with an XSQL page that uses a ref_cursor as it is not displaying any results. This is my PL/SQL packaged function with the cursor:
Code:
CREATE OR REPLACE PACKAGE ref_cursor_loan_price IS
TYPE loan_cursor_type IS REF CURSOR;
FUNCTION return_cursor(price NUMBER,op VARCHAR2) RETURN loan_cursor_type;
END;
/
CREATE OR REPLACE PACKAGE BODY ref_cursor_loan_price IS
FUNCTION return_cursor(price NUMBER,op VARCHAR2) RETURN loan_cursor_type IS
loan_price_cursor loan_cursor_type;
BEGIN
IF op='greater' THEN
OPEN loan_price_cursor FOR
SELECT DISTINCT b.ISBN, b.title, b.description, b.author, b.publisher, b.pages
FROM loan l, book b, copy c, loan_type t
WHERE t.type_id = l.type_id
AND l.copy_id = c.copy_id
AND c.ISBN = b.ISBN
AND t.price > price;
ELSE
OPEN loan_price_cursor FOR
SELECT DISTINCT b.ISBN, b.title, b.description, b.author, b.publisher, b.pages
FROM loan l, book b, copy c, loan_type t
WHERE t.type_id = l.type_id
AND l.copy_id = c.copy_id
AND c.ISBN = b.ISBN
AND t.price < price;
END IF;
RETURN loan_price_cursor;
END;
END;
/
The XQSL page that returns the ref_cursor (b.xsql):
Code:
<?xml version="1.0"?>
<page connection="myconnection" xmlns:xsql="urn:oracle-xsql">
<ref-cursor>
<xsql:ref-cursor-function>
ref_cursor_loan_price.return_cursor(3.00,'greater')
</xsql:ref-cursor-function>
</ref-cursor>
</page>
which returns this:
<?xml version="1.0" ?>
- <page>
- <ref-cursor>
<ROWSET />
</ref-cursor>
</page>
I have run the sql query in SQL* Plus and it returns the data that I require.
Please can someone shine some light on the problem? Thank you for your time.
XSQL ref_cursor
