Home » SQL & PL/SQL » SQL & PL/SQL » Problem in getting Date Range Data from a Procedure
Problem in getting Date Range Data from a Procedure [message #237334] Mon, 14 May 2007 03:20 Go to next message
amit85
Messages: 11
Registered: May 2007
Location: DELHI
Junior Member

I have created two procedures

GetDayData It's results are ok for a day
GetData It's was created successfully but
when executing showing error.

I am trying to get results from GetData Procedure
for a period range but its giving error as under:-



SQL> create or replace Procedure GetData(vtype in number,
2 vclient in varchar2,
3 dtfrom in date, dtto in date,vscrip in varchar2,
4 vsymbol in varchar2, vtotstt out number) IS
5 zdate date;
6 vstt number(14,2);
7 begin
8 zdate := dtfrom;
9 vtotstt := 0;
10 While zdate <= dtto
11 Loop
12 vstt :=0;
13 GetDayData(vtype,vclient,zdate,vscrip,vsymbol,vstt);
14 vtotstt := vtotstt+nvl(vstt,0);
15 zdate := zdate+1;
16 End Loop;
17 End GetData;
18 /

Procedure created.

SQL> declare
2 x number(14,2) :=0;
3 y number(14,2) :=0;
4 dttemp date;
5 begin
6 GetDayData(3,'PRO01','15-jan-2007','','DABUR-29MAR2007',x);
7 dbms_output.put_line('Result = '||x);
8 end;
9
10 /
Result = 46.36

PL/SQL procedure successfully completed.

SQL> declare
2 x number(14,2) :=0;
3 y number(14,2) :=0;
4 dttemp date;
5 begin
6 GetData(1,'CA001','26-dec-2006','25-jan-2007','RELI01','RELI01',x);
7 dbms_output.put_line('Result = '||x);
8 end;
9
10
11
12 /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SYSADM.GETDAYDATA", line 29
ORA-06512: at "SYSADM.GETDATA", line 13
ORA-06512: at line 6


Thanks in advance

Amit
Re: Problem in getting Date Range Data from a Procedure [message #237337 is a reply to message #237334] Mon, 14 May 2007 03:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ You have to put your code BETWEEN (CODE) and (/CODE) tags not after
2/ You have to give your Oracle version (4 decimals)
3/ Your function GetDayData did not find any data for the parameters you gave so it returns "no data found".

Regards
Michel
Re: Problem in getting Date Range Data from a Procedure [message #237344 is a reply to message #237337] Mon, 14 May 2007 03:50 Go to previous messageGo to next message
amit85
Messages: 11
Registered: May 2007
Location: DELHI
Junior Member

I appreciate your immediate reply.

Oracle Version := 8.1.5.0.0

There is data in given period I am sure that there is something wrong in Procedure GetData.

Pl. see the problem :-

SQL> create or replace Procedure GetData(vtype in number, 
  2                    vclient in sysadm.partymst.par_code%type,                          
  3             dtfrom in date, dtto in date,vscrip in varchar2, 
  4      vsymbol in varchar2, vtotstt out number) IS
  5  zdate  date;
  6  vstt  number(14,2);
  7  begin
  8   zdate        := dtfrom;
  9   vtotstt      := 0;
 10   While zdate <= dtto 
 11       Loop
 12    vstt  :=0;
 13           GetDayData(vtype,vclient,zdate,vscrip,vsymbol,vstt);
 14           vtotstt := vtotstt+nvl(vstt,0);        
 15    zdate := zdate+1;
 16       End Loop;
 17  End GetData;
 18  /

Procedure created.

SQL>  declare
  2    x number(14,2) :=0;
  3    y number(14,2) :=0;
  4    dttemp date;
  5   begin
  6   GetDayData(1,'CA001','11-jan-2007','RELI01','RELI01',x);
  7   dbms_output.put_line('Result = '||x);
  8   end;
  9  /

PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON
SQL> /
Result = 161.09

PL/SQL procedure successfully completed.

SQL> declare
  2    x number(14,2) :=0;
  3    y number(14,2) :=0;
  4    dttemp date;
  5   begin
  6   GetData(1,'CA001','26-dec-2006','25-jan-2007','RELI01','RELI01',x);
  7   dbms_output.put_line('Result = '||x);
  8   end;
  9  /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SYSADM.GETDAYDATA", line 29
ORA-06512: at "SYSADM.GETDATA", line 13
ORA-06512: at line 6

Re: Problem in getting Date Range Data from a Procedure [message #237345 is a reply to message #237344] Mon, 14 May 2007 03:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Line 29 of getdaydata probably contains a select..into..
This query returns no rows for the values you provided.
Re: Problem in getting Date Range Data from a Procedure [message #237350 is a reply to message #237345] Mon, 14 May 2007 04:01 Go to previous messageGo to next message
amit85
Messages: 11
Registered: May 2007
Location: DELHI
Junior Member


Pl. help me in fixing out the error, when there is data for a given period it's not giving any error
but data is not found it's giving error.

SQL> declare
  2    x number(14,2) :=0;
  3    y number(14,2) :=0;
  4    dttemp date;
  5   begin
  6   GetData(1,'CA001','26-dec-2006','25-jan-2007','RELI01','RELI01',x);
  7   dbms_output.put_line('Result = '||x);
  8   end;
  9  /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SYSADM.GETDAYDATA", line 29
ORA-06512: at "SYSADM.GETDATA", line 13
ORA-06512: at line 6

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    x number(14,2) :=0;
  3    y number(14,2) :=0;
  4    dttemp date;
  5   begin
  6   GetData(1,'CA001','11-jan-2007','11-jan-2007','RELI01','RELI01',x);
  7   dbms_output.put_line('Result = '||x);
  8*  end;
SQL> /
Result = 161.09

PL/SQL procedure successfully completed.



Re: Problem in getting Date Range Data from a Procedure [message #237355 is a reply to message #237350] Mon, 14 May 2007 04:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You are searching for data and they do not exist.
This is the issue.

This is not an error.
This is the expected behaviour.
You can trap this exception if you want: just add an exception block and handle this "no data" exception.

Regards
Michel
Re: Problem in getting Date Range Data from a Procedure [message #237368 is a reply to message #237355] Mon, 14 May 2007 04:58 Go to previous messageGo to next message
amit85
Messages: 11
Registered: May 2007
Location: DELHI
Junior Member

Thank you very much.

After providing EXCEPTION part its working fine.

Amit
Re: Problem in getting Date Range Data from a Procedure [message #237372 is a reply to message #237368] Mon, 14 May 2007 05:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Just out of curiosity. What does your exception block look like?
Not when others then null; right??
Re: Problem in getting Date Range Data from a Procedure [message #237378 is a reply to message #237372] Mon, 14 May 2007 05:25 Go to previous messageGo to next message
amit85
Messages: 11
Registered: May 2007
Location: DELHI
Junior Member

Yes, you are right i have kept this only.

Regards,

Amit
Re: Problem in getting Date Range Data from a Procedure [message #237390 is a reply to message #237378] Mon, 14 May 2007 06:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Aaargh!
How can you say that whatever the error is everything is fine? ./fa/1580/0/

Regards
Michel

Re: Problem in getting Date Range Data from a Procedure [message #237426 is a reply to message #237350] Mon, 14 May 2007 08:10 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
amit85 wrote on Mon, 14 May 2007 05:01

SQL> declare
  2    x number(14,2) :=0;
  3    y number(14,2) :=0;
  4    dttemp date;
  5   begin
  6   GetData(1,'CA001','26-dec-2006','25-jan-2007','RELI01','RELI01',x);
  7   dbms_output.put_line('Result = '||x);
  8   end;
  9  /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SYSADM.GETDAYDATA", line 29
ORA-06512: at "SYSADM.GETDATA", line 13
ORA-06512: at line 6

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    x number(14,2) :=0;
  3    y number(14,2) :=0;
  4    dttemp date;
  5   begin
  6   GetData(1,'CA001','11-jan-2007','11-jan-2007','RELI01','RELI01',x);
  7   dbms_output.put_line('Result = '||x);
  8*  end;
SQL> /
Result = 161.09

PL/SQL procedure successfully completed.




Ummm, here is my take on this. I notice that you are getting NO DATA FOUND when you use the first part, but data when you use the second. To me, this is because you cannot have any data between the STRINGS '26-dec-2006' and '25-jan-2007'. '26' is greater than '25,' so you will never get any data.
Notice the second block, where the two strings are the same then you get data.
You have one of two errors occurring here. If you think you are sending DATEs to this function, then why would you get data for a single date (January 11th 2007) but no data for a range of December 26 2006 through January 25 2007 which would incorporate the 11th of January 2007? The other error is that you are sending character strings to a function that expects a date. You'll have to address these issues.
Re: Problem in getting Date Range Data from a Procedure [message #237427 is a reply to message #237390] Mon, 14 May 2007 08:16 Go to previous messageGo to next message
amit85
Messages: 11
Registered: May 2007
Location: DELHI
Junior Member

There are only two cases
1 Data is available
2 Data is not available

I want any of the cases above but without any error

when i used

EXCEPTION
WHEN NO_DATA_FOUND THEN
null;
or

EXCEPTION
WHEN OTHERS THEN
null;

In both the exception cases it's working fine;
Re: Problem in getting Date Range Data from a Procedure [message #237429 is a reply to message #237427] Mon, 14 May 2007 08:21 Go to previous messageGo to next message
amit85
Messages: 11
Registered: May 2007
Location: DELHI
Junior Member

But I would like to mention here that I have used

EXCEPTION
WHEN NO_DATA_FOUND THEN
null;

in my procedure....

Regards

Amit
Re: Problem in getting Date Range Data from a Procedure [message #237432 is a reply to message #237427] Mon, 14 May 2007 08:23 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
That's because of the poor and sloppy coding. Of course these exception blocks are going to work, because you code is invalid.

Please explain why you think you should get data for January 11th 2007, but no data for the range December 26 2006 through January 25th 2007.

Please reread my other answer and use DATEs (hint: TO_DATE functino), not character strings as the values you are passing to the functino.
Re: Problem in getting Date Range Data from a Procedure [message #237435 is a reply to message #237432] Mon, 14 May 2007 08:36 Go to previous message
amit85
Messages: 11
Registered: May 2007
Location: DELHI
Junior Member

I got your point...

Thank you very much
using to_date function for dates (parameters)
there is no error it's working fine.
You are great.
Regards,

Amit



Previous Topic: Deadlock by deletion
Next Topic: XSQLConfig.xml
Goto Forum:
  


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

Total time taken to generate the page: 0.11205 seconds