| creating a procedure [message #372278] |
Wed, 24 January 2001 15:43  |
gary
Messages: 35
Registered: January 2001
|
Member |
|
|
I am having trouble creating a procedure that enables me to run all six of the following queries together in Oracle 8.1.5. Theses six queries work when I run them together, but will not compile as a procedure. This IS NOT a problem with SQL Server 7!! Any suggestions are greatly appreciated!!
select falcon50.filename,dir_level_1,falcondocset.member_rec_no from falcon50,falcondocset where falcondocset.member_rec_no=falcon50.record_number;
select filename,dir_level_1 from falcon50 where file_format='paper';
select filename dir_level_1 from falcon50 where dir_level_1='' or dir_level_1=' ' or dir_level_1 is null;
select distinct dir_level_1 from falcon50;
select distinct servername from falcon50;
select distinct dmsresource from falcon50;
|
|
|
|
| Re: creating a procedure [message #372279 is a reply to message #372278] |
Wed, 24 January 2001 17:46  |
Suresh Vemulapalli
Messages: 624
Registered: August 2000
|
Senior Member |
|
|
I didn't get your point..
do you want to execute all these queries?.
If you want to place these queries in procedure, there must be an INTO clause and query should return single row.. To handle multirow queries use cursors..
Suresh
|
|
|
|
| Re: creating a procedure [message #372282 is a reply to message #372278] |
Thu, 25 January 2001 00:04 |
Robert Moy
Messages: 15
Registered: December 2000
|
Junior Member |
|
|
This package/procedure should do it. I did for two select statements. You can put in as many as you want. One cursor for one select statement.
SQL> run
1 create or replace package find_letter as
2 procedure locate_letter
3 (get_name out players.name%type,
4 get_town out players.town%type);
5* end find_letter;
Package created.
SQL> run
1 create or replace package body find_letter as
2 procedure locate_letter
3 (get_name out players.name%type,
4 get_town out players.town%type) as
5 cursor meet_letter is
6 select name
7 from players;
8 cursor meet_letter2 is
9 select town
10 from players;
11 begin
12 open meet_letter;
13 dbms_output.put_line('Name');
14 loop
15 fetch
16 meet_letter into get_name;
17 dbms_output.put_line(get_name);
18 exit when meet_letter%NotFound;
19 end loop;
20 open meet_letter2;
21 dbms_output.put_line('town');
22 loop
23 fetch
24 meet_letter2 into get_town;
25 dbms_output.put_line(get_town);
26 exit when meet_letter2%NotFound;
27 end loop;
28 exception
29 when no_data_found then
30 dbms_output.put_line('No Data');
31 return;
32 end locate_letter;
33* end find_letter;
Package body created.
SQL> declare
2 got_name players.name%type;
3 got_town players.town%type;
4 begin
5 find_letter.locate_letter(got_name,got_town);
6 end;
7 /
Good Luck
|
|
|
|
| Re: creating a procedure [message #372284 is a reply to message #372278] |
Thu, 25 January 2001 20:22 |
Robert Moy
Messages: 15
Registered: December 2000
|
Junior Member |
|
|
Hello Gary:
I try to send this last night. This program run two select or more on a single procedure. You can add as many select statements as you wish provided that you match the number of select statements with that of cursors
SQL> create or replace package find_letter as
2 procedure locate_letter
3 (get_name out players.name%type,
4 get_street out players.street%type,
5 get_town out players.town%type);
6 end find_letter;
7 /
Package created.
SQL> run
1 create or replace package body find_letter as
2 procedure locate_letter
3 (get_name out players.name%type,
4 get_street out players.street%type,
5 get_town out players.town%type) as
6 cursor meet_letter is
7 select name
8 from players;
9 cursor meet_letter2 is
10 select town, street
11 from players;
12 begin
13 open meet_letter;
14 dbms_output.put_line('Name');
15 loop
16 fetch
17 meet_letter into get_name;
18 dbms_output.put_line(get_name);
19 exit when meet_letter%NotFound;
20 end loop;
21 open meet_letter2;
22 dbms_output.put_line('town'||' '||'street');
23 loop
24 fetch
25 meet_letter2 into get_town,get_street;
26 dbms_output.put_line(get_town||' '||get_street);
27 exit when meet_letter2%NotFound;
28 end loop;
29 exception
30 when no_data_found then
31 dbms_output.put_line('No Data');
32 return;
33 end locate_letter;
34* end find_letter;
Package body created.
SQL> run
1 declare
2 got_name players.name%type;
3 got_town players.town%type;
4 got_street players.street%type;
5 begin
6 find_letter.locate_letter(got_name,got_town,got_street);
7* end;
Good Luck
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
