„It’s not a bug, it’s a feature”
PL/SQL like any other procedural extension to SQL has an ability to execute dynamic statements: EXECUTE IMMEDIATE. But not everyone knows that it works differently for SQL statements and for PL/SQL blocks. The difference lies in parameters passing.
(continue reading…)
One could expect that if it is possible to name a table’s column „DATE” then it is safe to do so. It appears, however, that using such a column’s name would cause the following compilation error in any procedure using an input parameter of the row type based on this table:
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed
Surprisingly, view on this table works correctly and you can insert data through it. It seems that only PL/SQL has this error. The same errors occurs when the column is named NUMBER. However there is no problem with names: VARCHAR, VARCHAR2, INTEGER, FLOAT, TIMESTAMP, CLOB, LOB, CHAR, LONG
Here is the test for DATE name
create table test_date ( "DATE" date );
create table test_date2 ( "DATE2" date );
-- this procedure would not compile
create procedure pr_test_date(p_date test_date%rowtype) as
begin
null;
end;
create procedure pr_test_date2(p_date2 test_date2%rowtype) as
begin
null;
end;
create view v_test_date as select * from test_date;
create view v_test_date2 as select * from test_date2;
insert into v_test_date("DATE") values (sysdate);
Wydawałoby się, że skoro można nazwać kolumnę „DATE” jeśli umieści się ten wyraz w cudzysłowach to jest to bezpieczne. Okazuje się jednak, że używanie tej nazwy powoduje następujący błąd kompilacji w procedurze wykorzystującej na wejściu typ wierszowy na tej tabeli:
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed
Dzieje się tak, mimo że widok na tej tabeli działa poprawnie i można przez niego wrzucać dane. Podobny błąd występuje, gdy kolumna nazywa się NUMBER, ale dla VARCHAR, VARCHAR2, INTEGER, FLOAT, TIMESTAMP, CLOB, LOB, CHAR, LONG precedura działa poprawnie.
Poniżej test dla typu DATE
create table test_date ( "DATE" date );
create table test_date2 ( "DATE2" date );
-- ta procedura się nie kompiluje
create procedure pr_test_date(p_date test_date%rowtype) as
begin
null;
end;
create procedure pr_test_date2(p_date2 test_date2%rowtype) as
begin
null;
end;
create view v_test_date as select * from test_date;
create view v_test_date2 as select * from test_date2;
insert into v_test_date("DATE") values (sysdate);
This is an example about how to pass string, string array or string pairs array from Javy to Oracle
You should create the following types in your database:
create or replace type VARCHAR2_PAIR as object( k varchar2(200), v varchar2(200)); create or replace type VARCHAR2_PAIRS_TABLE as table of VARCHAR2_PAIR; create or replace type VARCHAR2_TABLE as table of VARCHAR2(200);
.
Then you should create stored procedure of the following signature
CREATE OR REPLACE PROCEDURE arrays_from_java(p_scalar varchar2 , p_array IN VARCHAR2_TABLE , p_pairs_array in VARCHAR2_PAIRS_TABLE);
.
And in the end call it from Java
public static void callArrayProcedure() throws Exception{
System.out.println("Preparing data");
String[] list1=new String[2];
list1[0] = "first string";
list1[1] = "second string";
String[][] list2=new String[2][];
{
String[] pair1 = new String[2];
pair1[0] = "key1";
pair1[1] = "value1";
list2[0] = pair1;
}
{
String[] pair2 = new String[2];
pair2[0] = "key2";
pair2[1] = "value2";
list2[1] = pair2;
}
System.out.println("Preparing connection");
SingleConnectionDataSource scds = new SingleConnectionDataSource();
scds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
scds.setUrl("jdbc:oracle:thin:@host:1521:service_name");
scds.setUsername(...);
scds.setPassword(...);
Connection conn = scds.getConnection();
System.out.println("Preparing array of strings");
ArrayDescriptor descriptor1 = ArrayDescriptor.createDescriptor("VARCHAR2_TABLE", conn );
ARRAY arrayToPass1 = new ARRAY( descriptor1, conn, list1 );
System.out.println("Preparing array of string pairs");
ArrayDescriptor descriptor2 = ArrayDescriptor.createDescriptor("VARCHAR2_PAIRS_TABLE", conn );
ARRAY arrayToPass2 = new ARRAY( descriptor2, conn, list2 );
System.out.println("Calling procedure");
OraclePreparedStatement ps = (OraclePreparedStatement) conn.prepareStatement( "{ call arrays_from_java( ?, ?, ? ) }" );
ps.setString( 1, "string");
ps.setARRAY( 2, arrayToPass1 );
ps.setARRAY( 3, arrayToPass2 );
ps.execute();
ps.close();
}
.
You should also add orai18n.jar to Java classpath, otherwise strings may become null after passing them to stored procedure if the basic character set (NLS_CHARACTERSET) is not UTF
Poniżej znajduje się opis jak przekazywać string, tablicę stringów oraz tablicę par stringów z Javy do Oracle’a
Należy utworzyć typy w bazie danych:
create or replace type VARCHAR2_PAIR as object( k varchar2(200), v varchar2(200)); create or replace type VARCHAR2_PAIRS_TABLE as table of VARCHAR2_PAIR; create or replace type VARCHAR2_TABLE as table of VARCHAR2(200);
.
Potem utworzyć procedurę składowaną
CREATE OR REPLACE PROCEDURE arrays_from_java(p_scalar varchar2 , p_array IN VARCHAR2_TABLE , p_pairs_array in VARCHAR2_PAIRS_TABLE);
.
A na końcu zawołać ją z Javy:
public static void callArrayProcedure() throws Exception{
System.out.println("Preparing data");
String[] list1=new String[2];
list1[0] = "first string";
list1[1] = "second string";
String[][] list2=new String[2][];
{
String[] pair1 = new String[2];
pair1[0] = "key1";
pair1[1] = "value1";
list2[0] = pair1;
}
{
String[] pair2 = new String[2];
pair2[0] = "key2";
pair2[1] = "value2";
list2[1] = pair2;
}
System.out.println("Preparing connection");
SingleConnectionDataSource scds = new SingleConnectionDataSource();
scds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
scds.setUrl("jdbc:oracle:thin:@host:1521:service_name");
scds.setUsername(...);
scds.setPassword(...);
Connection conn = scds.getConnection();
System.out.println("Preparing array of strings");
ArrayDescriptor descriptor1 = ArrayDescriptor.createDescriptor("VARCHAR2_TABLE", conn );
ARRAY arrayToPass1 = new ARRAY( descriptor1, conn, list1 );
System.out.println("Preparing array of string pairs");
ArrayDescriptor descriptor2 = ArrayDescriptor.createDescriptor("VARCHAR2_PAIRS_TABLE", conn );
ARRAY arrayToPass2 = new ARRAY( descriptor2, conn, list2 );
System.out.println("Calling procedure");
OraclePreparedStatement ps = (OraclePreparedStatement) conn.prepareStatement( "{ call arrays_from_java( ?, ?, ? ) }" );
ps.setString( 1, "string");
ps.setARRAY( 2, arrayToPass1 );
ps.setARRAY( 3, arrayToPass2 );
ps.execute();
ps.close();
}
.
Należy też dodać do classpath orai18n.jar, gdyż w przeciwnym wypadku jeśli baza nie będzie miała formatu UTF8 może występować błąd, że stringi się nullują podczas przekazywania do procedury
Ostatnio musiałem importować dane z bazy danych z kodowaniem w której zachodnioeuropejskiego w której były przechowywane polskie znaki. Było z tym trochę problemów, więc opiszę rozwiązanie
Zagadnienie
Baza A – źródłowa, u klienta. Kodowanie zachodnioeuropejskie WE8DEC. Przechowywane są w niej jednak polskie znaki w kodowaniu EE8ISO8859P2 czyli ISO-8859-2. Takie kodowanie stosuje klient bazy. Oracle pozwala na takie kombinacje. Miałem kiedyś do czynienia z bazą danych w kodowaniu US7ASCII, która też przechowywała polskie znaki, mimo że to kodowanie zawiera tylko na znaki do kodu 127 a więc bez narodowych. Można zapisywać lub odczytywać dane z poziomu klienta tak aby konwersje nie miały miejsca – dzięki temu obce kodowanie nie przeszkadza. Problem pojawił się przy dostępie przez DB linki, ale o tym za chwilę.
Baza B – docelowa, u nas. Optymalne kodowanie AL32UTF8 pozwalające przechowywać wszystkie znaki łącznie ze wschodnioazjatyckimi.
(continue reading…)
Hierarchical queries (CONNECT BY … START WITH …) are used when retrieving data from tree-like structures. You use them if for example you want to retrieve all people who are directly or indirectly below a certain person in a company hierarchy. However, if you use joins and add some other conditions in the WHERE clause then the effect of the query is not obvious.
(continue reading…)
Once we had a need of accessing a table on an Microsoft SQL Server database from an Oracle database. Thus we had to install Oracle Transparent Server on the computer hosting SQL Server. Oracle Transparent Services is a kind of adapter for non-Oracle servers and allows access to it through database link. During the instalation of Oracle Transparent Server you have to choose modules dedicated to particular databases. The modules can translate calls to some functions and allows to call a T-SQL stored procedure from Oracle.
(continue reading…)
I take part in supporting an ODS system. One of the biggest problem during deployment of revisions is the dependency between packages. Suppose a package A has to be changed and that it is referenced be two packages: B1 and B2 which are used in some database sessions. If the the A package is altered and it contained some global variables then the next execution of the packages B1 or B2 in those sessions triggers „ORA-04061: existing state of package has been invalidated” error. This is because global variables preserve their values during database sessions. Alteration of the package code may change the variables, delete some of them, add others. Therefore, they have to be initialized again. Reseting the global variables during the session could lead to inconsistency. In order to prevent this. Oracle throws the ORA-04061 exception. This mechanism is described in details on the page http://oraclequirks.blogspot.com/2007/03/ora-04061-existing-state-of-package-has.html
(continue reading…)
