Category: SQL

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 […]

Something I really often use is this select: SELECT distinct type,line,name,text FROM user_source WHERE lower(Text) LIKE(‘%’||lower(:search_source)||’%’) UNION SELECT distinct ‘column’ type,null line,table_name||’.’|| COLUMN_NAME name,null FROM user_tab_columns WHERE lower(COLUMN_NAME) LIKE(‘%’||lower(:search_source)||’%’) UNION 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 […]

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 […]

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: […]