Writing an sql query output to a File in PLSQL Package

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:

BEGIN 

PKG_QUERY_TO_FILE.WRITE(
pv_sql_query => ‘SELECT * FROM ODS_ETL.TTA01EXTLOAD_DEFINITIONS WHERE SOURCE_SYSTEM = ”BSCS”’,
pv_disk_directory => ‘/ETLfs’,
pv_file_name => ‘extload.txt’,
pb_include_column_header => TRUE,
pv_column_header_encloser => ‘”‘,
pv_column_header_seperator => ‘;’,
pv_file_seperator => ‘,’,
pb_append_mode => FALSE,
pv_null_replacer => ‘NULL’
);

PKG_QUERY_TO_FILE.WRITE(
pv_sql_query => ‘SELECT * FROM ODS_ETL.TTA01EXTLOAD_DEFINITIONS WHERE SOURCE_SYSTEM = ”BSCS”’,
pv_disk_directory => ‘/ETLfs’,
pv_file_name => ‘extload2.txt’
);

EXECUTE IMMEDIATE ‘CREATE OR REPLACE DIRECTORY SIL_TMPDIR AS ”/ETLfs/fuat”’;
PKG_QUERY_TO_FILE.WRITE_SIMPLE(
pv_sql_query => ‘SELECT * FROM ODS_ETL.TTA01EXTLOAD_DEFINITIONS WHERE SOURCE_SYSTEM = ”BSCS”’,
pv_directory_object => ‘SIL_TMPDIR’,
pv_file_name => ‘extload3.txt’
);
EXECUTE IMMEDIATE ‘DROP DIRECTORY SIL_TMPDIR’;
END;

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

Running Shell Script in PL/SQL on a RAC Environment

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 :

node1 : galatats1
node2 : galatats2

By the way you can learn your instance name in your session via querying v$instance system view.

On instance2 :

[oracle@galatats02.no.turkcell.tgc]:/home/oracle/demoSh> ls -la
total 20
drwxr-xr-x 2 oracle oinstall 4096 Sep 26 01:49 .
drwx—— 6 oracle 501 4096 Sep 26 01:49 ..
-rwxr-xr-x 1 oracle oinstall 1239 Sep 26 01:49 demo.sh
-rw-r–r– 1 oracle oinstall 20 Sep 26 01:49 output.txt

In order to execute demo.sh inside the PL/SQL we are creating a DBMS_SCHEDULER job like that :

BEGIN
dbms_scheduler.create_job(
job_name => ‘DEMO’,
job_type => ‘EXECUTABLE’,
job_action => ‘/home/oracle/demoSh/demo.sh’,
start_date => SYSTIMESTAMP,
number_of_arguments=>0,
enabled => true,
auto_Drop => true,
comments => ‘Demo’);
END;

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:

ORA-27369: job of type EXECUTABLE failed with exit code: No such file or directory

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.

Log4PL/SQL – Log Api For PL/SQL

Log4PL/SQL

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.

An Example:

DECLARE

ln_count NUMBER;

pCTX PLOG.LOG_CTX := PLOG.init(pSECTION     => ‘RTDF’,

pLEVEL       => PLOG.LALL,

pLOG4J       => FALSE,

pLOGTABLE    => TRUE,

pOUT_TRANS   => TRUE,

pALERT       => FALSE,

pTRACE       => FALSE,

pDBMS_OUTPUT => FALSE);

BEGIN

SELECT COUNT(*) INTO ln_count FROM ALL_OBJECTS WHERE OBJECT_NAME = ‘TLEVEL’ AND OBJECT_TYPE = ‘TABLE’;

IF ( ln_count > 0 ) THEN

PLOG.info(pctx, ‘Table is exist.’);

ELSE

PLOG.info(pctx, ‘Table is exist.’);

END IF;

EXCEPTION

WHEN OTHERS THEN

plog.error;

plog.full_call_stack;

END;

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.

Pairing Java objects with Oracle Types

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

import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;

/**
*
* @author TTASUNGUR
*/
public class StreamType implements SQLData
{

private String sql_type;
public String dataType = “”;
public String activation_Type = “”;

public
StreamType()
{
}

public
StreamType(String sql_type)
{
this.sql_type = sql_type;
}

public void readSQL(SQLInput inStream, String typeName) throws
SQLException
{

sql_type = typeName;
dataType = inStream.readString();
activation_Type = inStream.readString();
// this order should be similar to defining order

}

public void writeSQL(SQLOutput outStream) throws SQLException
{
outStream.writeString(dataType);
outStream.writeString(activation_Type);
// this order should be similar to defining order

}

public
String getSQLTypeName() throws SQLException
{
return sql_type;
}
}

2 ) You should create Oracle type that will be matched your java object :

CREATE OR REPLACE TYPE stream_type AS OBJECT
(
dataType VARCHAR2(50),
activation_Type VARCHAR2(50)
);

3 ) There should be mapping java object to oracle object.

java.util.Map myMap = (Map)conn.getTypeMap(); // conn is a Connection object, Map class casts it ( also Map class should be imported your java class )
myMap.put(oracle_gb_file_type, Class.forName(“nor.GB_File”));

4 ) If your new object is ready for sending database, you can pass it to a statement object.

StreamType st = new StreamType();
st.dataType = “foo”;
st.activation_Type = “foo2″;
CallableStatement cstmt = conn.prepareCall(“{ call AQ_ADM_PRTP.AQ_PRTP.ENQUEUE_STREAM_TYPE(?) }”); //plsql procedure that gets StreamType type that has already created in Database
cstmt.setObject(1,st,OracleTypes.STRUCT);

Other link(s):
> Working with Oracle Objects : http://download.oracle.com/docs/cd/B19306_01/java.102/b14355/oraoot.htm