Home » Infrastructure » Unix » SP2-0670: Internal number conversion failed even if I return a number (Unix-Oracle 10g)
SP2-0670: Internal number conversion failed even if I return a number [message #625564] Fri, 10 October 2014 05:02 Go to next message
prateetibasu
Messages: 1
Registered: October 2014
Junior Member
Hi,

My shell script looks like


LOG_FILE="/o2/appl/cnvrtBkgsMonitor/logs/monNewCriticalRlocs.log"

echo "$`date +"%c"` : Starting monNewCriticalRloc" >> $LOG_FILE

export ORACLE_HOME=/opt/oracle/10.2.0.4

$ORACLE_HOME/bin/sqlplus -S 'ods_mon_usr/omu_O222R@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = o2uatdb11)(PORT = 1521))(CONNECT_DATA =(SERVICE_NAME =O2UAT_APP)))' <<EOF

VARIABLE return_cd number
VARIABLE error_msg VARCHAR2(255)
VARIABLE error_typ VARCHAR2(5)

SET PAGESIZE 0;
SET FEEDBACK OFF;
SET SERVEROUT ON;
DECLARE
O_NEW_REJECTION_COUNT NUMBER;
BEGIN

COUNTNEWCRITICALRLOC(O_NEW_REJECTION_COUNT);
:return_cd:= O_NEW_REJECTION_COUNT;
end;
/
print :return_cd
exit :return_cd
EOF

RETURN_CD=$?
echo "RETURN_CD="$RETURN_CD >> $LOG_FILE


if (( $RETURN_CD < 1 ))
then
echo "No new critical rlocs in the recent times " >> $LOG_FILE
exit 0
else
echo "Huuray!! New Critical Rlocs!!Total number of critical rlocs "$RETURN_CD >> $LOG_FILE
exit 1
fi


The called stored proc returns a number but still I get the following error


COUNTNEWCRITICALRLOC(O_NEW_REJECTION_COUNT);
*
ERROR at line 5:
ORA-06550: line 5, column 1:
PLS-00201: identifier 'COUNTNEWCRITICALRLOC' must be declared
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored




SP2-0670: Internal number conversion failed
Usage: { EXIT | QUIT } [ SUCCESS | FAILURE | WARNING | n |
<variable> | :<bindvariable> ] [ COMMIT | ROLLBACK ]
Re: SP2-0670: Internal number conversion failed even if I return a number [message #625579 is a reply to message #625564] Fri, 10 October 2014 09:47 Go to previous message
Michel Cadot
Messages: 67131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have no value in the variable as the error comes before you assign it.

Previous Topic: Unable to initiate crontab Job
Next Topic: How to pass parameter from file to sqlplus in UNIX?
Goto Forum:
  


Current Time: Sun May 24 18:48:49 CDT 2020