> Richard, I think in order to call an Oracle stored procedure through Cold Fusion, you need to create a package spec.
> Don't think you can solely reference the procedure name directly.
>
> Create a package spec and put your procedure in the package body. After you have done that, modify your calling code to
>
> <cfstoredproc datasource="DLCampus" procedure="PACKAGE_SPEC_NAME.PROCEDURE_NAME">
>
> Procedure name in this case is "test_dlc_sp_getStudentInfo"
Thanks for the tip. I've done that (I've been planning on putting all
of our SP's into one or two packages anyway). Unfortunately, I got the
same error when I tried again.
>
>
>
> Nick Han
>
>
>>>>[EMAIL PROTECTED] 03/08/04 11:28AM >>>
>
> Richard Crawford wrote:
>
>
>>Kristopher Pilles wrote:
>>
>>
>>
>>>What kind of issues are ya having? I have use CF and oracle together
>>>extensively... perhaps i can help
>>
>>
>>Kristopher,
>>
>>I've posted about my issues before, but I'll happily do so again. :)
>>
>>I have a stored procedure written in PL/SQL, which I'm attempting to
>>call from Cold Fusion, but only errors are generated.
>>
>>Here is the stored procedure:
>
>
> Hm... My earlier post seems to have gotten truncated.
>
> Let's try again.
>
>
> Kristopher Pilles wrote:
>
> > What kind of issues are ya having? I have use CF and oracle together
> > extensively... perhaps i can help
>
>
> Kristopher,
>
> I've posted about my issues before, but I'll happily do so again. :)
>
> I have a stored procedure written in PL/SQL, which I'm attempting to
> call from Cold Fusion, but only errors are generated.
>
> Here is the stored procedure:
> ===============================
> CREATE OR REPLACE procedure test_dlc_sp_getStudentInfo (
> studentID IN number,
> studentInfo OUT types.cursorType
> )
> as
>
> sFirst varchar2(50);
> sLast varchar2(50);
> sOrient char(1);
>
> begin
>
> open studentInfo for
> select
> sFirst,
> sLast,
> sOrient
> from
> tblStudentInfo
> where
> sid = studentID;
>
> fetch studentInfo into sFirst, sLast, sOrient;
>
> close studentInfo;
>
> end;
> /
> ===============================
>
> Here is how I call the SP from Cold Fusion:
> ===============================
> <cfstoredproc datasource="DLCampus" procedure="test_dlc_sp_getStudentInfo">
> <cfprocparam type="in" value="1881" cfsqltype="cf_sql_number">
> <cfprocresult name="getName">
> </cfstoredproc>
> ===============================
>
> At this point, I've trimmed the CF page down to just this little tiny
> snippet. If it works, it should generate no output at all, but I should
> be able to reference, say, sFirst like this later on in the same page:
>
> <cfoutput query="getName">#sFirst#</cfoutput>
>
> or
>
> <cfoutput>#getName.sFirst#</cfoutput>
>
> In older versions of CF, a result set from an Oracle SP would have to be
> referenced with a variable in the cfstoredproc section; with MX (which
> is what I'm using; not MX6.1), it's sufficient to reference a result set
> from an Oracle SP which is returned by a ref cursor with the
> cfprocresult attribute. I think I'm right about this, but I will
> happily accept that I'm wrong if it means that I can get this all
> working. ;-)
>
> At any rate, when I try to execute the script, I get this error:
> ===============================
> Error Executing Database Query.
> [Macromedia][Oracle JDBC Driver]Unsupported data conversion.
> ===============================
>
> I don't know if this means that I'm experiencing a problem with the way
> I've written my stored procedure, the way I'm referencing it in Cold
> Fusion, or if I've got a problem with JDBC (I've got the native CFMX
> drivers referenced in my datasource definition in Cold Fusion).
>
> Any help would be more than appreciated. I'll send you a six-pack if
> you can help me figure out this problem. :-D
>
--
Richard S. Crawford
Programmer III,
UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu)
(916)327-7793 / [EMAIL PROTECTED]
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
