Hello Tom,

I have created the next procedure into the database:

PROCEDURE DACQP00_PIVOT_DATES (p_schema in VARCHAR2, p_party out TIMESTAMP) IS

BEGIN

BEGIN
EXECUTE IMMEDIATE ('SELECT TO_TIMESTAMP(FILE_CREATION_DATE,''DD/MM/YY HH24:MI:SS,FF'') FROM '|| p_schema ||'.DACQT_PIVOT_FILES WHERE FILE_NAME = ''PARTY''') INTO p_party;
EXCEPTION
WHEN NO_DATA_FOUND THEN p_party := TO_DATE('31/12/9999','DD/MM/YYYY');
END;

END;

When I execute the procedure into the database directly, the procedure works fine, extracting the timestamp from the table DACQT_PIVOT_FILES. The p_schema variable is defined to read from different database schemas using the same procedure.

When I execute the procedure using a shell script I obtain the error: "The execution has finished with error ORA-01830: date format picture ends before converting entire input string" and I don´t understand why.

Please, can you tell me if there is a problem to use the TIMESTAMP function?

Thank you very much.

Best regards.

*****************

The procedure DACQP00_PIVOT_DATES is executed by other main procedure:

PROCEDURE DACQP00_PARTY (p_full_load BOOLEAN, p_country VARCHAR2 ) IS

vs_schema VARCHAR2(30) := 'DAC01';

vc_party_exec_date TIMESTAMP;

DACQP00_PIVOT_DATES (vs_schema, vc_party_exec_date );

IF vc_party_exec_date <> TO_DATE('31/12/9999','DD/MM/YYYY') THEN

....

END IF;

END;

*****************

The Shell Script that execute the procedure DACQP00_PARTY is:

function Usage
{
echo "Error in parametres"
echo "Usage $0 <User> <Password> <tns_entry> <procedure> <TRUE-FALSE> <pais> <Repertoire Fichier Log> <Nom Fichier Log>"
echo "ejemplo $0 dac00 pass234 DAC00_PP2_SCAN DAC00.DACQP00_PARTY TRUE FR $UNXLOG idq_ep01.log"
exit 1
}

# Function for controle parametres
# --------------------------------------------------------------------------
function ControlerParametres
{
if [ $# -ne 8 ]
then
Usage
fi

USER=$1
PASSWORD=$2
CONEXION=$3
PROCEDURE=$4
TRUEOFALSE=$5
PAIS=$6
FICLOG=$7
NOMLOG=$8
}

# Programa principal
# -----------------
ControlerParametres $*

#sqlplus -s ${USER}/${PASSWORD} 1>>$FICLOG/$NOMLOG 2>&1 <<-FIN
OUTPUT=$(sqlplus -s $USER/$PASSWORD@$CONEXION >> $FICLOG/$NOMLOG <<-FIN
set heading off feedback off
set serveroutput on
BEGIN
$PROCEDURE($TRUEOFALSE,'$PAIS');
DBMS_OUTPUT.PUT_LINE(0);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE);
END;
/
FIN)


echo 'code retour dps del pl'
cat $OUTPUT | grep 'ORA-' > /dev/null
if [ $? -eq 0 ]
then
#echo $OUTPUT
exit 99
#exit
fi

exit 0

*************************

Thank you very much for your help.

SQL> create table t ( FILE_CREATION_DATE timestamp);

Table created.

SQL> insert into t values (systimestamp);

1 row created.

SQL>
SQL> alter session set nls_timestamp_format = 'DD/MM/YY HH24:MI:SS,FF';

Session altered.

SQL>
SQL> select to_timestamp(FILE_CREATION_DATE,'DD/MM/YY HH24:MI:SS,FF') from t;

TO_TIMESTAMP(FILE_CREATION_DATE,'DD/MM/YYHH24:MI:SS,FF')
---------------------------------------------------------------------------
27/03/17 09:28:10,352000

1 row selected.

--
-- that looks ok, but in reality we were converting FILE_CREATION_DATE to a string,
-- because the first param of to_timestamp has to be a string.  So we were in fact
-- relying on the format masks to match.  If I change the format mask (eg the 
-- shell script might have a different value set)
--

SQL>
SQL> alter session set nls_timestamp_format = 'MON/DD/YYYY';

Session altered.

SQL>
SQL> select to_timestamp(FILE_CREATION_DATE,'DD/MM/YY HH24:MI:SS,FF') from t;
select to_timestamp(FILE_CREATION_DATE,'DD/MM/YY HH24:MI:SS,FF') from t
                    *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected