Home » SQL & PL/SQL » SQL & PL/SQL » cursor with parameter inside function (oracle 10)
cursor with parameter inside function [message #446313] Sun, 07 March 2010 20:14 Go to next message
yurkoz
Messages: 26
Registered: September 2009
Location: bronx
Junior Member
Hi All

I have code inside function

.....
cursor cur1 is
select *
from sarchkler
where sarchkler_appl_no = in_appl_no


begin

select max(saradap_appl_no) into in_appl_no from saradap;
for rec1 in cur1 loop
......

my question I get varible for cursor after cursor declaration
Does it work ?

Thanks
Re: cursor with parameter inside function [message #446314 is a reply to message #446313] Sun, 07 March 2010 20:22 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: cursor with parameter inside function [message #446318 is a reply to message #446313] Sun, 07 March 2010 21:07 Go to previous messageGo to next message
yurkoz
Messages: 26
Registered: September 2009
Location: bronx
Junior Member
I found answer

Opening the cursor executes the query and identifies the result set, which consists of all rows that meet the query search criteria


It means it should work

Thanks
Re: cursor with parameter inside function [message #446320 is a reply to message #446318] Sun, 07 March 2010 21:13 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Choice #1
Post question here & hope to get correct answer.

Choice #2

Compose a few lines of code & see the results yourself.

Which is better option?
Re: cursor with parameter inside function [message #446321 is a reply to message #446320] Sun, 07 March 2010 21:16 Go to previous messageGo to next message
yurkoz
Messages: 26
Registered: September 2009
Location: bronx
Junior Member
today it is working , tommorow it could be stop?
Documentation should get answer.
I do not know what heppend inside the server.
Re: cursor with parameter inside function [message #446324 is a reply to message #446321] Sun, 07 March 2010 21:39 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>today it is working , tommorow it could be stop?
Software does not spontaneously break.

>Documentation should get answer.
Documentation is never 100% correct.

Trust, but VERIFY with own testing.
Re: cursor with parameter inside function [message #446380 is a reply to message #446324] Mon, 08 March 2010 01:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
BlackSwan wrote on Mon, 08 March 2010 04:39
>today it is working , tommorow it could be stop?
Software does not spontaneously break.

>Documentation should get answer.
Documentation is never 100% correct.

Trust, but VERIFY with own testing.

Unfortunately, verification is not what the OP is after. He wants to know if his assumptions can be FALSIFIED. Therefor his question is very valid, since depending on data, a piece of code that seems to be working fine, might (from the look of it) "suddenly go awry".

@yurkoz: Yes, you are correct. What you intend to do can be done.
Re: cursor with parameter inside function [message #446418 is a reply to message #446313] Mon, 08 March 2010 04:17 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
my question I get varible for cursor after cursor declaration
Does it work ?


Yes it does.
The Cursor query is parsed and variables are bound into the query at the point where the query is opened, not where it is defined.

I tend to use a slightly different notation for explicit cursors that makes what is going on much clearer :
cursor cur1 (p_appl_no  in  sarchkler.sarchkler_appl_no%type) is
select *
from   sarchkler
where  sarchkler_appl_no = p_appl_no


This way it's quote clear that p_appl_no is a parameter passed to the cursor, and exists only in the scope of the cursor definition.
Previous Topic: create table usning bind variables in EXECUTE IMMEDIATE.
Next Topic: Query to get output in a particular format (merged 3)
Goto Forum:
  


Current Time: Fri Dec 09 08:10:06 CST 2016

Total time taken to generate the page: 0.05191 seconds