Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

cronjob, perl, plsql

wucisOct 14 2019 — edited Oct 14 2019

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 ?

This post has been answered by John Thorton on Oct 14 2019
Jump to Answer
Comments
Post Details
Added on Oct 14 2019
4 comments
280 views