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 ?