dgupta70
asked on
ORA-06550 in executing a pl/sql in shell
I am executiing this shell script
#!/usr/bin/ksh
/usr/bin/mv C:/Inetpub/ftproot/*.*.lds sfile.* E:/errorfile
/usr/bin/cp E:/errorfile/*.*.ldssfile. * E:/errorfile/errct.dat
#set -x
export ORACLE_SID=LDSS2
sqlplus -s ldss/bum021 2>DEVNULL <<EOF
truncate table error_data;
exit;
EOF
sqlldr ldss/bum021 errorct.ctl
sqlplus -s ldss/bum021 2>DEVNULL <<EOF1
exec newcode.sql;
exit;
EOF1
/usr/bin/mv E:/errorfile/errct.dat E:/errorfile/archive
Error
====
Commit point reached - logical record count 12
BEGIN "newcode.sql"; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'newcode.sql' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
My newcode.sql is
=============
create or replace procedure error_correct as
c_internal_identifier varchar(13);
c_bill_message_code varchar2(3);
c_tcop varchar2(4);
c_billing_date date;
record_count number := 0;
billing_count number := 0;
billing_history_count number := 0;
update_count number := 0;
insert_count number := 0;
no_action_count number := 0;
cursor c1 is select substr(long_line,2,13),sub str(long_l ine,15,3), substr(lon g_line,18, 4),
to_date(substr(long_line,2 2,6),'YYMM DD')
from error_data where substr(long_line,1,1) = 'D';
BEGIN
select to_number(substr(long_line ,38)) into record_count from error_data where substr(long_line,1,1) = 'T';
if record_count > 0 then
open c1;
loop
fetch c1 into c_internal_identifier,c_bi ll_message _code,c_tc op,c_billi ng_date;
exit when c1%notfound;
select count(*) into billing_count
from billing_history
where internal_identifier = c_internal_identifier
and event_date = c_billing_date
and type = c_tcop;
if billing_count > 0 then
select count(*) into billing_history_count
from bill_message_history
where internal_identifier = c_internal_identifier
and event_date = c_billing_date;
if billing_history_count > 0 then
update bill_message_history set status = 'N'
where internal_identifier = c_internal_identifier
and event_date = c_billing_date;
update_count := update_count + 1;
else
insert into bill_message_history values (c_internal_identifier,c_b illing_dat e,NULL,'N' );
insert_count := insert_count + 1;
end if;
else
no_action_count := no_action_count + 1;
end if;
end loop;
else
-- NULL;
insert into log_table values ('No records found',0);
end if;
insert into log_table values ('Records Updated',update_count);
insert into log_table values ('Records Inserted',update_count);
insert into log_table values ('Records No Action Count',update_count);
commit;
END error_correct;
/
Doersn't understand what the problem is
/
Can someone help.
Thanks
Deepak
#!/usr/bin/ksh
/usr/bin/mv C:/Inetpub/ftproot/*.*.lds
/usr/bin/cp E:/errorfile/*.*.ldssfile.
#set -x
export ORACLE_SID=LDSS2
sqlplus -s ldss/bum021 2>DEVNULL <<EOF
truncate table error_data;
exit;
EOF
sqlldr ldss/bum021 errorct.ctl
sqlplus -s ldss/bum021 2>DEVNULL <<EOF1
exec newcode.sql;
exit;
EOF1
/usr/bin/mv E:/errorfile/errct.dat E:/errorfile/archive
Error
====
Commit point reached - logical record count 12
BEGIN "newcode.sql"; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'newcode.sql' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
My newcode.sql is
=============
create or replace procedure error_correct as
c_internal_identifier varchar(13);
c_bill_message_code varchar2(3);
c_tcop varchar2(4);
c_billing_date date;
record_count number := 0;
billing_count number := 0;
billing_history_count number := 0;
update_count number := 0;
insert_count number := 0;
no_action_count number := 0;
cursor c1 is select substr(long_line,2,13),sub
to_date(substr(long_line,2
from error_data where substr(long_line,1,1) = 'D';
BEGIN
select to_number(substr(long_line
if record_count > 0 then
open c1;
loop
fetch c1 into c_internal_identifier,c_bi
exit when c1%notfound;
select count(*) into billing_count
from billing_history
where internal_identifier = c_internal_identifier
and event_date = c_billing_date
and type = c_tcop;
if billing_count > 0 then
select count(*) into billing_history_count
from bill_message_history
where internal_identifier = c_internal_identifier
and event_date = c_billing_date;
if billing_history_count > 0 then
update bill_message_history set status = 'N'
where internal_identifier = c_internal_identifier
and event_date = c_billing_date;
update_count := update_count + 1;
else
insert into bill_message_history values (c_internal_identifier,c_b
insert_count := insert_count + 1;
end if;
else
no_action_count := no_action_count + 1;
end if;
end loop;
else
-- NULL;
insert into log_table values ('No records found',0);
end if;
insert into log_table values ('Records Updated',update_count);
insert into log_table values ('Records Inserted',update_count);
insert into log_table values ('Records No Action Count',update_count);
commit;
END error_correct;
/
Doersn't understand what the problem is
/
Can someone help.
Thanks
Deepak
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think a split is in order
/usr/bin/mv C:/Inetpub/ftproot/*.*.lds
/usr/bin/cp E:/errorfile/*.*.ldssfile.
#set -x
export ORACLE_SID=LDSS2
sqlplus -s ldss/bum021 2>DEVNULL <<EOF
truncate table error_data;
exit;
EOF
sqlldr ldss/bum021 errorct.ctl
sqlplus -s ldss/bum021 2>DEVNULL <<EOF1
@ newcode.sql;
exit;
EOF1
/usr/bin/mv E:/errorfile/errct.dat E:/errorfile/archive