Automic Workload Automation

Expand all | Collapse all

One Automation reporting Oracle error when DollarUniverse does not

Jump to Best Answer
  • 1.  One Automation reporting Oracle error when DollarUniverse does not

    Posted 11-27-2019 01:30 PM
    ​Why does One Automation report Oracle errors when DollarUniverse does not?

    I create the following piece of code as a simple example.
    Only when I call the package procedure from within One Automation do I get the Oracle error ORA-01858.
    Before you start pointing out issues -- Yes, I am aware it is useless piece of code and YES I am aware of the error in the NVL function.

    create or replace PACKAGE date_pkg AUTHID CURRENT_USER AS

       PROCEDURE do_date;

    END date_pkg;

    /

    create or replace PACKAGE BODY date_pkg AS

       PROCEDURE do_date AS

           this_date DATE;

       BEGIN

           WITH x AS (SELECT TO_DATE(NULL, 'YYYY-MM-DD') AS my_date FROM DUAL)

           SELECT my_date INTO this_date FROM x WHERE NVL(my_date, '01-JAN-1900') = TO_DATE('2019-11-27', 'YYYY-MM-DD');

     

       EXCEPTION

         WHEN OTHERS THEN

           IF SQLCODE = -01858 THEN

               DBMS_OUTPUT.PUT_LINE('Received an oracle exception');

           END IF;

           RAISE;

       END;

    END date_pkg;



    ------------------------------
    Newbie
    ------------------------------


  • 2.  RE: One Automation reporting Oracle error when DollarUniverse does not

    Posted 11-28-2019 02:19 AM
    Hi Gerald,

    I'm not a DBA, nor do I know DollarUniverse. There can be several reasons why the call from Automic behaves differently than, for example, from the command line (other user, (not) using the user profile, etc.).

    How is the package procedure called in both applications? For example, is the Automic SQL Agent used? Or is it a command line program (sqlplus?) in both cases? If yes: Unix or Windows? Can you call the call the package procedure "manually"?

    Best regards,
    Tim

    ------------------------------
    Automation Evangelist
    Fiducia & GAD IT AG
    ---
    Mitglied des deutschsprachigen Automic-Anwendervereins FOKUS e.V.
    Member of the German speaking Automic user association FOKUS e.V.
    ------------------------------



  • 3.  RE: One Automation reporting Oracle error when DollarUniverse does not

    Posted 12-14-2019 05:24 PM
    The procedure is called in One Automation via the SQL agent.  Calling the procedure manually via SQL Developer or Toad does not result in the error.

    ------------------------------
    Newbie
    ------------------------------



  • 4.  RE: One Automation reporting Oracle error when DollarUniverse does not
    Best Answer

    Posted 11-28-2019 03:40 AM
    ​Hi.

    The error you refer to is, according to a Google search, dependent on the setting of LC_ALL.

    LC_ALL (and friends, such as LC_LANG) are UNIX environment settings. So if the shell you use in One Automation has a different environment from the one you use with Dollar Universe, that could explain why the Oracle client throws an error from one product and not the other.

    Hth,

    ------------------------------
    I will not respond to PM asking for help unless there's an actual reason to keep the discussion off of the public forums.
    ------------------------------



  • 5.  RE: One Automation reporting Oracle error when DollarUniverse does not

    Posted 12-14-2019 06:17 PM
    Running on a Windows bases system.  The procedure was not executed through UNIX r any batch script.

    ------------------------------
    Newbie
    ------------------------------