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

User_GEMBH

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.

User_H3J7U

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

BluShadow

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

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

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

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

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

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