0
Explorer
,
/t5/coldfusion-discussions/execute-oracle-stored-procedure-in-cf8/td-p/494689
Nov 14, 2007
Nov 14, 2007
Copy link to clipboard
Copied
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
<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
TOPICS
Database access
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
1 Correct answer
Mentor
,
Nov 14, 2007
Nov 14, 2007
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.GetAct...
Oh, and be sure to change your cfstoredproc procedure parameter to add the package name to the proc name.
<cfstoredproc procedure="your_pkg.GetAct...
Advocate
,
/t5/coldfusion-discussions/execute-oracle-stored-procedure-in-cf8/m-p/494690#M44867
Nov 14, 2007
Nov 14, 2007
Copy link to clipboard
Copied
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.
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.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Mentor
,
/t5/coldfusion-discussions/execute-oracle-stored-procedure-in-cf8/m-p/494691#M44868
Nov 14, 2007
Nov 14, 2007
Copy link to clipboard
Copied
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
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
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Community Beginner
,
LATEST
/t5/coldfusion-discussions/execute-oracle-stored-procedure-in-cf8/m-p/14153979#M196218
Oct 12, 2023
Oct 12, 2023
Copy link to clipboard
Copied
May I know what do you mean by declaring a ref cursor globally? . Appreciate if you could provide with an example.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
gayanb
AUTHOR
Explorer
,
/t5/coldfusion-discussions/execute-oracle-stored-procedure-in-cf8/m-p/494692#M44869
Nov 14, 2007
Nov 14, 2007
Copy link to clipboard
Copied
hi paross1,
Thank you very much for the answer . Now it works fine.
regards,
gayanb
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more

