Ravi Vedala's blog - exclusively for Java/Oracle developers: March 2008

If you are a pl/sql developer and if you looking for best practices on unit testing, this is for you.

Usually we run into situations where we need to debug some code, but we don't want to be running the debug code in production. Do we ???

Oracle 10g provides a beautiful feature - conditional compilation. (Like #ifdef in C).

Let's see an example.

Create a test procedure ...

create or replace procedure test_proc
as
begin
dbms_output.put_line( 'Debug 1' );
dbms_output.put_line( 'Debug 2' );
$IF $$debug_code $THEN
dbms_output.put_line( 'Debug 3' );
dbms_output.put_line( 'Debug 4' );
$END
dbms_output.put_line( 'Debug 5' );
end;

set echo on;
set serveroutput on;

Let us suppose that we are running this procedure on our dev/qa environment.

Then, you have to run the following command :
------------------------------------------------------------------------------------
SQL> alter procedure test_proc compile plsql_ccflags = 'debug_code:true' reuse settings;

Procedure altered

SQL>
------------------------------------------------------------------------------------

Now let's run the procedure and see what the output is ....
------------------------------------------------------------------------------------
SQL> exec test_proc;

Debug 1
Debug 2
Debug 3
Debug 4
Debug 5

PL/SQL procedure successfully completed

SQL>
------------------------------------------------------------------------------------

Let's suppose that we are all set to go to production and we need to deploy this procedure on production and we don't want all the debugging.

Then, execute the command :

------------------------------------------------------------------------------------
SQL> alter procedure test_proc compile plsql_ccflags = 'debug_code:false' reuse settings;

Procedure altered

SQL>
------------------------------------------------------------------------------------
That's it. Let's see the output of the procedure, if we run it on production.

------------------------------------------------------------------------------------
SQL> exec test_proc;

Debug 1
Debug 2
Debug 5

PL/SQL procedure successfully completed

SQL>
------------------------------------------------------------------------------------
So, on production when the debug is disabled, you might have noticed that the statements "Debug 3" and "Debug 4" did not print.

Plan B : If you don't want to pollute your code, you can you Log4PLSQL.

Long Live "Tom Kyte".

Good Luck !!

r-a-v-i

Environment : JDK 1.5 (did not test on older versions of java but it should work on them !!)

Keep the JDBC driver in the classpath : '''ojdbc14.jar'''

Note : Oracle ARRAY is supported on Oracle9i Database version 9.0.1 or later.

Take a look at the package : Tests

'''Java Code''' :
(Look at the technique - don't look at the java standard practices like using try-catch-finally ...etc., to keep the code readable, I have just taken them out.)
----------------------------------------------------------------------------------------------------------
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
public class TestArray {
private static Connection getConnection() {
Connection conn = null;
try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
conn =
DriverManager.getConnection("",
"test", "test");
conn.setAutoCommit(false);
System.out.println("Connection :- " + conn);
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return conn;
}

private static void insertEmp(Connection conn) throws Exception {
/* let us create 20 names in a string array*/
String[] names = new String[20];
for (int i = 0; i < 20; i++) {
names[i] = "Ravi" + i;
}
/*Create an array descriptor of type STRINGS */
ArrayDescriptor desc =
ArrayDescriptor.createDescriptor("STRINGS", conn);
/* Create an array object */
ARRAY nameArray = new ARRAY(desc, conn, names);
String st = "begin Tests.insert_emp(:1); end;";
CallableStatement cs = conn.prepareCall(st);
cs.setArray(1, nameArray);
cs.execute();
cs.close();
conn.commit();
System.out.println("Successfully INSERTED " + names.length +
" EMPLOYEE records :- ");
}

private static void getEmp(Connection conn) throws Exception {
String[] names = { };
/*Create an array descriptor of type STRINGS */
ArrayDescriptor desc =
ArrayDescriptor.createDescriptor("STRINGS", conn);
/* Create an array object */
ARRAY nameArray = new ARRAY(desc, conn, names);
String st = "begin Tests.get_emp(:1); end;";
CallableStatement cs = conn.prepareCall(st);
/* Register the out parameter as STRINGS */
cs.registerOutParameter(1, OracleTypes.ARRAY, "STRINGS");
cs.execute();
/* Get the array into a local ARRAY object */
nameArray = (ARRAY)cs.getArray(1);
System.out.println("Array is of type " + nameArray.getSQLTypeName());
System.out.println("Array element is of type code "+nameArray.getBaseType());
System.out.println("Array is of length " + nameArray.length());
/* Get the array of names into a string array */
names = (String[])nameArray.getArray();
System.out.println("*****************************************************************");
System.out.println("Retrieving ONLY the names of employees as an ARRAY \n");
System.out.println("*****************************************************************");
for (int i = 0; i < names.length; i++)
System.out.println(names[i]);
cs.close();
}

private static void getEmpRecs(Connection conn) throws SQLException {
/* Create an array of objects (since we are getting heterogeneous data)*/
Object[] empRecs = { };
/* Create an array descriptor for EMP_REC_ARRAY */
ArrayDescriptor desc =
ArrayDescriptor.createDescriptor("EMP_REC_ARRAY", conn);
ARRAY nameArray = new ARRAY(desc, conn, empRecs);
String st = "begin Tests.get_emp_recs(:1); end;";
CallableStatement cs = conn.prepareCall(st);
/* Register OUT param as EMP_REC_ARRAY */
cs.registerOutParameter(1, OracleTypes.ARRAY, "EMP_REC_ARRAY");
cs.execute();
/* Get the Array into a local ARRAY object */
nameArray = (ARRAY)cs.getArray(1);
System.out.println("Array is of type " + nameArray.getSQLTypeName());
System.out.println("Array element is of type code "+nameArray.getBaseType());
System.out.println("Array is of length " + nameArray.length());
/* Get the Employee Records */
empRecs = (Object[])nameArray.getArray();
System.out.println("***********************************************************");
System.out.println("Retrieving the EMPLOYEE RECORDS as an ARRAY !!\n");
System.out.println("***********************************************************");
int id = 0;
String name = "";
for (int i = 0; i < empRecs.length; i++) {
/* Since we don't know the type of the record, get it into STRUCT !! */
oracle.sql.STRUCT empRec = (oracle.sql.STRUCT)empRecs[i];
/* Get the attributes - nothing but the columns of the table */
Object[] attributes = empRec.getAttributes();
/* 0- first column, 1 - second column ...*/
id = Integer.parseInt("" + attributes[0]);
name = "" + attributes[1];
System.out.println("id = " + id + " name = " + name);
}
cs.close();
}

public static void main(String[] args) throws Exception {
Connection conn = null;
try {
conn = getConnection();
/*Insert 20 employee names*/
insertEmp(conn);
/*Get the 20 employee names*/
getEmp(conn);
/*Get the 20 employee RECORDS*/
getEmpRecs(conn);
} catch (Exception e) {
System.out.println(e.getMessage());
} finally {
if (conn != null)
conn.close();
}
}
}

----------------------------------------------------------------------------------------------------------
'''SQL Scripts :'''

Create a test table and the types :

create table emp(id number , name varchar2(50))
/

create or replace type Strings IS VARRAY(20) of VARCHAR2(30)
/

create or replace type emp_rec IS OBJECT (id number, name varchar2(30))
/

create or replace type emp_rec_array AS VARRAY(100) of emp_rec
/

create or replace package Tests is

-- Author : r-a-v-i

procedure insert_emp(emp_names Strings);
procedure get_emp(emp_names out Strings);
procedure get_emp_recs(emp_recs out emp_rec_array);
end Tests;
/
create or replace package body Tests is
procedure insert_emp(emp_names Strings)
as
cnt Integer := 1;
v_id Integer := 0;
begin
dbms_output.put_line(emp_names.COUNT);
loop
if cnt > emp_names.COUNT then
exit;
else
dbms_output.put_line(cnt||' = '||emp_names(cnt));
select nvl(max(id),0) + 1 into v_id from emp;
insert into emp(id,name) values (v_id,emp_names(cnt));
end if;
cnt := cnt + 1;
end loop;
commit;
end;
procedure get_emp(emp_names out Strings)
as
v_emp_names Strings := Strings();
cursor c1 is select name from emp order by id;
v_name varchar2(30);
cnt Integer := 1;
begin
open c1;
loop
fetch c1 into v_name;
if c1%notfound then exit;
else
dbms_output.put_line(cnt||' = '||v_name);
v_emp_names.extend;
v_emp_names(cnt) := v_name;
end if;
cnt := cnt + 1;
end loop;
emp_names := v_emp_names;
end;
procedure get_emp_recs(emp_recs out emp_rec_array)
as
v_emp_recs emp_rec_array := emp_rec_array();
cursor c1 is select id,name from emp order by id;
v_id Integer;
v_name varchar2(30);
v_emp_rec emp_rec;
cnt Integer := 1;
begin
open c1;
loop
fetch c1 into v_id,v_name;
if c1%notfound then exit;
else
v_emp_rec := emp_rec(v_id,v_name);
v_emp_recs.extend;
v_emp_recs(cnt) := v_emp_rec;
end if;
cnt := cnt + 1;
end loop;
emp_recs := v_emp_recs;
end;
end Tests;
/

Long live "Tom Kyte".

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

Ravi Vedala's blog - exclusively for Java/Oracle developers

Wednesday, March 5, 2008

Java/Oracle : Should I Encrypt / Hash the passwords ? What is the difference ?

Tuesday, March 4, 2008

Oracle 10g : PL/SQL : Conditional Compilation

Oracle 10g : Case-Insensitive Searching

Oracle 10g : How to pass ARRAYS of records from Java/Tomcat to Oracle

Saturday, March 1, 2008

Oracle 10g : How to pass ARRAYS to Oracle using Java?

Blog Archive

About Me