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.

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;

About Oracle Date and useful functions

Oracle has powerful date functions. Sometimes you need to convert it to another type or another type has to be converted to date type or you need trunc date to display only part of date or anything else.

So, you can lots of things on date type. I will try to tell some useful operations on date types.

Date type placed as 7 bytes on disk. It stores year, month, day, hour, minute, second. Timestamp type placed as 11 bytes. And it stores addition to date fraction of seconds and time zone information. Especially, it is useful for real time applications to measure where does the time pass. ( because of I used this type )

For example for 15.05.2009 18:01:00, Oracle do not store it as how it was written. It is stored as a different internal style as 7 bytes and this style of storing is advantageous when you trunc or convert it to another type or sort date datas.

Formatting date is useful and necessary. When you rely on the default date format, your application may be in the future affected negatively ( I have experienced it twice ).

First and most known function is to_date and to_char functions.
For DATE type, you can convert this date type to char :

TO_CHAR(date,’DATEFORMAT’);
If you do not specify the date format, your date type will be trunced.

SQL> select to_Char(sysdate) from dual;

TO_CHAR(SYSDATE)
—————-
10/06/2009

What is the date format? What part of dates can be specified?
There are lots of documents on web that mentions about it and i don’t repeat it
( Oracle Date Parts that can be extracted ). Most known and used of them are DD, MM, YYYY, HH24, HH, MI, SS.

For example :

SQL> select to_Char(sysdate,’DD/MM/YYYY HH24:MI:SS’) from dual;

TO_CHAR(SYSDATE,’DD/MM/YYYYHH2
——————————
10/06/2009 23:39:39

So far, we saw that how date type can be converted to char. Inversely, convert char to date is similar to it.

SQL> select to_date(‘12:00:00 04.06.2009′,’HH24:MI:SS DD.MM.YYYY’) from dual;

TO_DATE(‘12:00:0004.06.2009′,’
——————————
6/4/2009 12:00:00 PM

Date Arithmetics and Useful other Date functions:

Adding X days to a date you use “datevariable + 1″ : SYSDATE + 1
Adding X seconds to a date you use “datevariable + 1/(24*60*60)” : SYSDATE + 1/(24*60*60)
Another adding or substracting operations you can use your mind

Adding months you can use ADD_MONTHS(variable,#months):

SQL> select sysdate, add_months(sysdate,1), add_months(add_months(sysdate,1),1) from dual;

SYSDATE ADD_MONTHS(SYSDATE,1) ADD_MONTHS(ADD_MONTHS(SYSDATE,
———– ——————— ——————————
6/10/2009 1 7/10/2009 11:55:22 PM 8/10/2009 11:55:22 PM

Format is : MONTH DAY YEAR HH24 MI SS

No difference, if current month has 30 or 31 days only month number will increase 1. If we are in the 30 January, according to year add_months(date,1) will return 28-Feb or 29-Feb.

Months_Between :

SQL> select months_between(sysdate+75,sysdate) from dual;

MONTHS_BETWEEN(SYSDATE+75,SYSD
——————————
2.45161290322581

You can trunc it to 2.

First Day Of Month :

SQL> SELECT TO_CHAR(TRUNC(SYSDATE,’MONTH’),’DAY’) FROM DUAL;

TO_CHAR(TRUNC(SYSDATE,’MONTH’)
——————————
MONDAY

Last Day Of Month :

SQL> SELECT TO_CHAR(LAST_DAY(SYSDATE),’DAY’) FROM DUAL;

TO_CHAR(LAST_DAY(SYSDATE),’DAY
——————————
TUESDAY

When next first Friday :

SQL> SELECT TO_CHAR(NEXT_DAY(SYSDATE,’FRIDAY’),’DD’) FROM DUAL;

TO_CHAR(NEXT_DAY(SYSDATE,’FRID
——————————
12

First day of Month

SQL> SELECT TO_CHAR(LAST_DAY(add_months(SYSDATE,-1))+1,’DAY’) FROM DUAL;

TO_CHAR(LAST_DAY(ADD_MONTHS(SY
——————————
PAZARTESI

Now, which # of week we are in :

SQL> select to_Char(sysdate,’IW’) from dual;

TO_CHAR(SYSDATE,’IW’)
———————
24
SQL>

EXTRACT
To extract specified date part from date or timestamp variable. Alternative way is convert date to char and perform substr operation. Former takes less time than latter.

EXTRACT (
{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
| { TIMEZONE_HOUR | TIMEZONE_MINUTE }
| { TIMEZONE_REGION | TIMEZONE_ABBR }
FROM { date_value | interval_value } )

SQL> select extract(hour from systimestamp)||’:'||extract(minute from systimestamp)||’:'||extract(second from systimestamp) from dual;

EXTRACT(HOURFROMSYSTIMESTAMP)|
——————————————————————————–
7:40:7,142039

HOUR, SECOND, MINUTE, TIMEZONE_HOUR, TIMEZONE_MINUTE can be extracted only timestamp.

A number that how many days, hours, minutes, seconds : NUMTODSINTERVAL(num,):

SQL> select numtodsinterval(1440,’MINUTE’) FROM DUAL;

NUMTODSINTERVAL(1440,’MINUTE’)
——————————
+000000001 00:00:00

1440 minutes equals to 1 day ( leftmost piece ).

SQL> SELECT NUMTODSINTERVAL(3602,’SECOND’) FROM DUAL;

NUMTODSINTERVAL(3602,’SECOND’)
——————————
+000000000 01:00:02

3602 seconds equals to 1 hour 2 seconds.

About NLS_DATE

To set default date format for session based.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = ‘YYYY MM DD’;

Session altered

SQL> select sysdate from dual;

SYSDATE
———–
6/17/2009 1

SQL>

Converting unix timestamp to Oracle in PL/SQL
This is may useful your application to convert unix timestamp to oracle date.
Read this post
( Responsibility of adding or subtracting GMT is belong to you )

Another DATE functions :

# NUMTOYMINTERVAL, converts a NUMBER or expression into a YEAR TO MONTH interval.
# TO_DSINTERVAL converts string to DAY TO SECOND interval.
# TO_YMINTERVAL converts a character string to YEAR TO MONTH type

jSch — SSH Api for Java Applications — Ssh Port Forwarding in Java

If you write a java network application, for some security reasons you need to make an ssh connection to remote host. To make an ssh connection you need some parameters. Host address, user name and password are some important of them. To pass typing password you can use “send / expect” application or you can use jSch api that i am going to tell you.

SshHandler class main class of this api, it is like interface that provides method to pass user name and password. And Awaker class is my application class that reads file that includes connection information for each line.

Example line is:

nxxast01;turkcell;xyz;110.15.122.20;9911;127.0.0.1;9999

First piece is name of machine, second is username, third is password, fourth is remote host address, fifth is local port, sixth is where remote host will be directed and seventh is which port that remote host forward. I mentioned this to understand code ( by the way i am not a kind of java expert )

It means that machine 110.15.122.20 connects to 127.0.0.1:9999 then forward data to localhost:9911
( SSH Port Forwarding )

SshHandler Class :

/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/

package nor;

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.JSchException;
import com.jcraft.jsch.Session;
import com.jcraft.jsch.UserInfo;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
*
* @author TTASUNGUR
*/
public class SshHandler {
JSch jsch=new JSch();

public void openSshConnection(String ps_user,String ps_password, String ps_host, String ps_localport, String ps_remotehost, String ps_remoteport ) throws SshConnectionException
{
try
{
System.out.println(Global.info_prefix+”It will be trying to connect remote host : Username : “+ps_user+”, Password : , Remote Host : “+ps_host+”, Localport : “+ps_localport+”, Remote : “+ps_remotehost+”, RemotePort : “+ps_remoteport);
Session session= jsch.getSession(ps_user, ps_host, Global.sshport);
UserInfo ui = new MyUserInfo(ps_password);
session.setUserInfo(ui);
session.connect();
int assigned_port=session.setPortForwardingL(Integer.parseInt(ps_localport), ps_remotehost, Integer.parseInt(ps_remoteport));
System.out.println(Global.info_prefix+”Ssh connection is established for :” + ps_host +”. Localport:”+assigned_port+”:”+ps_remotehost+”:”+ps_remoteport);
}
catch (JSchException ex)
{
System.err.println(Global.error_prefix+”Ssh connection could not be established for :”+ps_host+”, due to :”+ex.getMessage());
ex.printStackTrace();
throw new SshConnectionException(Global.error_prefix+”Ssh connection could not be established for :”+ps_host+”, due to :”+ex.getMessage());
}
}

public static class MyUserInfo implements UserInfo
{
public String getPassword(){ return passwd; }

public MyUserInfo(String password)
{
this.passwd = password;
}

public boolean promptYesNo(String str){
System.out.println(str+”promptYesNo”);

return true;
}

String passwd;

public String getPassphrase(){ return null; }
public boolean promptPassphrase(String message){ return true; }
public boolean promptPassword(String message){
//System.out.println(“promptPassword”);
// passwd = message;
//passwd = “turkcell”;

return true;

}
public void showMessage(String message){
System.out.println(message);
}

public String[] promptKeyboardInteractive(String destination,
String name,
String instruction,
String[] prompt,
boolean[] echo){
return new String[3];
}
}
}

Awaker Class :
( important line is start with ssh.openSshConnection… )

package nor;

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.JSchException;
import com.jcraft.jsch.Session;
import com.jcraft.jsch.UserInfo;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.util.Vector;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
*
* @author TTASUNGUR
*/
public class Awaker {

ExecutorService threadExecutor;
String s_location_file_name = “rtdf_location_ips.rtdf”;
SshHandler ssh = new SshHandler();
String sentinal_files_path;
public Awaker(String sentinal_files_path)
{
this.sentinal_files_path = sentinal_files_path;
}

public void setStreamLocations(String pstr_location_file_name)
{
FileReader l_fr = null;
String ls_temp=”",ls_temp_arr[],ls_local_port;

try
{
File l_f1 = new File(pstr_location_file_name);
l_fr = new FileReader(l_f1);
BufferedReader br = new BufferedReader(l_fr);

try
{
// first line was metada it can be passed
ls_temp = br.readLine();
}
catch (IOException ex)
{
ex.printStackTrace();
}

while ( ls_temp != null)
{
try
{
ls_temp = br.readLine();
System.out.println(Global.info_prefix+”Line was read : “+ls_temp);
ls_temp_arr = ls_temp.split(Global.location_file_splitter);

if ( ls_temp_arr.length != Global.numberof_location_file_fields)
{
System.out.println(Global.error_prefix+”This line has not “+Global.numberof_location_file_fields+” fields”);
}
else
{
try
{
ssh.openSshConnection(ls_temp_arr[1], ls_temp_arr[2], ls_temp_arr[3], ls_temp_arr[4], ls_temp_arr[5], ls_temp_arr[6]);
//StreamHandler sh1 = new StreamHandler(ls_temp_arr[0]+”(“+ls_temp_arr[3]+”)Local Port:”+ls_temp_arr[4],Global.localhost,Integer.parseInt(ls_temp_arr[4]));
//threadExecutor.submit(sh1);

}
catch (SshConnectionException ex)
{
System.out.println(ex.getMessage());
}

}

}
catch (IOException ex)
{
System.out.println(Global.error_prefix+”The line could not be read!”);

}

}
}
catch (FileNotFoundException ex)
{
System.out.println(Global.error_prefix+”Location file are not found : “+s_location_file_name);
ex.printStackTrace();
}
finally
{
try
{
l_fr.close();
}
catch (IOException ex)
{
ex.printStackTrace();
}
}

}

public void startThreads()
{

threadExecutor = Executors.newCachedThreadPool();

FileHandler fh;
fh = new FileHandler(sentinal_files_path);
fh.run();

// stream locations will be set
setStreamLocations(s_location_file_name);

threadExecutor.shutdown(); // nonsense code
if ( threadExecutor.isTerminated() && threadExecutor.isShutdown() )
{
// this message should not be displayed any time.
// because application will not never finish.
System.out.println(“Info : Application has been finished.”);
}
}

}

To download jSch Api, click here.

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

Installing jQuery to Application Express ( APEX )

Sometimes, you want to add different things your page such as different buttons, images, motions. JQuery API provides these wishes by using JavaScript and CSS.

For detail information, examples and to do download this api, visit http://jquery.com.

If you want to import and use this api in your Apex Application you will have to do some steps.

1 ) Download latest version of jQuery Api from http://jquery.com or customizable version at : http://jqueryui.com/download
2 ) Install those files to $ORACLE_HOME/apex/images/jquery/ folder. ( jQuery folder must be created ).
3 ) Then, you should edit the $ORACLE_HOME/apex/images/imagelist.xml file.
This file an xml file and you should add those under the directories node.

…
…
jquery
jquery/theme
jquery/theme/images

And, you should add file paths under the files node. ( Those file names should be changed if you downloaded another version instead of 1.6 Personalized JQuery )

/jquery/jquery-1.3.1.js
/jquery/jquery-ui-personalized-1.6rc6.min.js
/jquery/jquery-ui-personalized-1.6rc6.js
/jquery/theme/ui.accordion.css
/jquery/theme/ui.all.css
/jquery/theme/ui.base.css
/jquery/theme/ui.core.css
/jquery/theme/ui.datepicker.css
/jquery/theme/ui.dialog.css
/jquery/theme/ui.progressbar.css
/jquery/theme/ui.resizable.css
/jquery/theme/ui.slider.css
/jquery/theme/ui.tabs.css
/jquery/theme/ui.theme.css
/jquery/theme/images/ui-bg_diagonals-thick_18_b81900_40×40.png
/jquery/theme/images/ui-bg_diagonals-thick_20_666666_40×40.png
/jquery/theme/images/ui-bg_flat_10_000000_40×100.png
/jquery/theme/images/ui-bg_glass_65_ffffff_1×400.png
/jquery/theme/images/ui-bg_glass_100_f6f6f6_1×400.png
/jquery/theme/images/ui-bg_glass_100_fdf5ce_1×400.png
/jquery/theme/images/ui-bg_gloss-wave_35_f6a828_500×100.png
/jquery/theme/images/ui-bg_highlight-soft_75_ffe45c_1×100.png
/jquery/theme/images/ui-bg_highlight-soft_100_eeeeee_1×100.png
/jquery/theme/images/ui-icons_228ef1_256×240.png
/jquery/theme/images/ui-icons_222222_256×240.png
/jquery/theme/images/ui-icons_ef8c08_256×240.png
/jquery/theme/images/ui-icons_ffd27a_256×240.png
/jquery/theme/images/ui-icons_ffffff_256×240.png
…

4 ) After that, you should run apex_epg_config.sql ( in $ORACLE_HOME/apex directory ) and enter $ORACLE_HOME whichever it is, to refresh file paths.

SQL> @apex_epg_config

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Enter value for 1: D:\app\TTASUNGUR\product\11.1.0\db_1\
old 1: create directory APEX_IMAGES as ‘&1/apex/images’
new 1: create directory APEX_IMAGES as ‘D:\app\TTASUNGUR\product\11.1.0\db_1\/
apex/images’

Directory created.

PL/SQL procedure successfully completed.

Commit complete.

PL/SQL procedure successfully completed.

timing for: Load Images
Elapsed: 00:00:59.68

Session altered.

PL/SQL procedure successfully completed.

Commit complete.

Session altered.

Directory dropped.

5 ) So far, you imported and installed the jquery to your Apex environment. Now, you can use jquery in your apex pages. Let’s do an example, create a page and create a region and we will create a draggable div place.

$(document).ready(function()
{
$(“#tasinabilir”).draggable( { revert: true } );

}
);

Bura tasinabilir bir bolge

( There are many examples at http://jquery.com )
6 ) Then test your page, click div place and drag it to anywhere and release, it will return its first position.

Oracle Apex 3.1 Sunumu

18 Ekimde, OracleTURK grubuna, Oracle Akademi sponsorluğunda, Kadıköy Bireysel Eğitim Merkezinde gerçekleştirmiş olduğum, Oracle Application Express v3.1 sunumu ve workshop esnasında gösterdiğim bazı özelliklere ait kodlar için tıklayın.

For english:
I present the Oracle Application Express v3.1 to OracleTurk Group, at Kadıköy Bireysel Eğitim Merkezi, sponsored by Oracle Turkiye Akademi. To access this presentation click here.