Being a Java Developer or Oracle Developer?

Two weeks ago, i have assigned to call a middleware application via HTTP. The middleware app, accepts XML commands as a webservice manner. I got help from a java developer to write down java codes to establish the work. Finally a java application is created. I put java into Oracle:
create or replace and compile java source named java_notify as
public class MW
{
public static String ExecuteCommand( String _inputXML, String _MWUrl )
{
String ReturnMessage = "";
try
{
String Line;
java.net.URL MWUrl = new java.net.URL(_MWUrl);
java.net.URLConnection MWConnection = MWUrl.openConnection();
java.io.PrintWriter RequestWriter = null ;
java.io.BufferedReader RequestReader = null ;

MWConnection.setRequestProperty("Content-Type", "text/xml");
MWConnection.setDoOutput(true);

RequestWriter = new java.io.PrintWriter( MWConnection.getOutputStream() );

RequestWriter.println( _inputXML );
RequestWriter.close();
RequestReader = new java.io.BufferedReader( new java.io.InputStreamReader( MWConnection.getInputStream() ) );
Line = RequestReader.readLine() ;
while ( Line != null )
{
ReturnMessage += Line;
Line = RequestReader.readLine();
}
RequestReader.close();
}
catch ( Exception e)
{
ReturnMessage = "ERROR";
ReturnMessage += e.toString();
e.printStackTrace();
}

return ReturnMessage;
}
}
/

I wrap the java source with a function:
CREATE OR REPLACE FUNCTION fnc_Not
(
pis_InputXML IN VARCHAR2,
pis_MWUrl IN VARCHAR2
) RETURN VARCHAR2 AS
LANGUAGE JAVA NAME 'MW.ExecuteCommand( java.lang.String, java.lang.String ) return java.lang.String';
/

Then call this function, from packages. The process logic was loading a java source into Oracle. I start to write pure oracle codes to accomplish this task. I write some Oracle codes:
--create request : set meta data of request object
--POST method and HTTP 1.0 protocol
vt_HttpRequest := utl_http.begin_request(vs_MWUrl,'POST','HTTP/1.0');
--XML text and capable for turkish chars
utl_http.set_header(vt_HttpRequest,'Content-Type', 'text/xml;charset=UTF-8');
--set content length
utl_http.set_header(vt_HttpRequest,'Content-Length', length(pis_Request));
--assign input XML to request object
utl_http.write_text(vt_HttpRequest, pis_Request);
--execute command
vt_HttpResponse := utl_http.get_response(vt_HttpRequest);
--get response object from request.
utl_http.read_text(vt_HttpResponse, vs_Response);
--close response
utl_http.end_response(vt_HttpResponse);

These two approach does the same work. So, being a java programmer for oracle programmers is not important. For instance, i am not a java developer; but i can do all my work with Oracle.

Where Is Default Values Of Procedure Arguments?

Oracle has powerful utilities to extract metadata of database objects. [dba|all|user]_* views and some dbms_* packages gives related information. These features are very useful developers who writes code generation utilities like me. I always playing with these packages and views to generate automized PL/SQL codes. I will share my utilities as soon as possible with this blog.
Let's come the subject that i mentioned in the subject. "Where Is Default Values Of Procedure Arguments?" Has anyone knows where they are, please contact me :). I want to share my experiences about this job:

Suppose procedure below
SQL> CREATE OR REPLACE PROCEDURE temp_prc
2 (
3 pin_Argument1 IN NUMBER DEFAULT 123456,
4 pis_Argument2 IN VARCHAR2
5 ) IS
6 BEGIN
7 NULL;
8 END temp_prc;
9 /
Procedure created
SQL>
If you query [dba|all|user]_arguments view you can not see default values.
SQL> SELECT a.argument_name, a.data_type, a.default_value, a.in_out
2 FROM user_arguments a
3 WHERE a.object_name = 'TEMP_PRC';< /FONT >
ARGUMENT_NAME DATA_TYPE DEFAULT_VALUE IN_OUT
------------------ ------------ ------------------ ---------
PIS_ARGUMENT2 VARCHAR2 IN
PIN_ARGUMENT1 NUMBER IN
SQL>

In the documentation of 9i:

DEFAULT_VALUE( LONG )       : Default value for the argument

DEFAULT_LENGTH( NUMBER ) : Length of the default value for the argument
This information is wrong. ( Oracle Bug 24176 )

But in documentation 10g, it was corrected.
DEFAULT_VALUE( LONG )      : Reserved for future use
DEFAULT_LENGTH( NUMBER ) : Reserved for future use

Is there any way to find out deafult values? Why oracle hides this information? I have not answered the questions now. Maybe a bit later, they can be solved. I found some sys views that shows if an argument has a default value or not. But still no information about default values!

SQL> SELECT o.NAME, a.argument, a.default#, a.default$
2 FROM sys.obj$ o, sys.argument$ a
3 WHERE o.obj# = a.obj#
4 AND o.NAME = 'TEMP_PRC';
NAME ARGUMENT DEFAULT# DEFAULT$
------------------------------ ------------------------------ ---------- --------
TEMP_PRC PIN_ARGUMENT1 1
TEMP_PRC PIS_ARGUMENT2
SQL>
Another simple replacement for query above can be describe comamnd of SQL*Plus
SQL> describe TEMP_PRC;
Parameter Type Mode Default?
------------- -------- ---- --------
PIN_ARGUMENT1 NUMBER IN Y
PIS_ARGUMENT2 VARCHAR2 IN
SQL>

For programmatic interface, there is a supplied package, dbms_describe, that describes how program units has interfaces. Again there is no way to find out default values...
SQL> DECLARE
2 overload sys.dbms_describe.number_table;
3 position sys.dbms_describe.number_table;
4 LEVEL sys.dbms_describe.number_table;
5 argument_name sys.dbms_describe.varchar2_table;
6 datatype sys.dbms_describe.number_table;
7 default_value sys.dbms_describe.number_table;/*default_value 1 if the argument being described has a default value; otherwise, the value is 0. */
8 in_out sys.dbms_describe.number_table;
9 length sys.dbms_describe.number_table;
10 PRECISION sys.dbms_describe.number_table;
11 scale sys.dbms_describe.number_table;
12 radix sys.dbms_describe.number_table;
13 spare sys.dbms_describe.number_table;
14 BEGIN
15 sys.dbms_describe.describe_procedure(object_name => 'HR.TEMP_PRC',
16 reserved1 => NULL,
17 reserved2 => NULL,
18 overload => overload,
19 position => position,
20 LEVEL => LEVEL,
21 argument_name => argument_name,
22 datatype => datatype,
23 default_value => default_value,
24 in_out => in_out,
25 length => length,
26 PRECISION => PRECISION,
27 scale => scale,
28 radix => radix,
29 spare => spare,
30 include_string_constraints => TRUE);
31
32
33 FOR i IN 1 .. overload.LAST LOOP
34 dbms_output.put_line('default value for argument ' || argument_name(i) ||
35 ' is ' || default_value(i));
36 END LOOP;
37 END;
38 /
default value for argument PIN_ARGUMENT1 is 1
default value for argument PIS_ARGUMENT2 is 0
PL/SQL procedure successfully completed
SQL>

There are some other ways to get procedure codes programatically. One of them is metadata API:
SQL> SELECT dbms_metadata.get_ddl('PROCEDURE','TEMP_PRC', 'HR') prc_ddl FROM dual;
CREATE OR REPLACE PROCEDURE "HR"."TEMP_PRC"
(
pin_Argument1 IN NUMBER DEFAULT 123456,
pis_Argument2 IN VARCHAR2
) IS
BEGIN
NULL;
END temp_prc;
PL/SQL procedure successfully completed
--
The other way is dbms_preprocessor package:
SQL> exec dbms_preprocessor.print_post_processed_source('PROCEDURE', 'HR', 'TEMP_PRC' );
PROCEDURE temp_prc
(
pin_Argument1 IN NUMBER DEFAULT 123456,
pis_Argument2 IN VARCHAR2
) IS
BEGIN
NULL;
END temp_prc;

Finally I could not find out where they are....

Solution of "ORA-00314: log of thread , expected sequence# doesn't match "

ast week i struggled a problem that was caused by ORA 00314. We have an old database :) which was not opened for 3 or more months. When i try to open the database i get an error:
SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1302944 bytes
Variable Size 106700384 bytes
Database Buffers 205520896 bytes
Redo Buffers 1048576 bytes
Database mounted.ORA-00314: log 1 of thread 1, expected sequence# 50 doesn't match 377
ORA-00312: online log 1 thread 1: '/data07/oradata/testdb/redo01.log'

First i create a backup of control file:

SQL> alter database backup controlfile to trace;
Database altered.

Then i check udump directory. There are some trace files To find out which one contains control file, i simply make a text search :
testdb oracle@aurora:/data06/app/oracle/admin/testdb/udump> grep 'CREATE CONTROLFILE REUSE DATABASE' *.*
testdb_ora_181267.trc:CREATE CONTROLFILE REUSE DATABASE "testdb" NORESETLOGS NOARCHIVELOG
testdb_ora_181267.trc:CREATE CONTROLFILE REUSE DATABASE "testdb" RESETLOGS NOARCHIVELOG
I execute the commands which is included with CREATE CONTROLFILE REUSE DATABASE
SQL> startup nomount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1302944 bytes
Variable Size 106700384 bytes
Database Buffers 205520896 bytes
Redo Buffers 1048576 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "testdb" RESETLOGS NOARCHIVELOG
2 MAXLOGFILES 4
3 MAXLOGMEMBERS 4
4 MAXDATAFILES 1000
5 MAXINSTANCES 1
6 MAXLOGHISTORY 4544
7 LOGFILE
8 GROUP 1 '/data07/oradata/testdb/redo01.log' SIZE 50M,
9 GROUP 2 '/data07/oradata/testdb/redo02.log' SIZE 50M,
10 GROUP 3 '/data07/oradata/testdb/redo03.log' SIZE 50M
11 DATAFILE
12 '/data07/oradata/testdb/system01.dbf',
13 '/data07/oradata/testdb/undo01.dbf',
14 '/data07/oradata/testdb/sysaux01.dbf',
15 '/data07/oradata/testdb/users01.dbf',
16 '/data07/oradata/testdb/data01.dbf',
17 '/data07/oradata/testdb/work01.dbf',
18 '/data07/oradata/testdb/ascs01.dbf',
19 '/data07/oradata/testdb/uccs_data01.dbf',
20 '/data07/oradata/testdb/uccs_index01.dbf'
21 CHARACTER SET WE8ISO8859P9
22 ;
Control file created.
Then recover db.
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE

Workaround for "ORA-00600: internal error code, arguments: [kmgs_getgran_from_comp_1]"

A few days ago i was importing an dump file to oracle 10.1. I get error below at the end of import.
IMP-00058: ORACLE error 3113 encountered
ORA-03113: end-of-file on communication channel
IMP-00000: Import terminated unsuccessfully
Then i looked the trace file to learn more about error. The trace file says an internal error that is simply shown below:

*** SERVICE NAME:(SYS$BACKGROUND) 2007-05-24 07:10:49.050
*** SESSION ID:(170.1) 2007-05-24 07:10:49.050
*** 2007-05-24 07:10:49.050
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kmgs_getgran_from_comp_1], [], [], [], [], [], [], []
When i research, this error caused by 4153846 bug of oracle which is resolved 10.2 release. To workaround of this error which i follow was described as:

SQL> alter system set "_memory_broker_shrink_java_heaps"=0;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1526726656 bytes
Fixed Size 1302616 bytes
Variable Size 432807848 bytes
Database Buffers 1090519040 bytes
Redo Buffers 2097152 bytes
Database mounted.
Database opened.
SQL>

ORACLE_SID and TWO_TASK Environment Variables For Unix To Connect Oracle

There are two environment variables for Unix to connect an Oracle database.One of them is ORACLE_SID and the other one is TWO_TASK. ORACLE_SID is used for connecting a database which is installed to the same host. TWO_TASK is used for connecting an oracle database which is installed another host.
Normally, when you connect oracle via SQL*Plus you can specify a connect string such scott/tiger@PRODB. If this is specified, oracle uses tnsnames entry which is included in tnsnames.ora file. This kind of connection, uses listener and needs a running listener.
oracle@aurora:/usr/users/oracle> sqlplus scott/tiger@prodb
oracle@aurora:/usr/users/oracle> ps -ef | grep oracleprodb
oracle 351026 1 0.0 Jun 18 ?? 0:00.20 oracleprodb (LOCAL=NO)
If you do not specify connect string which is @PRODB suffix in given instance, uses BEQ adapter.
oracle@aurora:/usr/users/oracle> sqlplus scott/tiger@
oracle@aurora:/usr/users/oracle> ps -ef | grep oracleprodb
oracle 482176 482205 0.1 01:44:54 ?? 0:00.14 oracleprodb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

When clients connects to oracle they either use listener or not. Connection via ORACLE_SID does not require LISTENER process and uses BEQ SQL*Net protocol adapter which uses "Unix pipe"s ,not network hardware, to connect database whereas TWO_TASK not.

bash-3.00$ export ORACLE_SID=testpoc
bash-3.00$ export TWO_TASK=atest04.world
bash-3.00$ sqlplus test/test
SQL*Plus: Release 10.1.0.4.0 - Production on Wed Jun 20 12:28:10 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select name from v$database;
NAME
---------
ATEST04
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
bash-3.00$ echo $TWO_TASK
atest04.world
bash-3.00$ echo $ORACLE_SID
testpoc
bash-3.00$ export TWO_TASK=testpoc
bash-3.00$ export ORACLE_SID=testpoc
bash-3.00$ sqlplus test/test
SQL*Plus: Release 10.1.0.4.0 - Production on Wed Jun 20 12:30:52 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select name from v$database;
NAME
---------
TESTPOC
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
bash-3.00$ export ORACLE_SID=atest04.world
bash-3.00$ export TWO_TASK=testpoc
bash-3.00$ sqlplus test/test
SQL*Plus: Release 10.1.0.4.0 - Production on Wed Jun 20 12:31:20 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select name from v$database;
NAME
---------
TESTPOC
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
bash-3.00$ export TWO_TASK=atest04.world
bash-3.00$ export ORACLE_SID=atest04.world
bash-3.00$ sqlplus test/test
SQL*Plus: Release 10.1.0.4.0 - Production on Wed Jun 20 12:31:49 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select name from v$database;
NAME
---------
ATEST04
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
bash-3.00$

TWO_TASK has higher priority than ORACLE_SID
ORACLE_SID TWO_TASK SQLPLUS
____________ ____________ _______
testpoc atest04.world ATEST04
testpoc testpoc TESTPOC
atest04.world testpoc TESTPOC
atest04.world atest04.world ATEST04


Connection via BEQ, do not need a listener whereas w/out needs.
bash-3.00$ lsnrctl stop
LSNRCTL for Solaris: Version 10.1.0.4.0 - Production on 20-JUN-2007 12:53:05
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connectingto(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
The command completed successfully
bash-3.00$ sqlplus test/test@testpoc
SQL*Plus: Release 10.1.0.4.0 - Production on Wed Jun 20 12:53:28 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12541: TNS:no listener

bash-3.00$ export ORACLE_SID=testpoc
bash-3.00$ unset TWO_TASK
bash-3.00$ sqlplus test/test
SQL*Plus: Release 10.1.0.4.0 - Production on Wed Jun 20 12:55:51 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select name from v$database;
NAME
---------
TESTPOC
SQL>

Character Encoding Problem While Executing Web Services With Oracle

A few months ago, I have writen a post about web services in Oracle. It works funny until one of my colleagues opened me a defect :( The defect was about, utl_http package of oracle, was not able to handle Turkish characters. I walk around the code again I realized that, I have forgetten set Content-Type property in http header of web service. I made a small modification and it runs correctly again. The only thing i did is that setting charset property of http request.
I put down the simple code below:
DECLARE
vs_Request VARCHAR2(32767);
vs_Response VARCHAR2(32767);
http_request utl_http.req;
http_response utl_http.resp;
response_xml xmltype;
vs_Url VARCHAR2(64);
BEGIN
vs_Request := 'some well defined xml data.';
vs_Url := 'http://10.14.194.224/WS';


http_request := utl_http.begin_request(vs_Url, 'POST', 'HTTP/1.0');
utl_http.set_header(http_request, 'Content-Type', 'text/xml;charset=UTF-8');
utl_http.set_header(http_request, 'Content-Length', length(vs_Request));
utl_http.write_text(http_request, vs_Request);

http_response := utl_http.get_response(http_request);

utl_http.read_text(http_response, vs_Response);
utl_http.end_response(http_response);
dbms_output.put_line('Response of request is :');
dbms_output.put_line(substr(vs_Response, 1, 255));
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error occured while requesting...');
dbms_output.put_line('Error is ');
dbms_output.put_line(SQLERRM);

END;


But be careful on red line of code. If you put a space such as 'text/xml; charset=UTF-8' you can get error again.

Shortest Path Algorithm In Oracle

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL> create table distances(
2 from_city varchar2(16),
3 to_city varchar2(16),
4 distance number
5 );

Table created

SQL> insert into distances values('Istanbul', 'Ankara', 600);

1 row inserted

SQL> insert into distances values('Ankara', 'Izmir', 800);

1 row inserted

SQL> insert into distances values('Istanbul', 'Izmir', 500);

1 row inserted

SQL> insert into distances values('Izmir', 'Antalya', 250);

1 row inserted

SQL> insert into distances values('Ankara', 'Antalya', 450);

1 row inserted

SQL> SELECT * FROM distances;

FROM_CITY TO_CITY DISTANCE
---------------- ---------------- ----------
Istanbul Ankara 600
Ankara Izmir 800
Istanbul Izmir 500
Izmir Antalya 250
Ankara Antalya 450

SQL> SELECT connect_by_root from_city || sys_connect_by_path(to_city, ',') total_path,
2 sys_connect_by_path(distance, '+') total_distance
3 FROM distances
4 START WITH from_city = 'Istanbul'
5 CONNECT BY PRIOR to_city = from_city;

TOTAL_PATH TOTAL_DISTANCE
---------------------------------------- -------------------------------------------------
Istanbul,Ankara +600
Istanbul,Ankara,Izmir +600+800
Istanbul,Ankara,Izmir,Antalya +600+800+250
Istanbul,Ankara,Antalya +600+450
Istanbul,Izmir +500
Istanbul,Izmir,Antalya +500+250

6 rows selected

SQL> --
SQL> DECLARE
2 CURSOR cur_D IS(
3 SELECT tot_path, tot_dis
4 FROM (SELECT sys_connect_by_path(to_city, ',') tot_path,
5 sys_connect_by_path(distance, '+') tot_dis
6 FROM distances
7 START WITH from_city = 'Istanbul'
8 CONNECT BY PRIOR to_city = from_city) t
9 WHERE instr(tot_path,'Antalya') > 0);
10 rec_D cur_D%ROWTYPE;
11 vn_Total NUMBER;
12 vn_MinDistance NUMBER;
13 vs_MinPath VARCHAR2(255);
14 BEGIN
15 vn_MinDistance := 999999999999999999999999999999999999999;
16
17 OPEN cur_D;
18 LOOP
19 FETCH cur_D
20 INTO rec_D;
21 EXIT WHEN cur_D%NOTFOUND;
22 EXECUTE IMMEDIATE 'BEGIN :vn_Total := ' || rec_D.tot_dis || '; END;'
23 USING OUT vn_Total;
24 IF vn_MinDistance > vn_Total THEN
25 vn_MinDistance := vn_Total;
26 vs_MinPath := rec_D.tot_path;
27 END IF;
28
29 dbms_output.put_line(rec_D.tot_path || ' = ' || vn_Total);
30 END LOOP;
31 CLOSE cur_D;
32
33 dbms_output.put_line('Shortest ');
34 dbms_output.put_line(vs_MinPath || ' = ' || vn_MinDistance);
35
36 END;
37 /

,Ankara,Izmir,Antalya = 1650
,Ankara,Antalya = 1050
,Izmir,Antalya = 750
Shortest
,Izmir,Antalya = 750

PL/SQL procedure successfully completed

SQL>