execute oracle stored procedure in CF8

Question

I am executing a stored procedure like this.

<cfstoredproc procedure="GetAction" datasource="test_DSN">
<cfprocparam cfsqltype="CF_SQL_INTEGER" dbvarname="actionId" type="in" value="1">
<cfprocresult name="actions" resultset="1" >
</cfstoredproc>

and my stored procedure in oracle is,

create or replace function GetAction(actionId IN ACTION.ID%TYPE)

RETURN ACTION%ROWTYPE

IS
actions

ACTION%ROWTYPE;

begin

select *

INTO actions

FROM ACTION WHERE ID = actionId;

RETURN actions;

end GetAction;

then I get the error message like,
ORA-01008: not all variables bound

Please help me on this.

Thanx

paross1 · Accepted Answer

If you want to return a result set (result of a query) from your PL/SQL, you will have to do what Daverms suggested and convert your function to a procedure and use a reference cursor to return your result set. Your procedure will have to be contained in a package in order for you to declare a ref cursor "globally" so that it can be accessed by CF.

Oh, and be sure to change your cfstoredproc procedure parameter to add the package name to the proc name.

<cfstoredproc procedure="your_pkg.GetAction" datasource="test_DSN">

Phil

gayanb · Answer

hi paross1, Thank you very much for the answer . Now it works fine.regards,gayanb

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded