All posts in 'Oracle'

How to retrieve tables of custom object types with JDBC


The following is not only a personal reminder for me but also a first try to answer some questions on Stack Overflow… You can create custom object types in an Oracle database like so: CREATE TYPE t_demo_object AS OBJECT ( some_number NUMBER, some_string varchar2(32) ) / Those types can have member functions, custom constructors and […]

Read the complete article »

Oracle, JBDC, CallableStatements and named parameters


Update: Please take a note of comment #5 by DSurber. He works for Oracle and sheds some light on named parameters. In short: The names of the parameter do not refer to named placeholders in statement (like in JPA for example) but to PL/SQL named parameters!. Thank you very much. You might have wondered what […]

Read the complete article »

Optimizing the Oracle Query Optimizer


The Oracle Query Optimizer does a great job in rewriting Queries. For example the nested subquery in SELECT C.cust_last_name, C.country_id FROM customers C WHERE EXISTS (SELECT 1 FROM sales S WHERE S.quantity_sold > 1000 AND S.cust_id = C.cust_id); will be rewritten into the following query, using a semi-join: SELECT C.cust_last_name, C.country_id FROM customers C, sales […]

Read the complete article »

Oracle “sleep” procedure: DBMS_LOCK.SLEEP


There’s a nice little “sleep” procedure in Oracle: A procedure that stops the execution of the current thread for n seconds. Strangely, this method can be called in SQL*Plus like so: EXEC dbms_lock.sleep(10); but not in another stored procedure or function like so CREATE OR REPLACE PROCEDURE FOOBAR AS BEGIN DBMS_LOCK.SLEEP(1); END; / To use […]

Read the complete article »

Oracle NLS-Lang Settings


To use SQL*Plus Windows correctly, export NLS_LANG like so: set NLS_LANG=GERMAN_GERMANY.WE8MSWIN1252 If you prefer the command line client SQL*Plus, export NLS_LANG like so: set NLS_LANG=GERMAN_GERMANY.WE8PC850

Read the complete article »