Updating records on a oracle database using shell script

Question

I need to develop a shell script that updates some records on a oracle database, this programs read an input .txt file and then updates the record when some conditions are met, but for some reason this records were not updated, it seems that there's a problem with a variable that the plsql reads in order to update the records.

This is the shell script code

    ## #!/bin/sh

. /usr/local/bin/oracle.profile.prod
. /usr/local/bin/bscs.profile.prod      
hostname=`uname -n`
basedato=BSCS1REP

if [[ $basedato = "BSCS2PROD" && $hostname = "comp35" ]];then                                  
    export Y/Shell
    export Y/Sql
    export Y/Log
    export Y/Cfg
    . Y.MM.txt
elif [ $basedato = "BSCS1REP" and $hostname = "comp44" ]; then
    export X/Shell
    export X/Sql
    export X/Log
    export X/Cfg
    . X/.MM.txt
else                                                                                                                                
   echo "ERROR, Servidor no valido para la ejecucion de este programa"                             
   echo "Base de Datos = "$basedato                                                              
   echo "Maquina = "$hostname                                                  
   exit 1
fi              
FECHA=$(date "+%Y%m%d_%H_%M_%S")


cat $RUTA_CFG/MSISDN.txt | while read elemento
do
set $elemento
elemento=$1

echo "El registro es $elemento "insertado""

sqlplus -S $USER/$PASS@$basedato @$RUTA_PLS/Num_Act.sql $elemento > $RUTA_LOG/test.txt

done

rm -f $RUTA_CFG/Act_num.ctl

echo "DONE"

As you can see this script reads an input data, is the following:

123456789

Just this simple string.

Now this is the invoked Pl/sql

        ALTER SESSION SET OPTIMIZER_GOAL = CHOOSE;
ALTER SESSION SET NLS_LANGUAGE   = AMERICAN;
ALTER SESSION SET NLS_TERRITORY  = AMERICA;

SET serveroutput ON SIZE 1000000
SET term ON

WHENEVER SQLERROR CONTINUE

DECLARE

elemento constant varchar2(30) := '$1';

BEGIN

update directory_number dn
 set dn.dn_status = 'a' 
  where dn.dn_num = ('$1')
   and dn.dn_status in ('d','t','Z','f','r');

   DBMS_OUTPUT.PUT_LINE (elemento);


  END;
/
EXIT;

I added a log file to see what's going on, and it returned the following:

Session altered.

Session altered.

Session altered.

$1

PL/SQL procedure successfully completed.

The variable stands $1, and it should be the input data number.

Could be a sintax error ?, a variable declaration ?

When I put aside the variables and put directly the valor of the desire number in the plsql it updated the record with no problems.

Any help with be appreciated, thank so much for your time !

Answer 1

In your PL/SQL code try replacing $1 with &1.

See this link for an example and description.