Suresh : A Blog on Oracle Applications

Monday, May 15, 2017

OiilNativeException is thrown, While Installing Oracle Developer Suite( Forms10g Reports 10g) Installation

Recently we were forced to re-install Oracle Developer suite 10g (10.1.2.0.2) and came across a peculiar error and the installation log out file had the following entries




01

02

03

04

05

06

07

08

09

10

11


OiilNativeException is thrown 

OiilNativeException is thrown 

OiilNativeException is thrown 

OiilNativeException is thrown 

OiilNativeException is thrown 





path: C:\Users\rthampi\AppData\Local\Temp\OraInstall2013-04-28_08-56-48AM\jre\1.4.2\bin;.;C:\Windows\system32;C:\Windows;D:\oracle\product\10.2.0\db_1\bin;D:\WLS\Middleware\Oracle_FRHome1\bin;C:\orant\bin;C:\orant\jdk\bin;D:\WebLogic\Middleware\Oracle_FRHome1\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\Microsoft\Web Platform Installer\;C:\Program Files (x86)\Microsoft ASP.NET\ASP.NET Web Pages\v1.0\;C:\Program Files Microsoft SQL Server\110\Tools\Binn\;C:\Program Files (x86)\Windows Live\Shared;C:\Windows\System32\Windows System Resource Manager\bin;C:\Program Files (x86)\PC Connectivity Solution\;C:\Program Files (x86)\QuickTime\QTSystem\;D:\WLS\Middleware\Oracle_FRHome1\opmn\bin;D:\WLS\Middleware\Oracle_FRHome1\opmn\lib;D:\WLS\Middleware\Oracle_FRHome1\perl\bin;D:\WebLogic\Middleware\Oracle_FRHome1\opmn\bin;D:\WebLogic\Middleware\Oracle_FRHome1\opmn\lib;D:\WebLogic\Middleware\Oracle_FRHome1\perl\bin





toload is C:\Users\rthampi\AppData\Local\Temp\OraInstall2013-04-28_08-56-48AM\WindowsGPortQueries.dll

and by the end of install log file we were able to see that, the installation was terminated while trying to setup LD_LIBRARY_PATH environment variable




1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25


oracleHomes = C:\orant,D:\DevSuiteHome_1,D:\Oracle\Middleware\oracle_common,D:\oracle\product\10.2.0\db_1,D:\WLS\Middleware\oracle_common,D:\Weblogic\Middleware\oracle_common,D:\WebLogic\Middleware\Oracle_FRHome1,D:\DevSuiteHome_2,



configurationPath = D:\DevSuiteHome_2\j2ee\DevSuite\config\default-web-site.xml



attributeName = port



UDPFlag = null

Query Returned: 8892

Setting variable 's_httpPort' to '8892'. Received the value from the variable calculation. 

*** Entering Component: oracle.developer.forms.builder installation

Calling Query generalQueries10.1.0.3.0  getOSName

Query Returned: NT_X86

Setting variable 'SO_EXT' to 'so'. Received the value from the variable calculation. 

Calling Query generalQueries10.1.0.3.0  getOSName

Query Returned: NT_X86

Setting variable 'PROD_LIBDIR' to 'lib'. Received the value from the variable calculation. 

Calling Query generalQueries10.1.0.3.0  getOSName

Query Returned: NT_X86

Setting variable 'OS_CHECK' to 'NT_X86'. Received the value from the variable calculation. 

Calling Query generalQueries10.1.0.3.0  getOSName

Query Returned: NT_X86

Setting variable 'OHOME_LIBDIR' to 'lib'. Received the value from the variable calculation. 

Calling Query generalQueries10.1.0.3.0  getenv



name = LD_LIBRARY_PATH

Obvious, we had an installation for Oracle Weblogic Server 10.3.6 and Oracle developer suite 11g which were done much later stages, and the Environment was setup with a static entry for LD_LIBRARY_PATH=D:\WebLogic\Middleware\Oracle_FRHome1\lib;D:\instantclient32

During the installation time, the installer was trying to setup the LD_LIBRARY_PATH variable for sourcing the required files/other components, and failing to setup the same was causing an unexpected error and thus the entire installation failing without producing any specific error within the log file, other than the .out file stating an exception towards “OiilNativeException “

Workaround

Copy the LD_LIBRARY_PATH value to a text file and delete the environment variable after stopping Weblogic services.

Restart the computer and try to install your product. This should solve the dreaded issues with installation.

Tested environment: Windows 8 64Bit Professional edition running Oracle database 10g 10.2.0.3, Oracle weblogic server 10.3.6, Oracle developer suite 11g

Monday, February 13, 2017

Translate Special Characters using REGEXP_REPLACE

Translate Special Characters using REGEXP_REPLACE

select * from ar.hz_parties
where upper(trim(REGEXP_REPLACE(party_name ,'[^[:alnum:]'' '']', NULL))) = upper(trim(REGEXP_REPLACE(:party_name,'[^[:alnum:]'' '']', NULL)));

Monday, January 23, 2017

Spool query Oracle Sql

set echo off
set feedback off
set linesize 1000
set pagesize 0
set sqlprompt ''
set trimspool on
SET TERMOUT OFF
spool SFDC_ASSETS_ENRICH_DATA_PROD.csv
select 'ID' || ',',
'NAME'|| ',',
'ACCOUNT__C'|| ',',
'PARENT_ASSET__C'|| ',',
'PARENT_ASSET__NAME'|| ',',
'SERIAL_NUMBER__C'|| ',',
'PRODUCT__C'|| ',',
'PRODUCT__PRODUCTCODE'|| ',',
'SHIP_DATE__C'|| ',',
'SHIP_TO_CUSTOMER_DATE__C'|| ',',
'REVISION__C'|| ',',
'UPGRADEKEY__C'|| ',',
'OPERATINGSTATUS__C'|| ',',
'ASSETSYSTEM__C'|| ',',
'NOT_FOR_RESALE_ASSET__C'|| ',',
'NFR_START_DATE__C'|| ',',
'NFR_EXPIRATION_DATE__C'|| ',',
'NFR_SOLD_TO_END_CUST__C'|| ',',
'DATE_NFR_ASSET_SOLD_END_CUST_C'|| ',',
'STATUS__C'|| ',',
'ISACTIVE__C'|| ',',
'WARRANTY_ENTIT_ASSET_ID__C'|| ',',
'WARRANTY_ENTIT_ACCOUNTID'|| ',',
'WARRANTY_ENTIT_NAME'|| ',',
'PRODUCT_NAME'|| ',',
'CUSTWARRANTYSTARTDATE__C'|| ',',
'WAR_ENTIT_HW_DURATION_YRS_C'|| ',',
'WAR_ENTIT_WARRANTY_DURATION__C'|| ',',
'WAR_ENTIT_LIMITED_LFTIME_WAR_C'|| ',',
'PRODUCT_WARRANTY_NAME'|| ',',
'LIMITED_OR_LIFETIME_WAR__C'|| ',',
'PROD_WAR_DURATION__C'|| ',',
'PROD_WAR_HW_DURATION_YEARS__C'|| ',',
'ETS_ASSET_PARENT_ID'|| ',',
'UPGRADE_KEY'|| ',',
'MFG_PN'|| ',',
'HW_ASSET_CURRENT_LEVEL'|| ',',
'LICENSE_GENERATED_DATE'|| ',',
'SITE_ID_SFDC'
from dual;
select '"' ||ID || '",' ||
'"' ||NAME || '",' ||
'"' ||ACCOUNT__C || '",' ||
'"' ||PARENT_ASSET__C || '",' ||
'"' ||PARENT_ASSET__NAME || '",' ||
'"' ||SERIAL_NUMBER__C || '",' ||
'"' ||PRODUCT__C || '",' ||
'"' ||PRODUCT__PRODUCTCODE || '",' ||
'"' ||SHIP_DATE__C || '",' ||
'"' ||SHIP_TO_CUSTOMER_DATE__C || '",' ||
'"' ||REVISION__C || '",' ||
'"' ||UPGRADEKEY__C || '",' ||
'"' ||OPERATINGSTATUS__C || '",' ||
'"' ||ASSETSYSTEM__C || '",' ||
'"' ||NOT_FOR_RESALE_ASSET__C || '",' ||
'"' ||NFR_START_DATE__C || '",' ||
'"' ||NFR_EXPIRATION_DATE__C || '",' ||
'"' ||NFR_SOLD_TO_END_CUST__C || '",' ||
'"' ||DATE_NFR_ASSET_SOLD_END_CUST_C || '",' ||
'"' ||STATUS__C || '",' ||
'"' ||ISACTIVE__C || '",' ||
'"' ||WARRANTY_ENTIT_ASSET_ID__C || '",' ||
'"' ||WARRANTY_ENTIT_ACCOUNTID || '",' ||
'"' ||WARRANTY_ENTIT_NAME || '",' ||
'"' ||PRODUCT_NAME || '",' ||
'"' ||CUSTWARRANTYSTARTDATE__C || '",' ||
'"' ||WAR_ENTIT_HW_DURATION_YRS_C || '",' ||
'"' ||WAR_ENTIT_WARRANTY_DURATION__C || '",' ||
'"' ||WAR_ENTIT_LIMITED_LFTIME_WAR_C || '",' ||
'"' ||PRODUCT_WARRANTY_NAME || '",' ||
'"' ||LIMITED_OR_LIFETIME_WAR__C || '",' ||
'"' ||PROD_WAR_DURATION__C || '",' ||
'"' ||PROD_WAR_HW_DURATION_YEARS__C || '",' ||
'"' ||ETS_ASSET_PARENT_ID || '",' ||
'"' ||UPGRADE_KEY || '",' ||
'"' ||MFG_PN || '",' ||
'"' ||HW_ASSET_CURRENT_LEVEL || '",' ||
'"' ||LICENSE_GENERATED_DATE || '",' ||
'"' ||Site_Id_SFDC|| '"'
from tmp_sfdc_assetenrich_prod_mv;
spool off;
exit;

Sunday, October 9, 2016

Substr Instr to get first middle and last name

select :party_name,

TRIM(SUBSTR(:party_name, 1, INSTR(:party_name, ' ', -1,1))) fname,

SUBSTR(:party_name, INSTR(:party_name, ' ', -1)+1) lname

from dual;

/* Formatted on 7/10/2023 3:19:47 PM (QP5 v5.294) */

SET SERVEROUTPUT ON

DECLARE

--i number

l_count_line_id NUMBER;

l_party_name VARCHAR2 (100);

l_name VARCHAR2 (100);

BEGIN

l_count_line_id := 4;

l_party_name := '012,345,678,910';

DBMS_OUTPUT.put_line ('l_party_name : ' || l_party_name);

FOR i IN 1 .. l_count_line_id

LOOP

l_name :=

REGEXP_SUBSTR (l_party_name

, '[^,]+'

, 1

, i);

DBMS_OUTPUT.put_line ('l_name : ' || l_name);

END LOOP;

END;