CREATE OR REPLACE PACKAGE MyTablespace.MyPackage
AS
TYPE MyRefCursorType
IS REF CURSOR RETURN MyProcedure(MyRecordset OUT MyRefCusorType);
END MyPackage;
/
CREATE OR REPLACE PACKAGE BODY MyTablespace.MyPackage AS
PROCEDURE MyProcedure (MyRecordset OUT MyRefCusorType)
IS
BEGIN
OPEN MyRecordset FOR
Select * from MyTable
END MyProcedure
END MyPackagel;
/
Jeremy Brodie
Intelix
an Edgewater Technology Solutions Company
web: http://www.edgewater.com
phone:(703) 815-2500
nasdaq symbol: EDGE
>On Fri, 2004-01-30 at 07:50, David Jones wrote:
>> I have a client that wants me to remove all inline SQL from his application
>> even though I am using the CFQueryParam tag to invoke the use of bind
>> variables. I am fairly familiar with Oracle Stored Procedures but what I
>> not familiar with is returning queries from a Stored Procedure to CF 5.0.
>> Is this possible, if so can someone provide me with a simple example.
>
>if you havent found this out already, here is an example of using a
>stored procedure
>
><cfstoredproc procedure="sp_login"
> datasource="#request.dsn#" debug="no"
>>
><cfprocparam dbVarName="ipaddress" value="#cgi.REMOTE_ADDR#"
>CFSQLtype="CF_SQL_VARCHAR" null="No">
><cfprocparam dbVarName="login" value="#cli_username#"
>CFSQLType="CF_SQL_VARCHAR" null="No">
><cfprocparam dbVarName="password" value="#rmsEncrypt(cli_password)#"
>CFSQLType="CF_SQL_VARCHAR" null="No">
>
><cfprocresult name="checkClientCreds" resultSet="1">
><!--- if you had other result sets you could do this as well
><cfprocresult name="otherstuff" resultSet="2">
>...
> --->
></cfstoredproc>
>
>One of the reasons I like stored procedures so much is you can get multi
>query sets back for the price of one database hit.
>
>anyway, hope that helps.
>
>--
>Vale,
>Rob
>
>Luxuria immodica insaniam creat.
>Sanam formam viatae conservate!
>
>http://www.rohanclan.com
>http://treebeard.sourceforge.net
>http://ashpool.sourceforge.net
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
