All posts in 'Oracle'

How to retrieve tables of custom object types with JDBC

24-Jul-12

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) ) /CREATE TYPE t_demo_object AS OBJECT ( some_number number, some_string varchar2(32) […]

Read the complete article »

Oracle, JBDC, CallableStatements and named parameters

23-Jul-12

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

20-Oct-10

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);SELECT C.cust_last_name, C.country_id FROM customers C WHERE EXISTS (SELECT 1 FROM sales S WHERE S.quantity_sold > 1000 and […]

Read the complete article »

Oracle “sleep” procedure: DBMS_LOCK.SLEEP

07-Sep-10

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);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; /CREATE OR […]

Read the complete article »

Oracle NLS-Lang Settings

13-Aug-10

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 »