Home » SQL & PL/SQL » SQL & PL/SQL » Cursor with parameters
Cursor with parameters [message #268277] Tue, 18 September 2007 01:58 Go to next message
gajini
Messages: 259
Registered: January 2006
Senior Member
Hi,

Please find below the simple anonymous pl/sql block...

declare
cursor c1(p_name varchar2) is select name from test where name like '%p_name%';
n1 varchar2(5);
begin
open c1('M');
fetch c1 into n1;
if c1%notfound then
dbms_output.put_line('No Data found');
end if;
close c1;
end;


When this cursor is opened,open c1('M'),I want the query to execute like this,

select name from test where name like '%M%';

How to rewrite my cursor query to achieve this so that I can cursor parameter with comparision operator LIKE in WHERE clause,

cursor c1(p_name varchar2) is select name from test where name like '%p_name%';

Thanks...
Re: Cursor with parameters [message #268286 is a reply to message #268277] Tue, 18 September 2007 02:08 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Quote:

cursor c1(p_name varchar2) is select name from test where name like '%p_name%';



use this

cursor c1(p_name varchar2) is select name 
from test 
where name like '%'||p_name||'%';
Re: Cursor with parameters [message #268299 is a reply to message #268277] Tue, 18 September 2007 02:32 Go to previous messageGo to next message
gajini
Messages: 259
Registered: January 2006
Senior Member
Thanks..

It's working now. In cursor using %ROWCOUNT,we can find how many rows we've fetched so far.Is there any attribute like this to find the total number of rows contained in a cursor?

Thanks in advance...
Re: Cursor with parameters [message #268301 is a reply to message #268299] Tue, 18 September 2007 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next time, please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel
Re: Cursor with parameters [message #268308 is a reply to message #268299] Tue, 18 September 2007 02:47 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
%ROWCOUNT will return the number of rows you have currently fetched.
You could add a COUNT(*) to the cursor and check that to see the total number of rows the cursor will fetch, but other than that there isn't a way of determining the size of the rowset.
Previous Topic: Insert into openquery error
Next Topic: oracle 9i
Goto Forum:
  


Current Time: Sun Dec 11 04:06:37 CST 2016

Total time taken to generate the page: 0.04328 seconds