Exit
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
  • 한국 커뮤니티
0

execute oracle stored procedure in CF8

Explorer ,
Nov 14, 2007 Nov 14, 2007
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
TOPICS
Database access
835
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 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...
Translate
Advocate ,
Nov 14, 2007 Nov 14, 2007
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.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
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.GetAction" datasource="test_DSN">

Phil
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Oct 12, 2023 Oct 12, 2023
LATEST

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

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Nov 14, 2007 Nov 14, 2007

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

regards,
gayanb
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources