Home » SQL & PL/SQL » SQL & PL/SQL » error in executing the package
error in executing the package [message #301900] Fri, 22 February 2008 03:51 Go to next message
maschender
Messages: 9
Registered: February 2008
Junior Member
Hi,

I am trying to write a simple package with a procedure and a function. Along with them, i want to display today's date. But, i am not clear where to put this code. I have tried various methods but in vain. I am also posting my code.

--package specification
create or replace package test_1 is
procedure dis_date;
function avg_sum return number;
end test_1;


--package body
create or replace package body test_1 is
today date;
procedure dis_date is
begin
dbms_output.put_line('hi');
end;
function avg_sum return number is
begin
return (12*10);
select sysdate into today from dual;
dbms_output.put_line(today);
end;
end test_1;
/

I tried creating a separate begin-end block for that. But, that also didnt work.

Thanks and Regards,
Maschender
Re: error in executing the package [message #301903 is a reply to message #301900] Fri, 22 February 2008 03:59 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
please format your code using CODE tags.And as for your query you can do this way

var_date date :=sysdate;

package body....
<your_proc>

<your_func>

begin
dbms_output.put_line(to_char(var_date,'dd-mm-yyyy'));
end package body




regards,
Re: error in executing the package [message #301908 is a reply to message #301900] Fri, 22 February 2008 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to put dbms_output BEFORE "return" statement otherwise it is never reached.

In addition you don't have to use a select on dual, you can directly use today:=sysdate.

Regards
Michel
Re: error in executing the package [message #301918 is a reply to message #301903] Fri, 22 February 2008 05:12 Go to previous messageGo to next message
maschender
Messages: 9
Registered: February 2008
Junior Member
Hi, thanks for your reply. I tried both ways and the package has been successfully executed. But, I am unable to display the date (TODAY), even though I have put in dbms_output.out_line. How can I do that? I am enclosing the code as well.

Method 1:
create or replace package body test_1 is
today date :=sysdate;
procedure dis_date is
begin
dbms_output.put_line('hi');
end;
function avg_sum return number is
begin
return (12*10);
end;
begin
dbms_output.put_line(to_char(today,'dd-mm-yyyy'));
end test_1;

Method 2:

create or replace package body test_1 is
today date;
procedure dis_date is
begin
dbms_output.put_line('hi');
end;
function avg_sum return number is
begin
today:=sysdate;
dbms_output.put_line(today);
return (12*10);
end;
end test_1;

Thanks and Regards,
Maschender

[Updated on: Fri, 22 February 2008 05:14]

Report message to a moderator

Re: error in executing the package [message #301919 is a reply to message #301918] Fri, 22 February 2008 05:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Please use code tags around your code!

make sure you do a "set serveroutput on" prior to executing your procedure
Re: error in executing the package [message #301920 is a reply to message #301919] Fri, 22 February 2008 05:28 Go to previous messageGo to next message
maschender
Messages: 9
Registered: February 2008
Junior Member
Hi Frank,

the serveroutput is already on. Moreover, how will u call that dbms_output? I mean, just by compiling the package, will it be displayed?

Can you help me in adding the tags?

Regards,
Maschender
Re: error in executing the package [message #301924 is a reply to message #301920] Fri, 22 February 2008 05:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In the end what is your problem?
SQL> create or replace package test_1 is
  2  procedure dis_date;
  3  function avg_sum return number;
  4  end test_1;
  5  /

Package created.

SQL> create or replace package body test_1 is
  2  today date;
  3  procedure dis_date is
  4  begin
  5  dbms_output.put_line('hi');
  6  end;
  7  function avg_sum return number is
  8  begin
  9  today:=sysdate;
 10  dbms_output.put_line(today);
 11  return (12*10);
 12  end;
 13  end test_1;
 14  /

Package body created.

SQL> select test_1.avg_sum from dual;
   AVG_SUM
----------
       120

1 row selected.

22/02/2008 12:56:21

Current date is there.

Regards
Michel
Re: error in executing the package [message #301929 is a reply to message #301924] Fri, 22 February 2008 06:02 Go to previous messageGo to next message
maschender
Messages: 9
Registered: February 2008
Junior Member
Hi Michel,

Still the date is not displayed.

Regards,
Maschender
Re: error in executing the package [message #301930 is a reply to message #301929] Fri, 22 February 2008 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How can we know what you don't post?
Also, you didn't tell us what is Oracle version.

Regards
Michel
Re: error in executing the package [message #301931 is a reply to message #301929] Fri, 22 February 2008 06:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you actually CALL the package, or just compile it?
Show a copy-paste from the sqlplus session where you called it.
use [code][/code] around it for readability.


[Edit: Ah, took me too long to answer. Michel already told you the same]

[Updated on: Fri, 22 February 2008 06:11]

Report message to a moderator

Re: error in executing the package [message #301935 is a reply to message #301931] Fri, 22 February 2008 06:44 Go to previous messageGo to next message
maschender
Messages: 9
Registered: February 2008
Junior Member
Hi Michel and Frank,

Thanks for your replies. I am using 8i version and did everything whatever you told. But, still the date is not displayed. I am attaching the screenshot of that. Please look into this.

Regards,
Maschender
Re: error in executing the package [message #301936 is a reply to message #301935] Fri, 22 February 2008 06:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In 8i, you have to execute "exec null;" after SELECT as SQL*Plus calls dbms_output only after a PL/SQL block.
Or you can use:
SQL> declare
  2    n number;
  3  begin
  4    n := test_1.avg_sum;
  5  end;
  6  /
22/02/2008 13:58:19

PL/SQL procedure successfully completed.

Everything would be faster if you started to post what you did and what is your version.
Next time, do it and read OraFAQ Forum Guide, especially "How to format your post?" section.

Regards
Michel
Re: error in executing the package [message #301941 is a reply to message #301936] Fri, 22 February 2008 07:10 Go to previous messageGo to next message
maschender
Messages: 9
Registered: February 2008
Junior Member
Thanks Michel,

It is working fine now. One more observation, even though the dbms is in the function, when I execute the procedure, the date is displayed. How can I understand this scenario?

Regards,
Maschender
Re: error in executing the package [message #301942 is a reply to message #301941] Fri, 22 February 2008 07:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand your description.
Copy and paste what you do just like as I did.

Regards
Michel

[Updated on: Fri, 22 February 2008 07:18]

Report message to a moderator

Re: error in executing the package [message #301946 is a reply to message #301942] Fri, 22 February 2008 07:34 Go to previous message
maschender
Messages: 9
Registered: February 2008
Junior Member
Michel,

It has been resolved. Thanks once again.

Regards,
Maschender
Previous Topic: commit or rollback
Next Topic: Re-write SQL in Single Query? (merged)
Goto Forum:
  


Current Time: Thu Dec 08 04:16:26 CST 2016

Total time taken to generate the page: 0.14891 seconds