-
Hi Gurus,
CREATE OR REPLACE procedure ppqt_test_dyn2
(v_id in number) is
v_quote_id varchar2(50);
begin
select quote_id into v_quote_id from test_dyn where id = v_id ;
dbms_output.put_line(v_quote_id);
end ppqt_test_dyn1;
This works perfectly since I have the condition as
id = v_id;
But if I give id > v_id, the result fetches more than one record.
How do I handle this?
Can anyone help me with the syntax?
Thanks!
Hemant
-
-
Hi,
I know that I have to use a cursor but Im not familiar with the syntax I should be using here...
Thanks!
Hemant
-
Originally posted by hemanr
CREATE OR REPLACE procedure ppqt_test_dyn2
(v_id in number) is
CURSOR c_my_quote_ids IS select quote_id from test_dyn
where id > v_id;
begin
dbms_output.enable(1000000);
FOR r_my_quote_ids IN c_my_quote_ids LOOP
dbms_output.put_line(r_my_quote_ids.quote_id);
END LOOP;
end ppqt_test_dyn1;
I did not declare the rowtype since it will get an implicit declaration. The rest is up to you.
-
Hi Gandolf,
This code does not work.
Here are the records in test_dyn
1019693
1019699
1019705
1019708
1019717
1019720
1019723
1019732
1019735
And here is ur code slightly altered to write to a table:
CREATE OR REPLACE procedure ppqt_test_dyn2
(v_id in number) is
CURSOR c_my_quote_ids IS select id,quote_id from test_dyn
where id > v_id;
begin
FOR r_my_quote_ids IN c_my_quote_ids LOOP
insert into test_dyn_hold(id,quote_id)
values(r_my_quote_ids.id,r_my_quote_ids.quote_id);
END LOOP;
end ppqt_test_dyn2;
If I SQL> exec ppqt_test_dyn2(1019717), the proc writes 16 records to the test_dyn_hold table.
It should write only 4 records since we have only 4 greater than the input parameter in the source table...
I guess the cursor itself is not recognizing the input parameter here.
Last edited by hemanr; 10-24-2002 at 11:16 AM.
Hemant
-
Sorry Gandolf!
I messed big on this one.
It works!
Thanks!
Hemant
-
Glad to be of service.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|