Home » SQL & PL/SQL » SQL & PL/SQL » error ORA-0650 +PLS-0905
| error ORA-0650 +PLS-0905 [message #10769] |
Mon, 16 February 2004 18:44  |
shilpi
Messages: 2
Registered: October 2003
|
Junior Member |
|
|
I m getting error when i execute my procedure Mis
the error is:
BEGIN mis; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object RBS.MIS is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
please help
my procedure is as below:
CREATE OR REPLACE procedure MIS
As
rtl_sdr Number;
spi_sdr Number;
bsn_sdr Number:=0;
mtn_sdr Number:=0;
net_spi_sdr Number:=0;
out_file utl_file.file_type;
err_file utl_file.file_type;
Status1 VarChar2(100);
INR Number:=68.03050;
spi_sdr_till Number;
rtl_sdr_till Number;
bsn_sdr_till Number:=0;
mtn_sdr_till Number:=0;
gr_sdr Number;
gr_inr Number;
gr_spr Number;
gr_mtd Number;
spi_mtd Number:=23206889.506;
rtl_mtd Number:=1271510.810;
Begin
out_file:=utl_file.fopen('/data2/ciber/spice/Reports','config.txt','w');
err_file:=utl_file.fopen('/data2/ciber/spice/Reports','ERR.txt','w');
Begin -- SPI SDR's
select sum(totalchargevalue00) into spi_sdr
from rbs_statistics,rbs_filerecords
where rbs_statistics.filerecordid = rbs_filerecords.filerecordid
and rbs_filerecords.status = 'S'
and aggregated = 'Y'
and trunc(AGGREGATIONtime) >trunc(sysdate-2)
and trunc(AGGREGATIONtime) <=trunc(sysdate-1);
Exception
when others then
utl_file.put_Line(err_file,'SPI SDR'||sqlerrm);
End; -- SPI SDR's
Begin -- RTL SDR's
select sum(totalchargevalue00) into rtl_sdr
from rbs_statistics,rbs_filerecords
where rbs_statistics.filerecordid = rbs_filerecords.filerecordid
and rbs_filerecords.status = 'S'
and fromplmn='ROAMR'
and trunc(AGGREGATIONtime) >trunc(sysdate-2)
and trunc(AGGREGATIONtime) <=trunc(sysdate-1);
Exception
when others then
utl_file.put_Line(err_file,'RTL SDR'||sqlerrm);
End; -- RTL SDR's
Begin -- BSNL SDR's
select nvl(SUM(totalchargevalue00),0) into bsn_sdr
from rbs_statistics,rbs_filerecords
where rbs_statistics.filerecordid = rbs_filerecords.filerecordid
and rbs_filerecords.status = 'S'
and fromplmn='ROAMB'
and trunc(AGGREGATIONtime) >trunc(sysdate-2)
and trunc(AGGREGATIONtime) <=trunc(sysdate-1);
Exception
when others then
utl_file.put_Line(err_file,'BSNL SDR'||sqlerrm);
End; -- BSNL SDR's
Begin -- MTNL SDR's
select nvl(sum(totalchargevalue00),0) into mtn_sdr
from rbs_statistics,rbs_filerecords
where rbs_statistics.filerecordid = rbs_filerecords.filerecordid
and rbs_filerecords.status = 'S'
and fromplmn='ROAMM'
and trunc(AGGREGATIONtime) >trunc(sysdate-2)
and trunc(AGGREGATIONtime) <=trunc(sysdate-1);
Exception
when others then
utl_file.put_Line(err_file,'MTNL SDR'||sqlerrm);
End; -- MTNL SDR's
Begin --SPI SDR TILL DATE
select sum(totalchargevalue00) into spi_sdr_till
from rbs_statistics,rbs_filerecords
where rbs_statistics.filerecordid = rbs_filerecords.filerecordid
and rbs_filerecords.status = 'S'
and aggregated = 'Y'
and trunc(AGGREGATIONtime) <trunc(sysdate)
;
Exception
when others then
utl_file.put_Line(err_file,'SPI SDR TILL'||sqlerrm);
End; -- SPI SDR TILL DATE
Begin --RTL SDR TILL DATE
select sum(totalchargevalue00) into rtl_sdr_till
from rbs_statistics,rbs_filerecords
where rbs_statistics.filerecordid = rbs_filerecords.filerecordid
and rbs_filerecords.status = 'S'
and fromplmn='ROAMR'
and trunc(AGGREGATIONtime) <trunc(sysdate);
Exception
when others then
utl_file.put_Line(err_file,'RTL SDR TILL'||sqlerrm);
End; -- RTL SDR TILL DATE
Begin --BSNL SDR TILL DATE
select nvl(sum(totalchargevalue00),0) into bsn_sdr_till
from rbs_statistics,rbs_filerecords
where rbs_statistics.filerecordid = rbs_filerecords.filerecordid
and rbs_filerecords.status = 'S'
and fromplmn='ROAMB'
and trunc(AGGREGATIONtime) <trunc(sysdate)
;
Exception
when others then
utl_file.put_Line(err_file,'BSNL SDR TILL'||sqlerrm);
End; -- BSNL SDR TILL DATE
Begin --MTNL SDR TILL DATE
select nvl(sum(totalchargevalue00),0) into mtn_sdr_till
from rbs_statistics,rbs_filerecords
where rbs_statistics.filerecordid = rbs_filerecords.filerecordid
and rbs_filerecords.status = 'S'
and fromplmn='ROAMM'
and trunc(AGGREGATIONtime) <trunc(sysdate)
;
Exception
when others then
utl_file.put_Line(err_file,'MTNL SDR TILL'||sqlerrm);
End; -- MTNL SDR TILL DATE
net_spi_sdr:=(spi_sdr-rtl_sdr-bsn_sdr-mtn_sdr);
gr_sdr:= net_spi_sdr+rtl_sdr+bsn_sdr+mtn_sdr;
gr_inr:= (net_spi_sdr*INR)+(rtl_sdr*INR)+(bsn_sdr*INR)+(mtn_sdr*INR);
gr_spr:=(net_spi_sdr*INR)+((rtl_sdr*INR)*0.33)+((bsn_sdr*INR)*0.33)+((mtn_sdr*INR)*0.33);
gr_mtd:=(net_spi_sdr*INR)+((rtl_sdr*INR)*0.33)+((bsn_sdr*INR)*0.33)+((mtn_sdr*INR)*0.33)+spi_mtd+rtl_mtd;
--(((23206889.505816+(net_spi_sdr*INR))+(1271510.809986+(rtl_sdr*INR*0.33))+((bsn_sdr_till*INR)*0.33)+((mtn_sdr_till*INR)*0.33));
utl_file.put_Line(out_file,'********************************DAILY REVENUE REPORT*******************************************');
utl_file.put_line(out_file,' ');
utl_file.put_line(out_file,'Date: '||(sysdate));
utl_file.put_line(out_file,'Total Revenue(IN-Bound Roamers): '||round(((rtl_sdr*INR)*0.33),2));
utl_file.put_line(out_file,'Total Revenue(OUT-Bound Roamers): ');
utl_file.put_line(out_file,' ');
utl_file.put_line(out_file,'********************************Operator-Wise Listing******************************************');
utl_file.put_line(out_file,Rpad('Name:',9,' ')||Lpad('SPICE',20,' ')||'!'||Lpad('RTL',20,' ')||'!'||Lpad('BSNL',20,' ')||'!'||Lpad('MTNL',22,' ')||'!');
utl_file.put_line(out_file,'=============================!====================!====================!======================!');
utl_file.put_line(out_file,Rpad('SDR:',9,' ')||lpad(round(nvl((net_spi_sdr),0),2),20,' ')||'!'||lpad(round(nvl((rtl_sdr),0),2),20,' ')||'!'||lpad(round(nvl((bsn_sdr),0),2),20,' ')||'!'||lpad(round(nvl((mtn_sdr),0),2),22,' ')||'!');
utl_file.put_line(out_file,'=============================!====================!====================!======================!');
utl_file.put_line(out_file,Rpad('INR:',9,' ')||lpad(round(nvl((net_spi_sdr*INR),0),2),20,' ')||'!'||lpad(round(nvl((rtl_sdr*INR),0),2),20,' ')||'!'||lpad(round(nvl((bsn_sdr*INR),0),2),20,' ')||'!'||lpad(round(nvl((mtn_sdr*INR),0),2),22,' ')||'!');
utl_file.put_line(out_file,'=============================!====================!====================!======================!');
utl_file.put_line(out_file,Rpad('Rev(INR):',9,' ')||lpad(round(nvl((net_spi_sdr*INR),0),2),20,' ')||'!'||lpad(round((nvl((rtl_sdr*INR),0)*0.33),2),20,' ')||'!'||lpad(round((nvl((bsn_sdr*INR),0)*0.33),2),20,' ')||'!'||lpad(round((nvl((mtn_sdr*INR),0)*0.33),2),22,' ')||'!');
utl_file.put_line(out_file,'=============================!====================!====================!======================!');
utl_file.put_line(out_file,Rpad('MTD:',9,' ')||lpad(round((nvl((spi_mtd)+(net_spi_sdr*INR)),0),2),20,' ')||'!'||lpad(round(nvl((rtl_mtd)+(rtl_sdr*INR),0)*0.33),2),20,' ')||'!'||lpad(round(nvl((bsn_sdr_till*INR*0.33),0),2),20,' ')||'!'||lpad(round(nvl((mtn_sdr_till*INR*0.33),0),2),22,' ')||'!');
utl_file.put_line(out_file,'***********************************************************************************************');
utl_file.put_line(out_file,'===========');
utl_file.put_line(out_file,'GROSS TOTAL');
utl_file.put_line(out_file,'===========');
utl_file.put_line(out_file,'SDR: '|| round(gr_sdr,2));
utl_file.put_line(out_file,'INR: '|| round(gr_inr,2));
utl_file.put_line(out_file,'Spice Revenue(INR): '|| round(gr_spr,2));
utl_file.put_line(out_file,'MTD(INR): '|| round(gr_mtd,2));
utl_file.put_line(out_file,'***********************************************************************************************');
utl_file.put_line(out_file,' ');
utl_file.put_line(out_file,'Abbreviations Used: ');
utl_file.put_line(out_file,'MTD=Amount Till Date(INR)');
utl_file.put_line(out_file,'INR=Indian National Rupees');
utl_file.put_line(out_file,'SDR=Standard Dollar Rate');
utl_file.put_line(out_file,'Rev(INR)=Spice Revenenue');
utl_file.put_line(out_file,' ');
utl_file.put_line(out_file,'NOTES:');
utl_file.put_line(out_file,'Revenue calculated on the basis of following formulaes: ');
utl_file.put_line(out_file,'SPICE# Expected Revenue= SDR*68.03050');
utl_file.put_line(out_file,'Other partners# Expected Revenue= 33% of (SDR*68.03050');
utl_file.fclose(out_file);
utl_file.fclose(err_file);
End;
/
|
|
|
|
| Re: error ORA-0650 +PLS-0905 [message #10778 is a reply to message #10769] |
Tue, 17 February 2004 02:14  |
jigar
Messages: 74
Registered: July 2002
|
Member |
|
|
Syntax is proper, so just check the following things
1. Procedure is created successfully?
2. Have u created procedure in the same schema from where you are executing this?
|
|
|
|
| Re: error ORA-0650 +PLS-0905 [message #10782 is a reply to message #10769] |
Tue, 17 February 2004 03:57 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I have marked the lines that need to be corrected by placing two hyphens in front of them and a blank line before and after.
CREATE OR REPLACE procedure MIS
As
rtl_sdr Number;
spi_sdr Number;
bsn_sdr Number:=0;
mtn_sdr Number:=0;
net_spi_sdr Number:=0;
out_file utl_file.file_type;
err_file utl_file.file_type;
Status1 VarChar2(100);
INR Number:=68.03050;
spi_sdr_till Number;
rtl_sdr_till Number;
bsn_sdr_till Number:=0;
mtn_sdr_till Number:=0;
gr_sdr Number;
gr_inr Number;
gr_spr Number;
gr_mtd Number;
spi_mtd Number:=23206889.506;
rtl_mtd Number:=1271510.810;
Begin
out_file:=utl_file.fopen('/data2/ciber/spice/Reports','config.txt','w');
err_file:=utl_file.fopen('/data2/ciber/spice/Reports','ERR.txt','w');
Begin -- SPI SDR's
select sum(totalchargevalue00) into spi_sdr
from rbs_statistics,rbs_filerecords
where rbs_statistics.filerecordid = rbs_filerecords.filerecordid
and rbs_filerecords.status = 'S'
and aggregated = 'Y'
and trunc(AGGREGATIONtime) >trunc(sysdate-2)
and trunc(AGGREGATIONtime) <=trunc(sysdate-1);
Exception
when others then
utl_file.put_Line(err_file,'SPI SDR'||sqlerrm);
End; -- SPI SDR's
Begin -- RTL SDR's
select sum(totalchargevalue00) into rtl_sdr
from rbs_statistics,rbs_filerecords
where rbs_statistics.filerecordid = rbs_filerecords.filerecordid
and rbs_filerecords.status = 'S'
and fromplmn='ROAMR'
and trunc(AGGREGATIONtime) >trunc(sysdate-2)
and trunc(AGGREGATIONtime) <=trunc(sysdate-1);
Exception
when others then
utl_file.put_Line(err_file,'RTL SDR'||sqlerrm);
End; -- RTL SDR's
Begin -- BSNL SDR's
select nvl(SUM(totalchargevalue00),0) into bsn_sdr
from rbs_statistics,rbs_filerecords
where rbs_statistics.filerecordid = rbs_filerecords.filerecordid
and rbs_filerecords.status = 'S'
and fromplmn='ROAMB'
and trunc(AGGREGATIONtime) >trunc(sysdate-2)
and trunc(AGGREGATIONtime) <=trunc(sysdate-1);
Exception
when others then
utl_file.put_Line(err_file,'BSNL SDR'||sqlerrm);
End; -- BSNL SDR's
Begin -- MTNL SDR's
select nvl(sum(totalchargevalue00),0) into mtn_sdr
from rbs_statistics,rbs_filerecords
where rbs_statistics.filerecordid = rbs_filerecords.filerecordid
and rbs_filerecords.status = 'S'
and fromplmn='ROAMM'
and trunc(AGGREGATIONtime) >trunc(sysdate-2)
and trunc(AGGREGATIONtime) <=trunc(sysdate-1);
Exception
when others then
utl_file.put_Line(err_file,'MTNL SDR'||sqlerrm);
End; -- MTNL SDR's
Begin --SPI SDR TILL DATE
select sum(totalchargevalue00) into spi_sdr_till
from rbs_statistics,rbs_filerecords
where rbs_statistics.filerecordid = rbs_filerecords.filerecordid
and rbs_filerecords.status = 'S'
and aggregated = 'Y'
-- and trunc(AGGREGATIONtime) ;
Exception
when others then
utl_file.put_Line(err_file,'SPI SDR TILL'||sqlerrm);
End; -- SPI SDR TILL DATE
Begin --RTL SDR TILL DATE
select sum(totalchargevalue00) into rtl_sdr_till
from rbs_statistics,rbs_filerecords
where rbs_statistics.filerecordid = rbs_filerecords.filerecordid
and rbs_filerecords.status = 'S'
and fromplmn='ROAMR'
-- and trunc(AGGREGATIONtime)
Exception
when others then
utl_file.put_Line(err_file,'RTL SDR TILL'||sqlerrm);
End; -- RTL SDR TILL DATE
Begin --BSNL SDR TILL DATE
select nvl(sum(totalchargevalue00),0) into bsn_sdr_till
from rbs_statistics,rbs_filerecords
where rbs_statistics.filerecordid = rbs_filerecords.filerecordid
and rbs_filerecords.status = 'S'
and fromplmn='ROAMB'
-- and trunc(AGGREGATIONtime) ;
Exception
when others then
utl_file.put_Line(err_file,'BSNL SDR TILL'||sqlerrm);
End; -- BSNL SDR TILL DATE
Begin --MTNL SDR TILL DATE
select nvl(sum(totalchargevalue00),0) into mtn_sdr_till
from rbs_statistics,rbs_filerecords
where rbs_statistics.filerecordid = rbs_filerecords.filerecordid
and rbs_filerecords.status = 'S'
and fromplmn='ROAMM'
-- and trunc(AGGREGATIONtime) ;
Exception
when others then
utl_file.put_Line(err_file,'MTNL SDR TILL'||sqlerrm);
End; -- MTNL SDR TILL DATE
net_spi_sdr:=(spi_sdr-rtl_sdr-bsn_sdr-mtn_sdr);
gr_sdr:= net_spi_sdr+rtl_sdr+bsn_sdr+mtn_sdr;
gr_inr:= (net_spi_sdr*INR)+(rtl_sdr*INR)+(bsn_sdr*INR)+(mtn_sdr*INR);
gr_spr:=(net_spi_sdr*INR)+((rtl_sdr*INR)*0.33)+((bsn_sdr*INR)*0.33)+((mtn_sdr*INR)*0.33);
gr_mtd:=(net_spi_sdr*INR)+((rtl_sdr*INR)*0.33)+((bsn_sdr*INR)*0.33)+((mtn_sdr*INR)*0.33)+spi_mtd+rtl_mtd;
--(((23206889.505816+(net_spi_sdr*INR))+(1271510.809986+(rtl_sdr*INR*0.33))+((bsn_sdr_till*INR)*0.33)+((mtn_sdr_till*INR)*0.33));
utl_file.put_Line(out_file,'********************************DAILY REVENUE REPORT*******************************************');
utl_file.put_line(out_file,' ');
utl_file.put_line(out_file,'Date: '||(sysdate));
utl_file.put_line(out_file,'Total Revenue(IN-Bound Roamers): '||round(((rtl_sdr*INR)*0.33),2));
utl_file.put_line(out_file,'Total Revenue(OUT-Bound Roamers): ');
utl_file.put_line(out_file,' ');
utl_file.put_line(out_file,'********************************Operator-Wise Listing******************************************');
utl_file.put_line(out_file,Rpad('Name:',9,' ')||Lpad('SPICE',20,' ')||'!'||Lpad('RTL',20,' ')||'!'||Lpad('BSNL',20,' ')||'!'||Lpad('MTNL',22,' ')||'!');
utl_file.put_line(out_file,'=============================!====================!====================!======================!');
utl_file.put_line(out_file,Rpad('SDR:',9,' ')||lpad(round(nvl((net_spi_sdr),0),2),20,' ')||'!'||lpad(round(nvl((rtl_sdr),0),2),20,' ')||'!'||lpad(round(nvl((bsn_sdr),0),2),20,' ')||'!'||lpad(round(nvl((mtn_sdr),0),2),22,' ')||'!');
utl_file.put_line(out_file,'=============================!====================!====================!======================!');
utl_file.put_line(out_file,Rpad('INR:',9,' ')||lpad(round(nvl((net_spi_sdr*INR),0),2),20,' ')||'!'||lpad(round(nvl((rtl_sdr*INR),0),2),20,' ')||'!'||lpad(round(nvl((bsn_sdr*INR),0),2),20,' ')||'!'||lpad(round(nvl((mtn_sdr*INR),0),2),22,' ')||'!');
utl_file.put_line(out_file,'=============================!====================!====================!======================!');
utl_file.put_line(out_file,Rpad('Rev(INR):',9,' ')||lpad(round(nvl((net_spi_sdr*INR),0),2),20,' ')||'!'||lpad(round((nvl((rtl_sdr*INR),0)*0.33),2),20,' ')||'!'||lpad(round((nvl((bsn_sdr*INR),0)*0.33),2),20,' ')||'!'||lpad(round((nvl((mtn_sdr*INR),0)*0.33),2),22,' ')||'!');
utl_file.put_line(out_file,'=============================!====================!====================!======================!');
-- utl_file.put_line(out_file,Rpad('MTD:',9,' ')||lpad(round((nvl((spi_mtd)+(net_spi_sdr*INR)),0),2),20,' ')||'!'||lpad(round(nvl((rtl_mtd)+(rtl_sdr*INR),0)*0.33),2),20,' ')||'!'||lpad(round(nvl((bsn_sdr_till*INR*0.33),0),2),20,' ')||'!'||lpad(round(nvl((mtn_sdr_till*INR*0.33),0),2),22,' ')||'!');
utl_file.put_line(out_file,'***********************************************************************************************');
utl_file.put_line(out_file,'===========');
utl_file.put_line(out_file,'GROSS TOTAL');
utl_file.put_line(out_file,'===========');
utl_file.put_line(out_file,'SDR: '|| round(gr_sdr,2));
utl_file.put_line(out_file,'INR: '|| round(gr_inr,2));
utl_file.put_line(out_file,'Spice Revenue(INR): '|| round(gr_spr,2));
utl_file.put_line(out_file,'MTD(INR): '|| round(gr_mtd,2));
utl_file.put_line(out_file,'***********************************************************************************************');
utl_file.put_line(out_file,' ');
utl_file.put_line(out_file,'Abbreviations Used: ');
utl_file.put_line(out_file,'MTD=Amount Till Date(INR)');
utl_file.put_line(out_file,'INR=Indian National Rupees');
utl_file.put_line(out_file,'SDR=Standard Dollar Rate');
utl_file.put_line(out_file,'Rev(INR)=Spice Revenenue');
utl_file.put_line(out_file,' ');
utl_file.put_line(out_file,'NOTES:');
utl_file.put_line(out_file,'Revenue calculated on the basis of following formulaes: ');
utl_file.put_line(out_file,'SPICE# Expected Revenue= SDR*68.03050');
utl_file.put_line(out_file,'Other partners# Expected Revenue= 33% of (SDR*68.03050');
utl_file.fclose(out_file);
utl_file.fclose(err_file);
End;
/
|
|
|
|
Goto Forum:
Current Time: Tue Apr 23 13:19:33 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
