Home » SQL & PL/SQL » SQL & PL/SQL » Why not in a single procedure??? (oracle 9i)
Why not in a single procedure??? [message #359973] Wed, 19 November 2008 00:55 Go to next message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
Hi All,

here my procedure is

create or replace procedure pr2(sales_org1 IN s_delivery.created_by%type, date1 s_delivery.creation_date%type,date2 s_delivery.creation_date%type)
is
a1 varchar2(30);
a2 varchar2(30);
a3 timestamp;
a4 date;
a5 number;
a6 varchar2(30);

begin
select sd.sales_org, sd.created_by,sd.creation_time,sd.creation_date, sd.ship_to_party, sp.name into a1,a2,a3,a4,a5,a6
from s_delivery sd, s_partner sp
where( (sd.ship_to_party = sp.partner_no) AND (sales_org1=sd.created_by) AND (creation_date between date1 AND date2));
delete from t1;
insert into t1 values(a1,a2,a3,a4,a5,a6);

end;


this procedure executes succesfully..
but if i want to print values of table t1,, is it possible to add select * into same code?

i want o/p in same procedure execution only once.

thanks
Re: Why not in a single procedure??? [message #359974 is a reply to message #359973] Wed, 19 November 2008 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Quote:
is it possible to add select * into same code?

Yes
Quote:
i want o/p in same procedure execution only once.

PL/SQL is not for displaying things. Use dbms_output or pipelined function or return a ref cursor and display the result with your client.

Regards
Michel
Re: Why not in a single procedure??? [message #359976 is a reply to message #359973] Wed, 19 November 2008 01:13 Go to previous messageGo to next message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
Thanks Michel,
will follow your instructions..
i have tried to add select * from t1 into same code.. my code successfuly executes,but not printing output.

thanks
Re: Why not in a single procedure??? [message #359977 is a reply to message #359976] Wed, 19 November 2008 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because:
Quote:
PL/SQL is not for displaying things. [You have to] Use dbms_output or pipelined function or return a ref cursor and display the result with your client.

Regards
Michel
Re: Why not in a single procedure??? [message #359980 is a reply to message #359973] Wed, 19 November 2008 01:35 Go to previous message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
ok thanks michel
Previous Topic: Getting wrong type of arguments
Next Topic: Join Statement
Goto Forum:
  


Current Time: Mon Dec 05 02:56:04 CST 2016

Total time taken to generate the page: 0.18394 seconds