Informatica – [PCSF_46002] Failed to communicate with the server

Leave a reply

After serving me well for several years, my Informatica client suddenly stopped working.
I got the following error when trying to connect to one of my domains.

Error: [PCSF_46002] Failed to communicate with the server at [http://[server]:[port]/coreservices/DomainService].

Since it took me a good few hours to find the solution for the problem, I will write all my failed attempts and the actual solution in my case. I hope it will save someone (probably me) some time in the future. (TL;DR – delete proxy environment variables)

Verify that server is working– In my case, the domain I was trying to connect to was Production, the first thing I did is to verify that the server is up and that the integration service is running. Very quickly I found that all the target tables still get updated as expected. Knowing that the server is running, allowed to proceed with less stress.

Verify that Informatica Administration console is available.
Since I was able to connect to my Informatica Administration Web Console on http://server:port/administrator/#admin I came to the conclusion that the problem is on the client side. Luckily I was able to confirm it by connecting to the domain using one of my colleague workstation. Only after that, I understood that I am not able to connect to any domain, so the problem is on my machine without a doubt.

On the client, I followed in formatica KB suggestion at https://kb.informatica.com/solution/23/Pages/61/509256.aspx and clicked on ” Configure Domains” and tried to delete the domain entries and recreate them. This time I got almost identical message

Unable to save information for domain .
Error: [PCSF_46002] Failed to communicate with the server at [http://[server]:[port]/coreservices/DomainService].

Since the message started with “Unable to save”, the next suspect was a problem in the OS. I verified that I have permission to write to the INFA_HOME location (\clients\PowerCenterClient\domains.infa in my case). After some more googling I also tried to manually write/edit the domains.infa file and created INFA_DOMAINS_FILE environment variable pointing to it. Still, no success.

The next step is to check network connectivity. I verified that I am able to ping or telnet the server. Since pinging the computer was successful, my only remaining option was to verify that no firewall or proxy is blocking me. While searching the knowledge base on how to check the proxy setting, I came across this article
https://kb.informatica.com/solution/18/Pages/121821.aspx
from which I learned that setting the following environment variables can interfere with the efforts to connect to the domain.
• PROXY
• http_proxy
• https_proxy
Only then, I remembered that as part of the Docker installation on my PC, I did set the HTTP_PROXY environment variable. I deleted the HTTP_PROXY environment variable (on windows: My computer – properties – Advanced system settings – Environment Variables) and after a restart my Informatica client came back to life. I wish the error messages were a little clearer but I hope this blog post will help.

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

Leave a reply

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;

ALTER SEQUENCE sequence-name INCREMENT BY 500;

2) Issue a dummy nextval request

SELECT sequence-name.nextval FROM dual;

select sequence-name.nextval from dual;

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

ALTER SEQUENCE sequence-name INCREMENT BY 1;

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);

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

Leave a reply

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)

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

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;

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

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

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

A short reminder: Why OSB Database Adapter returns ORA-01403 when the function return results on SQLPlus

Leave a reply

In OSB (Oracle Service Bus) a Database Adapter is a great tool for wrapping a database function and using it as part of a complex process or providing it as a web service. A common practice is that the function returns a table of user defined types (UDT) that can can be read by the adapter.

During the creation of the DB adapter, the oracle user that is used by JDeveloper needs to be granted with permissions for the function and the returned user defined types (the object and the table of the object).

However, during runtime, the OSB Database Adapter somtimes fails with BEA-382500 OSB Service Callout action received SOAP Fault response. Inside the results you can find the following line:

” Cause: java.sql.SQLException: ORA-01403: no data found … … nested exception is: BINDING.JCA-11812 or BINDING.JCA-11811 ”

This message can be misleading because many times , running the same function with same user in SQLPlus will return results. Luckily, the solution is simple, in order for the Data Base Adapter to work properly, all you need to do is to grant the user running the db adapter with permission for ALL types used by the function (even internally)
for some reason (maybe a difference between the Oracle Clients), the DB adapter requires permissions on the internal types that are used inside the function while SQLPlus (Toad or plsql Developer) can work without these permissions.

How to schedule a job to run on a different hour on a specific date.

Leave a reply

Oracle jobs are a simple way to schedule procedure and processes. However, sometimes the scheduling requirements can get really complicated. A very common request is to run a job on a specific hour on weekdays and not to run it on the weekend. This request can be easily delivered using the “WHAT” parameter of the dbms_job.submit procedure.

BEGIN
  IF to_char(sysdate, 'D') BETWEEN 2 AND 6 THEN
      my_procedure ;
  END IF;
END;

begin if to_char(sysdate, 'D') between 2 and 6 then my_procedure ; end if; end;

However, what will you do if you need to run the procedure at a specific hour on Monday to Friday but on a different hour on the weekend?
In this case you will be happy to know that you can use CASE on the “INTERVAL” parameter of the dbms_job.submit procedure.

DECLARE
  my_job NUMBER;
BEGIN
  dbms_job.submit(job => my_job, 
    what => 'my_procedure ;',
 
     INTERVAL => 'case
                     when  to_number(to_char(sysdate, ''D'')) between 1 and 5 then 
                        TRUNC(SYSDATE+1) + 16 /24 
                     when  to_number(to_char(sysdate, ''D'')) between 6 and 7 then
                         TRUNC(SYSDATE+1) + 8 /24 
                  end'
 
 
    );
END;

declare my_job number; begin dbms_job.submit(job => my_job, what => 'my_procedure ;', interval => 'case when to_number(to_char(sysdate, ''D'')) between 1 and 5 then TRUNC(SYSDATE+1) + 16 /24 when to_number(to_char(sysdate, ''D'')) between 6 and 7 then TRUNC(SYSDATE+1) + 8 /24 end' ); end;

remember that this calculation is for NEXT run not for the current. Therefore, you will need to take into consideration that the timinig for Monday will be calculated on Sunday (I know it sounds obvious but you will be surprised how many time you can do the same mistake).

While the case clause can handle many calculations and complications, there is one caveat. The case statement can handle only 200 characters. This becomes a problem when you try to write a longer statement. For example:

CASE
  WHEN  to_number(to_char(sysdate, 'D')) BETWEEN 1 AND 4 THEN 
      TRUNC(SYSDATE+1) + 16 /24 
  WHEN  to_number(to_char(sysdate, 'D'))= 5 THEN
      TRUNC(SYSDATE+1) + 13 /24     
  WHEN  to_number(to_char(sysdate, 'D'))= 6 THEN
      TRUNC(SYSDATE+2) + 16 /24          
  ELSE
      TRUNC(SYSDATE+1) + 16 /24 
 END

case when to_number(to_char(sysdate, 'D')) between 1 and 4 then TRUNC(SYSDATE+1) + 16 /24 when to_number(to_char(sysdate, 'D'))= 5 then TRUNC(SYSDATE+1) + 13 /24 when to_number(to_char(sysdate, 'D'))= 6 then TRUNC(SYSDATE+2) + 16 /24 else TRUNC(SYSDATE+1) + 16 /24 end

if you try to submit a job with this statement, you will probably get the following error

ORA-12899: VALUE too LARGE FOR COLUMN "SYS"."JOB$"."INTERVAL#" (actual: 257, maximum: 200)
ORA-06512: at "SYS.DBMS_IJOB", line 290
ORA-06512: at "SYS.DBMS_JOB", line 159
ORA-06512: at line 4

ORA-12899: value too large for column "SYS"."JOB$"."INTERVAL#" (actual: 257, maximum: 200) ORA-06512: at "SYS.DBMS_IJOB", line 290 ORA-06512: at "SYS.DBMS_JOB", line 159 ORA-06512: at line 4

You can workaround this problem if you use a function to calculate the INTERVAL value.

CREATE OR REPLACE FUNCTION get_next_run_date RETURN DATE IS
 
res DATE;
BEGIN
SELECT
 
CASE
  WHEN  to_number(to_char(sysdate, 'D')) BETWEEN 1 AND 4 THEN
      TRUNC(SYSDATE+1) + 16 /24
  WHEN  to_number(to_char(sysdate, 'D'))= 5 THEN
      TRUNC(SYSDATE+1) + 13 /24
  WHEN  to_number(to_char(sysdate, 'D'))= 6 THEN
      TRUNC(SYSDATE+2) + 16 /24
  ELSE
      TRUNC(SYSDATE+1) + 16 /24
 END
 INTO res
 FROM dual
  ;
RETURN res;
 
END;

create or replace function get_next_run_date return date is res date; begin select case when to_number(to_char(sysdate, 'D')) between 1 and 4 then TRUNC(SYSDATE+1) + 16 /24 when to_number(to_char(sysdate, 'D'))= 5 then TRUNC(SYSDATE+1) + 13 /24 when to_number(to_char(sysdate, 'D'))= 6 then TRUNC(SYSDATE+2) + 16 /24 else TRUNC(SYSDATE+1) + 16 /24 end into res from dual ; return res; end;

once the function is created, all you need to do is use it in your dbms_job submit under the INTERVAL parameter

DECLARE
  my_job NUMBER;
BEGIN
  dbms_job.submit(job => my_job, 
    what => 'my_procedure ;',
 
    INTERVAL => 'get_next_run_date'
    );
END

declare my_job number; begin dbms_job.submit(job => my_job, what => 'my_procedure ;', interval => 'get_next_run_date' ); end