Shell Programming and Scripting

Hi there,

I got this situation at job. First i have a Perl script that connect to Oracle 10g and execute a package. That package execute severals transactions and must generate log about process status each interval of time.

What i'm doing now is saving the plsql package log in an auxiliar table and after it ends and return the control to perl, i read that log table and write on my fisical log file.

But what i need is a way to directly write on my log file even while the plsql package is still executing.

I got an idea , make other perl that goes reading the log table on database and writing to log file parallel to the package execution, but i got no data on that table for other sessions until i commit and i cannot commit with the package until it ends OK.

Any idea, Thanks,
Jparra

Until the package commits, you cannot see anything it put into a table, regardless of what your perl script may be doing.

If you need to see the changes, either change the pl/sql script to commit after every transaction, so your perl script can see it, or change the package itself to do the edits. It makes more sense, design-wise, to have the pl/sql package edit the changes - it should have gotten them correct to start with.

Thanks Jim,
Well, what i'm going to do is change the pl/sql to commit after every transaction.

Would you like I'll let you know about the tests and result?

Have a look at the utl_file package which provides functions for handling files via PL/SQL.

I know about utl_file package, but the database server and scripts server are different machines. I don't know how to manage remote file with utl_file and what would happen when if the pl/sql script and the Perl script attempt to write to the file at the same time and keep the loggin time sequence.