We are developing ETL projects and sometimes we need to write output of a table or view query to a file. The problem is that for each project it should be written another package or procedure to write table or query data to a file. This is a needless operation and I decided to write a package to overcome this problem.
The package that I coded is PKG_QUERY_TO_FILE that has two public procedure to write a query’s output to a file. Example calling of the procedures are listed below:
You can analyse, download and use free the code of PKG_QUERY_TO_FILE package, from here. You can add your log system instead DBMS_OUTPUT.
It may have some bugs if you specify that I will fix it or you can create your own generic package by using my package.
Also Thomas Kyte has written a package about this problem ( http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:95212348059 ).
Sometimes we need to run shell script within the PL/SQL code. To do accompplish this we have two options :
1 ) Create a java stored class that runs shell script with Java Runtime class.
2 ) Using DBMS_SCHEDULER Oracle supplied package
When you are on second option and you work on a RAC environment you should be aware of somethings that are listed below:
1 ) Unix user who had installed the Oracle Database ( generally oracle ) should have been granted to run specified shell script. This is a requirement whether you use Oracle RAC or not.
2 ) In shell script, use exact path of supplied unix utilities instead of using its actual name. For example, if shell script use “echo” utility and you do not call explicitly profile file or set environment variables, you should specify its path exactly what it is, like “/bin/echo”.
3 ) Shell script that will be run by Oracle DB should be placed on all of the nodes with same grants. Because you do not know which node will execute the shell script.
Assume that you have 2 nodes a. Your shell script is located on /home/oracle/demoSh/demo.sh . And instance names that match nodes which are listed below :
By the way you can learn your instance name in your session via querying v$instance system view.
On instance2 :
In order to execute demo.sh inside the PL/SQL we are creating a DBMS_SCHEDULER job like that :
As soon as job is created, it started to run. As soon as job completed its run, it is dropped automatically. Because of auto_drop clause was set to true while job was creating. ( By the way you do not necessary to do this ). How can I check status of my job that has already dropped? You can use USER_SCHEDULER_JOB_RUN_DETAILS view to check status of all scheduler jobs that run. Also In a RAC environment you can see node that job was executed on.
If you change name of demo.sh file on node1, the job we have created above will run successfully sometimes. If you check the status code of user_scheduler_job_run_details you can see both SUCCESSFUL and FAILED status of your job. In the FAILED row you may see this in ADDITIONAL_INFO column:
So that, in order to execute shell script as successful via PL/SQL you have to obey some rules. In this article I tried to tell what they are.
In some situations you need to developed big applications with PL/SQL. And you want to see that what operations were executed so far and which of them caused an error? May be you want to logged exception blocks to solve your application leaks, logged other informations to debug your application. To perform all of these you can use Log4PL/SQL api.
It is similar to Log4J and logged various information about your application. It is a PL/SQL package. If you want, you can logged only specific information for example, you can log only exception or all information. You can select log level to perform this operation. You may use this package in your code but if you don’t want to log anymore, you can disable logging only by changing a little piece of code.
SELECT * FROM TLOG
SELECT * FROM VLOG — This view only one column that consists of columns of tlog and only includes last 24 record. You can edit view sql to show more records.
For more information, visit Log4PL/SQL Homepage.
When you send your java object variables to Oracle, you can divide objects into attributes, then send them to database. It makes no sense and takes much development time than sending directly as object. Before sending java object to oracle, you should do some steps.
1 ) Firstly, you should set up your java objects.
> Import java.sql.SQLData, SQLException, SQLInput, SQLOutput classes to your object class
> Your class implements SQLData Interface but not necessary
> readSql, writeSql, getSqlTypeName methods must be declared and they process (read or write) class variables
2 ) You should create Oracle type that will be matched your java object :
3 ) There should be mapping java object to oracle object.
4 ) If your new object is ready for sending database, you can pass it to a statement object.
> Working with Oracle Objects : http://download.oracle.com/docs/cd/B19306_01/java.102/b14355/oraoot.htm