Blog : Oracle « Duh! Microsoft did it again

Oracle services disappear from Windows Services panel after upgraded to Windows 8.1

Posted by windows7bugs on October 31, 2013

Just a week back we upgraded two of our development laptops to Windows 8.1 64bit PRO, which were having Oracle 10g 10.2.0.3.0 locally installed.

Once after the upgrade, we realized that the Windows services panel totally missing the entries for Oracle services (yes WTF from our end as well)

There is a question thrown at http://stackoverflow.com/questions/19533791/not-able-to-see-oracle-services-in-windows-services-after-upgrading-to-win-8-1

which is yet to receive any answers. We were in the same situation and started our experiments to find a solution.

Without boring you with details, here is the complete solution

Re-configure your listener, do not delete, just re-configure using the GUI. This will re-create the Windows service for listener.

Use oradim to create a new service (Please note, this scenario is strictly for creating services while they are missing, if you want to recreate services which are already available, YOU must drop them)

set ORACLE_SID=

D:\oracle\product\10.2.0\db_1>oradim -new -sid %ORACLE_SID% -intpwd PASSWORD -st
artmode M -pfile D:\oracle\product\10.2.0\admin\sct\pfile\init.ora

There is a high possibility that you may find the init.ora file like following

D:\oracle\product\10.2.0\admin\sct\pfile>dir
Volume in drive D is New Volume
Volume Serial Number is 866B-F095

Directory of D:\oracle\product\10.2.0\admin\sct\pfile

10/31/2013  12:31 PM 

  .
10/31/2013  12:31 PM    ..
02/18/2013  09:24 AM  2,613 init.ora
02/18/2013  09:24 AM  2,613 init.ora.11820139272
10/31/2013  12:31 PM  745 sqlnet.log
3 File(s)  5,971 bytes
2 Dir(s)  97,706,209,280 bytes free

D:\oracle\product\10.2.0\admin\sct\pfile>

just copy the init.ora.NNNNNNNNNNN file to init.ora

Now start registry edit. This is required for the situation when you will notice the database service you have just created starts, however will not mount the database.

Navigate to

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\KEY_OraDb10g_home1

image

(We are using Windows 8.1 64bit)

By the right side details panel, find the key “ORA__AUTOSTART” which will have a value “FALSE”. Change this value to “TRUE” and get back to Windows services panel and start the database service.

That’s all folks

if you are not cursed, the Oracle database service must start, mount the database without any troubles.

referenced threads: https://forums.oracle.com/thread/541840

http://www.dba-oracle.com/tips_oradim_utility.htm

Oracle E-Business Suite R12 Internet explorer 10 â€œunidentified”

Posted by windows7bugs on September 29, 2013

Just 24 hours back, I made another attempt to setup a vision instance at home using VM templates from Oracle and, guys, it was a total success *grin* (Will post the installation steps later, stay tuned, it is loads of fun!)

However I was stuck with an issue, the html/jsp pages will not render properly in Internet Explorer 10 (I am using Windows 8 Pro 64bit) and started with my favorite search toy “google” once again

image

image

I came across a blog post from Oracle discussing about the certification path here

and within the thread there was another reference towards a later blog entry, which explained the certification details(here). Unless you are in a hurry, you shouldn’t miss the minimized quote says

Compatibility Mode for IE 10 + EBS 12

To prevent issues with certain OA Framework pages, you should run IE 10 in “Compatibility Mode.” See the “Known Issues” section in Note 389422.1 for more details.

 

image

How to? have a look at the below images

image

That’s all Smile

image

hope the post was useful for minimum few out there

regards,

admin

Posted in Oracle Applications(R12), Oracle related, Windows 8 | Tagged: "undefined", 12.1.3, 12.1.3 Templates, compatibility issues, e-business suite, Internet Explorer 10, Oracle, R 12.0.4, r12 | 1 Comment »

Oracle Database 11g Release 2 (11.2) Installation On Oracle Linux 6

Posted by windows7bugs on September 22, 2013

We always refer www.oracle-base.com for exclusive tips on installing various Oracle products and we would suggest, it is one of the best repositories you can also depend upon for.

Recently we were having a requirement to migrate our legacy 10.1.2.x Oracle database to 11gR2 and we followed this thread by www.oracle-base.com to.

Our scenario was involving a VM instead of physical server which was hosted on Windows 8 64 Professional edition using Oracle’s VirtualBox 4.2.18

Following the straight forward instructions we installed Oracle Linux 6.2 X86_64 (64bit) and added Oracle’s own public repository for yum repository (instructions here)

Next we ran an update cycle, and our distro was upgraded to 6.4 from 6.2 and the first issue we realized was X terminal. To resolve, we did the following few activities

Uninstalled the VirtualBox addons

Re-configured the X terminal

Re-installed VirtualBox addons

Then went ahead with instructions available with the oracle-base post for the 11gR2 installation.

Even though all the instructions were tailored for a successful installation of 11GR2 we were stuck as soon as we tried to start the installation process by calling

./runInstaller

A number of Java exceptions where thrown and while digging around, came to a conclusion that, it was due to the exporting DISPLAY and later allowing access to the same.

We found a thread on stackoverflow, with a solution and the solution was

as root

set DISPLAY

$DISPLAY=:0.0

$export DISPLAY

$xhost +

then as user oracle

$su – oracle

$DISPLAY=:0.0

$export DISPLAY

$cd /stage/database

$./runInstaller

A number of .so files were upgraded to recent versions during the 6.2 to 6.4 upgrade process, hence the installer will prompt missing library files which you can safely ignore (We would recommend you to resolve such warning if you are doing a production installation, a test scenario could overlook these wanings)

It is painful, however, please do confirm all the recommended packages or new versions are already available with your linux installation prior going ahead with the installation.

We installed unixODBC package(s) using the add remove software, a newer version than the one suggested by Oracle and proceeded with the database installation.

Even though we were able to complete the installation successfully, left us with huge concerns like the time and efforts required, the level of knowledge about linux and complexities resolving the dependency which is almost nothing in the case of Windows environment.

Oracle linux 6.x Re-configure x server

as root

X –configure xorg.conf.new

X will start,  however with a blank screen (or this was our experience)

Reset the terminal, logon as root from shell prompt

cd /etc/X11

rename or move the xorg.conf, we chose to move

mv xorg.conf xorg.conf.backup

startx

If you are not truly unlucky, your X terminal should startup

Oracle Database, Send SMS through SMS Gateway

Posted by windows7bugs on July 3, 2013

Tested on 10g 10.2.0.3 database with default installation(no additional packages were installed to achieve the results)

There are many APIs (developed by 3rd parties) which will allow you to send SMS from a Oracle database on demand. Many times such APIs would become costly, depending upon your requirements.

Here, we are providing a zero cost solution, incase if your SMS gateway provider allows you to send SMS through a web service portal.

You may wrap the entire procedure and call it against a table trigger or through a button click available with user form(s)

<div><div><table><tbody><tr><td><div><br><div><code>SET</code> <code>serveroutput </code><code>ON</code><p><code>SET</code> <code>Define </code><code>OFF</code></p><p><code>DECLARE</code></p><p><code>HTTP_REQ  UTL_HTTP.REQ;</code></p><p><code>HTTP_RESP  UTL_HTTP.RESP;</code></p><p><code>URL_TEXT  VARCHAR2(32767);</code></p><p><code>URL VARCHAR2(2000);</code></p><p><code>SMS_MSG VARCHAR2(160) := </code><code>'Congratulations! Your database has been configured propoerly for sending SMS through a 3rd party SMS Gateway'</code><code>;</code></p><p><code>BEGIN</code></p><p><code>DBMS_OUTPUT.ENABLE(1000000);</code></p><p><code>--Based on your service provider, the following link format may differ from</code></p><p><code>--What we have specified below!  </code></p><p><code>URL := </code><code>'http://yourwebsmsdomain.com/alerts/api/web2sms.php?username=demo&password=demo2&to=95xxxxxxx&sender=ODBSMS&message='</code><code>||</code></p><p><code>UTL_URL.</code><code>Escape</code><code>(SMS_MSG,</code><code>TRUE</code><code>); </code></p><p><code>--UTL_URL.Escape manages escape characters like SPACE between words in a message.</code></p><p><code>HTTP_REQ  := UTL_HTTP.BEGIN_REQUEST(URL);</code></p><p><code>UTL_HTTP.SET_HEADER(HTTP_REQ, </code><code>'User-Agent'</code><code>, </code><code>'Mozilla/4.0'</code><code>);</code></p><p><code>HTTP_RESP := UTL_HTTP.GET_RESPONSE(HTTP_REQ);</code></p><p><code>-- Process Request</code></p><p><code>LOOP</code></p><p><code>BEGIN</code></p><p><code>URL_TEXT := </code><code>null</code><code>;</code></p><p><code>UTL_HTTP.READ_LINE(HTTP_RESP, URL_TEXT, </code><code>TRUE</code><code>);</code></p><p><code>DBMS_OUTPUT.PUT_LINE(URL_TEXT);</code></p><p><code>EXCEPTION</code></p><p><code>WHEN</code> <code>OTHERS </code><code>THEN</code> <code>EXIT;</code></p><p><code>END</code><code>;</code></p><p><code>END</code> <code>LOOP;</code></p><p><code>UTL_HTTP.END_RESPONSE(HTTP_RESP);</code></p><p><code>END</code><code>;</code></p></div></div></td><td><div><code>SET</code> <code>serveroutput </code><code>ON</code><p><code>SET</code> <code>Define </code><code>OFF</code></p><p><code>DECLARE</code></p><p><code>HTTP_REQ  UTL_HTTP.REQ;</code></p><p><code>HTTP_RESP  UTL_HTTP.RESP;</code></p><p><code>URL_TEXT  VARCHAR2(32767);</code></p><p><code>URL VARCHAR2(2000);</code></p><p><code>SMS_MSG VARCHAR2(160) := </code><code>'Congratulations! Your database has been configured propoerly for sending SMS through a 3rd party SMS Gateway'</code><code>;</code></p><p><code>BEGIN</code></p><p><code>DBMS_OUTPUT.ENABLE(1000000);</code></p><p><code>--Based on your service provider, the following link format may differ from</code></p><p><code>--What we have specified below!  </code></p><p><code>URL := </code><code>'http://yourwebsmsdomain.com/alerts/api/web2sms.php?username=demo&password=demo2&to=95xxxxxxx&sender=ODBSMS&message='</code><code>||</code></p><p><code>UTL_URL.</code><code>Escape</code><code>(SMS_MSG,</code><code>TRUE</code><code>); </code></p><p><code>--UTL_URL.Escape manages escape characters like SPACE between words in a message.</code></p><p><code>HTTP_REQ  := UTL_HTTP.BEGIN_REQUEST(URL);</code></p><p><code>UTL_HTTP.SET_HEADER(HTTP_REQ, </code><code>'User-Agent'</code><code>, </code><code>'Mozilla/4.0'</code><code>);</code></p><p><code>HTTP_RESP := UTL_HTTP.GET_RESPONSE(HTTP_REQ);</code></p><p><code>-- Process Request</code></p><p><code>LOOP</code></p><p><code>BEGIN</code></p><p><code>URL_TEXT := </code><code>null</code><code>;</code></p><p><code>UTL_HTTP.READ_LINE(HTTP_RESP, URL_TEXT, </code><code>TRUE</code><code>);</code></p><p><code>DBMS_OUTPUT.PUT_LINE(URL_TEXT);</code></p><p><code>EXCEPTION</code></p><p><code>WHEN</code> <code>OTHERS </code><code>THEN</code> <code>EXIT;</code></p><p><code>END</code><code>;</code></p><p><code>END</code> <code>LOOP;</code></p><p><code>UTL_HTTP.END_RESPONSE(HTTP_RESP);</code></p><p><code>END</code><code>;</code></p></div></td></tr></tbody></table></div></div>

Oracle Jdeveloper 10g shows splash screen and terminates

Posted by windows7bugs on April 28, 2013

Okay, do not want to copy and paste the content from another forum :)

If you are facing the above said issue, kindly refer to this thread and it does work!

image


OiilNativeException is thrown, While Installing Older Oracle products (Windows)


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

<div><div><table><tbody><tr><td><div>01<p>02</p><p>03</p><p>04</p><p>05</p><p>06</p><p>07</p><p>08</p><p>09</p><p>10</p><p>11</p></div></td><td><div><code>OiilNativeException is thrown </code><p><code>OiilNativeException is thrown </code></p><p><code>OiilNativeException is thrown </code></p><p><code>OiilNativeException is thrown </code></p><p><code>OiilNativeException is thrown </code></p><p><code>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</code></p><p><code>toload is C:\Users\rthampi\AppData\Local\Temp\OraInstall2013-04-28_08-56-48AM\WindowsGPortQueries.dll</code></p></div></td></tr></tbody></table></div></div>

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

<div><div><table><tbody><tr><td><div>1<div><code>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,</code><p><code>configurationPath = D:\DevSuiteHome_2\j2ee\DevSuite\config\default-web-site.xml</code></p><p><code>attributeName = port</code></p><p><code>UDPFlag = null</code></p><p><code>Query Returned: 8892</code></p><p><code>Setting variable 's_httpPort' to '8892'. Received the value from the variable calculation. </code></p><p><code>*** Entering Component: oracle.developer.forms.builder installation</code></p><p><code>Calling Query generalQueries10.1.0.3.0  getOSName</code></p><p><code>Query Returned: NT_X86</code></p><p><code>Setting variable 'SO_EXT' to 'so'. Received the value from the variable calculation. </code></p><p><code>Calling Query generalQueries10.1.0.3.0  getOSName</code></p><p><code>Query Returned: NT_X86</code></p><p><code>Setting variable 'PROD_LIBDIR' to 'lib'. Received the value from the variable calculation. </code></p><p><code>Calling Query generalQueries10.1.0.3.0  getOSName</code></p><p><code>Query Returned: NT_X86</code></p><p><code>Setting variable 'OS_CHECK' to 'NT_X86'. Received the value from the variable calculation. </code></p><p><code>Calling Query generalQueries10.1.0.3.0  getOSName</code></p><p><code>Query Returned: NT_X86</code></p><p><code>Setting variable 'OHOME_LIBDIR' to 'lib'. Received the value from the variable calculation. </code></p><p><code>Calling Query generalQueries10.1.0.3.0  getenv</code></p><p><code>name = LD_LIBRARY_PATH</code></p></div></div></td><td><div><code>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,</code><p><code>configurationPath = D:\DevSuiteHome_2\j2ee\DevSuite\config\default-web-site.xml</code></p><p><code>attributeName = port</code></p><p><code>UDPFlag = null</code></p><p><code>Query Returned: 8892</code></p><p><code>Setting variable 's_httpPort' to '8892'. Received the value from the variable calculation. </code></p><p><code>*** Entering Component: oracle.developer.forms.builder installation</code></p><p><code>Calling Query generalQueries10.1.0.3.0  getOSName</code></p><p><code>Query Returned: NT_X86</code></p><p><code>Setting variable 'SO_EXT' to 'so'. Received the value from the variable calculation. </code></p><p><code>Calling Query generalQueries10.1.0.3.0  getOSName</code></p><p><code>Query Returned: NT_X86</code></p><p><code>Setting variable 'PROD_LIBDIR' to 'lib'. Received the value from the variable calculation. </code></p><p><code>Calling Query generalQueries10.1.0.3.0  getOSName</code></p><p><code>Query Returned: NT_X86</code></p><p><code>Setting variable 'OS_CHECK' to 'NT_X86'. Received the value from the variable calculation. </code></p><p><code>Calling Query generalQueries10.1.0.3.0  getOSName</code></p><p><code>Query Returned: NT_X86</code></p><p><code>Setting variable 'OHOME_LIBDIR' to 'lib'. Received the value from the variable calculation. </code></p><p><code>Calling Query generalQueries10.1.0.3.0  getenv</code></p><p><code>name = LD_LIBRARY_PATH</code></p></div></td></tr></tbody></table></div></div> 

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

Regards,

Admin

Posted in Oracle related, Windows 8 | Tagged: Developer suite 10g, OiilNativeException is thrown, Oracle | Leave a Comment »

Oracle Applications style “set_who” for custom applications

Posted by windows7bugs on April 16, 2013

Why not? From our experiences, Oracle applications has one of the best transaction auditing approach, by logging who created and updated transactions while data is processed through a form based application.

Adapting the same methodology, recently we have created almost similar functionality for our upcoming custom applications. If you intend to use this solution with your own forms applications, make sure that your tables have the following four columns as mandatory.

<div><div><table><tbody><tr><td><div><br><p><code>PROCEDURE</code> <code>set_who </code><code>IS</code></p><p><code>blk_name VARCHAR2(40);</code></p><p><code>curr_item VARCHAR2(40);</code></p><p><code>curr_mode VARCHAR2(40);</code></p><p><code>trx_date </code><code>date</code> <code>:= sysdate;</code></p><p><code>curr_user NUMBER;</code></p><p><code>BEGIN</code></p></div></td><td><div><code>ALTER</code> <code>TABLE</code> <code>BAC_MENU</code><p><code>ADD</code><code>(</code></p><p><code>CREATED_BY  NUMBER  ,</code></p><p><code>CREATION_DATE  </code><code>DATE</code>  <code>,</code></p><p><code>LAST_UPDATE_DATE  </code><code>DATE</code>  <code>,</code></p><p><code>LAST_UPDATED_BY  NUMBER  </code></p><p><code>);</code></p></div></td></tr></tbody></table></div></div> 

set_who procedure

<div><div><table><tbody><tr><td><p><code>blk_name := name_in(</code><code>':SYSTEM.CURRENT_BLOCK'</code><code>);</code></p><p><code>--Judge the current transaction mode</code></p><p><code>curr_mode := name_in(</code><code>':SYSTEM.RECORD_STATUS'</code><code>);</code></p><p><code>--if you are passing login details </code></p><p><code>--using global variables, adjust the following</code></p><p><code>--lines accordingly</code></p><p><code>curr_user := name_in(</code><code>':PARAMETER.P_USER_ID'</code><code>);</code></p><p><code>if curr_mode=</code><code>'CHANGED'</code> <code>then</code></p><p><code>COPY(curr_user,blk_name||</code><code>'.LAST_UPDATED_BY'</code><code>);</code></p><p><code>COPY(to_char(trx_date,</code><code>'dd/mm/yyyy HH24:MI:SS'</code><code>),blk_name||</code><code>'.LAST_UPDATE_DATE'</code><code>);</code></p><p><code>elsif curr_mode = </code><code>'INSERT'</code> <code>then</code></p><p><code>COPY(curr_user,blk_name||</code><code>'.CREATED_BY'</code><code>);</code></p><p><code>COPY(to_char(trx_date,</code><code>'dd/mm/yyyy HH24:MI:SS'</code><code>),blk_name||</code><code>'.CREATION_DATE'</code><code>);</code></p><p><code>end</code> <code>if;</code></p><p><code>--Mandatory columns with tables</code></p><p><code>--against which you will log</code></p><p><code>--the insert, update user details & time</code></p><p><code>/*</code></p><p><code>CREATED_BY</code></p><p><code>CREATION_DATE</code></p><p><code>LAST_UPDATE_DATE</code></p><p><code>LAST_UPDATED_BY</code></p><p><code>*/  </code></p><p><code>END</code><code>;</code></p></td><td><div><code>PROCEDURE</code> <code>set_who </code><code>IS</code><p><code>blk_name VARCHAR2(40);</code></p><p><code>curr_item VARCHAR2(40);</code></p><p><code>curr_mode VARCHAR2(40);</code></p><p><code>trx_date </code><code>date</code> <code>:= sysdate;</code></p><p><code>curr_user NUMBER;</code></p><p><code>BEGIN</code></p><p><code>--Get the current block name</code></p><p><code>blk_name := name_in(</code><code>':SYSTEM.CURRENT_BLOCK'</code><code>);</code></p><p><code>--Judge the current transaction mode</code></p><p><code>curr_mode := name_in(</code><code>':SYSTEM.RECORD_STATUS'</code><code>);</code></p><p><code>--if you are passing login details </code></p><p><code>--using global variables, adjust the following</code></p><p><code>--lines accordingly</code></p><p><code>curr_user := name_in(</code><code>':PARAMETER.P_USER_ID'</code><code>);</code></p><p><code>if curr_mode=</code><code>'CHANGED'</code> <code>then</code></p><p><code>COPY(curr_user,blk_name||</code><code>'.LAST_UPDATED_BY'</code><code>);</code></p><p><code>COPY(to_char(trx_date,</code><code>'dd/mm/yyyy HH24:MI:SS'</code><code>),blk_name||</code><code>'.LAST_UPDATE_DATE'</code><code>);</code></p><p><code>elsif curr_mode = </code><code>'INSERT'</code> <code>then</code></p><p><code>COPY(curr_user,blk_name||</code><code>'.CREATED_BY'</code><code>);</code></p><p><code>COPY(to_char(trx_date,</code><code>'dd/mm/yyyy HH24:MI:SS'</code><code>),blk_name||</code><code>'.CREATION_DATE'</code><code>);</code></p><p><code>end</code> <code>if;</code></p><p><code>--Mandatory columns with tables</code></p><p><code>--against which you will log</code></p><p><code>--the insert, update user details & time</code></p><p><code>/*</code></p><p><code>CREATED_BY</code></p><p><code>CREATION_DATE</code></p><p><code>LAST_UPDATE_DATE</code></p><p><code>LAST_UPDATED_BY</code></p><p><code>*/  </code></p><p><code>END</code><code>;</code></p></div></td></tr></tbody></table></div></div> 

Now attach this procedure with individual forms or make it a part of a custom PL/SQL library for global calls

You can call the procedure from PRE-INSERT and PRE-UPDATE triggers at block level to achieve the scope.

Enjoy!

Regards,

Admin

Posted in Oracle related | Tagged: application, custom.pll, forms 11g, forms 6i, Oracle, procedure, r12, set_who | Leave a Comment »

Create Oracle Application Navigator Style Menu with Forms 6i

Posted by windows7bugs on April 14, 2013

Okay, we are moving from Client/Server architecture to Weblogic+Developer 11g. One of the major requirements was to design and deploy a menu, which resembles Oracle application navigator.

We managed to design a menu, using LIST item (tlist) manipulation and the complete solution you can download from following link

https://skydrive.live.com/redir?resid=68080371B15625F8!128&authkey=!AEeoR-SI6z2WpjQ

We hope you will enjoy the solution.


Install & Configure Oracle Weblogic Server 10.3.6 & Forms & Reports 11g on Windows8/Windows 2008 R2


We are running a blog explaining many workaround solutions for installing and configuring discontinued, uncertified Oracle software on Windows 7 & Windows 8 from last year onwards.

Let us accept it, Developer 6i applications face multiple issues while used on both Windows 7&8 like:

  1. Intermittent crashes
  2. Rendering menu modules
  3. Stability
  4. Running products like Reports and Graphs

Usually a legacy application compiled using patch sets below 18 are less prone to above mentioned issues, however an application completely developed using patch 18 regularly get into issues on Windows 7 & 8 where hacks are used.

Recently we were forced to look towards a better environment for a new application, which by certain reasons cannot be accommodated and hosted within Oracle Application entity, and we started dwelling the possibilities of setting up a Weblogic server (old application server) for our forms and reports modules.

in short, we did it! and we are sharing the experience with you.

you need the following to carryout the installation and configuration

Windows 8/Windows 2008 R2 box (VM for testing)

Oracle Weblogic Server 10.3.6 (32Bit)

http://www.oracle.com/technetwork/middleware/weblogic/downloads/wls-main-097127.html

image

Oracle Forms and Reports 11g Release 2

http://www.oracle.com/technetwork/developer-tools/forms/downloads/index.html

image

(Hurry, you may not find the above software bundles with OTN after few weeks or months time)

Part 1 – Weblogic Server 10.3.6 Installation

Installation of Weblogic is pretty straight forward on Windows 8. Only once the compatibility issue is raised, which you can safely ignore by the click of a button.

The Weblogic 10.3.6 (wls1036_win32.exe) installation comes as a single .exe file, which you can start like any other installer.

Please have a look at the images

1

Choose a directory name without spaces, this is going to be your middleware home.

2

Uncheck the email notification, you don’t need it as far you don’t have a support from oracle

3

Select “Custom” installation type, as you can select which JDK to be selected and other components at later stages. We installed both JDK for our installation.

4

Click next to continue

5

Leave the selection as it is

6

Select Yes for “Node Manager Service”, it will make few things simpler at later stages, especially while you are using Admin Server for managing both forms and reports servers.

7

Select “All Users”

8

Clicking “Next” will start installing the Weblogic server 10.3.6 for you. Relax and wait until the installation completes.

9

10

Once installation has been done, you will be given an option to setup the Weblogic server with an instance, which we will do as a part of Forms & Reports 11g installation and configuration.

Part 2 – Installation of Oracle Forms and Reports 11g Release 2

Once you download the  Oracle Forms and Reports 11g Release 2, the zip archive must be unzipped to a folder, which will create Disk1, Disk2, Disk3 folders. You should start the “Setup.exe” from Disk1 folder. Once again the installation is straight forward, without requiring to make any specific hack to the installation media.

12

You must skip the “Software Update” option, unless you have the paid support from Oracle for the product you are installing.

3

Most important: You should select “Install Software – Do not configure” at this level, as the configuration will be done once after the installation completed.

4

On Windows 8 you will be notified about two vital check failures, however allowing you to continue the installation at own risk by clicking the “Continue” button.

5

Next page will clearly notify you that you must have an application server already installed. Application server akka Weblogic server. Without Weblogic server you cannot use Oracle Forms and Reports 11g Release 2 even for development purpose.

6

7

You can safely ignore being updated about the software updates once again

8

Once you say yes to software update related prompt, the installation will start

9

10

11

Now you have finished installing Oracle Forms and Reports 11g Release 2 on Windows 8/Windows 2008 R2

Part 3 – Configuring the Weblogic server with forms and reports 11g

As we have installed “Node Manager Service” prior configuring the forms and reports, we need to stop the Node Manager Service from Windows services console

Invoke “services.msc” and stop the following service “Oracle Weblogic NodeManager(D_Weblogic_Middleware_wlserver_10.3)

If you don’t stop this service, you are going to come across issues as discussed here

To configure the Weblogic server for forms and reports, you need to invoke the “Configure Classic Instance” cmd file from Windows menu folder “Oracle Classic 11g – Home1”

1

2

It may take couple of minutes until you are provided the installation application GUI as shown below. Click next

3

Select the option “Configure for Deployment”

4

Unless you are entitled for Oracle support ignore the email part

56

Now, choose a meaningful name for your Oracle instance.

7

Important: You must note down the following details for future references. We opted to use the username suggested by Oracle during the configuration which is “weblogic” and provide a password consist of alphabets and minimum one digit (number)

Domain is the node which will be setup with Weblogic for Administration server as well for both Forms and Reports Servers.

8

By default, Developer tools are not selected, you MUST select this node, incase if you are going to use the same machine for development (which is our objective with this exercise). You may uncheck the “Clustered” option, unless used in a production environment.

9

Select “Auto Port Configuration”

10

Try to avoid using Proxy. Microsoft ISA server can make the whole experience a parody, if used.

11

Use Identity management, only if already implemented and business mandatory. Else leave it unchecked

12

Click on “Configure” button and sit back, until the process is over!

13141516

You may save the installation responses for a future installation by clicking the Save button. Click “Finish” button to exit the configuration wizard

17

You can access the Weblogic Admin Server by visiting http://localhost:7001/console & the Enterprise manager through http://localhost:7001/em

You may run into issues accessing the Enterprise Manager, incase if you have automated the weblogic admin server startup part using services & we have another post explaining how to tackle it here

weblogic console

If the node manager service is installed you can start and stop various components for the domain through this console.

Part 4 – Starting and Stopping domain components AdminServer, WLS_FORMS(Forms Server), WLS_REPORTS(Reports server)

Oracle provides batch files to start and stop all the above mentioned components. You may start just WLS_FORMS and WLS_REPORTS services, unless planning to manage them through the Admin Server console.

Starting Weblogi Admin Server

AdminServer Start

Starting Forms Server

Forms Server Start

Starting Reports Server

Report Server Start

Part 5 – Automating Weblogic Admin Server, WLS_FORMS(Forms Server), WLS_REPORTS(Reports server) startup with Windows Services

Let us agree, the cmd windows look ugly, dangerous and a user can close them down, thus bringing the entire servers down unintentionally. A windows service looks much favorable under such scenarios and wise people from Oracle do provide solutions to create windows services for

  1. Weblogic Admin Server
  2. WLS_FORMS (Forms Server)
  3. WLS_REPORTS (Reports Server)

Creating Windows services for Admin Server and Forms server are pretty simple and straight forward business, while CMD command line length limitations will make Reports Server service creation a little difficult, don’t worry we have solution for it also.

Part 6 – Creating Windows Service for Weblogic Admin Server

Pre-requisite(s)

Notepad++ (or any decent text editor other than Windows notepad)

Create a .cmd file named “Install_AdminServer.cmd” with the following lines

<div><div><table><tbody><tr><td><div><code>echo off</code><p><code>SETLOCAL</code></p><p><code>set DOMAIN_NAME=appsdomain</code></p><p><code>set USERDOMAIN_HOME=D:\WebLogic\Middleware\user_projects\domains\appsdomain</code></p><p><code>set SERVER_NAME=AdminServer</code></p><p><code>set WL_HOME=D:\WebLogic\Middleware\wlserver_10.3</code></p><p><code>set WLS_USER=weblogic</code></p><p><code>set WLS_PW=pass123</code></p><p><code>set PRODUCTION_MODE=true</code></p><p><code>set MEM_ARGS=-Xms512m -Xmx512m</code></p><p><code>call "%WL_HOME%\server\bin\installSvc.cmd"</code></p><p><code>ENDLOCAL</code></p></div><br></td><td><div><code>echo off</code><p><code>SETLOCAL</code></p><p><code>set DOMAIN_NAME=appsdomain</code></p><p><code>set USERDOMAIN_HOME=D:\WebLogic\Middleware\user_projects\domains\appsdomain</code></p><p><code>set SERVER_NAME=AdminServer</code></p><p><code>set WL_HOME=D:\WebLogic\Middleware\wlserver_10.3</code></p><p><code>set WLS_USER=weblogic</code></p><p><code>set WLS_PW=pass123</code></p><p><code>set PRODUCTION_MODE=true</code></p><p><code>set MEM_ARGS=-Xms512m -Xmx512m</code></p><p><code>call "%WL_HOME%\server\bin\installSvc.cmd"</code></p><p><code>ENDLOCAL</code></p></div></td></tr></tbody></table></div></div>

Please note, you must provide the absolute domain name and physical directory names based on your installation preferences!

Save the script to “USERDOMAIN_HOME” folder, ie, “D:\WebLogic\Middleware\user_projects\domains\appsdomain” in our case

From elevated command line, execute the batch file, and the Windows service for Admin Server must be created, unless there were mismatches with the information supplied

Part 7 - Creating Windows Service for WLS_FORMS(Forms Server)

Create a .cmd file “InstallWLS_FORMS.cmd” using Notepad++ with following lines

<div><div><table><tbody><tr><td><div><p><div><code>echo off</code><p><code>SETLOCAL</code></p><p><code>set DOMAIN_NAME=appsdomain</code></p><p><code>set USERDOMAIN_HOME=D:\WebLogic\Middleware\user_projects\domains\appsdomain</code></p><p><code>set SERVER_NAME=WLS_FORMS</code></p><p><code>set WL_HOME=D:\WebLogic\Middleware\wlserver_10.3</code></p><p><code>set WLS_USER=weblogic</code></p><p><code>set WLS_PW=pass123</code></p><p><code>set PRODUCTION_MODE=true</code></p><p><code>set ADMIN_URL=http://localhost:7001</code></p><p><code>cd %USERDOMAIN_HOME%</code></p><p><code>call %USERDOMAIN_HOME%\bin\setDomainEnv.cmd</code></p><p><code>rem *** call "D:\Oracle\Middleware\wlserver_10.3\server\bin\installSvc.cmd"</code></p><p><code>call "%WL_HOME%\server\bin\installSvc.cmd"</code></p><p><code>ENDLOCAL</code></p></div><br></p></div><br></td><td><div><code>echo off</code><p><code>SETLOCAL</code></p><p><code>set DOMAIN_NAME=appsdomain</code></p><p><code>set USERDOMAIN_HOME=D:\WebLogic\Middleware\user_projects\domains\appsdomain</code></p><p><code>set SERVER_NAME=WLS_FORMS</code></p><p><code>set WL_HOME=D:\WebLogic\Middleware\wlserver_10.3</code></p><p><code>set WLS_USER=weblogic</code></p><p><code>set WLS_PW=pass123</code></p><p><code>set PRODUCTION_MODE=true</code></p><p><code>set ADMIN_URL=http://localhost:7001</code></p><p><code>cd %USERDOMAIN_HOME%</code></p><p><code>call %USERDOMAIN_HOME%\bin\setDomainEnv.cmd</code></p><p><code>rem *** call "D:\Oracle\Middleware\wlserver_10.3\server\bin\installSvc.cmd"</code></p><p><code>call "%WL_HOME%\server\bin\installSvc.cmd"</code></p><p><code>ENDLOCAL</code></p></div></td></tr></tbody></table></div></div> 

Save the script to “USERDOMAIN_HOME” folder, ie, “D:\WebLogic\Middleware\user_projects\domains\appsdomain” in our case

From elevated command line, execute the batch file, and the Windows service for WLS_FORMS must be created, unless there were mismatches with the information supplied

Part 8 - Creating Windows Service for WLS_REPORTS(Reports Server)

Create a .cmd file “InstallWLS_REPORTS.cmd” using Notepad++ with following lines

<div><div><table><tbody><tr><td><div><code>SETLOCAL</code><p><code>set DOMAIN_NAME=appsdomain</code></p><p><code>set USERDOMAIN_HOME=D:\WebLogic\Middleware\user_projects\domains\appsdomain</code></p><p><code>set SERVER_NAME=WLS_REPORTS</code></p><p><code>set WL_HOME=D:\WebLogic\Middleware\wlserver_10.3</code></p><p><code>set WLS_USER=weblogic</code></p><p><code>set WLS_PW=pass123</code></p><p><code>set PRODUCTION_MODE=true</code></p><p><code>set ADMIN_URL=http://localhost:7001</code></p><p><code>cd %USERDOMAIN_HOME%</code></p><p><code>call %USERDOMAIN_HOME%\bin\setDomainEnv.cmd</code></p><p><code>rem *** call "D:\Oracle\Middleware\wlserver_10.3\server\bin\installSvc.cmd"</code></p><p><code>call "%WL_HOME%\server\bin\installSvc.cmd"</code></p><p><code>ENDLOCAL</code></p></div><br></td><td><div><code>SETLOCAL</code><p><code>set DOMAIN_NAME=appsdomain</code></p><p><code>set USERDOMAIN_HOME=D:\WebLogic\Middleware\user_projects\domains\appsdomain</code></p><p><code>set SERVER_NAME=WLS_REPORTS</code></p><p><code>set WL_HOME=D:\WebLogic\Middleware\wlserver_10.3</code></p><p><code>set WLS_USER=weblogic</code></p><p><code>set WLS_PW=pass123</code></p><p><code>set PRODUCTION_MODE=true</code></p><p><code>set ADMIN_URL=http://localhost:7001</code></p><p><code>cd %USERDOMAIN_HOME%</code></p><p><code>call %USERDOMAIN_HOME%\bin\setDomainEnv.cmd</code></p><p><code>rem *** call "D:\Oracle\Middleware\wlserver_10.3\server\bin\installSvc.cmd"</code></p><p><code>call "%WL_HOME%\server\bin\installSvc.cmd"</code></p><p><code>ENDLOCAL</code></p></div></td></tr></tbody></table></div></div> 

Unfortunately, you cannot just run the .cmd file and create a service for WLS_REPORTS like other services, as the java CLASSPATH for reports server has more than 3000 character length, which is beyond 32Bit command line architecture.

Hence you have to do a minor hack, as provided as a solution by Oracle.

Go to “D:\WebLogic\Middleware\wlserver_10.3\server\bin” folder (equivalent on your machine) and make a backup for the file “InstallSvc.cmd”

If the report server is running, terminate it by using Ctrl+C within the open cmd window

Wait until the server shutdown. Check the status through Admin Console.

Now restart the Report Server, using the menu interface

Report Server Start

Mark and copy the CLASSPATH= output and paste it to a new text document. You have to spend few minutes to wrap up the text and make sure the entire text is wrapped into a single line (This is why we love Notepad++)

Marking_Classpath

Add “set” in front of CLASSPATH= and your txt file content should look something like below and save the text file as “myClasspath.txt”

<div><div><table><tbody><tr><td><div>1</div></td><td><div><code>set CLASSPATH=D:\WebLogic\Middleware\Oracle_FRHome1\jdbc\lib\ojdbc6.jar;;;D:\WebLogic\MIDDLE~1\ORACLE~1\modules\oracle.jdbc_11.1.1\ojdbc6dms.jar;D:\WebLogic\MIDDLE~1\patch_wls1036\profiles\default\sys_manifest_classpath\weblogic_patch.jar;D:\WebLogic\MIDDLE~1\JROCKI~1.0-1\lib\tools.jar;D:\WebLogic\MIDDLE~1\WLSERV~1.3\server\lib\weblogic_sp.jar;D:\WebLogic\MIDDLE~1\WLSERV~1.3\server\lib\weblogic.jar;D:\WebLogic\MIDDLE~1\modules\features\weblogic.server.modules_10.3.6.0.jar;D:\WebLogic\MIDDLE~1\WLSERV~1.3\server\lib\webservices.jar;D:\WebLogic\MIDDLE~1\modules\ORGAPA~1.1/lib/ant-all.jar;D:\WebLogic\MIDDLE~1\modules\NETSFA~1.0_1/lib/ant-contrib.jar;D:\WebLogic\Middleware\wlserver_10.3\server\lib\weblogic.jar;D:\WebLogic\Middleware\oracle_common\modules\oracle.dms_11.1.1\dms.jar;D:\WebLogic\Middleware\oracle_common\modules\oracle.jmx_11.1.1\jmxframework.jar;D:\WebLogic\Middleware\oracle_common\modules\oracle.jmx_11.1.1\jmxspi.jar;D:\WebLogic\Middleware\oracle_common\modules\oracle.odl_11.1.1\ojdl.jar;D:\WebLogic\Middleware\Oracle_FRHome1\common\wlst\lib\adf-share-mbeans-wlst.jar;D:\WebLogic\Middleware\Oracle_FRHome1\common\wlst\lib\mdswlst.jar;D:\WebLogic\Middleware\Oracle_FRHome1\common\wlst\resources\auditwlst.jar;D:\WebLogic\Middleware\Oracle_FRHome1\common\wlst\resources\jps-wlst.jar;D:\WebLogic\Middleware\Oracle_FRHome1\common\wlst\resources\jrf-wlst.jar;D:\WebLogic\Middleware\Oracle_FRHome1\common\wlst\resources\oamap_help.jar;D:\WebLogic\Middleware\Oracle_FRHome1\common\wlst\resources\oamAuthnProvider.jar;D:\WebLogic\Middleware\Oracle_FRHome1\common\wlst\resources\ossoiap_help.jar;D:\WebLogic\Middleware\Oracle_FRHome1\common\wlst\resources\ossoiap.jar;D:\WebLogic\Middleware\Oracle_FRHome1\common\wlst\resources\sslconfigwlst.jar;D:\WebLogic\Middleware\Oracle_FRHome1\common\wlst\resources\wsm-wlst.jar;D:\WebLogic\Middleware\wlserver_10.3\server\lib\weblogic.jar;D:\WebLogic\Middleware\Oracle_FRHome1\opmn\lib\nonj2eembeans.jar;D:\WebLogic\Middleware\Oracle_FRHome1\jdbc\lib\ojdbc6.jar;D:\WebLogic\Middleware\Oracle_FRHome1\opmn\lib\optic.jar;D:\WebLogic\Middleware\Oracle_FRHome1\opmn\lib\iasprovision.jar;D:\WebLogic\Middleware\Oracle_FRHome1\common\wlst\lib\adf-share-mbeans-wlst.jar;D:\WebLogic\Middleware\Oracle_FRHome1\common\wlst\lib\mdswlst.jar;D:\WebLogic\Middleware\Oracle_FRHome1\common\wlst\resources\auditwlst.jar;D:\WebLogic\Middleware\Oracle_FRHome1\common\wlst\resources\jps-wlst.jar;D:\WebLogic\Middleware\Oracle_FRHome1\common\wlst\resources\jrf-wlst.jar;D:\WebLogic\Middleware\Oracle_FRHome1\common\wlst\resources\oamap_help.jar;D:\WebLogic\Middleware\Oracle_FRHome1\common\wlst\resources\oamAuthnProvider.jar;D:\WebLogic\Middleware\Oracle_FRHome1\common\wlst\resources\ossoiap_help.jar;D:\WebLogic\Middleware\Oracle_FRHome1\common\wlst\resources\ossoiap.jar;D:\WebLogic\Middleware\Oracle_FRHome1\common\wlst\resources\sslconfigwlst.jar;D:\WebLogic\Middleware\Oracle_FRHome1\common\wlst\resources\wsm-wlst.jar;D:\WebLogic\Middleware\oracle_common\modules\oracle.adf.share_11.1.1\commons-el.jar;D:\WebLogic\Middleware\Oracle_FRHome1\jlib\dfc.jar;D:\WebLogic\Middleware\Oracle_FRHome1\dvt\lib\dvt-jclient.jar;D:\WebLogic\Middleware\Oracle_FRHome1\dvt\lib\dvt-utils.jar;D:\WebLogic\Middleware\oracle_common\jlib\ewt3.jar;D:\WebLogic\Middleware\oracle_common\modules\oracle.iau_11.1.1\fmw_audit.jar;D:\WebLogic\Middleware\Oracle_FRHome1\oui\jlib\http_client.jar;D:\WebLogic\Middleware\oracle_common\modules\oracle.idm_11.1.1\identitystore.jar;D:\WebLogic\Middleware\oracle_common\modules\oracle.idm_11.1.1\identityutils.jar;D:\WebLogic\Middleware\oracle_common\modules\oracle.jps_11.1.1\jaccprovider.jar;D:\WebLogic\Middleware\oracle_common\modules\oracle.jps_11.1.1\jacc-spi.jar;D:\WebLogic\Middleware\Oracle_FRHome1\ord\jlib\jai_codec.jar;D:\WebLogic\Middleware\Oracle_FRHome1\ord\jlib\jai_core.jar;D:\WebLogic\Middleware\oracle_common\modules\oracle.oc4j-obsolete_11.1.1\jazn.jar;D:\WebLogic\Middleware\oracle_common\modules\oracle.oc4j-obsolete_11.1.1\jazncore.jar;D:\WebLogic\Middleware\oracle_common\jlib\jewt4.jar;D:\WebLogic\Middleware\Oracle_FRHome1\jlib\jta.jar;D:\WebLogic\Middleware\oracle_common\modules\oracle.ldap_11.1.1\ldapjclnt11.jar;D:\WebLogic\Middleware\Oracle_FRHome1\lib\mail.jar;D:\WebLogic\Middleware\Oracle_FRHome1\jlib\netcfg.jar;D:\WebLogic\Middleware\Oracle_FRHome1\jlib\oracle_ice.jar;D:\WebLogic\Middleware\oracle_common\jlib\share.jar;D:\WebLogic\Middleware\Oracle_FRHome1\jlib\zrclient.jar;D:\WebLogic\Middleware\Oracle_FRHome1\reports\jlib\aolj.jar;D:\WebLogic\Middleware\Oracle_FRHome1\reports\jlib\confmbean.jar;D:\WebLogic\Middleware\Oracle_FRHome1\reports\jlib\runtimembean.jar;D:\WebLogic\Middleware\Oracle_FRHome1\reports\jlib\rwadmin.jar;D:\WebLogic\Middleware\Oracle_FRHome1\reports\jlib\rwenv.jar;D:\WebLogic\Middleware\Oracle_FRHome1\reports\jlib\rwrun.jar;D:\WebLogic\Middleware\Oracle_FRHome1\reports\jlib\rwxdo.jar;D:\WebLogic\Middleware\Oracle_FRHome1\jlib\rts2.jar;;D:\WebLogic\MIDDLE~1\ORACLE~1\soa\modules\commons-cli-1.1.jar;D:\WebLogic\MIDDLE~1\ORACLE~1\soa\modules\oracle.soa.mgmt_11.1.1\soa-infra-mgmt.jar;D:\WebLogic\MIDDLE~1\ORACLE~1\modules\oracle.jrf_11.1.1\jrf.jar;D:\WebLogic\MIDDLE~1\WLSERV~1.3\common\derby\lib\derbyclient.jar;D:\WebLogic\MIDDLE~1\WLSERV~1.3\server\lib\xqrl.jar</code></div></td></tr></tbody></table></div></div> 

Now open the “InstallSvc.cmd” using Notepad++ and locate the line “set CLASSPATH=%WEBLOGIC_CLASSPATH%;%CLASSPATH%” and comment the same like

rem *** set CLASSPATH=%WEBLOGIC_CLASSPATH%;%CLASSPATH%

Now locate the line “set CMDLINE” within the “InstallSvc.cmd” and change the line like following

<div><div><table><tbody><tr><td><div>1</div></td><td><div><code>set CMDL