Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

mNem

Works fine for me.

public static void main(String[] args)

  { 

    String str = "asffass".replaceAll("", "");

    System.out.print(str);

  }

unknown-7404

but it did not work?

If you want help with code you have to POST THE CODE.

Standard Java exceptions NEVER say 'did not work' - they ALWAYS provide an exception number and message.

3513491

Exception :unmmaped charachter

private List<Card> ankiConvert(File ankiLesson) throws UnsupportedEncodingException{

        int i=1;

        List<Card> notes=new ArrayList();

         Connection conn = null;

         Statement query=null;

        try {

            // db parameters

            String url = "jdbc:sqlite:"+ankiLesson.getAbsolutePath();

            // create a connection to the database

            try{

            conn = DriverManager.getConnection(url);

            }catch(Exception e){

            System.out.println("Connection to SQLite has been established.");

            }

            query=conn.createStatement();

            String sql="SELECT flds,sfld FROM 'notes'";

            ResultSet rs=query.executeQuery(sql);

            while(rs.next()){

               

                org.jsoup.nodes.Document doc =  Jsoup.parse(rs.getString("flds"));

                org.jsoup.nodes.Document doc1 =  Jsoup.parse(rs.getString("sfld"));

                String front=doc1.text();

                String back=doc.text().replaceAll("&#31;","").replace(front,"");

                String image=doc.getElementsByTag("img").attr("src");

                Card card=new Card();

                card.setId(String.valueOf(i));

                card.setFrontside(front.trim().replaceAll("(\\[)(.*)|(\\()(.*)|(\\{)(.*)",""));

                card.setBackside(back.trim().replaceFirst("(/.*/)",""));

                card.setLevel("0");

                card.setTesthit("0");

                card.setChapter("All");

                if(image!=null){

                card.setImg(image);

                }

                notes.add(card);

                i++;

            }

            rs.close();

        } catch (SQLException e) {

            System.out.println(e.getMessage());

        } finally {

            try {

                if (conn != null) {

                    conn.close();

                }

            } catch (SQLException ex) {

                System.out.println(ex.getMessage());

            }

        }

        return notes;

    }

mNem

It is difficult to identify which line is throwing exception.... when we do not have access to your 3rd party libraries.

Exception :unmmaped charachter

May be it is useful to identify the issue, if you could paste the full exception trace.

One more thing, IMHO, it is always pays to write readable code when it comes to maintainable code.

Normally, I avoid chaining method calls.

String back=doc.text().replaceAll("&#31;","").replace(front,"");

instead,

String back = doc.text();

back = back.replaceAll(.....); // side note: this could throw NPE if back is null.

back = back.replace(....);

This allows me to step over line by line using the debugger and identify what is being returned when troubleshooting.

unknown-7404

I think you missed reading this part of what I said:

Standard Java exceptions NEVER say 'did not work' - they ALWAYS provide an exception number and message.

The code you posted pretty much says you do NOT care about what exceptions may occur - and that processing should just continue no matter what happens:

try{

            conn = DriverManager.getConnection(url);

            }catch(Exception e){

            System.out.println("Connection to SQLite has been established.");

            }

            query=conn.createStatement();

Really?

Even if 'getConnection' raises an exception you just want to go ahead and try to create a statement and continue processing the rest of the code?

You should ONLY capture exceptions if you plan to handle them.

You code has multiple exception blocks and NONE OF THEM actually handle an exception.

  } catch (SQLException e) {

            System.out.println(e.getMessage());

        } finally {

            try {

                if (conn != null) {

                    conn.close();

                }

            } catch (SQLException ex) {

                System.out.println(ex.getMessage());

            }

        }

Get rid of ALL of those exception handlers so you can see the actual exceptions that occur and get the information Java provides about exactly WHERE they occur.

Post the actual stack trace that Java provides - it will show the FULL list of events that are happening.

Then start troubleshooting your problem by examining EVERY STEP of the code so you can see what it does

Don't underestimate the importance of what mNem said:

String back=doc.text().replaceAll("&#31;","").replace(front,"");

That contains THREE different statements jammed together on one line. That makes it difficult to know which of them, if any, are causing an exception.

If that one line causes a problem it could be the '.replace', the 'replaceAll' or even the 'doc.text()'. Either the 'doc' or the 'front' could be null or problematic.

Write SIMPLE code so you know what the code does. Then you can worry about taking shortcuts and trying to write complex, single-line statements.

Then if you still have trouble identifying the actual line with a problem add a try .. except block around every line until you find the one throwing the exception.

Then examine EACH AND EVERY variable using a debugger (e.g. NetBeans) to make sure they have the value you expect.

Until you remove those garbage exception handlers and post the actual exception stack trace (printStackTrace) we can't really help.

1 - 5

Post Details

Added on Oct 14 2019
4 comments
471 views