?
Solved

ORA-06550 in executing a pl/sql in shell

Posted on 2005-05-16
7
Medium Priority
?
1,145 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:dgupta70
  • 3
4 Comments
 
LVL 2

Accepted Solution

by:
shuminzhou earned 500 total points
ID: 14011190
Do you mean execute the stored procedure like this:

sqlplus -s ldss/bum021   2>DEVNULL <<EOF1
@newcode.sql; --Creates the proc
exec error_correct;
exit;
EOF1

Or create the proc before hand, then:
sqlplus -s ldss/bum021   2>DEVNULL <<EOF1
exec error_correct;
exit;
EOF1
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 14011308
#!/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
0
 
LVL 13

Assisted Solution

by:anand_2000v
anand_2000v earned 500 total points
ID: 14011384
this @ will execute your script newcode.sql....however this will only create the procedure not execute it...
so a better option will be

#!/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;
exec error_correct;
exit;
EOF1
/usr/bin/mv E:/errorfile/errct.dat E:/errorfile/archiv
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 14528449
I think a split is in order
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question