Author Archives: Asaf Tal

Short reminder: Why IMPDP hangs on a specific stage

If you are importing a dump file and the import simply hang on:

Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE

or

Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION

or

Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE

or

Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY

or

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER

or

Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW

and so on,

In my case, most of the time the reason is that there are dblinks inside these objects and the DATABASE LINKS are not working on the target machine.

During the import, the database is trying to compile each objects but fails only after it gets a timeout for the connection. This could take even 2 minutes per object.

One workaround could be to exclude these object upfront (many times they are invalid anyway in the source DB)

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: how to handle Informatica http transformation failure response

A common way to invoke rest services is using informatica http transformation. This is an easy solution and it works great. You can get the response in the HTTPOUTPUT field you can write the response to the Database or  parse it using Data Process Mappllet,  or even simple string functions.

While this works great when the service returns a successful response, by default, a failure response  ( 4xx and 5xx http errors for example) the HTTPOUT port is empty and an error is raised.

So, what can you do if you want to write the failure response to the Data Base and continue to the next row? This way, the task can be completed and the failed records can be logged or retried.

The simple solution is to Set the Custom property IncludeErrorResponsesInHTTPTransformation=Yes

This can be done in the session level but also in the integration server level.

After setting the custom property Workflow Manager -> Edit Tasks -> Config Object -> Custom Properties -> Add attribute IncludeErrorResponsesInHTTPTransformation and write Yes in Value field

The error response will come out of the HTTP OUT port of the HTTP transformation just like a  successful response.

Custom Property IncludeErrorResponsesInHTTPTransformation

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  ;