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';