oracle – Page 3 – Tarik Billa

Is there any way to have the JBoss connection pool reconnect to Oracle when connections go bad?

December 20, 2023 by Tarik

Whilst you can use the old “select 1 from dual” trick, the downside with this is that it issues an extra query each and every time you borrow a connection from the pool. For high volumes, this is wasteful. JBoss provides a special connection validator which should be used for Oracle: <valid-connection-checker-class-name> org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker </valid-connection-checker-class-name> This … Read more

Multiple REPLACE function in Oracle

December 18, 2023 by Tarik

Even if this thread is old is the first on Google, so I’ll post an Oracle equivalent to the function implemented here, using regular expressions. Is fairly faster than nested replace(), and much cleaner. To replace strings ‘a’,’b’,’c’ with ‘d’ in a string column from a given table select regexp_replace(string_col,’a|b|c’,’d’) from given_table It is nothing … Read more

PL/SQL ORA-01422: exact fetch returns more than requested number of rows

December 14, 2023 by Tarik

A SELECT INTO statement will throw an error if it returns anything other than 1 row. If it returns 0 rows, you’ll get a no_data_found exception. If it returns more than 1 row, you’ll get a too_many_rows exception. Unless you know that there will always be exactly 1 employee with a salary greater than 3000, … Read more

oracle varchar to number

December 12, 2023 by Tarik

You have to use the TO_NUMBER function: select * from exception where exception_value = to_number(‘105’)

Oracle query execution time

December 12, 2023 by Tarik

One can issue the SQL*Plus command SET TIMING ON to get wall-clock times, but one can’t take, for example, fetch time out of that trivially. The AUTOTRACE setting, when used as SET AUTOTRACE TRACEONLY will suppress output, but still perform all of the work to satisfy the query and send the results back to SQL*Plus, … Read more

Loop through pre defined values

December 11, 2023 by Tarik

You could do this, though probably not as slick as you’d like: declare type nt_type is table of number; nt nt_type := nt_type (1, 3, 5); begin for i in 1..nt.count loop dbms_output.put_line(nt(i)); end loop; end; If you create a type in the database: create type number_table is table of number; then you can do … Read more

Oracle client installation error – path too long

December 10, 2023 by Tarik

This limitation is based on older Windows restrictions, where length of environmental variables was important. This limitation is still there in the Oracle installation. Work around this: Step 1: Copy the value of your ‘path’ variable to a text-editor (Ex.: notepad) and save this value as backup. Step 2: Reduce the size of this path … Read more

DataGrip: how to connect to Oracle as SYSDBA

December 8, 2023 by Tarik

Coming back here two years later, it seems to be not possible anymore to change the advanced properties in the connection settings. Instead you have to supply sys as sysdba as username. (This might seem obvious but took me a while to find this out)

oracle blob text search

December 8, 2023 by Tarik

This is quite possible and easy to do. Simply use dbms_lob.instr in conjunction with utl_raw.cast_to_raw So in your case, if t1 is a BLOB the select would look like: select * from table1 where dbms_lob.instr (t1, — the blob utl_raw.cast_to_raw (‘foo’), — the search string cast to raw 1, — where to start. i.e. offset … Read more

PLS-00201 – identifier must be declared

December 6, 2023 by Tarik

When creating the TABLE under B2BOWNER, be sure to prefix the PL/SQL function with the Schema name; i.e. B2BOWNER.F_SSC_Page_Map_Insert. I did not realize this until the DBAs pointed it out. I could have created the table under my root USER/SCHEMA and the PL/SQL function would have worked fine.