Forum Stats

  • 3,781,551 Users
  • 2,254,529 Discussions
  • 7,879,752 Comments

Discussions

How do I log the result of a script/ pl/sql query to a custom table?

User_GEMBH
User_GEMBH Member Posts: 2 Green Ribbon
edited Nov 23, 2021 11:40AM in SQL & PL/SQL

How might I go about logging the result of an automated sql script and or pl/sql procedure in oracle sql developer in a custom table?

I'd like to capture:

success or failure of the script/query, error code if present, date time of completion and the target table for insertion.

We are using stone branch to automate the jobs and can see each query result there, but cannot see a consolidated result of 12 daily scheduled jobs.

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 814 Gold Trophy

    SQL*Plus set errorlogging. It seems sqlcl stopped supporting this feature a long time ago.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,615 Red Diamond

    Better not to use PL/SQL as a scripting language and use it as a procedural language as it was intended (the clue is in the name "PL").

    Put all your code in the database in packages/procedures etc. and then instrument your code so that it logs progress and errors etc. to table(s) using some autonomous transactions (that means the tracing of progress can be viewed even before the actual workings of the SQL inside have committed as the logging will be done as a separate transaction).

    Not sure why people use external schedulers to run jobs as scripts when Oracle provides perfectly good scheduled job management within the database itself (and it keeps the security all within the database by doing so). DBMS_JOB or DBMS_SCHEDULER can be used to create scheduled jobs to execute the procedures as needed.

  • User_GEMBH
    User_GEMBH Member Posts: 2 Green Ribbon

    I'm using stonebranch because that what the client prescribes to ingest data from several sources and won't grant privileges to add or edit procedures directly in the production database. I'm looking for advice from others who may have run into these types of restrictions because it is nonstandard. What about PL / SQL in your opinion makes it a poor choice for this?

  • BluShadow
    BluShadow Member, Moderator Posts: 41,615 Red Diamond

    PL/SQL isn't a poor choice. The poor choice seems to be the restrictions your client is putting on you.

    If you need to log things outside the database then that will depend on how you are executing you SQL scripts e.g. are you using SQL*Plus? or SQLCl (as User_<whatever>) has commented on, or something else?. Different interfaces have different ways of presenting output, and different ways of capturing that and logging it.

    In essence though, it's likely that you'll be calling the script from an operating system command of some sort and that needs o/s output redirection to a log file of some sort. If there's something specific to stonebranch, then you'd be better seeking assistance from stonebranch (or it's community if it has one) as that's not an Oracle product as far as I'm aware. Maybe it has it's own way of logging output from scripts... some such tools do automatically redirect output to their own logs.

  • Marwim
    Marwim Member Posts: 3,649 Gold Trophy

    Not sure why people use external schedulers to run jobs as scripts when Oracle provides perfectly good scheduled job management within the database itself

    We use Automic to schedule jobs across different environments - it's much more comfortable to coordinate jobs if you have to call shell scripts on serves that are not connected to the database :-)

    We use dbms_scheduler, too, but only for jobs that have no external dependencies

  • Mike Kutz
    Mike Kutz Member Posts: 5,947 Silver Crown

    Idea 1

    This sounds like a Stonebranch issue.

    I know Autosys records STDOUT/STDERR and exit value of the scripts along with Start end End times.

    If you need this in the database, you'll need to write the scraper.

    Idea 2

    Most people would prefer a modification of the code to call the OraLogger ( log )

    Idea 3

    At the beginning of my SQL*Plus scripts, I call DBMS_APPLICATION_INFO.SET_MODULE

    Each step calls set_action (usually at the beginning inside my procedures)

    IIRC - DBMS_APPLICATION_INFO is a synonym. Oracle allows you to replace it with your own customized version.

    This would be another way to log things into the database.

    Idea 4

    Modify your shell scripts to call an ORDS end point.

  • sandeepgupta_18
    sandeepgupta_18 Member Posts: 44 Red Ribbon

    Hi ...

    I am not much aware of Stone Branch but in Pl/sql you can write the PL/Sql code for the same..

    For Example :-

    Suppose you have below type of of code

    Declare

    ----

    ---

    Begin


    Insert Statement 1

    Insert statement 2


    Update statement 1


    End;





    you can modify like Below


    Declare

    ----

    ---

    Begin

    begin

    Insert Statement 1

    insert into custom table --for success

    exception when others then (whatever suits as per code logic)--for error or failure

    insert into custom table

    end;


    begin

    Insert Statement 2

    insert into custom table --for success

    exception when others then (whatever suits as per code logic)--for error or failure

    insert into custom table

    end;


    begin

    update Statement 1

    insert into custom table --for success

    exception when others then (whatever suits as per code logic)--for error or failure

    insert into custom table

    end;


    End;


    this is one of the manual way of doing this..

    Hope i was able to advice correctly...