SQL & PL/SQL

Hello,

I'm using the following plsql procedure to backup the Table VERBRAUCHSMAT

create or replace PROCEDURE EXTERNBACKUP_VERBRAUCHSMAT
AS
--DECLARE
export_file        UTL_FILE.FILE_TYPE;
l_line            VARCHAR2(4000);
l_filename      VARCHAR2(100);
l_date          VARCHAR2(100); 
l_tz            VARCHAR2(1) := '|' ;
CURSOR c1 IS
SELECT
VERBRAUCHSMAT_ID
, ARTNR, BEZEICHNUNG, LIEFERANTEN, ANF_MENGE, TAT_MENGE, LP, RABATT_IN, EK
, TAT_MENGE * EK    GESAMTPREIS
     
, LZ, ZUSTAND, ERSATZ, INFO, GARANTIE, ANK, JAHRESMENGE, DATUM, BEARBEITER, KFDATUM ,KF, BP
FROM VERBRAUCHSMAT , LOV_BT_LIEFERANTEN 
WHERE VERBRAUCHSMAT.LIEFERANTEN = LOV_BT_LIEFERANTEN.WERT
ORDER BY VERBRAUCHSMAT_ID ASC;
BEGIN 
--DBMS_OUTPUT.ENABLE(1000000);
-- ab oracle 10g
DBMS_OUTPUT.ENABLE (buffer_size => NULL);
SELECT TO_CHAR(SYSDATE, 'yyyyMMDD_HH24MISS') INTO  l_date
FROM DUAL;
l_filename := l_date||'_VERBRAUCHSMAT_EXPORT.CSV' ;
export_file := UTL_FILE.FOPEN('EXTERNBACKUP',l_filename,'W');
--APPEND
--export_file := UTL_FILE.FOPEN('EXPORTDIR','EXPORT_1.TXT','A');
l_line := '';
-- Werte direkt
l_line := 'VERBRAUCHSMAT_ID'||l_tz||'ARTNR'||l_tz||'BEZEICHNUNG'||l_tz||'LIEFERANTEN'||l_tz||'ANF_MENGE'||l_tz||'TAT_MENGE'||l_tz||'LP'||l_tz||'RABATT_IN'||l_tz||'EK'
||l_tz||'GESAMTPREIS'
||l_tz||'LZ'||l_tz||'ZUSTAND'||l_tz||'ERSATZ'||l_tz||'INFO'||l_tz||'GARANTIE'||l_tz||'ANK'||l_tz||'JAHRESMENGE'||l_tz||'DATUM'||l_tz||'BEARBEITER'||l_tz||'KFDATUM'||l_tz||'KF'||l_tz||'BP' ;
-- HEADER schreiben
    UTL_FILE.PUT_LINE(export_file, l_line);
FOR rec IN c1
LOOP
-- Werte nicht eingeschlossen
l_line := REC.VERBRAUCHSMAT_ID  
|| l_tz ||REPLACE( REC.ARTNR , l_tz , CHR(47) ) 
|| l_tz ||REPLACE( REC.BEZEICHNUNG , l_tz , CHR(47) ) 
|| l_tz ||REPLACE( REC.LIEFERANTEN , l_tz , CHR(47) )
|| l_tz ||REPLACE( REC.ANF_MENGE , l_tz , CHR(47) )
|| l_tz ||REPLACE( REC.TAT_MENGE , l_tz , CHR(47) )
|| l_tz ||REPLACE( REC.LP , l_tz , CHR(47) )
|| l_tz ||REPLACE( REC.RABATT_IN , l_tz , CHR(47) )
|| l_tz ||REPLACE( REC.EK , l_tz , CHR(47) )
--|| l_tz ||REPLACE( REC.GESAMTPREIS , l_tz , CHR(47) )
|| l_tz ||REPLACE( REC.LZ , l_tz , CHR(47) )
|| l_tz ||REC.ZUSTAND
|| l_tz ||REPLACE( REC.ERSATZ , l_tz , CHR(47) )
|| l_tz ||REPLACE( REC.INFO , l_tz , CHR(47) )
|| l_tz ||REC.GARANTIE
|| l_tz ||REPLACE( REC.ANK , l_tz , CHR(47) )
|| l_tz ||REPLACE( REC.JAHRESMENGE , l_tz , CHR(47) )
|| l_tz ||REPLACE( REC.DATUM , l_tz , CHR(47) )
|| l_tz ||REPLACE( REC.BEARBEITER , l_tz , CHR(47) )
|| l_tz ||REC.KFDATUM|| l_tz ||REC.KF|| l_tz ||REC.BP  
;
-- chr(10)  zu chr(9)   :  LF zu TAB
--l_line := REPLACE( l_line , CHR(10) , CHR(9) );
-- chr(13)  zu chr(9)   :  CR zu TAB
--l_line := REPLACE( l_line , CHR(13) , CHR(9) );
-- chr(124)  zu chr(47)   :  | zu /
--l_line := REPLACE( l_line , CHR(124) , CHR(47) );
-- mehr als zwei aufeinanderfolgende SPACE, TAB usw durch ein TAB:   TABTAB zu TAB
--l_line := regexp_replace( l_line , '([ ]{2,})', chr(9) );
-- CHR(13) und CHR(10) in TAB umwandeln
l_line := REGEXP_REPLACE ( REGEXP_REPLACE ( l_line , CHR(13), CHR(9) ) , CHR(10), CHR(9) ) ;
    DBMS_OUTPUT.PUT_LINE (l_line);
    UTL_FILE.PUT_LINE(export_file, l_line);
END LOOP;
UTL_FILE.FCLOSE(export_file);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE ('Fehler --> ' || SQLERRM );
--END;
NULL;
END  EXTERNBACKUP_VERBRAUCHSMAT;

This PLSQL-Procedure is called per perl-script

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use DBD::Oracle qw(:ora_types);
## minimal benoetigte Umgebungsvariable fuer cron
$ENV{ORACLE_HOME}="/u01/app/oracle/product/11.2.0/xe";
q^
## weitere Umgebungsvariablen
##$ENV{ORACLE_SID}="XE";
##$ENV{NLS_LANG}="GERMAN_GERMANY.AL32UTF8";
##$ENV{PATH}="ORACLE_HOME/bin:PATH";
^if 0;
foreach my $key (sort keys(%ENV)) {
  print "$key = $ENV{$key}\n";
}
my $dbh = DBI->connect(
    "dbi:Oracle:host=localhost;sid=XE",
    "myusername",    ## username
    "mypassword",
    {
        RaiseError => 1,
        AutoCommit => 1
    }) || die "Database connection not made: $DBI::errstr";
q^
eval {
    my $func = $dbh->prepare(q{
        BEGIN
                :return := SCHEMA.PACKAGE.test(
                :parameter1,
                :parameter2,
                :parameter3
            );
        END;
                              });
   
^if 0;
eval {
    my $func = $dbh->prepare( q {
           
BEGIN
 externbackup_verbrauchsmat;  --Prozeduraufruf
NULL;
END;                                
                                
        } );
      
$func->execute;    
};  ## eval
if( $@ ) {
    warn "Execution of stored procedure failed: $DBI::errstr\n";
    print $@;
    $dbh->rollback;
}
$dbh->disconnect;

If I call the perl-script from console, the calculated column GESAMTPREIS is also exported { with the respective uncommented line --|| l_tz ||REPLACE( REC.GESAMTPREIS , l_tz , CHR(47) )  }.

Normally, the perl-script is called as a cronjob

####  extern backup #####
35 13  * * * /usr/bin/perl /home/myunixuser/cron/externbackup_export_verbrauchsmat.pl

But as soon as the cursor c1 in the plsql procedure contains the calculated column TAT_MENGE * EK the created csv-file contains only the header-line.

Why is the behaviour  different when working with the cronjob ?