Home » SQL & PL/SQL » SQL & PL/SQL » "if condition" and "how to get output" (merged)
"if condition" and "how to get output" (merged) [message #359645] Mon, 17 November 2008 17:54 Go to next message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
Hi ALL,

Actually i m trying to print a1,a2,a3,a4,a5,a6. but my prob is that my procedure p1 executes successfully, but its not printing its value. serveroutput is ON.i m using oracle 10g software.


create or replace procedure p1(sales_org1 IN s_delivery.sales_org%type)
IS
a1 varchar2(30);
a2 varchar2(30);
a3 timestamp;
a4 date;
a5 number;
a6 varchar2(30);

begin

if(a1=sales_org1)
then

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) );
dbms_output.put_line(a1||a2||a3||a4||a5||a6);

end if;
end;


please help me.
thanks

Re: if condition [message #359647 is a reply to message #359645] Mon, 17 November 2008 18:22 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
haven't tried your code but...

Does sales_org1 has a value in sales_org1 IN s_delivery.sales_org%type?

also probably you need to type 'Set SERVEROUTPUT ON;' before your declaration block..

Regards,
Wilbert
Re: if condition [message #359648 is a reply to message #359647] Mon, 17 November 2008 18:49 Go to previous messageGo to next message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
yes you are right.
Re: if condition [message #359657 is a reply to message #359645] Mon, 17 November 2008 21:45 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


How did you execute the procedure ?
What did you pass as an argument?
Copy and paste your SQL*PLUS session.

By the way ,

Quote:
if(a1=sales_org1)
then


wont never be satisfied . So the dbms_output statement inside the block wont work.

Smile
Rajuvan.
Re: if condition [message #359675 is a reply to message #359657] Tue, 18 November 2008 00:03 Go to previous messageGo to next message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
Hi tanks for reply Rajuvan,
but my prob is solved, another auestion related to the same code is::
here i m using dbms_output.put_line so it will give me output but not in the table format. so how can i get my output in the table format.. when we write select * from emp; then it will give o/p in the format of table, i want same in this code..

My code is:

create or replace procedure p1(sales_org1 IN s_delivery.sales_org%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.sales_org));
if(a1=sales_org1)
then
dbms_output.put_line(a1||a2||a3||a4||a5||a6);


end if;
exception
when no_data_found then
dbms_output.put_line('No data found');


end;
and when i execute it::

execute p1('ABC');

it will give me o/p in the form of simple row...


thanks
how to get output [message #359701 is a reply to message #359645] Tue, 18 November 2008 00:56 Go to previous messageGo to next message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
Hi all,

can u help me for following question...??
we can get output on screen using dbms_output.put_line then it will give o/p in the row form

butttttt

if i want to get output in the form of table then how can i get it?


tahnks
Re: if condition [message #359705 is a reply to message #359675] Tue, 18 November 2008 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Write it in a tabular format.
Or return a ref cursor that you can print as you want from your client, for instance with a "print" in SQL*Plus.

Regards
Michel
Re: how to get output [message #359709 is a reply to message #359701] Tue, 18 November 2008 01:06 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
As far as i know we cannot format dbms_output.put_line output is table format.
Even i use the Tool for oracle i.e. Toad , it also doesnt shows the output in table format.

Thanks,
Re: how to get output [message #359714 is a reply to message #359701] Tue, 18 November 2008 01:12 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
As I said in the other topic you posted this:
Michel Cadot wrote on Tue, 18 November 2008 08:00
Write it in a tabular format.
Or return a ref cursor that you can print as you want from your client, for instance with a "print" in SQL*Plus.

Regards
Michel



Previous Topic: Find No Of Days Between Two Dates
Next Topic: checking the flag and comparing
Goto Forum:
  


Current Time: Thu Feb 06 16:17:28 CST 2025