Configure Oracle database for Virdi fingerprint machines

On May 9, 2016October 18, 2021 By windows7bugsIn Oracle relatedLeave a comment

Updated on 18th October 2021

Google drive have security requirements changed & you have to log on to your google account to download the file attached with the post. I’ve edited the code sections, so that you can copy the scripts from the post without downloading.

Hi guys

Recently we changed our age old SAGEM finger print machines with cheaper and affordable VIRDI devices & bit the bullet when it was about configuring UNIS Remote Manager software to our Oracle ERP database server.

Virdi openly states they prefer MSACCESS database and other databases (SQL and Oracle)are “supported”, you read it, supported by the CUSTOMER

The FAQ available with Virdi website must be the work of a GENIUS, I have never seen such a structured FAQ with any other products & a wonderful close button that is a page big itself!

Oracle database setup is done for Virdi UNIS Remote Manager 3.2.3.6

Make sure your UNIS software matches the above version or later.

Default installation source includes a folder called “Database”

and within the “Database” folder you will find Oracle, SQL Server folders

Inside the Oracle folder you should see another folder “SQL Script”, within which you will find the SQL file for creating Oracle database elements for the UNIS software. Virdi wants a DBA to carefully analyze the script, execute sets one after another…

I’ve split the SQL into two portions which you can download from here. Please execute the scripts like following

(Please note the attached SQL script for creating the user is not updated, if you are going to use the downloaded scripts, please update the script using the one provided below)

As System or a user with DBA

“UNIS_User.sql”

create tablespace unis_db
datafile 'unis_db.dbf' 
size 10M
autoextend on;

create user unisuser
identified by unisamho
default tablespace unis_db
temporary tablespace temp;

GRANT connect, resource to unisuser;

Please remove DBA privileges once after the initial data migration etc are over! (Useful when you are migrating from SQL to Oracle to carry out different DBA activities ;) )

Once the unisuser account created. Run the

“UNIS_Tables.sql” as unisuser

CREATE TABLE iUserFinger(
    L_UID int NOT NULL,
    L_IsWideChar int NULL,
    B_TextFIR blob NULL
);

ALTER TABLE iUserFinger ADD PRIMARY KEY ( L_UID    ) ;

CREATE TABLE tClientLog(
    C_EventTime char(14) NULL,
    L_LogonID int NULL,
    L_Type int NULL,
    L_Result int NULL
);

CREATE TABLE tTerminalLog(
    C_EventTime char(14) NULL,
    L_TID int NULL,
    L_Type int NULL
) ;
/*
CREATE TABLE tEnter(
    C_Date char(8) NOT NULL,
    C_Time char(6) NOT NULL,
    L_TID int NOT NULL,
    L_UID int NOT NULL,
    C_Name varchar(30) NULL,
    C_Unique varchar(20) NULL,
    C_Office varchar(30) NULL,
    C_Post varchar(30) NULL,
    C_Card varchar(24) NULL,
    L_UserType int NULL,
    L_Mode int NULL,
    L_MatchingType int NULL,
    L_Result int NULL,
    L_IsPicture int NULL
);
*/

CREATE TABLE UNISUSER.TENTER
(
  C_Date          CHAR(8 BYTE)                NOT NULL,
  C_Time          CHAR(6 BYTE)                NOT NULL,
  L_TID             INTEGER                     NOT NULL,
  L_UID             INTEGER                     NOT NULL,
  C_Name          VARCHAR2(30 BYTE),
 C_Unique        VARCHAR2(20 BYTE),
  C_Office        VARCHAR2(30 BYTE),
  C_Post          VARCHAR2(30 BYTE),
  C_Card          VARCHAR2(24 BYTE),
  L_UserType      INTEGER,
  L_Mode          INTEGER,
  L_MatchingType  INTEGER,
  L_Result        INTEGER,
  L_IsPicture     INTEGER,
  L_Device        INTEGER,
  L_OverCount     INTEGER,
  C_Property      VARCHAR2(8 BYTE),
  L_JobCode       INTEGER,
  L_Etc           INTEGER,
  L_Trans         INTEGER,
  L_NvrChannel1   INTEGER,
  L_NvrChannel2   INTEGER,
  L_NvrChannel3   INTEGER,
  L_NvrChannel4   INTEGER
);

ALTER TABLE tEnter ADD PRIMARY KEY 
    (
        C_Date,
        C_Time,
        L_TID,
        L_UID
    )  ;


CREATE TABLE tAuditTerminal(
    C_EventTime char(14) NOT NULL,
    L_TID int NOT NULL,
    L_AdminID int NOT NULL,
    C_AdminName varchar(30) NULL,
    L_Type int NOT NULL,
    L_UserID int NOT NULL,
    C_UserName varchar(30) NULL
) ;


ALTER TABLE tAuditTerminal ADD PRIMARY KEY
    (
        C_EventTime, 
        L_TID,
        L_AdminID,
        L_Type,
        L_UserID  
    )  ;


CREATE TABLE tAuditServer(
    C_EventTime char(14) NOT NULL,
    L_LogonID int NOT NULL,
    L_Section int NULL,
    C_Target varchar(30) NULL,
    L_Process int NULL,
    L_Detail int NULL
) ;

CREATE TABLE cHoliday(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    C_Remark varchar(255) NULL
) ;

ALTER TABLE cHoliday ADD PRIMARY KEY
    (
        C_Code 
    )  ;

CREATE TABLE cOffice(
    c_code varchar(30) NOT NULL,
    c_name varchar(30) NULL
) ;

ALTER TABLE cOffice ADD PRIMARY KEY 
    (
        C_Code 
    )  ;

CREATE TABLE cPost(
    c_code varchar(30) NOT NULL,
    c_name varchar(30) NULL
) ;

ALTER TABLE cPost ADD PRIMARY KEY
    (
        C_Code 
    )  ;

CREATE TABLE cStaff(
    C_Code varchar(30) NOT NULL,
    C_Name varchar(30) NULL
) ;

ALTER TABLE cStaff ADD PRIMARY KEY
    (
        C_Code 
    )  ;

CREATE TABLE iCantTerminal(
    L_UID int NULL,
    L_TID int NULL
) ;

CREATE TABLE iHoliday(
    C_Code char(4) NOT NULL,
    C_Holiday char(4) NULL,
    C_DayName varchar(30) NULL
) ;

CREATE TABLE iTerminalAdmin(
    L_TID int NOT NULL,
    L_UID int NOT NULL
) ;

ALTER TABLE iTerminalAdmin ADD PRIMARY KEY
    (
        L_TID, 
        L_UID 
    )  ;

CREATE TABLE tCmdDown(
    C_RegTime char(14) NOT NULL,
    L_TID int NOT NULL,
    L_UID int NOT NULL,
    C_Time char(14) NULL,
    B_Data blob NULL,
    L_Retry int NULL
) ;

ALTER TABLE tCmdDown ADD PRIMARY KEY
    (
        C_RegTime,
        L_TID,
        L_UID
    )  ;

CREATE TABLE tMealType(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL
) ;

ALTER TABLE tMealType ADD PRIMARY KEY
    (
        C_Code 
    )  ;

CREATE TABLE tMoney(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    L_Unit int NULL,
    L_Early int NULL,
    L_Basic int NULL,
    L_Over int NULL,
    L_Night int NULL,
    L_Holi int NULL
) ;

ALTER TABLE tMoney ADD PRIMARY KEY
    (
        C_Code 
    )  ;

CREATE TABLE tWorkType(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    C_BasicDay char(8) NULL,
    L_Spin int NULL,
    C_WorkCode varchar(60) NULL,
    L_InoutMode int NULL,
    L_AutoIn int NULL,
    L_AutoOut int NULL,
    L_LateMargin int NULL,
    L_LackMargin int NULL,
    C_HoliCode char(4) NULL
) ;

ALTER TABLE tWorkType ADD PRIMARY KEY
    (
        C_Code 
    )  ;

CREATE TABLE iUserPicture(
    L_UID int NOT NULL,
    B_Picture blob NULL
) ;

ALTER TABLE iUserPicture ADD PRIMARY KEY
    (
        L_UID
    )  ;

CREATE TABLE tConfig(
    C_MasterPwd varchar(30) NULL,
    L_UniqueType int NULL,
    L_AutoDown int NULL,
    C_DownTime char(4) NULL,
    L_AutoUp int NULL,
    L_RegSameFp int NULL,
    L_FpNum int NULL,
    L_UidCipher int NULL,
    L_TidCipher int NULL,
    L_UniqueCipher int NULL,
    L_MinVID int NULL,
    L_MaxVID int NULL,
    L_tSockPort int NULL,
    L_PollTime int NULL,
    L_SaveMode int NULL,
        C_PicturePath varchar(255)
) ;

CREATE TABLE iTimezone(
    C_Code char(4) NOT NULL,
    C_Timezone char(8) NOT NULL
) ;

CREATE TABLE cTimezone(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NOT NULL,
    L_Flag int NULL,
    C_Remark varchar(255) NULL
) ;

ALTER TABLE cTimezone ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE cAccessTime(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    L_Flag int NULL,
    C_Holiday char(4) NULL,
    C_Sun char(4) NULL,
    C_Mon char(4) NULL,
    C_The char(4) NULL,
    C_Wed char(4) NULL,
    C_Thu char(4) NULL,
    C_Fri char(4) NULL,
    C_Sat char(4) NULL,
    C_Hol char(4) NULL,
    C_Remark varchar(255) NULL
) ;

ALTER TABLE cAccessTime ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE iAccessArea(
    C_Code char(4) NOT NULL,
    L_TID int NOT NULL
) 
;
ALTER TABLE iAccessArea ADD PRIMARY KEY 
    (
        C_Code,
        L_TID
    )  ;

CREATE TABLE cAccessArea(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    L_Flag int NULL,
    C_Remark varchar(255) NULL
) ;

ALTER TABLE cAccessArea ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE cAccessGroup(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    L_Flag int NULL,
    C_Remark varchar(255) NULL
) ;

ALTER TABLE cAccessGroup ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE iAccessGroup(
    C_Code char(4) NOT NULL,
    L_Type int NOT NULL,
    C_AccessCode char(4) NOT NULL
) ;

CREATE TABLE tUser(
    L_ID int NOT NULL,
    C_Name varchar(30) NULL,
    C_Unique varchar(20) NULL,
    L_Type int NULL,
    C_RegDate char(14) NULL,
    L_OptDateLimit int NULL,
    C_DateLimit char(16) NULL,
    L_AccessType int NULL,
    C_Password varchar(8) NULL,
    L_Identify int NULL,
    L_VerifyLevel int NULL,
    C_AccessGroup char(4) NULL,
    C_PassbackStatus char(4) NULL
) ;

ALTER TABLE tUser ADD PRIMARY KEY
    (
        L_ID 
    )  
;
CREATE TABLE tEmploye(
    L_UID int NOT NULL,
    C_IncludeDate char(8) NULL,
    C_RetiredDate char(8) NULL,
    C_Office varchar(30) NULL,
    C_Post varchar(30) NULL,
    C_Staff varchar(30) NULL,
    C_Authority char(4) NULL,
    C_Work char(4) NULL,
    C_Money char(4) NULL,
    C_Meal char(4) NULL,
    C_Phone varchar(255) NULL,
    C_Email varchar(255) NULL,
    C_Address varchar(255) NULL,
    C_Remark varchar(255) NULL
) ;

ALTER TABLE tEmploye ADD PRIMARY KEY
    (
        L_UID 
    )  ;

CREATE TABLE tVisitor(
    L_UID int NOT NULL,
    C_Office varchar(30) NULL,
    C_Post varchar(30) NULL,
    C_Target varchar(30) NULL,
    C_Goal varchar(255) NULL,
    C_Company varchar(30) NULL,
    C_Info varchar(255) NULL,
    C_Phone varchar(255) NULL,
    C_Email varchar(255) NULL,
    C_Address varchar(255) NULL,
    C_Remark varchar(255) NULL
) ;

ALTER TABLE tVisitor ADD PRIMARY KEY
    (
        L_UID 
    )  ;

CREATE TABLE tVisited(
    C_Name varchar(30) NULL,
    C_Unique varchar(20) NULL,
    C_RegDate char(14) NULL,
    C_LastDate char(8) NULL,
    C_Company varchar(30) NULL,
    C_Info varchar(255) NULL,
    C_Phone varchar(255) NULL,
    C_Email varchar(255) NULL,
    C_Address varchar(255) NULL,
    C_Remark varchar(255) NULL
) ;

CREATE TABLE tTerminal(
    L_ID int NOT NULL,
    C_Name varchar(30) NULL,
    L_FnWork int NULL,
    L_FnMeal int NULL,
    L_FnSchool int NULL,
    C_Office varchar(30) NULL,
    C_Place varchar(255) NULL,
    C_RegDate char(14) NULL,
    L_CommType int NULL,
    C_IPAddr varchar(255) NULL,
    L_IPPort int NULL,
    L_ComPort int NULL,
    L_Baudrate int NULL,
    L_Passback int NULL,
    C_AreaIn char(4) NULL,
    C_AreaOut char(4) NULL,
    C_lastup char(14) NULL,
    C_Version varchar(255) NULL,
    C_Remark varchar(255) NULL
) ;

ALTER TABLE tTerminal ADD PRIMARY KEY
    (
        L_ID 
    )  ;

CREATE TABLE iCardInfo(
    L_CardSize int NULL,
    L_CardType int NULL,
    L_ReadType int NULL,
    L_TemplateSize int NULL,
    L_TemplateCount int NULL
) ;

CREATE TABLE iCardLayout(
    L_Index int NULL,
    L_Sector int NULL,
    L_Block int NULL,
    L_Start int NULL,
    L_Length int NULL,
    L_KeyType int NULL,
    C_KeyValue char(12) NULL
) 
;
CREATE TABLE iUserCard(
    C_CardNum varchar(24) NOT NULL,
    L_UID int NULL
) ;

ALTER TABLE iUserCard ADD PRIMARY KEY
    (
        C_CardNum 
    )  
;
CREATE TABLE cAuthority(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    L_SetLocal int NULL,
    L_RegInfo int NULL,
    L_DataBackup int NULL,
    L_MgrTerminal int NULL,
    L_RegControl int NULL,
    L_SetControl int NULL,
    L_RegEmploye int NULL,
    L_ModEmploye int NULL,
    L_OutEmploye int NULL,
    L_RegVisitor int NULL,
    L_OutVisitor int NULL,
    L_RegMoney int NULL,
    L_RegWork int NULL,
    L_SetWork int NULL,
    L_ModWork int NULL,
    L_RegMeal int NULL,
    L_SetMeal int NULL,
    L_ModMeal int NULL,
    L_DelResult int NULL,
    L_DelWork int NULL,
    L_DelMeal int NULL,
    L_MgrScope int NULL
) ;

ALTER TABLE cAuthority ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE cPassback(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    C_Remark varchar(255) NULL
);

ALTER TABLE cPassback ADD PRIMARY KEY
    (
        C_Code
    )  ;


CREATE TABLE tMapImage(
    C_Code char(4) NOT NULL,
    C_Name varchar(30) NULL,
    C_FileName varchar(255) NULL,
    L_FileSize int NULL,
    B_FileData blob NULL
)
;
ALTER TABLE tMapImage ADD PRIMARY KEY
    (
        C_Code
    )  ;

CREATE TABLE iMapDrawing(
    C_MapCode char(4),
    L_PosX int NULL,
    L_PosY int NULL
);

CREATE TABLE iMapTerminal(
    C_MapCode char(4),
    L_TID int NULL,
    L_Type int NULL,
    L_PosX int NULL,
    L_PosY int NULL
);

Insert into tconfig values ('1',1,0,'0000',1,1,3,4,4,20,7000,9999,9870,10,0,'');
Insert into cAuthority values ('****', 'User', 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
Insert into cAuthority values ('1000', 'Department Admin', 0,0,0,0,0,0,1,1,1,1,1,1,1,1,1,1,1,1,0,0,0,0);
Insert into cAuthority values ('2000', 'Terminal Admin', 0,0,0,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1);
Insert into cAuthority values ('3000', 'Branch Admin', 1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);
Insert into cAuthority values ('4000', 'All Admin', 1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2);
Insert into cOffice values ('****', 'Unassigned');
Insert into cPost values ('****', 'Unassigned');
Insert into cStaff values ('****', 'Unassigned');
Insert into cAccessGroup values ('****', 'Unassigned',0,'');
Insert into cPassback values ('****', 'Unassigned','');
Insert into cTimezone values ('****', 'Unassigned', 1, '');
Insert into cHoliday values ('****', 'Unassigned', '');
Insert into tMoney values ('****', 'Unassigned', 0,0,0,0,0,0);
Insert into tMealType values ('****', 'Unassigned');
Insert into tWorkType values ('****', 'Unassigned', '', 0, '', 0,0,0,0,0,'');

If you use the Virdi provided Oracle SQL script, be sure you will not able to use the log monitoring screen as the tenter table misses many vital columns.

Sequence of Oracle software installation

Scenario #1

64Bit Windows Server 2008 r2 or later with ONE STATIC IP address
Oracle 10g R2 64Bit (You can try 11g or 12c databases,provided you are pretty sure aware of the password complexity & expiry parts)
Oracle 11g 32Bit client, full administrator installation (UNIS Remote manager is strictly 32Bit software & if you don’t have Oracle client 32bit version installed, won’t able to communicate with the 64bit database). A hacked installation of Oracle 10g 31bit on Windows 10 will list the drivers under 32bit ODBC, however I strongly discourage using the driver (2006) as it is not at all compatible with Windows 10. If your business strictly needs to use Oracle 10g database, please make sure that you have either 11g or 12c 32bit client installed for the System DSN part.

As I clearly mentioned, the scripts I have provided are for UNIS remote manager version 3.2.3.6. In addition to it, I have modified the table creation script for tEnter table, which holds the attendance details as per the SQL table structure so that the log monitor part of UNIS remote manager could work. I don’t guarantee or answerable for any data loss that caused by slightly hacked scripts. If you are truly concerned about data safety and reliability, please contact Virdi for support (as if they care!)

(I have tried the same against a 12c 64bit installation, unfortunately, there are some errors which fail to fetch tables after the database opened. I need reconfirm it is nothing related to the case sensitive password or complex password related mess-ups)

Windows 10 has two entries for odbc with the control panel, while Windows 2008 R2 you have to startup the 32bit odbc from the following place (Assuming Windows is installed in default location)

C:\Windows\SysWOW64\odbcad32.exe

Restart the services (Incase if you setup the Oracle part once after the UNIS software was installed)

UNIS Software instalation

Install the software using setup & and no specific settings changes are necessary to make the software communicate with the Oracle database. Said, there are no settings available with the UNIS Remote Manager to setup the database. UNIS software looks for a system DSN with the names UNIS and UNIS_Temp and if connections could be initiated, works against the available database through the DSN(s)

Try to start UNIS Remote Manager software and if your configurations were correct in the place, should start without giving any errors

(Check the Master Logon, that will put “0000” in Admin ID and the default password for Admin account “0000” is “1”)

I have populated the Oracle table tenter with data extracted from SQL table in order to make sure that UNIS is communicating with Oracle database properly.

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

On October 31, 2013March 19, 2014 By windows7bugsIn Oracle related5 Comments

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)

From an elevated command prompt

set ORACLE_SID=<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    <DIR>          .
10/31/2013 12:31 PM    <DIR>          ..
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

(We are using Windows 8.1 64bit)

By the right side details panel, find the key “ORA_<SID>_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

For Windows7bugs

raj

Oracle Database, Send SMS through SMS Gateway

On July 3, 2013November 9, 2020 By windows7bugsIn Oracle related38 Comments

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)

SET SERVEROUTPUT ON;
 SET DEFINE OFF;
 DECLARE
 HTTP_REQ    UTL_HTTP.REQ;
 HTTP_RESP   UTL_HTTP.RESP;
 URL_TEXT    VARCHAR2 (32767);
 URL         VARCHAR2 (2000);
 SMS_MSG     VARCHAR2 (160):= 'Congratulations! Your database has been configured propoerly for sending SMS through a 3rd party SMS Gateway';
 BEGIN
 DBMS_OUTPUT.ENABLE (1000000);
 --Based ON your SERVICE provider, THE FOLLOWING LINK FORMAT may differ FROM
 --What we have specified below!
 URL :='http://yourwebsmsdomain.com/alerts/api/web2sms.php?username=demo&password=demo2&to=95xxxxxxx&sender=ODBSMS&message='
 || UTL_URL.Escape (SMS_MSG, TRUE);
 --UTL_URL.Escape manages ESCAPE CHARACTERS LIKE SPACE BETWEEN words IN A message.
 HTTP_REQ := UTL_HTTP.BEGIN_REQUEST (URL);
 UTL_HTTP.SET_HEADER (HTTP_REQ, 'User-Agent', 'Mozilla/4.0');
 HTTP_RESP := UTL_HTTP.GET_RESPONSE (HTTP_REQ);
 -- PROCESS Request
 LOOP
 BEGIN
 URL_TEXT := NULL;
 UTL_HTTP.READ_LINE (HTTP_RESP, URL_TEXT, TRUE);
 DBMS_OUTPUT.PUT_LINE (URL_TEXT);
 EXCEPTION
 WHEN OTHERS
 THEN
 EXIT;
 END;
 END LOOP;
 UTL_HTTP.END_RESPONSE (HTTP_RESP);
 END;

Enjoy another quality solution from us :)

Regards,

Admin

Windows 7 64bit, Classic ASP, Visual Studio connection to Oracle Database

On October 23, 2012December 26, 2013 By windows7bugsIn Oracle related, Windows 7Leave a comment

References

26.12.2013 Update

We realized, over a 64bit OS you may need to change the default application pool to accommodate 32bit applications. Please refer to this post

https://help.webcontrolcenter.com/KB/a1114/how-to-enable-32-bit-application-pool-iis-7-dedicatedvps.aspx

http://www.connectionstrings.com/

http://stackoverflow.com/questions/234657/binding-asp-net-gridview-to-an-oracle-sys-refcursor

So, you are still using classic ASP or develop 32bit .NET applications using Visual Studio and Oracle databases on a Windows 7 64bit box and completely lost as connection cannot be established?

Here are few workaround solutions

Install the latest Oracle clients (both 64bit and 32bit)

If you installed Oracle client in the following folders (example)

D:\app\yourusername\product

right click on the folder, under security tab check whether authenticated users have read privileges.

If not, provide the basic privileges (Read & Execute, list folder contents, Read) Refer the image

Now add the user “IIS_ISURS” user and provide the same rights mentioned above

RESTART your Windows 7 “64bit” box now!

You can establish the connection from classic ASP like following

<%
    Dim adoCon                'Holds Connection
    Dim rsViewRecords        'Holds Recordset

    Set adoCon = Server.CreateObject("ADODB.Connection")
    if Err.Number <> 0 then 
        Response.Clear() 
        response.Write "<hr>ORASESSION Error<br>" & err.number & "  --  " & err.Description & "<hr>"
        response.End
    end if 

    adoCon.Open "provider=oraoledb.oracle;data source=test;user id=apps;password=apps;plsqlrset=true"

    Set rsViewRecords = Server.CreateObject("ADODB.Recordset")

    %>

Regards,

Admin

Oracle Left outer join, right outer join

On May 22, 2012May 22, 2012 By windows7bugsIn Oracle related, Windows 7Leave a comment

We are trying to explain Oracle’s “left outer join”, “right outer join” scenarios with simple examples for beginners. If experts find wrongs with the explanations provided, please forward us correct explanations and we will be more than glad to amend this thread

What you need

2 Tables

Access to Oracle database (obviously we know you have one Smile )

Execute the following as script (SCOTT/TIGER)

Create table authors (auth_id number, auth_name varchar2(60) ); / Create table book_titles (auth_id number, book_title varchar2(240) ); /


insert into authors
values(1002,'Abdul Aziz Marafi');
/
insert into authors
values(1100,'Rajesh Thampi');
/
insert into book_titles
values(1006, 'I.T Policies, 2011 Approaches');
/
insert into book_titles
values(1002, 'ASP.3 Switch from ASP');
/
insert into book_titles
values(1112, 'PL/SQL Tricks and Tips');
/
COMMIT;
/
Select 'There are total '||to_char(count(*))||' records in authors table' no_recs from authors;
/

Select 'There are total '||to_char(count(*))||' records in book_titles table' no_recs from book_titles; /

Once you created tables and inserted rows, let us move to trying out the “left out join” and “right outer join” against our new tables

Right Outer Join Example

/*Here authors table becomes left side table, book_title becomes
right side table and the join condition is trying to fetch all the records which
satisfy auth_id column available in both tables and then all records from left table
even though corresponding condition with table right fails*/

Select a.*, b.book_title from authors a, book_titles b where --b.auth_id(+) = a.auth_id --Uncomment and comment next line to TEST a.auth_id = b.auth_id(+);

Here table A (authors) is joined with table B (book_titles) against column auth_id and all rows satisfying the condition are fetched first
then balance records from Table A are fetched

This scenario could be once again explained as
bring everything from both tables A,B matching specific condition, then everything from Table A what do not satisfy the join condition
Thus right outer join is could be explained by “us” as “bring everything from left table (A) that are not joinable with a MERE relation expression: eg ‘=’

Left Outer Join Example

Select a.*, b.book_title from authors a, book_titles b where b.auth_id = a.auth_id(+) ;

Left outer join matches the condition first, then brings all rows from table B (book_titles) immediately after the condition satisfied rows. Thus left outer join could be explained by “us” as “bring everything from right table (B) that are not joinable with a MERE relative expression: eg ‘=’

Regards,

Admin

How to test Developer Suite 10g forms locally using JRE instead of jinitiator

On February 27, 2012November 19, 2012 By windows7bugsIn Oracle related, Windows 729 Comments

Update: We just noticed, [default] section change what we mentioned with this post works with almost all 1.6.0.xx JRE versions. ~~Firefox 10.0.2(<== WTF it is btw) doesn’t load java plug-in, while Google Chrome works excellent with this workaround method.~~

An excellent article explaining more about setting up local testing could be found here

As a leaner/student one has to configure Developer suite 10g to run forms and reports locally prior porting the locally developed applications to Oracle application Server (OAS)

In addition to providing workaround solutions for installing Oracle Developer Suite 10g on Windows 7 machines, we are providing additional information how to test the forms modules locally with this post. Prior copying the below instructions please make sure

· You already installed Developer 10g (Tested version Forms [32 Bit] Version 10.1.2.0.2 (Production))
· Java Run Time
·
· TNS Entries properly configured and connections tested.

Unfortunately, Windows 7 doesn’t work with Jinitiator and there are multiple solutions provided to “get jinitiator” work with Windows 7. However this post is solely dealing with JRE instead of jinitiator to test the forms locally.

Hence, you need to change few parameters with formsweb.cgf file (open with notepad, notepad++ or alternative text editor). Location of this file is usually Driverletter:\ DevSuiteHome_1\forms\server (incase if you selected default location for installing the product)

Now locate the following string “[default]”

And you should find the following entries

#[default]
# System parameter: default base HTML file
baseHTML=base.htm
# System parameter: base HTML file for use with JInitiator client
baseHTMLjinitiator=basejini.htm
# System parameter: base HTML file for use with Sun’s Java Plug-In
baseHTMLjpi=basejpi.htm
# System parameter: delimiter for parameters in the base HTML files
HTMLdelimiter=%
# System parameter: working directory for Forms runtime processes
# WorkingDirectory defaults to <oracle_home>/forms if unset.
workingDirectory=
# System parameter: file setting environment variables for the Forms runtime processes
envFile=default.env

We need to edit [default] area first, as following

#[default]
# System parameter: default base HTML file
#baseHTML=base.htm
# System parameter: base HTML file for use with JInitiator client
#baseHTMLjinitiator=basejini.htm
# System parameter: base HTML file for use with Sun’s Java Plug-In
#baseHTMLjpi=basejpi.htm
# System parameter: delimiter for parameters in the base HTML files
#HTMLdelimiter=%
# System parameter: working directory for Forms runtime processes
# WorkingDirectory defaults to <oracle_home>/forms if unset.
#workingDirectory=
# System parameter: file setting environment variables for the Forms runtime processes
#envFile=default.env

Thus commenting all parameters

Now replace the [default] segment with following entries. As you already have JRE installed, just change few values according to the java –version command returned values

[default]
jpi_download_page=http://www.oracle.com/technetwork/java/javase/downloads/index.html
jpi_classid=clsid:CAFEEFAC-0016-0000-0027-ABCDEFFEDCBA
jpi_codebase=http://java.sun.com/update/1.6.0/jinstall-6-windows-i586.cab#Version=1,6,0,27
jpi_mimetype=application/x-java-applet
#;jpi-version=1.6.0_27 so that firefox would load JRE!
baseHTMLjinitiator=basejpi.htm
HTMLdelimiter=%
#Please note the / (forward slash) used with workingDirectory parameter. If you have more #than one directory with .fmx files, add them separated by ‘;’
workingDirectory=E:/10gTESTForms; D:/OrderManagement
envFile=default.env

Now start “Start OC4J Instance” from

During the first initialization it make take few minutes until the OC4J instance get setup on your computer. Please accept all firewall prompts and if required allow the instance to connect to local networks

If you are not behind ISA firewall or not using personal firewalls, you can try to run the form modules from the forms designer console or by entering the url into INTERNET EXPLORER as following

http://localhost:8889/forms/frmservlet?config=scott

8889 is the default listening port for OAS component on client machine (unless changed manually during installation or other methods)

Config=scott explanation

You can create multiple configuration set for your test applications with formsweb.cfg file, which will eliminate the need to expose userid, password or connection string details to end user, an example as following

#Custom config for SCOTT application
[scott]
separateFrame=false
lookandfeel=oracle
userid=scott/tiger@test
form=10g_test

By calling the application as below

http://localhost:8889/forms/frmservlet?config=scott

checks for following details within [scott] configuration set

user id, password, connection string, look and feel, determines whether the forms session should load the java console in a separate windows etc.

Further you can tell whether you want to use generic, oracle, … (themes)

Which should be something like

http://localhost:8889/forms/frmservlet?form=10g_test&userid=scott/tiger@test &….

Successful JRE load

Our formsweb.cfg file looks like following:

# $Id: formsweb.cfg 15-apr-2005.13:17:30 pkuhn Exp $
# formsweb.cfg defines parameter values used by the FormsServlet (frmservlet)
# This section defines the Default settings. Any of them may be overridden in the
# following Named Configuration sections. If they are not overridden, then the
# values here will be used.
# The default settings comprise two types of parameters: System parameters,
# which cannot be overridden in the URL, and User Parameters, which can.
# Parameters which are not marked as System parameters are User parameters.
# SYSTEM PARAMETERS
# —————–
# These have fixed names and give information required by the Forms
# Servlet in order to function. They cannot be specified in the URL query
# string. But they can be overridden in a named configuration (see below).
# Some parameters specify file names: if the full path is not given,
# they are assumed to be in the same directory as this file. If a path
# is given, then it should be a physical path, not a URL.
# USER PARAMETERS
# —————
# These match variables (e.g. %form%) in the baseHTML file. Their values
# may be overridden by specifying them in the URL query string
# (e.g. “http://myhost.mydomain.com/forms/frmservlet?form=myform&width=700″)
# or by overriding them in a specific, named configuration (see below)
#[default]
# System parameter: default base HTML file
#baseHTML=base.htm
# System parameter: base HTML file for use with JInitiator client
#baseHTMLjinitiator=basejini.htm
# System parameter: base HTML file for use with Sun’s Java Plug-In
#baseHTMLjpi=basejpi.htm
# System parameter: delimiter for parameters in the base HTML files
#HTMLdelimiter=%
# System parameter: working directory for Forms runtime processes
# WorkingDirectory defaults to <oracle_home>/forms if unset.
#workingDirectory=E:/ERP-Inhouse Developments/SCM
# System parameter: file setting environment variables for the Forms runtime processes
#envFile=default.env
[default]
jpi_download_page=http://www.oracle.com/technetwork/java/javase/downloads/index.html
jpi_classid=clsid:CAFEEFAC-0016-0000-0027-ABCDEFFEDCBA
jpi_codebase=http://java.sun.com/update/1.6.0/jinstall-6-windows-i586.cab#Version=1,6,0,27
jpi_mimetype=application/x-java-applet;jpi-version=1.6.0_27
baseHTMLjinitiator=basejpi.htm
HTMLdelimiter=%
workingDirectory=E:/ERP-Inhouse Developments/SCM
envFile=default.env
# Forms runtime argument: whether to escape certain special characters
# in values extracted from the URL for other runtime arguments
escapeparams=true
# Forms runtime argument: which form module to run
form=test.fmx
# Forms runtime argument: database connection details
userid=
# Forms runtime argument: whether to run in debug mode
debug=no
# Forms runtime argument: host for debugging
host=
# Forms runtime argument: port for debugging
port=
# Other Forms runtime arguments: grouped together as one parameter.
# These settings support running and debugging a form from the Builder:
otherparams=buffer_records=%buffer% debug_messages=%debug_messages% array=%array% obr=%obr% query_only=%query_only% quiet=%quiet% render=%render% record=
%record% tracegroup=%tracegroup% log=%log% term=%term%
# Sub argument for otherparams
buffer=no
# Sub argument for otherparams
debug_messages=no
# Sub argument for otherparams
array=no
# Sub argument for otherparams
obr=no
# Sub argument for otherparams
query_only=no
# Sub argument for otherparams
quiet=yes
# Sub argument for otherparams
render=no
# Sub argument for otherparams
record=
# Sub argument for otherparams
tracegroup=
# Sub argument for otherparams
log=
# Sub argument for otherparams
term=

# HTML page title
pageTitle=Oracle Application Server Forms Services
# HTML attributes for the BODY tag
HTMLbodyAttrs=
# HTML to add before the form
HTMLbeforeForm=
# HTML to add after the form
HTMLafterForm=

# Forms applet parameter: URL path to Forms ListenerServlet
serverURL=/forms/lservlet
# Forms applet parameter
codebase=/forms/java
# Forms applet parameter
imageBase=DocumentBase
# Forms applet parameter
width=750
# Forms applet parameter
height=600
# Forms applet parameter
#separateFrame=false
separateFrame=true
# Forms applet parameter
splashScreen=
# Forms applet parameter
background=
# Forms applet parameter
lookAndFeel=Oracle
# Forms applet parameter
colorScheme=teal
# Forms applet parameter
logo=
# Forms applet parameter
restrictedURLparams=HTMLbodyAttrs,HTMLbeforeForm,pageTitle,HTMLafterForm,log,allow_debug,allowNewConnections
# Forms applet parameter
formsMessageListener=
# Forms applet parameter
recordFileName=
# Forms applet parameter
serverApp=default
# Forms applet archive setting for JInitiator
archive_jini=frmall_jinit.jar
# Forms applet archive setting for other clients (Sun Java Plugin, Appletviewer, etc)
archive=frmall.jar
# Number of times client should retry if a network failure occurs. You should
# only change this after reading the documentation.
networkRetries=0

# EM config parameter
# Set this to “1” to enable Enterprise Manager to track Forms processes

em_mode=0

# Single Sign-On OID configuration parameter
oid_formsid=%OID_FORMSID%
# Single Sign-On OID configuration parameter
oracle_home=E:\DevSuiteHome_1
# Single Sign-On OID configuration parameter
formsid_group_dn=%GROUP_DN%
# Single Sign-On OID configuration parameter: indicates whether we allow
# dynamic resource creation if the resource is not yet created in the OID.
ssoDynamicResourceCreate=true
# Single Sign-On parameter: URL to redirect to if ssoDynamicResourceCreate=false
ssoErrorUrl=
# Single Sign-On parameter: Cancel URL for the dynamic resource creation DAS page.
ssoCancelUrl=
# Single Sign-On parameter: indicates whether the url is protected in which
# case mod_osso will be given control for authentication or continue in
# the FormsServlet if not. It is false by default. Set it to true in an
# application-specific section to enable Single Sign-On for that application.
ssoMode=false
# The parameter allow_debug determines whether debugging is permitted.
# Administrators should set allow_debug to “true” if servlet
# debugging is required, or to provide access to the Forms Trace Xlate utility.
# Otherwise these activities will not be allowed (for security reasons).
allow_debug=false
# Parameter which determines whether new Forms sessions are allowed.
# This is also read by the Forms EM Overview page to show the
# current Forms status.
allowNewConnections=true

# EndUserMonitoring

# EndUserMonitoringEnabled parameter
# Indicates whether EUM/Chronos integration is enabled
EndUserMonitoringEnabled=

# EndUserMonitoringURL
# indicates where to record EUM/Chronos data
EndUserMonitoringURL=

# Example Named Configuration Section
# Example 1: configuration to run forms in a separate browser window with
# “generic” look and feel (include “config=sepwin” in the URL)
# You may define your own specific, named configurations (sets of parameters)
# by adding special sections as illustrated in the following examples.
# Note that you need only specify the parameters you want to change. The
# default values (defined above) will be used for all other parameters.
# Use of a specific configuration can be requested by including the text
# “config=<your_config_name>” in the query string of the URL used to run
# a form. For example, to use the sepwin configuration, your could issue
# a URL like “http://myhost.mydomain.com/forms/frmservlet?config=sepwin”.
[sepwin]
separateFrame=True
lookandfeel=Generic

#Custom config for SCOTT application
[scott]
separateFrame=false
lookandfeel=oracle
userid=scott/tiger@test
form=10g_test

# Example Named Configuration Section
# Example 2: configuration forcing use of the Java Plugin in all cases (even if
# the client browser is on Windows)
[jpi]
baseHTMLJInitiator=basejpi.htm

# Example Named Configuration Section
# Example 3: configuration running the Forms ListenerServlet in debug mode
# (debug messages will be written to the servlet engine’s log file).
[debug]
serverURL=/forms/lservlet/debug

# Sample configuration for deploying WebUtil. Note that WebUtil is shipped with
# DS but not AS and is also available for download from OTN.
[webutil]
WebUtilArchive=frmwebutil.jar,jacob.jar
WebUtilLogging=off
WebUtilLoggingDetail=normal
WebUtilErrorMode=Alert
WebUtilDispatchMonitorInterval=5
WebUtilTrustInternal=true
WebUtilMaxTransferSize=16384
baseHTMLjinitiator=webutiljini.htm
baseHTMLjpi=webutiljpi.htm
archive_jini=frmall_jinit.jar
archive=frmall.jar
lookAndFeel=oracle

We truly hope this post helps the beginners, intermediates to test 10g forms locally using Java Run Time instead of Jinitiator!

Regards,

Admin

(We thank forums.oracle.com & hundreds of Oracle enthusiastic developers who spent time to figure out a “how to” for this particular scenario)

Oracle EBS R12 how to call standard API/packages from custom form or reports

On April 17, 2011April 14, 2014 By windows7bugsIn Oracle related1 Comment

Okay guys and gals

We had a requirement to use certain Oracle standard API/packages from custom developer forms. After loads of struggle and asking various Oracle related forums, we hardly had a chance to get any satisfactory answers to our query “how to call oracle standard API or packages through custom forms or reports”

Finally with the help of a long term friend cum technical support person Mr. Anil Menon, we were able to successfully call the standard API or package call with our custom (test) form.

For the example we used “apps.INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES” procedure which indirectly refers fnd_api.g_false and apps.inv_quantity_tree_pub.g_transaction_mode parameters/constants defined in the package definitions.

Actual call to the API through a PL/SQL environment (Toad/Oracle PL/SQL) code is as following
SET SERVEROUTPUT ON; DECLARE v_api_return_status VARCHAR2 (1); v_qty_oh NUMBER; v_qty_res_oh NUMBER; v_qty_res NUMBER; v_qty_sug NUMBER; v_qty_att NUMBER; v_qty_atr NUMBER; v_msg_count NUMBER; v_msg_data VARCHAR2(1000); v_inventory_item_id VARCHAR2(250); v_organization_id VARCHAR2(10); BEGIN

v_inventory_item_id := 24445; v_organization_id := 901; inv_quantity_tree_grp.clear_quantity_cache; DBMS_OUTPUT.put_line ('Transaction Mode'); DBMS_OUTPUT.put_line ('Onhand For the Item :'|| v_inventory_item_id ); DBMS_OUTPUT.put_line ('Organization :'|| v_organization_id); apps.INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES (p_api_version_number => 1.0, p_init_msg_lst => apps.fnd_api.g_false, x_return_status => v_api_return_status, x_msg_count => v_msg_count, x_msg_data => v_msg_data, p_organization_id => v_organization_id, p_inventory_item_id => v_inventory_item_id, p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode, p_onhand_source => 3, p_is_revision_control => FALSE, p_is_lot_control => FALSE, p_is_serial_control => FALSE, p_revision => NULL, p_lot_number => NULL, p_subinventory_code => 'ABC-01', --NULL for whole org p_locator_id => NULL, x_qoh => v_qty_oh, x_rqoh => v_qty_res_oh, x_qr => v_qty_res, x_qs => v_qty_sug, x_att => v_qty_att, x_atr => v_qty_atr); DBMS_OUTPUT.put_line ('on hand Quantity :'|| v_qty_oh); DBMS_OUTPUT.put_line ('Reservable quantity on hand :'|| v_qty_res_oh); DBMS_OUTPUT.put_line ('Quantity reserved :'|| v_qty_res); DBMS_OUTPUT.put_line ('Quantity suggested :'|| v_qty_sug); DBMS_OUTPUT.put_line ('Quantity Available To Transact :'|| v_qty_att); DBMS_OUTPUT.put_line ('Quantity Available To Reserve :'|| v_qty_atr); END;

This PL/SQL procedure will successfully print the results during a PL/SQL session through Toad or Oracle PL/SQL session. However, if you would try to invoke the same code through a PL/SQL procedure from a developer form development instance, the errors will start from the call to “fnd_api.g_false” stating “Implementation restriction: ‘APPS.FND_API.G_FALSE’ cannot directly access remote package variable or cursor. Under the package specification you will find G_FALSE constant has the the value ‘F’ and apps.inv_quantity_tree_pub.g_transaction_mode has a value ‘2’ pre-defined.

Umm, not a straight forward method to dwell the packages, find the constant values and modifying the PL/SQL script. For a small API call like above one, a programmer could use this approach as a work around, however, while dealing with APIs which take 10s of parameters, it could be really tiring.

Here is the workaround we used

Created a custom database level package

CREATE OR REPLACE PACKAGE XX_CHK_QTY IS PROCEDURE retrive_quantity(item_id NUMBER, org_id NUMBER, subinv VARCHAR2, oqtt OUT NUMBER, oqtr OUT NUMBER); END;

and created corresponding package body as following

CREATE OR REPLACE PACKAGE BODY XX_CHK_QTY AS PROCEDURE retrive_quantity(item_id NUMBER, org_id NUMBER, subinv VARCHAR2, oqtt OUT NUMBER, oqtr OUT NUMBER) IS v_api_return_status VARCHAR2 (1); v_qty_oh NUMBER; v_qty_res_oh NUMBER; v_qty_res NUMBER; v_qty_sug NUMBER; v_qty_att NUMBER; v_qty_atr NUMBER; v_msg_count NUMBER; v_msg_data VARCHAR2(1000); --v_inventory_item_id VARCHAR2(250); --v_organization_id VARCHAR2(10) ; BEGIN inv_quantity_tree_grp.clear_quantity_cache; apps.INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES (p_api_version_number => 1.0, p_init_msg_lst => apps.fnd_api.g_false, --p_init_msg_lst => 'F', x_return_status => v_api_return_status, x_msg_count => v_msg_count, x_msg_data => v_msg_data, p_organization_id => org_id, --v_organization_id, p_inventory_item_id => item_id, --v_inventory_item_id, p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode, --p_tree_mode => 2, p_onhand_source => 3, p_is_revision_control => FALSE, p_is_lot_control => FALSE, p_is_serial_control => FALSE, p_revision => NULL, p_lot_number => NULL, p_subinventory_code => subinv, p_locator_id => NULL, x_qoh => v_qty_oh, x_rqoh => v_qty_res_oh, x_qr => v_qty_res, x_qs => v_qty_sug, x_att => v_qty_att, x_atr => v_qty_atr); oqtt := v_qty_att; oqtr := v_qty_atr; END; END XX_CHK_QTY;

As you could see we are using 2 numbers of OUT variables to store the output data, which will be referred in the forms at later stages.

Our sample form has a control block and 3 items

:ITEM_ID, :QTY_AVL_TRANSACT,:QTY_AVL_RESERVE and under the WVI(WHEN-VALIDATE-ITEM) scope for the block item :ITEM_ID we wanted to populate total quantity available to transact and total quantity available to reserve into block items :QTY_AVL_TRANSACT,:QTY_AVL_RESERVE

Following is how we call the custom procedure through WVI trigger:

Declare
oqtt number := 0;
oqtr number := 0;
Begin
apps.XX_CHK_QTY.retrive_quantity(:CTRL.ITEM_ID,901,’ABC-01′,oqtt,oqtr);
:CTRL.QTY_AVL_TRANSACT := oqtt;
:CTRL.QTY_AVL_RESERVE := oqtr;
End;

and bingo! as the procedure was called from a database package, there were no compilation errors pointing towards referring remote variables or cursors and once executed all intended results were fetched to corresponding block items.

As we are also learning how to use Oracle APIs within custom forms/reports, we hope this guideline would be useful for those few who are trying to find a way to start calling Oracle’s standard API/packages from such environments.

for Windows7bugs

Admin

Testing Oracle 10g developer forms locally (On Windows 7)

On March 27, 2011 By windows7bugsIn Oracle related, Windows 7Leave a comment

Okay guys, another post after another break :) This time we are going to figure out how to test your Oracle 10g developer forms locally. Until recent comments we were not knowing that fellow folks were having issues running developer 10g forms locally on Windows 7 machines. Okay here we go
Step 1
Install the latest java run time from here and make sure you are installing 32bit. Usually java 32bit gets installed under this path “C:\Program Files (x86)\Java\jre6”
Following is test environment
OS: Windows 7 64bit Professional Editions, 3GB Mem 10g Developer: Forms [32 Bit] Version 10.1.2.0.2 (Production) Database: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
Usually when you try to run a form locally for the first time after starting the OC4J instance, an older version of java initiator is installed in your system. I don’t quite remember whether the installation offers any kind of customization so that the end user could change the path or other options. I won’t recommend one either!
If you have done a standard installation, the java initiator should get installed on this path “C:\Program Files (x86)\Oracle\JInitiator 1.3.1.22”.

Step 2

In order to let Developer 10g forms run locally you need to change the jvm.dll file found under “C:\Program Files (x86)\Oracle\JInitiator 1.3.1.22\bin\hotspot” with a most recent one. As we installed the latest java run time 32bit in “Step 1”, browse to the following folder “C:\Program Files (x86)\Java\jre6\bin\client” copy the jvm.dll which is above 2MB in size, and go back to folder “C:\Program Files (x86)\Oracle\JInitiator 1.3.1.22\bin\hotspot”

Now paste the new jvm.dll file inside this folder and try to run the forms again! It should work now!

	windows7bugs on Windows 11 \| Snipping tool…
	Dave on Windows 11 \| Snipping tool…
	windows7bugs on Oracle 19c \| Install sample sc…
	windows7bugs on Oracle 19c \| Install sample sc…
	Bin Wang on Oracle 19c \| Install sample sc…

Duh! Microsoft did it again

Tag: 10g