to set a variable to 1881 and pass that as a parameter.
Something like the following (I am not sure if I have the syntax exactly
right):
<cfset cstudentid =1881>
<cfProcParam type="In"
value="#cstudentid#" cfSqlType="cf_sql_numeric" variable="studentID">
Also, I am not sure about your cursor reference. Try using the ref cursor
definition by doing the following:
1. Add this line to the package specification:
TYPE student_type_ref_cursor IS REF CURSOR;
2. Change the procedure definition in the package spec and body from:
>PROCEDURE test_dlc_sp_getstudentinfo (
> studentID IN integer,
> studentInfo OUT types.cursorType
>);
to
PROCEDURE test_dlc_sp_getStudentInfo(studentID IN number,
student_cursor IN OUT student_type_ref_cursor);
3. Remove the close cursor statement from the package body.
Janet.
At 10:59 AM 3/9/2004 -0800, you wrote:
>Janet Schmitt wrote:
>
> > Richard -
> >
> > What is the format of the table tblStudentInfo? What are the datatypes
> > specified for sFirst, sLast, sOrient and studentId?
> >
> > What is the CFSTOREDPROC code that was used to call this procedure?
> >
> > Janet.
>
>Janet,
>
>Here is the table definition:
>================
>SIDNUMBERNOT NULL
>SFIRSTVARCHAR2(30)NOT NULL
>SLASTVARCHAR2(50)NOT NULL
>SORIENTCHAR(1)NOT NULL
>================
>
>There are a few other fields in the table, but I'm not referencing them,
>so they don't seem relevant.
>
>Here is the CFSTOREDPROC code:
>================
><cfstoredproc datasource="DLCampus"
>procedure="dlc.test_dlc_sp_getStudentInfo">
><cfprocparam type="in" value="1881" cfsqltype="cf_sql_integer">
><cfprocresult name="getName">
></cfstoredproc>
>================
>
>And, just for completeness's sake, here is the package definition,
>including the SP:
>================
>CREATE OR REPLACE PACKAGE dlc AS
>
>PROCEDURE test_dlc_sp_getstudentinfo (
> studentID IN integer,
> studentInfo OUT types.cursorType
>);
>
>END dlc;
>/
>
>CREATE OR REPLACE PACKAGE BODY dlc AS
>
> PROCEDURE test_dlc_sp_getStudentInfo (
> studentID IN integer,
>studentInfo OUT types.cursorType
>
> )
>
> as
>
> sFirst varchar2(30);
> sLast varchar2(50);
> sOrient char(1);
>
> begin
>
> open studentInfo for
> select
>sFirst,
>sLast,
>sOrient
> from
>tblStudentInfo
> where
>sid = 1881;
>
>CLOSE studentInfo;
>
> END test_dlc_sp_getStudentInfo;
>
>END dlc;
>================
>
> > At 09:45 AM 3/9/2004 -0800, you wrote:
> >
> >>Janet,
> >>
> >>Thanks for the suggestion. I tried it, but continue to receive the
> >>"unsupported data conversion" error.
> >>
> >>This is getting mighty frustrating.
> >>
> >>Janet Schmitt wrote:
> >>
> >>
> >>>I would try something like this for the Oracle portion:
> >>>
> >>>CREATE OR REPLACE
> >>>PACKAGE schemaname.studentpackage
> >>>IS
> >>> TYPE student_type_ref_cursor IS REF CURSOR;
> >>>
> >>> PROCEDURE test_dlc_sp_getStudentInfo(studentID IN number,
> >>>student_cursor IN OUT student_type_ref_cursor);
> >>>END;
> >>>
> >>>Create or replace package body schemaname.studentpackage
> >>>IS
> >>>
> >>>PROCEDURE test_dlc_sp_getStudentInfo (
> >>> studentID IN number, student_cursor IN OUT student_type_ref_cursor)
> >>>is
> >>>begin
> >>> open student_cursor
> >>> for
> >>> select sFirst, sLast, sOrient
> >>> from tblStudentInfo
> >>> where sid = studentID;
> >>>end;
> >>>
> >>>END studentpackage;
> >>>
> >>>
> >>>
> >>>
> >>
> >>----------
> >>[
> >
> >
> >
>
>----------
>[
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
