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

execute oracle stored procedure in CF8

Explorer ,
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
TOPICS
Database access

Views

731

Translate

Translate

Report

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...

Votes

Translate

Translate
Advocate ,
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.

Votes

Translate

Translate

Report

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

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

Votes

Translate

Translate

Report

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

Copy link to clipboard

Copied

LATEST

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

Votes

Translate

Translate

Report

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

Copy link to clipboard

Copied


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

regards,
gayanb

Votes

Translate

Translate

Report

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
Documentation