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;
--------------------------------------------------------------------------------------------------------------------------

#!/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;

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

00 16  * * * export NLS_LANG=GERMAN_GERMANY.AL32UTF8 ; /usr/bin/perl /home/myusername/cron/externbackup_export_verbrauchsmat.pl