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

Java DelayQueue test

DelayQueue Test

DelayQueue is a Java Queue that supports delaying for queue elements. An element inside the queue only taken after its expiration(delay) time.

Object class that inside the queue should implement the “Delayed” interface. This interface forces you to implement 2 methods:

getDelay,compareTo

getDelay method is important because Java decided to dequeue element from queue if getDelayed<=0 . For more information, visit Delayed Interface javadoc.

Lets start an example.

Firstly, you should define a class that represents the elements inside the queue. Our class name is NewString. This class has 2 important methods,getDelay and compareTo. If getDelay method returns 0 value or a value is lower than 0, this object will dequeue from the queue.

package com.turkcelltech.queuetests.DelayQueue;

import java.util.concurrent.Delayed;
import java.util.concurrent.TimeUnit;

public class NewString implements Delayed{
private long endOfDelay;
private String text;
private long queueInsertTime;

public long getQueueInsertTime() {
return queueInsertTime;
}

public void setQueueInsertTime(long queueInsertTime) {
this.queueInsertTime = queueInsertTime;
}

public String getText() {
return text;
}

public void setText(String text) {
this.text = text;
}

public NewString()
{
}

public long getEndOfDelay() {
return endOfDelay;
}
public void setEndOfDelay(long endOfDelay) {
this.endOfDelay = endOfDelay;
}

@Override
public long getDelay(TimeUnit unit) {
// TODO Auto-generated method stub
long tmp = unit.convert((getQueueInsertTime()-System.currentTimeMillis())+endOfDelay, TimeUnit.MILLISECONDS);
return tmp;

}

@Override
public int compareTo(Delayed o) {
// TODO Auto-generated method stub
int ret = 0;
NewString ns = (NewString) o;

if ( this.endOfDelay < ns.endOfDelay ) ret = -1;
else if ( this.endOfDelay > ns.endOfDelay ) ret = 1;
else if ( this.getQueueInsertTime() == ns.getQueueInsertTime() ) ret = 0;

return ret;

}

}

And we are preparing Producer and Consumer classes. For each step in producer, a NewString object is creating and setting its attributes; for each step in consumer, consumer tries to take an element from the queue, if it could not, it waits until an element will be put (Refer the java queue documentation to understand differences between poll and take methods of queue, http://download.oracle.com/javase/1.5.0/docs/api/java/util/Queue.html )

Producer:

package com.turkcelltech.queuetests.DelayQueue;
import java.math.BigInteger;
import java.security.SecureRandom;
import java.util.Random;
import java.util.concurrent.DelayQueue; 

public class Producer extends Thread {

private DelayQueue dq;
private SecureRandom random = new SecureRandom();

public Producer(DelayQueue dq) {
// TODO Auto-generated constructor stub
this.dq = dq;
}

@Override
public void run() {
// TODO Auto-generated method stub
while (true) {
try {
String str = getRandomString();
NewString nstr = new NewString();
int iRandom = 2000 + new Random().nextInt(1000);

nstr.setText(str);
nstr.setEndOfDelay(iRandom);
nstr.setQueueInsertTime(System.currentTimeMillis());
dq.put(nstr);

Thread.sleep(1);
} catch (InterruptedException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

public String getRandomString() {
return new BigInteger(130, random).toString(32);
}

}

Consumer:

package com.turkcelltech.queuetests.DelayQueue;
import java.util.concurrent.DelayQueue;
import java.util.concurrent.Delayed;
import java.util.concurrent.LinkedBlockingQueue; 

public class Consumer extends Thread {

private DelayQueue dq;
private NewString ns;

public Consumer(DelayQueue dq) {
// TODO Auto-generated constructor stub
this.dq = dq;
}

@Override
public void run()
{
long dequeueTime = 0;
while ( true )
{
String str = null;
try {
ns = (NewString) dq.take();
dequeueTime = System.currentTimeMillis();
} catch (InterruptedException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
StringBuffer sb = new StringBuffer();
sb.append(“——————–”+”\n”);
sb.append(“Queue Size ( Cons ) :”+dq.size()+”\n”);
sb.append(“Inserted Element :”+ns.getText()+”\n”);
sb.append(“Queue Insertion Time :”+ns.getQueueInsertTime()+”\n”);
sb.append(“Now ( dequeue time ) :”+dequeueTime+”\n”);
sb.append(“Expected Delay (ms):”+ns.getEndOfDelay()+”\n”);
sb.append(“Actual Delay (ms):”+(dequeueTime-ns.getQueueInsertTime())+”\n”);
sb.append(“Differences Actual and Expected Delay (ms):”+((dequeueTime-ns.getQueueInsertTime())-ns.getEndOfDelay())+”\n”);
sb.append(“——————–”+”\n”);
System.out.println(sb.toString());
//System.out.println(“Queue Size (Cons):”+dq.size()+”|Inserted Element:”+ns.getText()+”|Delay:”+ns.getEndOfDelay()+”|Queue Insertion Time:”+ns.getQueueInsertTime()+”|Now:”+System.currentTimeMillis());

}
}

}

And, create the main class that calls producer and consumer:

DelayQueueTest:

package com.turkcelltech.queuetests.DelayQueue;

import java.util.concurrent.DelayQueue;

public class DelayQueueTest {

private static DelayQueue dq;

public static void main(String args[])
{
System.out.println(“DelayQueue Example…”);
dq = new DelayQueue();
Producer p = new Producer(dq);
Consumer c = new Consumer(dq);
p.start();
c.start();

}
}

Lastly, run the DelayQueueTest.java, then lets analyse the output. After a
period of time, to find the elements that has expired become hard because the
queue size is increasing gradually because of in our example, producer is
faster than the consumer. I think this delay queing structure is great, useful and works well with middle-size queues but if the
queue size is big enough, deciding expired items much more hard.

——————–
Queue Size ( Cons ) :6190
Inserted Element :cqf44unuj7e54gm6ou8hafgt6b
Queue Insertion Time :1297351067151
Now ( dequeue time ) :1297351069152
Expected Delay (ms):2000
Actual Delay (ms):2001
Differences Actual and Expected Delay (ms):1
——————–

——————–
Queue Size ( Cons ) :6190
Inserted Element :q5a0atgilmik2jd6d3qu2bl2ic
Queue Insertion Time :1297351066479
Now ( dequeue time ) :1297351069152
Expected Delay (ms):2000
Actual Delay (ms):2673
Differences Actual and Expected Delay (ms):673
——————–

——————–
Queue Size ( Cons ) :6189
Inserted Element :o plb0ojmltu473jgbstbsdcauj
Queue Insertion Time :1297351065869
Now ( dequeue time ) :1297351069152
Expected Delay (ms):2001
Actual Delay (ms):3283
Differences Actual and Expected Delay (ms):1282
——————–

——————–
Queue Size ( Cons ) :6188
Inserted Element :la7uka3abq4t8ilbbki386dc2p
Queue Insertion Time :1297351065891
Now ( dequeue time ) :1297351069152
Expected Delay (ms):2001
Actual Delay (ms):3261
Differences Actual and Expected Delay (ms):1260
——————–

——————–
Queue Size ( Cons ) :6187
Inserted Element :35uesr3s7c8dbkrtfudjvg2tir
Queue Insertion Time :1297351066946
Now ( dequeue time ) :1297351069152
Expected Delay (ms):2001
Actual Delay (ms):2206
Differences Actual and Expected Delay (ms):205
——————–

——————–
Queue Size ( Cons ) :6186
Inserted Element :adcufi915fr0ehs43ebkl66apl
Queue Insertion Time :1297351063883
Now ( dequeue time ) :1297351069152
Expected Delay (ms):2002
Actual Delay (ms):5269
Differences Actual and Expected Delay (ms):3267
——————–

——————–
Queue Size ( Cons ) :8227
Inserted Element :sevfljifbd677h178l6t4225pp
Queue Insertion Time :1297351069243
Now ( dequeue time ) :1297351071244
Expected Delay (ms):2000
Actual Delay (ms):2001
Differences Actual and Expected Delay (ms):1
——————–

——————–
Queue Size ( Cons ) :8325
Inserted Element :unrvsodhafh02bcq21ords5ude
Queue Insertion Time :1297351069341
Now ( dequeue time ) :1297351071343
Expected Delay (ms):2001
Actual Delay (ms):2002
Differences Actual and Expected Delay (ms):1
……
……
……
Queue Size ( Cons ) :507047
Inserted Element :e4gjtvfq6b8hnlau27hlldvpve
Queue Insertion Time :1297350198016
Now ( dequeue time ) :1297350301063
Expected Delay (ms):2003
Actual Delay (ms):103047
Differences Actual and Expected Delay (ms):101044
——————–

——————–
Queue Size ( Cons ) :507046
Inserted Element :p8fe9d49o1ga0i9oe3ciepac37
Queue Insertion Time :1297350200057
Now ( dequeue time ) :1297350301063
Expected Delay (ms):2003
Actual Delay (ms):101006
Differences Actual and Expected Delay (ms):99003
——————–

——————–
Queue Size ( Cons ) :507045
Inserted Element :n5bd4saorqcmo3ahgfersfg4ns
Queue Insertion Time :1297350187489
Now ( dequeue time ) :1297350301063
Expected Delay (ms):2003
Actual Delay (ms):113574
Differences Actual and Expected Delay (ms):111571
——————–

——————–
Queue Size ( Cons ) :507044
Inserted Element :2522nr8jkk56qr3qbm06ie8er
Queue Insertion Time :1297350203764
Now ( dequeue time ) :1297350301063
Expected Delay (ms):2003
Actual Delay (ms):97299
Differences Actual and Expected Delay (ms):95296

UKOUG Conference 2010

We developed an Oracle CEP application which processes GSM network events and produces alerts. We also used Oracle Coherence to facilitate handling huge data.
My presentation about this topic has been selected for inclusion within the this year programme to present on Tuesday 30th November:

Processing Turkcell GSM Network Data with Oracle CEP
(30/11/2010 13:45 – 14:30)

I’m a bit excited because this will be my first visit to England and my first presentation in abroad :)

Split utility Performance on Linux

Generally processing huge files is a big deal. Parallel processing is a common method to get over this problem. There are several ways processing files as parallel. I used MPI api to process files as parallel when I was at Computer Hardware class. It gives you to lots of methods to accomplish this. Parallel processing, basically, one node splits file up to all of the nodes then each node responsible for processing its own data and after processed it informs the coordinator process, as is in MPI. For detailed information you can visit LAM-MPI User Guide.

In addition to MPI api, we can use Unix split function to split up our files then each process could take a file and process it. Every process read its file at the same time therefore we can reduce total file processing time.

In this text I want to describe linux/unix split utility with a demo. Our first scenario we have 2GB file ( output.txt ) and we’re going to split it up to various size files.

[oracle@galatats01.no.turkcell.tgc]:/home/oracle/split_test> ls -la output.txt
-rw-r–r– 1 oracle oinstall 1907874429 Oct 2 13:51 output.txt
[oracle@galatats01.no.turkcell.tgc]:/home/oracle/split_test> nohup ./split.sh > split.txt &
[1] 31883
[oracle@galatats01.no.turkcell.tgc]:/home/oracle/split_test> tail -f split.txt
Started ( Splitting up to 5000 lines per file ) at :1286016749781
Finished ( Splitting up to 5000 lines per file ) at :1286016757756. Total time:7975 ms.
Started ( Splitting up to 50000 lines per file ) at :1286016757769
Finished ( Splitting up to 50000 lines per file ) at :1286016778449. Total time:20680 ms.
Started ( Splitting up to 500000 lines per file ) at :1286016780335
Finished ( Splitting up to 500000 lines per file ) at :1286016845797. Total time:65462 ms.
[1] + Done nohup ./split.sh > split.txt &

As it seems, if size of the partitioned file increases, processing time will increase as well. Is this true always? I do not think so:

[oracle@galatats01.no.turkcell.tgc]:/home/oracle/split_test> ls -la output.txt
-rw-r–r– 1 oracle oinstall 1907874429 Oct 2 13:51 output.txt
[oracle@galatats01.no.turkcell.tgc]:/home/oracle/split_test> nohup ./split.sh > split.txt &
[1] 6335
[oracle@galatats01.no.turkcell.tgc]:/home/oracle/split_test> tail -f split.txt
Started ( Splitting up to 5000 lines per file ) at :1286018690329
Finished ( Splitting up to 5000 lines per file ) at :1286018699098. Total time:8769 ms.
Started ( Splitting up to 50000 lines per file ) at :1286018699109
Finished ( Splitting up to 50000 lines per file ) at :1286018706011. Total time:6902 ms.
Started ( Splitting up to 500000 lines per file ) at :1286018706022
Finished ( Splitting up to 500000 lines per file ) at :1286018731639. Total time:25617 ms.
[oracle@galatats01.no.turkcell.tgc]:/home/oracle/split_test> nohup ./split.sh > split.txt &
[1] 5900
[oracle@galatats01.no.turkcell.tgc]:/home/oracle/split_test>
[oracle@galatats01.no.turkcell.tgc]:/home/oracle/split_test> tail -f split.txt
Started ( Splitting up to 5000 lines per file ) at :1286018566979
Finished ( Splitting up to 5000 lines per file ) at :1286018577839. Total time:10860 ms.
Started ( Splitting up to 50000 lines per file ) at :1286018577850
Finished ( Splitting up to 50000 lines per file ) at :1286018584919. Total time:7069 ms.
Started ( Splitting up to 500000 lines per file ) at :1286018587039
Finished ( Splitting up to 500000 lines per file ) at :1286018607979. Total time:20940 ms.
[1] + Done nohup ./split.sh > split.txt &
[oracle@galatats01.no.turkcell.tgc]:/home/oracle/split_test>

But, if the size of file that is splitted is smaller, results will be more stable:

[oracle@galatats01.no.turkcell.tgc]:/home/oracle/split_test> ls -la output.txt
-rw-r–r– 1 oracle oinstall 41074528 Oct 2 13:53 output.txt
[oracle@galatats01.no.turkcell.tgc]:/home/oracle/split_test> nohup ./split.sh > split.txt &
[1] 5142
[oracle@galatats01.no.turkcell.tgc]:/home/oracle/split_test> tail -f split.txt
Started ( Splitting up to 5000 lines per file ) at :1286018340006
Finished ( Splitting up to 5000 lines per file ) at :1286018340174. Total time:168 ms.
Started ( Splitting up to 50000 lines per file ) at :1286018340186
Finished ( Splitting up to 50000 lines per file ) at :1286018340344. Total time:158 ms.
Started ( Splitting up to 500000 lines per file ) at :1286018340355
Finished ( Splitting up to 500000 lines per file ) at :1286018340499. Total time:144 ms.
[1] + Done nohup ./split.sh > split.txt &
[oracle@galatats01.no.turkcell.tgc]:/home/oracle/split_test> ls -la output.txt
-rw-r–r– 1 oracle oinstall 246447168 Oct 2 14:20 output.txt
[oracle@galatats01.no.turkcell.tgc]:/home/oracle/split_test>
[oracle@galatats01.no.turkcell.tgc]:/home/oracle/split_test>
[oracle@galatats01.no.turkcell.tgc]:/home/oracle/split_test>
[oracle@galatats01.no.turkcell.tgc]:/home/oracle/split_test> nohup ./split.sh > split.txt &
[1] 5650
[oracle@galatats01.no.turkcell.tgc]:/home/oracle/split_test> tail -f split.txt
Started ( Splitting up to 5000 lines per file ) at :1286018462209
Finished ( Splitting up to 5000 lines per file ) at :1286018463166. Total time:957 ms.
Started ( Splitting up to 50000 lines per file ) at :1286018463180
Finished ( Splitting up to 50000 lines per file ) at :1286018464039. Total time:859 ms.
Started ( Splitting up to 500000 lines per file ) at :1286018464054
Finished ( Splitting up to 500000 lines per file ) at :1286018464887. Total time:833 ms.

As a result, it could not be said that a number is the best for splitting, it depends on many factors. You ought to test to reduce splitting time if you use split utility .

This tests were performed in Linux 2.6.18-194.el5, SUN X4150. It has eight processors and 16 GB mem.

By the way, split.sh is below :

[oracle@galatats01.no.turkcell.tgc]:/home/oracle/split_test> cat split.sh
#!/bin/ksh

time1=0;
time2=0;
time3=0;
getTime()
{
time1=`perl -MTime::HiRes -e ‘print int(1000 * Time::HiRes::gettimeofday),”\n”‘`
}

getTime
time2=$time1;
echo “Started ( Splitting up to 5000 lines per file ) at :$time1″
split -a 5 -d output.txt -l 5000 output5K
getTime
time3=`echo “$time1 – $time2″ | bc`
echo “Finished ( Splitting up to 5000 lines per file ) at :$time1. Total time:$time3 ms.”

getTime
time2=$time1;
echo “Started ( Splitting up to 50000 lines per file ) at :$time1″
split -a 5 -d output.txt -l 50000 output50K
getTime
time3=`echo “$time1 – $time2″ | bc`
echo “Finished ( Splitting up to 50000 lines per file ) at :$time1. Total time:$time3 ms.”

getTime
time2=$time1;
echo “Started ( Splitting up to 500000 lines per file ) at :$time1″
split -a 5 -d output.txt -l 500000 output500K
getTime
time3=`echo “$time1 – $time2″ | bc`
echo “Finished ( Splitting up to 500000 lines per file ) at :$time1. Total time:$time3 ms.”

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.

Oracle CEP Bug – org.springframework.beans.TypeMismatchException

I have installed the Oracle Complex Event Processing 11.1.3. I will have described what I am doing by the end of this month. Anyway I decided to use Event
Partitioning feature to achieve scalability. When I use a channel with event partitioning feature I was getting error :

org.springframework.beans.factory.BeanCreationException: Error creating bean with name

‘inputChannel’: Invocation of init method failed; nested exception is

org.springframework.beans.PropertyBatchUpdateException; nested PropertyAccessExceptions (1) are:
PropertyAccessException 1: org.springframework.beans.TypeMismatchException: Failed to convert

property value of type [java.lang.String] to required type

[com.bea.wlevs.channel.EventPartitioner] for property ‘eventPartitioner’; nested exception is

java.lang.IllegalArgumentException: Cannot convert value of type [java.lang.String] to required

type [com.bea.wlevs.channel.EventPartitioner] for property ‘eventPartitioner’: no matching

editors or conversion strategy found

In CEP documentation,
( Section 19.1, “How to Configure Scalability With an Event Partitioner Channel” )

it says we could achieve scalability by putting this piece of code into channel configuration part of EPN assembly file:

<wlevs:instance-property name=”eventPartitioner” value=”true” />

However, it does not working. I created a Metalink SR and they answered me as they filed a bug about this issue(9964253).

Getting rid of this error is simple. Change name of instance-property from “eventPartitioner” to “partitionByEventProperty” and change value parameter to a varible name of your Event Class like this:

<wlevs:instance-property name=”partitionByEventProperty” value=”symbol” />

Your event class should have a “symbol” variable.

public String symbol;

SIMPLE_INTEGER as performance booster [ Comparing SIMPLE_INTEGER, PLS_INTEGER, NUMBER data types ]

I’ve given education about the Advanced features of PL/SQL in our company academy. While i was preparing the education, simple_integer data type drawn my attention. This data type introduced with Oracle 11g. I wanted to compare this data type to pls_integer and number.

If you are sure with simple_integer variable that won’t be null, you can use this data type instead of pls_integer ( Also you know that pls_integer faster than number variable because it uses the hardware calculations instead of programmatic calculations like number).

To compare this i’ve written 3 split functions that takes an in parameter and takes an out parameter that is array of splitted parts of this string.

Firstly we create a string array type :

CREATE OR REPLACE TYPE AQ_ADM_PRTP_V204.ARR_VARCHAR_100 IS TABLE OF VARCHAR2(100)
/

Secondly write 3 split functions that uses simple_integer, pls_integer, number data types respectively.

Procedure that uses simple_integer data types :

CREATE OR REPLACE PROCEDURE AQ_ADM_PRTP_V204.PROC_SPLIT_SIMPLE_INTEGER(pv_param_string VARCHAR2, strings IN OUT NOCOPY ARR_VARCHAR_100)
IS
ln_comma simple_integer := 1;
ln_next simple_integer := 1;
ln_index simple_integer := 0;
BEGIN

ln_comma := instr(pv_param_string, ‘,’, 1, 1);
WHILE (ln_comma > 0) LOOP
ln_index := ln_index + 1;
strings(ln_index) := substr(pv_param_string,
ln_next,
ln_comma – ln_next);
ln_next := ln_comma + 1;
ln_comma := instr(pv_param_string, ‘,’, ln_next, 1);
END LOOP;

ln_index := ln_index + 1;
ln_comma := length(pv_param_string) + 1;
strings(ln_index) := substr(pv_param_string, ln_next, ln_comma – ln_next);

END;
/

Procedure that uses pls_integer data types :

CREATE OR REPLACE PROCEDURE AQ_ADM_PRTP_V204.PROC_SPLIT_PLS_INTEGER(pv_param_string VARCHAR2, strings IN OUT NOCOPY ARR_VARCHAR_100)
IS
ln_comma pls_integer := 1;
ln_next pls_integer := 1;
ln_index pls_integer := 0;
BEGIN

ln_comma := instr(pv_param_string, ‘,’, 1, 1);
WHILE (ln_comma > 0) LOOP
ln_index := ln_index + 1;
strings(ln_index) := substr(pv_param_string,
ln_next,
ln_comma – ln_next);
ln_next := ln_comma + 1;
ln_comma := instr(pv_param_string, ‘,’, ln_next, 1);
END LOOP;

ln_index := ln_index + 1;
ln_comma := length(pv_param_string) + 1;
strings(ln_index) := substr(pv_param_string, ln_next, ln_comma – ln_next);

END;
/

Procedure that uses number data types :

CREATE OR REPLACE PROCEDURE AQ_ADM_PRTP_V204.PROC_SPLIT_NUMBER(pv_param_string VARCHAR2, strings IN OUT NOCOPY ARR_VARCHAR_100)
IS
ln_comma NUMBER := 1;
ln_next NUMBER := 1;
ln_index NUMBER := 0;
BEGIN

ln_comma := instr(pv_param_string, ‘,’, 1, 1);
WHILE (ln_comma > 0) LOOP
ln_index := ln_index + 1;
strings(ln_index) := substr(pv_param_string,
ln_next,
ln_comma – ln_next);
ln_next := ln_comma + 1;
ln_comma := instr(pv_param_string, ‘,’, ln_next, 1);
END LOOP;

ln_index := ln_index + 1;
ln_comma := length(pv_param_string) + 1;
strings(ln_index) := substr(pv_param_string, ln_next, ln_comma – ln_next);

END;
/

After those we write test cases :

DECLARE

ln_iteration_no SIMPLE_INTEGER := 1000000;
strings ARR_VARCHAR_100 := ARR_VARCHAR_100();
ln_temp VARCHAR2(1000);
ln_time1 NUMBER;
ln_time2 NUMBER;
ln_time3 NUMBER;
ln_time4 NUMBER;
BEGIN

strings.extend(47);
ln_temp :=

’2000,2,123553168,1,10,64895,65535,27662,64860,64895,65535,27662,64860,0,,,,,,0,0,2491039806,,,,,,,,,0,0,1,,24910391

06,,,,,,,’ ||
’1′ || ‘,,,,,’;

ln_time1 := DBMS_UTILITY.GET_TIME;
FOR i IN 1..ln_iteration_no
LOOP

proc_split_simple_integer(ln_temp,strings);

END LOOP;
ln_time2 := DBMS_UTILITY.GET_TIME;
FOR i IN 1..ln_iteration_no
LOOP

proc_split_pls_integer(ln_temp,strings);

END LOOP;
ln_time3 := DBMS_UTILITY.GET_TIME;
FOR i IN 1..ln_iteration_no
LOOP

proc_split_number(ln_temp,strings);

END LOOP;
ln_time4 := DBMS_UTILITY.GET_TIME;

DBMS_OUTPUT.PUT_LINE(‘Total Duration of proc_split_simple_integer procedure is : ‘||((ln_time2-ln_time1)/100)||

‘ seconds.’);
DBMS_OUTPUT.PUT_LINE(‘Total Duration of proc_split_pls_integer procedure is : ‘||((ln_time3-ln_time2)/100)|| ‘

seconds.’);
DBMS_OUTPUT.PUT_LINE(‘Total Duration of proc_split_number procedure is : ‘||((ln_time4-ln_time3)/100)|| ‘

seconds.’);

END;

results :

Total Duration of proc_split_simple_integer procedure is : 16,7 seconds.
Total Duration of proc_split_pls_integer procedure is : 18,6 seconds.
Total Duration of proc_split_number procedure is : 33,19 seconds.

In this example, SIMPLE_INTEGER data type is faster twice time than NUMBER data type.

If you do not use the native compilation method, you could see much time of previous results. Notice that as specified in document, if you do not use native compilation, procedure that uses simple_integer variable takes much time of pls_integer.

Interpreted compilation results :

Total Duration of proc_split_simple_integer procedure is : 29,1 seconds.
Total Duration of proc_split_pls_integer procedure is : 27,24 seconds.
Total Duration of proc_split_number procedure is : 46,42 seconds.

SIMPLE_INTEGER slower than PLS_INTEGER when you are not using NATIVE compilation method.

To get more information about NATIVE and INTERPRETED compilation, here.

Automatically Backuping Apex Applications

Automatically Backuping Apex Applications

We can export our apex applications as an sql file in “Home>Application Builder>Application $ID>Export / Import>Export” menu. You can do this as manually, so if you want to backup your apex application(s) you should do this for each time for each application.

As you guess this operation runs a plsql command in background to export application.

“wwv_flow_utilities” public package of apex, includes “export_application_to_clob” function that returns clob variable that containts sql statements. These sql statements those are application metadata that will be imported your workspace. Spec part of function is below :

function export_application_to_clob (
p_application_id in number,
p_export_saved_reports in varchar2 default ‘N’)
return clob
;

Application ID is a number that user specified or automatically got from apex. That is on right of f?p= parameter of url string. You should specify application id to export application. Another parameter is p_export_saved_reports is optional, if you want to export saved report you can set this as ‘Y’.

You can write a dbms_scheduler job that runs this plsql for every specified interval time. Or you can write a unix shell script that produces an sql file, put this shell script to crontab to run this backup operations for every specified interval time. Or anything else…

Also you can export only a page of an application, this function is under wwv_flow_utilities package too, spec part of this :

function export_page_to_clob (
p_application_id in number,
p_page_id in number)
return CLOB
;

Example :

CREATE TABLE EXPORT_CLOB
(
APP_EXPORT CLOB
);

BEGIN

INSERT INTO EXPORT_CLOB
VALUES (WWV_FLOW_UTILITIES.EXPORT_APPLICATION_TO_CLOB(’107′));

END;

SELECT length(app_export) FROM EXPORT_CLOB;

954956

To backup your all of applications you can get application id from “select * from apex_applications”.

This example was tested on Oracle 11.1.0.6 and Apex 3.2.

Apex Interactive Reports with Dynamic Sql

Interactive Reports in Apex, presents too many features that will be performed on reports. You can apply specified column filtering, highlighted specified values in a column, use aggregation functions, ajax paginations are some of these features. To use these features you can create a report region as Interactive report ( In a page attributes, Create Region > Report > Interactive Report ).

While creating Interactive Reports, apex wants from you an sql word. It must be pure sql unlike varchar2 variable that stores sql ( in standart reports you can use a varchar2 variable that stores sql. For example lv_dynamic_sql := ‘SELECT EMP_NO FROM EMPLOYEE’; return lv_dynamic_sql; ). So in normally you can’t use dynamic sql in interactive report. I said “in normally” :)

By using PIPELINED FUNCTIONS you can use dynamic sql in Apex Interactive Reports.

In Interactive Reports, you will specify sql as “SELECT * FROM TABLE(table_function);”

After that you should design your “table_function()” function.

First of all you should create an object type that matches a report row fields.

CREATE OR REPLACE TYPE OBJ_STREAM_FILTERED AS OBJECT
(
a_rowid VARCHAR2(100),
ActivationType VARCHAR2(100),
ActivationCellCgi VARCHAR2(100),
TerminationCellCgi VARCHAR2(100),
IMSI VARCHAR2(100),
IMEI_TAC VARCHAR2(100),
IMEI_LAC VARCHAR2(100),
ActivationSec VARCHAR2(100),
OriginationCellCgi VARCHAR2(100),
TargetCellCgi VARCHAR2(100),
ENQ_DATE DATE,
INSERT_DATE DATE,
REMOTE_HOST VARCHAR2(100),
IMEI VARCHAR2(100),
MSISDN_NUMBER VARCHAR2(100),
GENERAL_SEGMENT VARCHAR2(100),
GENERAL_SUB_SEGMENT VARCHAR2(100)
)
/

Your report fields should match these object type fields.

After that you should design your “table_function” function.

CREATE OR REPLACE FUNCTION AQ_ADM_PRTP_V204.get_table_rows2(p_table_name VARCHAR2)
RETURN OBJ_STREAM_FILTERED_ARR
PIPELINED IS
out_rec OBJ_STREAM_FILTERED := OBJ_STREAM_FILTERED(NULL,
null,
NULL,
NULL,
null,
null,
null,
null,
null,
null,
null,
null,
null,
NULL,
NULL,
NULL,
NULL);
p sys_refcursor;

v0 VARCHAR2(100);
v1 VARCHAR2(100);
v2 VARCHAR2(100);
v3 VARCHAR2(100);
v4 VARCHAR2(100);
v5 VARCHAR2(100);
v6 VARCHAR2(100);
v7 VARCHAR2(100);
v8 VARCHAR2(100);
v9 VARCHAR2(100);
v10 VARCHAR2(100);
v11 VARCHAR2(100);
v12 VARCHAR2(100);
v13 VARCHAR2(100);
v14 VARCHAR2(100);
v15 VARCHAR2(100);
v16 VARCHAR2(100);
t1 TIMESTAMP(6);
t2 TIMESTAMP(6);
BEGIN
OPEN p FOR ‘SELECT
t.rowid,
t.*,
t2.imei,
t2.msisdn_number,
t2.general_Segment,
t2.general_sub_segment
FROM
‘ || p_table_name || ‘ t,
syn_rds_imsi_info t2
WHERE
CONCAT(CONCAT(column15,column16),column17) = t2.IMSI(+)’;
LOOP
FETCH p
INTO v0, v1, v2, v3, v4, v5, v6, v7, v8, v9, v10, v11, t1, t2, v12, v13, v14, v15, v16;
EXIT WHEN p%NOTFOUND;
out_rec.a_rowid := v0;
out_rec.ActivationType := v1;
Out_rec.ActivationCellCgi := v2;
out_rec.TerminationCellCgi := v3;
out_rec.IMSI := v4 || v5 || v6;
out_rec.IMEI_TAC := v7;
out_rec.IMEI_LAC := v8;
out_rec.OriginationCellCgi := v10;
out_rec.TargetCellCgi := v11;
out_rec.ActivationSec := v9;
out_rec.ENQ_DATE := t1;
out_rec.INSERT_DATE := t2;
out_rec.REMOTE_HOST := v12;
out_rec.IMEI := v13;
out_rec.MSISDN_NUMBER := v14;
out_rec.GENERAL_SEGMENT := v15;
out_rec.GENERAL_SUB_SEGMENT := v16;
PIPE ROW(out_rec);
END LOOP;
CLOSE p;
RETURN;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.format_error_backtrace);
DBMS_OUTPUT.PUT_LINE(SQLCODE);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

You can check this function accuracy:

SELECT * FROM TABLE(get_table_rows2(‘galata_report.FUAT123′));

Anymore, there is no need to do anything to use this sql in interactive report.

If you get an uniquely related error you can specify a unique column ( a_rowid in this example ) in Report Attributes > Link Column section.

Edited on 05.10.2010 :
There is a better solution to handle more dynamic sql. Which means that you can prepare dynamic interactive report for any sql. Those sqls do not need to has same number of columns or same column names. It is demonstrated at http://www.oracleapplicationexpress.com/tutorials/71 .

Solution of “ORA-25307: Enqueue rate too high”

One of the method of to speed up the performance of Oracle Advanced Queue is using Buffered Messages. In default, maxiumum 5000 non-dequeued messages can reside in memory. If you enqueued more than 5000 messages that have not dequeued yet, you will get an error like : “ORA-25307: Enqueue rate too high”. So, your messages will be written to disk.

Oracle uses this error to prevent memory overflow when you are enqueuing messages. You can increase this number by setting a system parameter.

SQL> alter system set “_buffered_publisher_flow_control_threshold”=500000 scope=both;

System altered

SQL> show parameter buffered;

NAME TYPE VALUE
———————————— ———– ——————————
_buffered_publisher_flow_control_threshold integer 500000

SQL>

After that, you can observe how many messages were spilled to the disk:

SQL> select queue_name,subscriber_name,total_spilled_msg
2 from
3 v$buffered_subscribers
4 ;

QUEUE_NAME SUBSCRIBER_NAME TOTAL_SPILLED_MSG
—————————— —————————— —————–
NLIZT01_STREAM_Q NLIZT01 0
NLCR01_STREAM_Q NLCR01 0
NLER01_STREAM_Q NLER01 0
NLER03_STREAM_Q NLER03 0
NLER02_STREAM_Q NLER02 0
NLCR03_STREAM_Q NLCR03 0
NLCR02_STREAM_Q NLCR02 0
NLIZT03_STREAM_Q NLIZT03 0
NLIZT02_STREAM_Q NLIZT02 0
NLBRS03_STREAM_Q NLBRS03 0
NLBRS02_STREAM_Q NLBRS02 0
NLBRS01_STREAM_Q NLBRS01 0
NLBLK03_STREAM_Q NLBLK03 0
NLBLK02_STREAM_Q NLBLK02 0
NLBLK01_STREAM_Q NLBLK01 0

15 rows selected

SQL>

If you use dbms_capture package, you can use :
alter system set “_capture_publisher_flow_control_threshold”=500000 scope=both;

Follow

Get every new post delivered to your Inbox.