Home » SQL & PL/SQL » SQL & PL/SQL » NEED HELP W/ CURSOR LOOP
NEED HELP W/ CURSOR LOOP [message #218047] Tue, 06 February 2007 10:00 Go to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Any idea on how I can correctly loop around this refcursor an get 15 rec or less instead of what my output shows.
Please point me in the right direction.

Here is the code + output:

declare
sql_text varchar2(32767);
maxval number := 15;
startval number := 1 ;
i number;
begin
for i in startval..maxval loop
sql_text := 'select ';
sql_text := sql_text ||' t_num,';
sql_text := sql_text ||' l_num,' ;
sql_text := sql_text ||' p_num,';
sql_text := substr(sql_text,1,length(sql_text)-1) || ' FROM ttable';
sql_text := sql_text || ' WHERE t_num = ''0900-141.00-02.00-037.000'' ';
--sql_text := sql_text || ' order by trunc(t_date) DESC ' ;
i := startval +1; -- I added this line to no avail
end loop;
dbms_output.put_line (i);
dbms_output.put_line (sql_text);
open :c1 for sql_text;
end;
/
PL/SQL procedure successfully completed.

SQL> print c1

T_NUM L_NUM P_N
--------------------------- --------- ------
0900-141.00-02.00-037.000 M00019006 006
0900-141.00-02.00-037.000 M00019006 007
0900-141.00-02.00-037.000 M00021170 096
0900-141.00-02.00-037.000 M00021170 097
0900-141.00-02.00-037.000 M00020883 109
0900-141.00-02.00-037.000 M00019356 168
0900-141.00-02.00-037.000 M00021066 237
0900-141.00-02.00-037.000 M00019100 327
0900-141.00-02.00-037.000 M00021335 349
0900-141.00-02.00-037.000 D00011752 355
0900-141.00-02.00-037.000 M00021195 393

T_NUM L_NUM P_N
--------------------------- --------- ------
0900-141.00-02.00-037.000 D00012412 398
0900-141.00-02.00-037.000 D00012419 520
0900-141.00-02.00-037.000 M00021371 739
0900-141.00-02.00-037.000 M00019265 781
0900-141.00-02.00-037.000 M00020901 835
0900-141.00-02.00-037.000 M00020901 836
0900-141.00-02.00-037.000 D00012458 864
0900-141.00-02.00-037.000 M00021356 878

19 rows selected.

My goal to get 15 or less of the most recent records.
Re: NEED HELP W/ CURSOR LOOP [message #218048 is a reply to message #218047] Tue, 06 February 2007 10:06 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Please realize that if you eliminate the "FOR ... LOOP" & "END LOOP" the results will be identical to what you have now; or if you set MAXVAL to any arbitrary value.
Re: NEED HELP W/ CURSOR LOOP [message #218051 is a reply to message #218047] Tue, 06 February 2007 10:14 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
hmmm...
You want to get 15 or less records. Your FOR loop has nothing to do with that. What your program does is to assign to SQL_TEXT the same string again and again - 15 times. Notice that in all the iterations, your SQL_TEXT ends as
select  t_num, l_num, p_num FROM ttable WHERE t_num = '0900-141.00-02.00-037.000'

Your code 'sql_text := 'select ';' resets the value of SQL_TEXT to 'select..' and again build the same string.
If you just want the first 15 rows, just change
sql_text := sql_text || ' WHERE t_num = ''0900-141.00-02.00-037.000'' ';

to
sql_text := sql_text || ' WHERE t_num = ''0900-141.00-02.00-037.000'' and rownum < 16';


And please remove the i := startval +1; -- I added this line to no avail -- it's bad and it's not definitely going to help you...
Re: NEED HELP W/ CURSOR LOOP [message #218069 is a reply to message #218051] Tue, 06 February 2007 12:33 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Thanks a lot it worked.

PL/SQL procedure successfully completed.

SQL> print c1

L_NUM P_NUM
--------- ------
M00019006 006
M00019006 007
M00021170 096
M00021170 097
M00020883 109
M00019356 168
M00021066 237
M00019100 327
M00021335 349
D00011752 355
M00021195 393
D00012412 398
D00012419 520
M00021371 739
M00019265 781

15 rows selected.

Note I removed one col (not needed)

anacedent is right, I don't even need the for loop at all.
Re: NEED HELP W/ CURSOR LOOP [message #218071 is a reply to message #218047] Tue, 06 February 2007 12:41 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
You don't need PL/SQL at all either!
Re: NEED HELP W/ CURSOR LOOP [message #218077 is a reply to message #218071] Tue, 06 February 2007 13:35 Go to previous message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
You are probably right but, I am not sure of an efficient way to put it all together.:

1. I have this function which is called at the very start:
where a user enters T_num w/o the padding at a prompt: then I do my conversion as follows:

SQL> r
1 CREATE OR REPLACE FUNCTION PadTNum (usr_string IN VARCHAR2)
2 RETURN VARCHAR2
3 IS
4 BEGIN
5 RETURN
6 SUBSTR(usr_string, 1,4)
7 ||'-'||SUBSTR(usr_string, 5,3)
8 ||'.'||SUBSTR(usr_string, 10,2)
9 ||'.'||SUBSTR(usr_string, 12,2)
10 ||'-'||SUBSTR(usr_string, 14,3)
11 ||'.'||SUBSTR(usr_string, 17);
12* END;

Function created.

SQL> ed
Wrote file afiedt.buf

SQL> SELECT padtnum('0100215000200003000') FROM ttable;
SQL> r
PADTAXNUM('0100215000200003000')
----------------------------------------------------------------
0100-215.02.00-003.000

The above number is what I will pass into my where clause (right now it is hard coded for testing purposes) in my huge qry(the example given is 1/3 of the qry) but the data is accurate.

Then I get the data and return 15 or less records to user.

If I can do it all in SQL Plus, painlessly, I would welcome it.
I don't have a preference one way or the other. All I need is something bundled that is user friendly. We're dealing w/ very unsofisticated users. and I don't want to complicate matter by having them do too much except enter that darn number at the prompt. even then, I know, I have to test for input error. I was given sql and or pl/sql as my only tool.

I've gotten input from many of you expert and I am very appreciative of it all. I will certainly let you expert know when it all come to fruition.

P.S. When and if I can solve the 255 bytes limitation in dbms_output function, why can't I bundle everything as a procedure. Or can a view ( which will run in realtime), help me out in hiding all this mumbo jumbo that goes on behind the scene?

Many thanks & Regards,

[Updated on: Tue, 06 February 2007 13:42]

Report message to a moderator

Previous Topic: Full path to the spfile ?
Next Topic: Table as variable and looping
Goto Forum:
  


Current Time: Wed Dec 07 02:59:15 CST 2016

Total time taken to generate the page: 0.08669 seconds