Category Archives: Oracle

Oracle Client Installer crash during Installation on Windows

An old solution to  a new problem.

When running   an Oracle client (19 in my case) installation on a windows 2019 server, the installation opened a new command window and immediately crashed.

After taking a video of the crash I was able to see that the message was:

Starting  Oracle Universal Installer ..

Checking monitor: must be configure to display at least 256 colors Higher than 256. Actual 4294967296 Passed

Preparing to launch Oracle Universal Installer from C:\Users\{username}\AppData\local\temp\ ….

Please wait … Error: Unable to access jarfile C:\{installer directory}

Google provided little help until I remembered the solution described in my own post from 14 years ago (OUI.EXE has stopped working on Windows 2008).

Apparently, The simple solution to  many Oracle Installer problems is Copying the installer directory to a location with a short path and no spaces in the directory path.

For example “C:\temp\”  instead of “C:\Users\username\Downloads\oracle installer downlod\setup.exe”.

So, 14 years have passed, Windows 2008 is now Windows 2019 and this weird bug is not fixed. At least the workaround still works

Short reminder for Informatica ORA-24333: zero iteration count

When you run an Informatica  workflow and you get the following error message

ORA-24333: zero iteration count

Database driver error...
Function Name : Execute
SQL Stmt :       TABLE_NAME.COL1='A'
Oracle Fatal Error
Database driver error...
Function Name : Execute
SQL Stmt TABLE_NAME.COL1='A'
Oracle Fatal Error].

The Informatica error description in https://kb.informatica.com/solution/18/Pages/121171.aspx  say

” This error occurs because the SQL query sent to the database for the Source Qualifier only contains the Join statement – this is caused by putting the JOIN-statement in the SQL Query property instead of in the User Defined Join property of the Source Qualifier.”

This is a little confusing because the message talks about joins when in many cases, like the above example, there is no join.

 

Still the problem is the same and all you need to do is to move the query from the SQL QUERY to the USER DEFINED JOIN section

ORA-24333

Weird oracle bug – ORA-02019 when creating MATERIALIZED VIEW

ORA-02019: connection description for remote database not found

TL;DR
When you try to create a materialized view on a different schema and the query selects from more than one remote object you get a misleading error ORA-02019: connection description for remote database not found.
Your options:
change the query to use one objects.
Connect as the other user and create the materialized view
Ugly workaround – create a materialized view for one of the remote objects and use it in the query.

Long version:

Lately, I was trying to create a materialized view. The problem was that the query behind the view was using a sub query. As a result, when I tried to run the CREATE MATERIALIZED VIEW command, I got the famous ORA-22818: subquery expressions not allowed here.

create MATERIALIZED VIEW u1.JOIN_EMP_DEPT_MV as 
select a.employee_id,a.department_id ,(select department_name from hr.departments@orcl d where 
                                       d.department_id = a.department_id ) department_name
from hr.employees@orcl a  ;

According to Oracle error codes, creating a materialized view containing a sub query is not supported

ORA-22818: subquery expressions not allowed here
22818. 00000 -  "subquery expressions not allowed here"
*Cause:    An attempt was made to use a subquery expression where these
           are not supported.
*Action:   Rewrite the statement without the subquery expression.

In order to Rewrite the statement without the subquery expression I used the plsql guru Steven Feuerstein advice and created a regular view with the complex query and tried to create a materialized on top of the view.

As a side note, this way of building a simple materialized view on top of a regular view is a good practice since it will allow you to change the materialized view without having to drop and recreate it.

The first part of the process went well and the view was created and worked as expected

create  VIEW u1.JOIN_EMP_DEPT_V as 
select a.employee_id,a.department_id ,
                                     (select department_name from hr.departments@orcl d 
                                      where d.department_id = a.department_id ) department_name
from hr.employees@orcl a  ;

However, once I tried to create the materialized view on the application schema using my private schema, I got the following message

conn U2/U2

create MATERIALIZED VIEW u1.JOIN_EMP_DEPT_MV as 
select * from u1.JOIN_EMP_DEPT_V;

ORA-02019: connection description for remote database not found

This message is completely misleading. We know that the db link exists and we know it works. After all, the view was created using this db link.

create or replace VIEW u1.JOIN_EMP_DEPT_V as 
select a.employee_id,a.department_id from hr.employees@orcl a  ;

One remote object – Works!

In addition, user U1 can create the the materialized view without a problem

conn U1/U1
create MATERIALIZED VIEW u1.JOIN_EMP_DEPT_MV as 
select * from JOIN_EMP_DEPT_V;

also works!

Apparently, I am not the only one encountering the ORA-02019: connection description for remote database not found issue.

Creating materialized view of another user via private db link succeeded by selecting single table while failed by selecting more than one table (Doc ID 2305974.1)

Sadly, according to Oracle, this is not bug. Basically, they claim that this is the way it works and you can ask for an ENHANCEMENT REQUEST.

If creating the view with only one remote object or connecting as the other user are not an option , one ugly workaround would be to create a materialized view for one of the tables and create the join it with one remote object

create MATERIALIZED VIEW u1.JUST_DEPT_MV as 
select * from hr.departments@orcl;


create or replace VIEW u1.JOIN_EMP_DEPT_V as 
select a.employee_id,a.department_id ,(select department_name from u1.JUST_DEPT_MV d where d.department_id = a.department_id ) department_name
from hr.employees@orcl a  ;


Short and sweet way to increase Sequence value to be higher than the max value in the table

Many times during the development process, there is a need to copy /export/backup table data between environments or stages. While it is easy to export the data using SQL inserts, it is also very easy to forget the sequences related to the tables.
Sometimes, this practice results in DUP_VAL_ON_INDEX exception (ORA-00001) when you use NEXTVAL in your insert. The tables may already include high values but the sequence is lower than the max values in the table.
Therefore it is important to remember to increment the sequences to a value higher than the max value in the table.

The most common way to increase the sequence value to the next value in the table is to:

1) alter the sequence increment to the difference between the current value of the sequence and the max value in the table.

ALTER SEQUENCE sequence-name INCREMENT BY 500;

2) Issue a dummy nextval request

select sequence-name.nextval from dual;

3) Alter the sequence increment value back to the original increment

ALTER SEQUENCE sequence-name INCREMENT BY 1;

Another option of course is to drop the sequence and recreate it with the required value but this may invalidate objects referencing to it. Also, it will force you to re-apply all grants and permissions.
Both above methods will work but not everyone have permissions to change the sequence. In addition, DDL operations are always risky. So, If you are looking for a short and sweet (or quick and dirty) way to increase the sequence value, look no further.

select  level, sequence-name.NEXTVAL
from  dual 
connect by level <= (select max(column-using-the-sequence  ) from table-name);

Run a function with a table of User Defined Type (UDT) as a parameter using SQLPlus

If you want to send multiple records to an oracle function, a common practice is to create the function with one or more parameters as a table of an object type.

In the function itself, you can loop thru the array or select from it using to TABLE command.

Another option to send an array of multiple records is to use XML (xmltype) as the function parameter but this will be addressed in a separate post.

While I use this method for years, for some reason, many times I find myself struggling with the relatively simple syntax. This post will serve me (and maybe some of you) as a short, very simplistic template to copy from.

Basically, what you need to do is the create a user defined object type (UDT)

create or replace type order_type as object (
  num_of_units number
, cost_of_unit number)

Then you create a table of this user defined type

create or replace type order_tbl as  table of order_type

A simple function

create or replace function calc_total (i_orders order_tbl) return number is

res number;

begin


   select sum(num_of_units * cost_of_unit)
   into res
   from table (i_orders);

  

   return res;

end;

Testing this function with SQLPlus is a little challenging. The simple way to send a table of UDT type to a function is to cast it to the table of the user defined type.

SQL>   select  calc_total(
  2                      cast(MULTISET(
  3                        select * from
  4                        (
  5                        select 9 num_of_units, 2 cost_of_unit from dual union all
  6                        select 6, 3  from dual
  7                        ) ) as order_tbl   ))
  8    from dual;
 
CALC_TOTAL(CAST(MULTISET(SELEC
------------------------------
                            36

The reason for the additional inline view is the weird oracle bug the cause the function to hang (and sometimes crash with ora-03113 and ora-00600 ) when using cast and multiset together with union. The inline view is fast workaround for this.
Another option to work around this problem is to use multiset union

SQL>    select  calc_total( (cast(multiset(
  2                               select 9,2 from dual
  3                                MULTISET UNION
  4                               select 6,3 from dual
  5                           ) as order_tbl   ))
  6                       )
  7     from dual;
 
CALC_TOTAL((CAST(MULTISET(SELE
------------------------------
                            36