A simple demonstration about how to comparing 2 Oracle Database Instance(or Schema) objects(such as tables)

I want to show, how to compare objects of 2 oracle instances or schemas. Below, i show indexes and referential constraints. You can write for all other objects. There are some tools or scripts to compare 2 schemas. But they compare by object names. For instance if an index is renamed, you can see that as if the index does not exist. I compare the objects by their structures:

--QUERY_001
--This query compares SOURCE_DBLINK and current database in terms of INDEXes.
--Comparison is done only by INDEX_NAMEs. Renamed-indexes can not be found!!!
--Returns INDEXes which exist in only SOURCE_DBLINK
SELECT index_name, table_name
  FROM all_indexes@SOURCE_DBLINK md
WHERE table_owner = 'DBUSER'
   AND NOT EXISTS (SELECT 1
          FROM all_indexes db
         WHERE md.table_owner = db.table_owner
           AND md.index_name  = db.index_name);
--QUERY_002
--This query compares SOURCE_DBLINK and current database in terms of INDEXes.
--Comparison is done by index structure. Renamed-indexes can be found.This is am enhancement of QUERY_001
--Returns INDEXes which exist in only SOURCE_DBLINK
SELECT *
  FROM all_ind_columns@SOURCE_DBLINK md
WHERE table_owner = 'DBUSER'
   AND index_owner = 'DBUSER'
   AND NOT EXISTS (SELECT 1
          FROM all_ind_columns db
         WHERE md.table_owner     = db.table_owner
           AND md.index_owner     = db.index_owner
           AND md.table_name      = db.table_name
           AND md.column_name     = db.column_name
           AND md.column_position = db.column_position);
--QUERY_003
--This query compares SOURCE_DBLINK and current database in terms of Referantial Constraints.
--Comparison is done only by CONTRAINT_NAMEs. Renamed-ref-constraints can not be found!!!
--Returns REF-CONTRAINTs which exist in only SOURCE_DBLINK        
SELECT constraint_name, table_name,
  FROM all_constraints@SOURCE_DBLINK md
WHERE owner = 'DBUSER'
   AND constraint_type = 'R'
   AND NOT EXISTS
(SELECT 1
          FROM all_constraints db
         WHERE md.owner = db.owner
           AND md.constraint_type = db.constraint_type
           AND md.constraint_name = db.constraint_name);
--QUERY_004
--This query compares SOURCE_DBLINK and current database in terms of Referantial Constraints.
--Comparison is done by constraint structure. Renamed-ref-constraints can be found.This is am enhancement of QUERY_003
--Returns REF-CONTRAINTs which exist in only SOURCE_DBLINK         
SELECT *
  FROM all_cons_columns@SOURCE_DBLINK md
WHERE owner = 'DBUSER'
   AND NOT EXISTS
(SELECT 1
          FROM all_cons_columns db
         WHERE md.owner = db.owner
           AND md.table_name = db.table_name
           AND md.column_name = db.column_name
           AND NVL(md.position, 0) = NVL(db.position, 0));

Solution of ORA-02070 "database .... does not support ..... In this context"

Today, when i checking migration reliability i got an error, ORA-02070. When i analyzed that i realized that columns which are LONG data-type were caused of this problem:

Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.5.0
Connected as DBUSER
SQL>
SQL> SELECT *
  2    FROM all_constraints@UAT md
  3   WHERE owner = 'DBUSER'
  4     AND constraint_type = 'R'
  5     AND NOT EXISTS
  6   (SELECT 1
  7            FROM all_constraints db
  8           WHERE md.owner = db.owner
  9             AND md.constraint_type = db.constraint_type
10             AND md.constraint_name = db.constraint_name);
SELECT *
  FROM all_constraints@UAT md
WHERE owner = 'DBUSER'
   AND constraint_type = 'R'
   AND NOT EXISTS
(SELECT 1
          FROM all_constraints db
         WHERE md.owner = db.owner
           AND md.constraint_type = db.constraint_type
           AND md.constraint_name = db.constraint_name)
ORA-02070: veritabanı UAT bu içerik içinde operator USERENV desteklemez
database UAT does not support  USERENV in this context
SQL> describe all_constraints@UAT;
Object all_constraints@UAT does not exist.
SQL> describe all_constraints;
Name              Type         Nullable Default Comments                                                                   
----------------- ------------ -------- ------- ---------------------------------------------------------------------------
OWNER             VARCHAR2(30)                  Owner of the table                                                         
CONSTRAINT_NAME   VARCHAR2(30)                  Name associated with constraint definition                                 
CONSTRAINT_TYPE   VARCHAR2(1)  Y                Type of constraint definition                                              
TABLE_NAME        VARCHAR2(30)                  Name associated with table with constraint definition                      
---->>>>
SEARCH_CONDITION  LONG         Y                Text of search condition for table check                                   
----<<<<
R_OWNER           VARCHAR2(30) Y                Owner of table used in referential constraint                              
R_CONSTRAINT_NAME VARCHAR2(30) Y                Name of unique constraint definition for referenced table                  
DELETE_RULE       VARCHAR2(9)  Y                The delete rule for a referential constraint                               
STATUS            VARCHAR2(8)  Y                Enforcement status of constraint - ENABLED or DISABLED                     
DEFERRABLE        VARCHAR2(14) Y                Is the constraint deferrable - DEFERRABLE or NOT DEFERRABLE                
DEFERRED          VARCHAR2(9)  Y                Is the constraint deferred by default -  DEFERRED or IMMEDIATE             
VALIDATED         VARCHAR2(13) Y                Was this constraint system validated? -  VALIDATED or NOT VALIDATED        
GENERATED         VARCHAR2(14) Y                Was the constraint name system generated? -  GENERATED NAME or USER NAME   
BAD               VARCHAR2(3)  Y                Creating this constraint should give ORA-02436.  Rewrite it before 2000 AD.
RELY              VARCHAR2(4)  Y                If set, this flag will be used in optimizer                                
LAST_CHANGE       DATE         Y                The date when this column was last enabled or disabled                     
INDEX_OWNER       VARCHAR2(30) Y                The owner of the index used by this constraint                             
INDEX_NAME        VARCHAR2(30) Y                The index used by this constraint                                          
INVALID           VARCHAR2(7)  Y                                                                                           
VIEW_RELATED      VARCHAR2(14) Y                                                                                           
SQL>
SQL> SELECT constraint_name, table_name
  2    FROM all_constraints@UAT md
  3   WHERE owner = 'DBUSER'
  4     AND constraint_type = 'R'
  5     AND NOT EXISTS
  6   (SELECT 1
  7            FROM all_constraints db
  8           WHERE md.owner = db.owner
  9             AND md.constraint_type = db.constraint_type
10             AND md.constraint_name = db.constraint_name);
CONSTRAINT_NAME                TABLE_NAME
------------------------------ ------------------------------
....                           .....
....                           .....
....                           .....
26 rows selected
SQL>

Sequence In Oracle RAC System might not generate numbers In a sequence!

Today, after migration to RAC system, i realized that sequence numbers(usally for generating primary keys) are not generated in a sequence. Applications that read historical data would fail because of that.  Before RAC system ;
ID    INSERT_DATE
1     16.12.2007 16:21:00
2     16.12.2007 16:21:10
3     16.12.2007 16:23:49

Note that ID field is generated with an Oracle SEQUENCE.

After migration to RAC it became as;
ID    INSERT_DATE
81     16.12.2007 19:43:23
82     16.12.2007 16:39:00
83     16.12.2007 16:46:08

As you see, insert date of 82 is before than insert date of 81. There is a mismatch with insertion dates and sequences. Then i read Oracle documentation:

ORDER
Specify ORDER to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.
ORDER is necessary only to guarantee ordered generation if you are using Oracle Database with Real Application Clusters. If you are using exclusive mode, sequence numbers are always generated in order.

NOORDER
Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.

So, in order to keep numbers in an order, i altered the sequence and the problem was solved:

SQL>ALTER SEQUENCE SEQ_ID ORDER;

Dictionary Compressing

Aşağıda, "Veri Sıkıştırma" ile yapmış olduğum örnek bir uygulama bulunuyor. Bu konu ile ilgilenenler için örnek olacağını düşünüyorum:

1.Konu

Verilen bir sözlüğün sıkıştırılma işlemi yapılacaktır.

Alfabeden örnek bir kesit

aba    

abacı      

abacılık   

abadi      

abajur     

abajurcu   

Sıkıştırma sonucunda elde edilecek kesit:

aba    

3cı    

5lık   

3di    

3jur   

6cu    

Alfabe içinde ses olaylarından dolayı oluşmuş tekrarlı kayıtların temizlenmesi de yapılacaktır.

Tekrarlı kayıt örneği

abartmacılığ    

abartmacılık 

Silinmesi gereken kayıt

abartmacılığ

Bu ve buna benzer tekrarlar aşağıda belirtilen harflerin yumuşama olayı ile elde edilmişlerdir.

k à ğ

ç à c

p à b

t à d

Yapılan sıkıştırma işleminin analizi yapılacaktır.

2. Uygulama İçin Kullanılacak Yöntem ve Araçlar

Yapılması istenen sıkıştırma işlemi, verilerden oluştuğu için, bu verilerin bir veritabanına aktarılıp oradan işlenmesinin daha uygun olacağı varsayılmıştır. Kullanılacak veri tabanı yönetim sisteminin kendine has veriye erişim yöntemleri ile daha performanslı bir sıkıştırma işlemi yapılacaktır.

Veritabanı yönetim sistemi oalarak Oracle kullnılacaktır. Oracle ile bütünleşik gelen SQL*Plus adlı metin tabanlı dosyaların veritabanı yüklenmesini sağlayan bir araçtan yararlanılacaktır. Sıkıştırma işlemi için ise Oracle ile bütünleşik gelen programlama dili PL/SQL olacaktır.

3. Verileri Okuma

Sözlük, txt uzantılı bir dosyada bulunmaktadır. Bu dosya yapısı gereği, her satırda bir harf olacak şekilde sıralı bir şekilde bulunmaktadır. Oracle içinde sözlüğü tutacak bir tablo oluşturulmalıdır. Bunun oluşturulabilmesi için aşağıdaki SQL cümlesi çalıştırılmalıdır:

create table dict

(

  row_number      number,

  original_word   varchar2(32)

);

Oracle ile bütünleşik gelen SQL*Plus aracı ile veriler tabloya yüklenecektir. Bu nedenle SQL*Plus in anlayabileceği bir dilde kontrol dosyası oluşturulur:

LOAD DATA

--Input file name

INFILE 'tr-sozluk.txt'

--bad records put into

BADFILE  'dict_bad.dat'

--discarded records put into

DISCARDFILE 'dict_discarded.dat'

--truncate existing records

TRUNCATE

--add data to table

INTO TABLE dict

--every column is distinguished by "" caharacter

FIELDS TERMINATED BY ""

(

     --like a sequence, inc one-by-one

     row_number RECNUM,

     --replace TAB char and TRIM

     original_word "TRIM(REPLACE(original_word,CHR(9)))"

)

Yukarıdaki konfügrasyon dosyası ile veriler okunup veritabanında bulunan tabloya atılır. Bu işlem için komut satırına aşağıdaki komut yazılmalıdır:

> sqlldr userid=username/password@database control=dict.ctl log=dict.log

Buradaki ;

• userid=username/password@database; kısmı bağlantı bilgilerini
• control=dict.ctl; yukarıda oluşturulan kontrol dosyası bilgilerini
• log=dict.log; log dosyası bilgilerini

tutmaktadır. Yukarıdaki işlem sonucunda dosyada bulunan 49756 kayıt, veritabanına aktarılır.

4. Ön İşleme

Kullanılacak Türkçe sözlükte giriş kısmında da belirtildiği üzere tekrarlı kayıtlar bulunmaktadır. Bu kayıtların silinmesi için aşağıdaki SQL cümleleri çalıştırılmalıdır

DELETE FROM dict WHERE original_word IS NULL;

Yukarıdaki işlem sonucunda boş olan 4 satır silinir.

DELETE FROM dict

 WHERE original_word IN

       (SELECT substr(original_word, 1, length(original_word) - 1) ||

                decode(substr(original_word, -1, 1),

                       'ç',

                       'c',

                       'p',

                       'b',

                       'k',

                       'ğ',

                       't',

                       'd')

          FROM dict

         WHERE substr(original_word, -1, 1) IN ('ç', 'p', 'k', 't'));

Yukarıdaki işlem sonucunda mükerrer olan 8360 satır silinir.

Toplamda 8364 kayıt silinir. İşlenecek kayıt sayısı 42392 olarak kalır.

5.   Sıkıştırma İşleminin Yapılması

Veritabanına yüklenen kelimeler sözlük sırasına göre artan sıralama ile sıralanır. Her kelime, bir sonraki kelime ile karşılaştırılarak sıkıştırma işlemi yapılır. Aşağıdaki sorgu ile bu algoritma çalıştırılır.

SELECT curr AS original_word,

       decode(MAX(lev), 0, '', MAX(lev)) || substr(curr, MAX(lev) + 1) AS compressed_word

  FROM (SELECT *

           FROM (SELECT a.curr, a.prev, b.lev,

                         lag(b.lev, 1, 0) over(ORDER BY a.curr, a.prev, b.lev) - b.lev AS diff

                    FROM (SELECT curr, prev,

                                  least(length(curr), length(prev)) AS min_len

                             FROM (SELECT w AS curr,

                                           lag(w, 1, ' ') over(ORDER BY w) AS prev

                                      FROM (SELECT original_word AS w FROM dict))) a,

                         (SELECT LEVEL - 1 AS lev

                             FROM dual

                           CONNECT BY LEVEL < (SELECT MAX(length(original_word)) + 1

                                                 FROM dict)) b

                   WHERE a.min_len >= b.lev

                     AND (substr(a.curr, b.lev, 1) = substr(a.prev, b.lev, 1) OR

                         b.lev = 0))

          WHERE diff >= -1)

 GROUP BY curr, prev

Yukarıdaki sorgu sonucunda, orjinal kelimeler ile sıkıştırılmış kelimeler ayrı bir tabloya aktarılır. Sıkıştırılmış sözlüğün bir kısmı aşağıda belirtilmiştir.

aba

3cı

5lık

3di

3jur

6cu

3küs

3lı

Sıkıştırma işleminin sonucunda, Oracle içinde tanımlanan TEMP_DIR adlı dizin nesnesinin gösterdiği yerde(c:\temp) sıkıştırma öncesi ve sonrası oluşan kayıtlar metin halinde bulunmaktadır. Bunlar

• Original.txt : Sıkıştırma öncesi sözlüğün ilk hali.
• Compressed.txt: Sıkıştırma sonrası oluşan sözlük.

Dosya yolu, ekte belirtilen dosya içinden değiştirilerek, gerekli yerde oluşturulması sağlanabilir.

6.   Uygulamanın Çalıştırılması

Ön şart olarak, Oracle veritabanı 9 veya üzeri bir versiyonun yüklü ve çalışıyor olması gerekmektedir.

Uygulama ile ilgili gerekli olan dosyalar aşağıda belitilmiştir:

Dosya Adı	Görevi
dict_init.sql	Oracle içine atılacak dosya için gerekli tablo ve indexlerin oluşturulmasını sağlayan dosya. Bu dosya çalıştıktan sonra, veritabnı içinde dict adında bir tablo oluşturulur.
dict.ctl	SQL*Loader adlı metin dosyalarının Oracle veritabanı içine atılmasını sağlayan aracın okuduğu konfigürasyon dosyası. Bu dosya içinde sıkıştırılacak dosyanın(dict.txt) ismi bulunmaktadır.
dict_preprocess.sql	Sözlük ile ilgili ön işlemlerin yapıldığı dosya. Bu dosya çalıştırılarak gereksiz verilerin silinme işlemi yapılır.
dict_process.sql	Sözlük ile ilgili sıkıştırma işleminin yapıldığı dosya. Bu dosya çalıştırılarak sıkıştırma işlemi yapılır, veritabanında dict_compressed isimli bir tablo oluşturulur.
dict_start.bat	Sözlüğün yüklenmesi ve işlenmesi ile ilgili dosyaların hangi sıra ve parametreler ile çağrıldığının belirtildiği dosyadır. Bu dosya, uygulamanın ana dosyasıdır. Bu dosya, komut satırından çağrıldığı takdirde sıkıştırma ile ilgi işlemler sıra ile yapılır.
dict.txt	Sözlük verilerinin bulunduğu dosya.
UTL_EXP_v3.pck	Sıkıştırılan sözlüğün bir metin dosyasına aktarılmasını sağlayan PL/SQL paketidir. Bu paketin Oracle içine yüklenmiş olmalıdır.

Uygulama çalıştıktan sonra aşağıda belirtilen dosyalar oluşturulur:

Dosya Adı	Görevi
dict.log	SQL*Loader ile oluşacak dosyanın logunu tutulduğu dosyadır. Bu dosya içinde hata durumları ve istatistiki bilgileri bulunur.
dict_preprocess.log	Uygulamanın ön işleme kısmında tutulan loglar buraya yazılır.
dict_process.log	Uygulamanın sıkıştırma kısmında tutulan loglar buraya yazılır.
original.txt	bu bir bakıma sözlüğün bir kopyasıdır.  Eğer ön işlemler yapılmışsa, burada ön işlemler sonucunda elde edilien sözcükler bulunur.
compressed.txt	Bu dosya içinde sıkıştırma işlemi sonucunda sözcükller bulunur.

Uygulamayı çalıştırmak için belirtilen dosyalar belirli bir dizine toplanır. dict_start.bat adlı dosya çalıştılır. Bu dosya içinde Oracle veritabanına bağlanmak için gerekli değişiklikler yapılmalıdır. Örneğin aşağıda belirtilen dosyada

hr/hr@XE è username/password@database_identifier

olarak belirtilen bağlantı bilgileri değiştirilmelidir.

sqlplus hr/hr@XE  @dict_init.sql

sqlldr  userid=hr/hr@XE control=dict.ctl log=dict.log

sqlplus hr/hr@XE  @dict_preprocess.sql

sqlplus hr/hr@XE  @dict_process.sql

Eğer sözlük ile ilgili bir ön işleme yapılmayacaksa,

sqlplus hr/hr@XE  @dict_preprocess.sql

kısmı, bat uzantılı dosyadan çıkarılmalıdır.

Uygulama ile başka sözlüklerin de sıkıştırılması gerekiyorsa, uygulama dizini içine o sözlüğün dict.txt halinde kopyalanması gereklidir.

Uygulamanın örnek bir çalışma şekli aşağıda belirtilmiştir:

C:\DC>dict_start.bat

C:\DC>sqlplus hr/hr@XE  @dict_init.sql

SQL*Plus: Release 10.2.0.1.0 - Production on Sal Eki 23 00:14:13 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

PL/SQL procedure successfully completed.

Table created.

Index created.

Index created.

Connected.

Directory created.

Grant succeeded.

Grant succeeded.

Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

C:\DC>sqlldr  userid=hr/hr@XE control=dict.ctl log=dict.log

SQL*Loader: Release 10.2.0.1.0 - Production on Sal Eki 23 00:14:17 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

....

Commit point reached - logical record count 49728

Commit point reached - logical record count 49756

C:\DC>sqlplus hr/hr@XE  @dict_preprocess.sql

SQL*Plus: Release 10.2.0.1.0 - Production on Sal Eki 23 00:12:16 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

8360 rows deleted.

4 rows deleted.

Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

C:\DC>sqlplus hr/hr@XE  @dict_process.sql

SQL*Plus: Release 10.2.0.1.0 - Production on Sal Eki 23 00:12:18 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

PL/SQL procedure successfully completed.

Table created.

TOTAL_ORIG TOTAL_COMPR      PERCT

---------- ----------- ----------

    310030      143120 ,538367255

PL/SQL procedure successfully completed.

Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

C:\DC>

7.   Analiz ve Yorumlar

Sıkıştırma işlemi sonucunda sıkıştırma öncesi sözlükdeki toplam harf sayısı ile sıkıştırma sonrasındaki harf sayısı ve boyutu incelenmiştir.

	Sıkıştırma Öncesi	Sıkıştırma Sonrası
Harf Sayısı	310030	143120
Dosya Boyutu(KB)	419	233

Sıkıştırma Oranı : %54

Yapılan bu çalışma sonucunda, varolan veriler saklanma yerinde yaklaşık %54 oranında tasarruf edilmiştir. Bu hem maliyete hem de verilerin taşınması için gerekli ağ kaynaklarının kullanılmasında tasarruf demektir. Bu kazanımın yanında, sıkıştırma ve açma işlemlerinin yapılması gereklidir. Bu da zaman noktasında az da olsa bir kayıptır.

• Bu yöntemde fazla zaman almadan yüksek oranda bir sıkıştırma başarısı elde edilmiştir.
• Sadece sözlük tarzı uygulamalarda bu yöntem etkin bir şekilde kullanılabilir. Yani bir kelime, kendinden sonra gelen kelime içinde tamamen veya kısmen geçiyorsa, bu yöntem uygulanabilir.

On ORA-01503, ORA-01161, ORA-01565, ORA-27038 and ORA-27046

A few days ago, i clonned one oracle database. During the clonning i got some errors. I will explain how i overcome the errors in this post.

 I re-create the control files with following command:

STARTUP NOMOUNT

CREATE CONTROLFILE SET DATABASE "PRODB" RESETLOGS  NOARCHIVELOG

    MAXLOGFILES 4

....

But i got the errors:

ORA-01503: CREATE CONTROLFILE failed

ORA-00200: controlfile could not be created

ORA-00202: controlfile: '/dataapp/oracle/oradata/cmps01/control01.ctl'

ORA-27038: created file already exists

The error was about, i did not remove old control files. So, i use the keywords "REUSE SET DATABASE" in order to solve problem.

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE SET DATABASE "PRODB" RESETLOGS  NOARCHIVELOG    MAXLOGFILES 4    MAXLOGMEMBERS 4

 ...

Another information, if you do not specify "SET DATABASE" clause you will probably get the these errors:

ORA-01503: CREATE CONTROLFILE failed
ORA-01161: database name prod1 in file header does not match given name of PRODB
ORA-01110: data file 1: '/dataapp/oracle/oradata/PRODB/system01.dbf'

One more thing, clonned the database and i use ftp to upload data files to new host. If i do not make ftp with BINARY mode(use binary ftp command), got :

ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file '/dataapp/oracle/oradata/PRODB/system01.dbf'
ORA-27046: file size is not a multiple of logical block size

Convert UTC Time To Local Time In Oracle

Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.5.0
Connected as sysadm
SQL> SELECT SYSTIMESTAMP,
  2         SYS_EXTRACT_UTC(SYSTIMESTAMP) UTC_DATE,
  3         DBTIMEZONE,
  4         SYSTIMESTAMP - SYS_EXTRACT_UTC(SYSTIMESTAMP) DIFF
  5    FROM dual;
SYSTIMESTAMP                                      UTC_DATE                                          DBTIMEZONE DIFF
------------------------------------------------- ------------------------------------------------- ---------- -------------------
05/09/2007 11:39:27,992823 +03:00                 05/09/2007 08:39:27,992823                        +03:00     +000000000 03:00:00
SQL> SELECT * FROM v$timezone_names where tzname like 'Asia/Istanbul' and rownum = 1;
TZNAME                                                           TZABBREV
---------------------------------------------------------------- ----------------------------------------------------------------
Asia/Istanbul                                                    LMT
SQL> SELECT utc_date,
  2         FROM_TZ(CAST( utc_date AS TIMESTAMP), 'Greenwich') AT TIME ZONE 'Asia/Istanbul' as local_date,
  3         TO_CHAR(  FROM_TZ(CAST( utc_date AS TIMESTAMP), 'Greenwich') AT TIME ZONE 'Asia/Istanbul', 'MM.DD.RRRR HH24:MI:SS' ) local_date
  4   FROM ( SELECT TO_DATE('2007-12-01 11:00:00', 'YYYY-MM-DD HH:MI:SS') utc_date FROM dual
  5           UNION
  6           SELECT TO_DATE('2007-08-01 11:00:00', 'YYYY-MM-DD HH:MI:SS' ) FROM dual
  7         );
UTC_DATE    LOCAL_DATE                                        LOCAL_DATE
----------- ------------------------------------------------- -------------------
01.08.2007  01/08/2007 14:00:00,000000 ASIA/ISTANBUL          08.01.2007 14:00:00
01.12.2007  01/12/2007 13:00:00,000000 ASIA/ISTANBUL          12.01.2007 13:00:00
SQL>

Solution Of ORA-29531 : static Keyword Missing

SQL> create or replace and compile java source named java_version as
2 public class JavaVersion
3 {
4 public String Get()
5 {
6 String Version = System.getProperties().getProperty("java.version");
7 return Version;
8 }
9 }
10 / Java created
SQL> CREATE OR REPLACE FUNCTION fnc_JavaVersion RETURN VARCHAR2 AS
2 LANGUAGE JAVA NAME 'JavaVersion.Get( ) return java.lang.String';
3 /
Function created
SQL> SELECT fnc_JavaVersion() FROM dual;
SELECT fnc_JavaVersion() FROM dual
ORA-29531: no method Get in class JavaVersion
SQL> create or replace and compile java source named java_version as
2 public class JavaVersion
3 {
4 public static String Get()
5 {
6 String Version = System.getProperties().getProperty("java.version");
7 return Version;
8 }
9 }
10 /
Java created
SQL> SELECT fnc_JavaVersion() FROM dual;
SELECT fnc_JavaVersion() FROM dual
ORA-29549: class SYSADM.JavaVersion has changed, Java session state cleared
SQL> SELECT fnc_JavaVersion() FROM dual;
FNC_JAVAVERSION()
--------------------------------------------------------------------------------
1.4.1
SQL>

How to Export Table Data to Flat File :: Oracle UnLoader, Text File Exporter

As every developer knows, data always transfers between platforms. One advantage of XML is also exchanging and/or transferring data. One day, your boss may want to see some sales data in excel sheet. So, if you have a program that exports data in xls format, this will not be problem; but if you do not have one, you may some how fullfill the requirements, if you do not want loose your job :)
 
Using SQL*Plus may be one of solutions. If you have to do it programatically, you can use Oracle's scheduler. First you have to create an SQL file that specifies how exporting will be. One example may be:
set heading off;
set feedback off;
spool e:\log.txt
select * from &1;
spool off;
exit;
If you save above code E:\exp_table.sql as, you are ready to execute scheduler:
--create program
BEGIN
DBMS_SCHEDULER.create_program(program_name => 'EXP_DATA_PRG',
program_type => 'EXECUTABLE',
program_action => 'E:\oracle\product\10.2.0\db_2\BIN\sqlplus.exe hr/hr@ORCL@"E:\exp_table.sql" jobs',
number_of_arguments => 0,
enabled => FALSE,
comments => 'Export data Program');
END;
/

--create job
BEGIN
DBMS_SCHEDULER.create_job(job_name => 'EXP_DATA_JOB',
program_name => 'EXP_DATA_PRG',
start_date => NULL,
repeat_interval => NULL,
end_date => NULL,
enabled => FALSE,
auto_drop => FALSE,
comments => 'Export data Job');
END;
/
--enable program and job
BEGIN
DBMS_SCHEDULER.enable(NAME => 'EXP_DATA_PRG');
DBMS_SCHEDULER.enable(NAME => 'EXP_DATA_JOB');
END;
/
--run job
BEGIN
DBMS_SCHEDULER.RUN_JOB(job_name => 'EXP_DATA_JOB',use_current_session => TRUE);
END;
/
After job executes, e:\log.txt will be your export file. You can play with parameters, as your requirements.
 
As you think (like me) this is not a good solution. So, i started to write some codes to generate more flexible text exporter for Oracle. I developed a package, UTL_EXP, for this job.
There are two program in this package. Specs are below:
FUNCTION ExportTableData
(
--table name that will be exported
pis_TableName IN ALL_TABLES.TABLE_NAME%TYPE,
--filter criteria,if exists
pis_WhereCondition IN VARCHAR2 DEFAULT NULL,
--if table has more columns how fields will be seperated
pis_FieldDelimiter IN VARCHAR2 DEFAULT ',',
--row delimiter, CHR(10) for newline
pis_RecordDelimiter IN VARCHAR2 DEFAULT CHR(10),
--if some columns will not be included
pis_ExceptedFieldList IN VARCHAR2 DEFAULT NULL,
--1 for fields enclosed by ", such as "column_value"
pin_FieldEnclosedByQuote IN NUMBER DEFAULT 0,
--if column name will not be included, set as 0
pin_IncludeColumnName IN NUMBER DEFAULT 0,
--if field names included
pis_FieldNameDelimiter IN VARCHAR2 DEFAULT ':'
) RETURN CLOB;
PROCEDURE ExportFile
(
--oracle directory name
pis_DirectoryName in VARCHAR2,
--file name
pis_FileName in VARCHAR2,
--table name that will be exported
pis_TableName IN ALL_TABLES.TABLE_NAME%TYPE,
--filter criteria,if exists
pis_WhereCondition IN VARCHAR2 DEFAULT NULL,
--if table has more columns how fields will be seperated
pis_FieldDelimiter IN VARCHAR2 DEFAULT ',',
--row delimiter, CHR(10) for newline
pis_RecordDelimiter IN VARCHAR2 DEFAULT CHR(10),
--if some columns will not be included
pis_ExceptedFieldList IN VARCHAR2 DEFAULT NULL,
--1 for fields enclosed by ", such as "column_value"
pin_FieldEnclosedByQuote IN NUMBER DEFAULT 0,
--if column name will not be included, set as 0
pin_IncludeColumnName IN NUMBER DEFAULT 0,
--if field names included
pis_FieldNameDelimiter IN VARCHAR2 DEFAULT ':'
) ;
 
On exporting data you have some options:

• specify some criterias
• not to export some columns
• field and row delimiters
• put column values between quotes
• include table column name

A simple demostration will show how to use UTL_EXP package.
 
SQL> drop table t;
Table dropped
SQL> create table t(i number, v varchar2(12), d date);
Table created
SQL> insert into t values(1,'test data 1', sysdate + 1);
1 row inserted
SQL> insert into t values(2,'test data 2', sysdate + 2);
1 row inserted
SQL> insert into t values(3,'test data 3', sysdate + 3);
1 row inserted
SQL> SELECT * FROM t;
I V D
---------- ------------ -----------
1 test data 1 08.07.2007
2 test data 2 09.07.2007
3 test data 3 10.07.2007
SQL> set long 10000000;
SQL> SELECT UTL_EXP.ExportTableData('t','i > 0',';',chr(10),'i',1, 0,null) FROM dual;
UTL_EXP.EXPORTTABLEDATA('T','I
--------------------------------------------------------------------------------
"test data 1";"08/07/2007";
"test data 2";"09/07/2007";
"test data 3";"10/07/2007";

SQL> DECLARE
2 c CLOB;
3 BEGIN
4 c := UTL_EXP.ExportTableData('t','i > 0',';',chr(10),'i',1, 0,null);
5 utl_utl.PutLine(c);
6 END;
7 /
PL/SQL procedure successfully completed
SQL> SELECT DIRECTORY_NAME FROM all_directories;
DIRECTORY_NAME
------------------------------
DATA_PUMP_DIR
ORACLECLRDIR
TEST_FILE_DIR
 
SQL> BEGIN
2 UTL_EXP.ExportFile('TEST_FILE_DIR', 't.csv','t','i > 0',';',chr(10),'i',1, 0,null);
3 END;
4 /
PL/SQL procedure successfully completed
SQL>
After executing the procedure t.csv will contain:
"test data 1";"08/07/2007";
"test data 2";"09/07/2007";
"test data 3";"10/07/2007";
You can generate more complex export files with UTL_EXP package.

How to Find Java(JVM) Version of Oracle Programatically

SQL> create or replace and compile java source named java_version as
2 public class JavaVersion
3 {
4 public static String Get()
5 {
6 String Version = System.getProperties().getProperty("java.version");
7 return Version;
8 }
9 }
10 /
Java created
SQL>
SQL> CREATE OR REPLACE FUNCTION fnc_JavaVersion RETURN VARCHAR2 AS
2 LANGUAGE JAVA NAME 'JavaVersion.Get( ) return java.lang.String';
3 /
Function created
SQL> SELECT fnc_JavaVersion() FROM dual;
SELECT fnc_JavaVersion() FROM dual
ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission (java.util.PropertyPermission * read,write) has not been granted to SYSADM. The PL/SQL to grant this is dbms_java.grant_permission( 'SYSADM', 'SYS:java.util.PropertyPermission', '*', 'read,write' )
SQL> exec dbms_java.grant_permission( 'SYSADM', 'SYS:java.util.PropertyPermission', '*', 'read,write' );
PL/SQL procedure successfully completed
SQL> SELECT fnc_JavaVersion() FROM dual;
FNC_JAVAVERSION()
--------------------------------------------------------------------------------
1.4.1
SQL>

Some Notes on ORA-04061, ORA-04065 and ORA-06508

Today, i was doing unit tests of a PL/SQL package that is developed by me. As every oracle developer knows, if you compile a package and if it has some private or public variables in either package body or spec, they have to be reinstantiniated.(for more on package states, please refer to Dependent Objects and Object Statuses In Oracle). That is if you compile a package and then call a procedure in it, you can error. But when you call it again, the state will be refreshed and no more error would occur. But the situation i have faced today was a bit more different. I got the error, although i call the proc more than once:
ORA-04061: existing state of package body "HR.PKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "HR.PKG""
ORA-06508: PL/SQL: could not find program unit being called
When i investigate the error, all objects are valid. Normally on second run, session state is cleaned(expected) but; in one of my runs, i get the same error again and again whereas running the same procedure. Somehow Oracle could not clean session state(unexpected). When i run the same proc in another session, in a new sql window, it works fine.
I could not understand why this happened. If I were using a connection-pooled mechanism(such as web servers), the session may not have been reinstantiniated. I have to restart restart the web server, that is clean connections.But i am using a simple desktop application to connect and execute procedures in oracle.

Running Executables From PL/SQL with DBMS_SCHEDULER

It is possible two execute programs via PLSQL. In one of my previous post i have mentioned it. Now i want demonstrate how table data can be extraxted using SQL*PLUS with calling it programatically inside PLSQL.

I will use dbms_scheduler built-in package. First create a program that is an EXECUTABLE with points SQL*PLUS. Then create a job, enable them and run the job.

--create program
BEGIN
DBMS_SCHEDULER.create_program(program_name => 'EXP_DATA_PRG',
program_type => 'EXECUTABLE',
program_action => 'E:\oracle\product\10.2.0\db_2\BIN\sqlplus.exe hr/hr@ORCL @"E:\exp_table.sql" jobs',
number_of_arguments => 0,
enabled => FALSE,
comments => 'Export data Program');
END;
/
SELECT * FROM all_scheduler_programs p WHERE p.program_name = 'EXP_DATA_PRG';

--create job
BEGIN
DBMS_SCHEDULER.create_job(job_name => 'EXP_DATA_JOB',
program_name => 'EXP_DATA_PRG',
start_date => NULL,
repeat_interval => NULL,
end_date => NULL,
enabled => FALSE,
auto_drop => FALSE,
comments => 'Export data Job');
END;
/
SELECT * FROM all_scheduler_jobs j WHERE j.job_name = 'EXP_DATA_JOB';
--enable program and program
BEGIN
DBMS_SCHEDULER.enable(NAME => 'EXP_DATA_PRG');
DBMS_SCHEDULER.enable(NAME => 'EXP_DATA_JOB');
END;
/
--run job
BEGIN
DBMS_SCHEDULER.RUN_JOB(job_name => 'EXP_DATA_JOB',
use_current_session => TRUE);
END;
/
SELECT * FROM all_scheduler_running_jobs WHERE job_name = 'EXP_DATA_JOB';
SELECT * FROM all_scheduler_job_log l WHERE l.job_name = 'EXP_DATA_JOB' order by 1 DESC;
--stop job
BEGIN
DBMS_SCHEDULER.stop_job(job_name => 'EXP_DATA_JOB', force => TRUE);
END;
/

SELECT * FROM all_scheduler_running_jobs WHERE job_name = 'EXP_DATA_JOB';
--drop program, job and arguments
BEGIN
DBMS_SCHEDULER.disable(NAME => 'EXP_DATA_PRG', force => TRUE);
DBMS_SCHEDULER.drop_program(program_name => 'EXP_DATA_PRG',
force => TRUE);
DBMS_SCHEDULER.drop_job(job_name => 'EXP_DATA_JOB', force => TRUE);
END;
/
SELECT * FROM all_scheduler_job_run_details WHERE job_name = 'EXP_DATA_JOB';

After running job, e:\log.txt will be created.
Content of E:\exp_table.sql is
set heading off;
set feedback off;
spool e:\log.txt
select * from &1;
spool off;
exit;

Running Oracle Jobs with DBMS_SCHEDULER

--create program
BEGIN
DBMS_SCHEDULER.create_program(program_name => 'TEST_PRG',
program_type => 'STORED_PROCEDURE',
program_action => 'EVENT_HANDLING.TEST',
number_of_arguments => 4,
enabled => FALSE,
comments => 'AAE event fetcher Program');
END;
/
SELECT * FROM all_scheduler_programs p WHERE p.program_name = 'TEST_PRG';
--create program arguments
BEGIN
DBMS_SCHEDULER.define_program_argument(program_name => 'TEST_PRG',
argument_position => 1,
argument_name => 'pin_Start',
argument_type => 'NUMBER',
default_value => '1',
out_argument => FALSE);
DBMS_SCHEDULER.define_program_argument(program_name => 'TEST_PRG',
argument_position => 2,
argument_name => 'pin_End',
argument_type => 'NUMBER',
default_value => '10',
out_argument => FALSE);
DBMS_SCHEDULER.define_program_argument(program_name => 'TEST_PRG',
argument_position => 3,
argument_name => 'pin_DebugMode',
argument_type => 'NUMBER',
default_value => '0',
out_argument => FALSE);
DBMS_SCHEDULER.define_program_argument(program_name => 'TEST_PRG',
argument_position => 4,
argument_name => 'pin_Rem',
argument_type => 'NUMBER',
default_value => '0',
out_argument => FALSE);
END;
/
SELECT * FROM all_scheduler_program_args WHERE program_name = 'TEST_PRG';
--create job
BEGIN
DBMS_SCHEDULER.create_job(job_name => 'TEST_JOB',
program_name => 'TEST_PRG',
start_date => NULL,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',
end_date => NULL,
enabled => FALSE,
auto_drop => FALSE,
comments => 'AAE event fetcher Job');
END;
/
SELECT * FROM all_scheduler_jobs j WHERE j.job_name = 'TEST_JOB';
--enable program and program
BEGIN
DBMS_SCHEDULER.enable(NAME => 'TEST_PRG');
DBMS_SCHEDULER.enable(NAME => 'TEST_JOB');
END;
/
--run job
BEGIN
DBMS_SCHEDULER.RUN_JOB(job_name => 'TEST_JOB',
use_current_session => TRUE);
END;
/
SELECT * FROM all_scheduler_running_jobs WHERE job_name = 'TEST_JOB';
SELECT * FROM all_scheduler_job_log l WHERE l.job_name = 'TEST_JOB' order by 1 DESC;
--stop job
BEGIN
DBMS_SCHEDULER.stop_job(job_name => 'TEST_JOB', force => TRUE);
END;
/
--drop prg args
BEGIN
DBMS_SCHEDULER.drop_program_argument(program_name => 'TEST_PRG',
argument_position => 1);
DBMS_SCHEDULER.drop_program_argument(program_name => 'TEST_PRG',
argument_position => 2);
DBMS_SCHEDULER.drop_program_argument(program_name => 'TEST_PRG',
argument_position => 3);
DBMS_SCHEDULER.drop_program_argument(program_name => 'TEST_PRG',
argument_position => 4);
END;
/
SELECT * FROM all_scheduler_running_jobs WHERE job_name = 'TEST_JOB';
--drop program, job and arguments
BEGIN
DBMS_SCHEDULER.disable(NAME => 'TEST_PRG', force => TRUE);
DBMS_SCHEDULER.drop_program(program_name => 'TEST_PRG',
force => TRUE);
DBMS_SCHEDULER.drop_job(job_name => 'TEST_JOB', force => TRUE);
END;
/
SELECT * FROM all_scheduler_job_run_details order by 1 desc;
--change repeat interval
BEGIN
DBMS_SCHEDULER.set_attribute(NAME => 'TEST_JOB',
attribute => 'repeat_interval',
VALUE => 'FREQ=MINUTELY;INTERVAL=1');
END;
/
SELECT * FROM all_scheduler_jobs j WHERE j.job_name = 'TEST_JOB';

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