Link to home
Start Free TrialLog in
Avatar of dgupta70
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/*.*.ldssfile.*  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),substr(long_line,15,3),substr(long_line,18,4),
                to_date(substr(long_line,22,6),'YYMMDD')
           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_bill_message_code,c_tcop,c_billing_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_billing_date,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
ASKER CERTIFIED SOLUTION
Avatar of shuminzhou
shuminzhou

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of anand_2000v
#!/usr/bin/ksh
/usr/bin/mv C:/Inetpub/ftproot/*.*.ldssfile.*  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
@ newcode.sql;
exit;
EOF1
/usr/bin/mv E:/errorfile/errct.dat E:/errorfile/archive
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I think a split is in order