Recordsets from Oracle Stored procedures

This is link to an article that explains how to Oracle stored procedures to return recordsets.

The main trick is to add:

//<![CDATA[
<!–
document.write('’);
document.write(‘ .scripthide { display: none; }’);
document.write(‘ .scriptinline { display: inline; }’);
document.write(‘ .scriptblock { display: block; }’);
document.write(”);
//–>
//]]>
<style type=”text/css”><!– .noscripthide { display:none; } .noscriptinline { display:inline; } .noscriptblock { display:block; } //–></style>
//<![CDATA[
<!–
if (document.getElementById) {
document.write('’);
document.write(‘ .script12hide { display: none; }’);
document.write(‘ .script12inline { display: inline; }’);
document.write(‘ .script12block { display: block; }’);
document.write(”);
}
//–>
//]]>

//<![CDATA[
<!–
if (document.getElementById) {
document.write('’);
document.write(‘ .lnav { position: absolute; }’);
document.write(‘ .lnavch { margin-left:23.0ex;}’);
document.write(”);
}
//–>
//]]>

//<![CDATA[

//]]>

//<![CDATA[
<!–
// ———————————-
// visibility functions
function tog() {
// tog: toggle the visibility of html elements (arguments[1..]) from none to
// arguments[0]. Return what should be returned in a javascript onevent().
display = arguments[0];
for( var i=1; i
//]]>

//<![CDATA[

//]]>

//<![CDATA[

//]]>

//<![CDATA[

//]]>
Extended Properties=”PLSQLRSet=1″ to your connection string.

The cursor itself must be defined as a ref cursor :

CREATE OR REPLACE
PACKAGE Types AS
  TYPE cursor_type IS REF CURSOR
END;

Oracle 9i introduced the SYS_REFCURSOR type for this purpose.

Here is a valid example:

CREATE OR REPLACE
PROCEDURE USP_GET_UNIT(p_recordset OUT SYS_REFCURSOR) AS
BEGIN
    OPEN p_precordset FOR
       SELECT UNIT, DESCRIPTION FROM UNIT;
END;

On the delphi side don’t add the cursor as a parameter.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s