My quiz on the PL/SQL Challenge

On friday my quiz was on the PL/SQL Challenge.

Topic: “Guidelines for Designing Triggers: Avoid Non-Transactional Logic in DML Triggers”

Yes, about “statement restart” :-)
My idea about the quiz was “making developers aware of the Oracle behaviour”.
And I hope a lot of PL/SQL developers learned from it.

It wasn’t easy to come up with the question, the way of asking, the correct words, the answers…
But after a lot of mailing between Steven and the reviewers we finally had a quiz.

A lot people had it wrong, but I hope they don’t mind and are now aware of statement restart.

For more information and discussion about the quiz: PL/SQL Challenge blog

Search database objects with a variable in oracle

Something I really often use is this select:

SELECT distinct type,line,name,text
FROM user_source
WHERE lower(Text) LIKE(‘%’||lower(:search_source)||’%’)
SELECT distinct ‘column’ type,null line,table_name||’.’|| COLUMN_NAME name,null
FROM user_tab_columns
WHERE lower(COLUMN_NAME) LIKE(‘%’||lower(:search_source)||’%’)
SELECT  DISTINCT ‘table’ type,null line,object_name name,null
from user_objects
WHERE object_type IN  (‘TABLE’,’VIEW’)
AND lower(object_name) LIKE(‘%’||lower(:search_source)||’%’)
ORDER BY type,name,line

This select will show you most of the database objects in a schema(columns, table,view,code), where this ‘search_source’ string is used.

You can add this into you sqldeveloper as well, as a user defined report.
To accomplish this you will have to do the following steps:
1) Open sqldeveloper
2) go to Reports tab(If you don’t see this tab go to the Menu choose view->Reports
3) right-click ‘User Defined Reports’ -> add Report -> give name and put the sql code as above into SQL input screen(you can leave the rest as default)
You can use this Search in sqldeveloper now.

XMLTYPE column larger THAN 4000 bytes => ORA-19011

When you try to convert an xml file, which has a tag that contains for example one or more pdf files, into an XMLTYPE object, you will get the following error:
ORA-19011: CHARACTER string BUFFER too small

This error will be thrown because the content of a tag of an XMLTYPE is limited to 4000 bytes(this problem should be solved in 11g).
I have developed a solution/workaround for this.

Continue reading

Java ArrayList, Callable Statements and PL/SQL Procedures

If you’re developing a Java Application which integrates with an Oracle back-end, you’ve probably run into the following technicalities:
transforming an arraylist as a parameter to a callable statement to process and persist the data in your Oracle Database.

How can you accomplish this in a generic and re-usable manner:
1. Create an objecttype (PL/SQL) – Backend:

CREATE OR REPLACE type ot_emp as object
( emp_id number(10),
emp_cd varchar2(20),
name varchar2(100));

2. Create a collection-type based on the objecttype (PL/SQL) – Backend:

CREATE OR REPLACE type ct_emp as table of ot_emp;

3. Create a PL/SQL procedure to process and persist the data coming from the front-end app:

PROCEDURE p_insert_emp(
pi_employees IN ct_emp_coll,
po_message_info OUT ot_message_info);

4. Create a Java Object for each Oracle object-type implementing the SQLData-API:

public class Employee extends SuperEmployee implements SQLData
public void readSQL(SQLInput stream, String typeName) {}
public String
getSQLTypeName() { return
“HR.OT_EMP”; }
public void writeSQL(SQLOutput stream)
throws SQLException {

The readSQL() method is used to map the Oracle data to Java data, the getSQLTypeName() and writeSQL() is used to map java data to Oracle data.

After putting together the framework, you can then pass the arraylist with the callablestatement and process the information into your back-end environment.

Of course this kind of functionality is handled by ORM frameworks as well, such as Hibernate, Toplink, iBatis, …