getProcedureColumns doesn't distinquish between OUT param and Record column
843854Jul 3 2002 — edited Sep 18 2002HI. My problem is that when I do a getProcedureColumns call for
my stored procedure, I cannot distinquish between the stored
procedure OUT parameters and columns in the result set record. Both
are shown to be OUT parameters.
My stored procedure is defined as follows:
PROCEDURE MyProcedure
(state IN VARCHAR2, MyCur OUT MYPACKAGE.MyCurType, Zip OUT VARCHAR2) AS
BEGIN
OPEN MyCur FOR SELECT ACT_NAME, ACT_STATUS from sysadm.act;
END;
and MyCurType is a REF cursor pointing to a result set
record which is defined as follows:
TYPE MyRec is RECORD
(
NAME VARCHAR2(100),
STATUS VARCHAR2(100)
);
AND the REF CURSOR is defined as follows:
TYPE MyCurType is REF CURSOR RETURN MyRec;
When I do getProcedureColumns on the procedure, I get the following:
COL_NAME COL_TYPE TYPE_NAME
--------- ---------- ------------
STATE 1.0 VARCHAR2
MYCUR 4.0 REF CURSOR
<blank> 4.0 PL/SQL RECORD
NAME 4.0 VARCHAR2
STATUS 4.0 VARCHAR2
ZIP 4.0 VARCHAR2
As you can see, STATE, MYCUR, NAME, STATUS and ZIP are all shown as OUT columns. MYCUR is the only true OUT parameter, but I can't tell this. The reason I need to know this because I need to dynamically build the PrepareCall statment. Any help would be very greatly appreciated.
Thanks