execute oracle stored procedure in CF8

[gayanb]

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

[Daverms]

Hi,

I think you are trying to call an oracle 'function' instead of a 'procedure'. Try rewriting your function as a procedure and then rerun your page.

[paross1 · Accepted solution]

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

[me@Adobe]

May I know what do you mean by declaring a ref cursor globally? . Appreciate if you could provide with an example.

[gayanb]

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

regards,
gayanb