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;

Thursday, August 25, 2016

update and insert using for all bulk collect

DECLARE
CURSOR rec_cur IS
SELECT row_id, x_sfdc_account_id
FROM s_org_ext
where x_sfdc_account_id is not null
-- and row_id in ('1+12N+1177',
--'1+12N+1182',
--'1+12N+1185')
;

TYPE vc1_tab_t IS TABLE OF VARCHAR2(256);
TYPE vc2_tab_t IS TABLE OF VARCHAR2(256);

pk_tab vc1_tab_t;
-- fk_tab vc1_tab_t;
fill_tab VC2_TAB_T;
BEGIN
OPEN rec_cur;
LOOP
FETCH rec_cur BULK COLLECT INTO pk_tab, fill_tab LIMIT 1000;
EXIT WHEN pk_tab.COUNT() = 0;

FORALL i IN pk_tab.FIRST .. pk_tab.LAST
UPDATE md_parties
SET attribute_osr = fill_tab(i)
WHERE orig_system_reference = pk_tab(i)
and source_system =5;
commit;
END LOOP;
CLOSE rec_cur;
END;

DECLARE
CURSOR rec_cur IS
SELECT *
FROM md_party_asset_link_Bkp;

TYPE vc1_tab_t IS TABLE OF rec_cur%rowtype;

pk_tab vc1_tab_t;
-- fk_tab vc1_tab_t;
BEGIN
OPEN rec_cur;
LOOP
FETCH rec_cur BULK COLLECT INTO pk_tab LIMIT 100000;
EXIT WHEN pk_tab.COUNT() = 0;

FORALL i IN pk_tab.FIRST .. pk_tab.LAST
INSERT INTO md_party_asset_link
VALUES pk_tab (i);
commit;
END LOOP;
CLOSE rec_cur;
END;

/* Formatted on 9/13/2016 3:18:21 PM (QP5 v5.185.11230.41888) */
DECLARE
TYPE prod_tab IS TABLE OF md_party_asset_link_Bkp%ROWTYPE;

products_tab prod_tab := prod_tab ();
start_time NUMBER;
end_time NUMBER;
BEGIN
-- Populate a collection - 100000 rows
SELECT *
BULK COLLECT INTO products_tab
FROM md_party_asset_link_Bkp;

Start_time := DBMS_UTILITY.get_time;

FORALL i IN products_tab.FIRST .. products_tab.LAST
INSERT INTO md_party_asset_link
VALUES products_tab (i);

end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE ('Bulk Insert: ' || TO_CHAR (end_time - start_time));
COMMIT;
END;