Oracle| Altering table structures through PL/SQL block

February 21, 2018

Hi guys

Prior moving to Oracle EBS (R12) we were using a mini ERP system, that was as robust as possible, however lacked many features those were required for modern business environments. While the company that distributed the software in the market over a decade finally dissolved, we were also provided a full copy of the source codes & the same was in my custody for whole these years.

Recently I wanted to revive it, after WebLogic 12c & seeing the possibility of “JAVA WEBSTART” in small & middle scale industries.

One of the major hurdles I came across were with the columns those saved document numbers. This software which was designed during late 90s only had maximum 6 digits document numbers across modules, totaling 496 tables!

It was not a very feasible idea to go through each table & to modify them using GUI tools like Oracle SQL developer. Instead I planned for a PL/SQL procedure.

& here comes the PL/SQL block


SET SERVEROUTPUT ON SIZE UNLIMITED;

DECLARE

CURSOR T1(OWNER_NAME VARCHAR2) IS
SELECT TABLE_NAME FROM DBA_TABLES
WHERE
OWNER=OWNER_NAME;

CURSOR C1(OWNER_NAME VARCHAR2, TBL_NAME VARCHAR2) IS
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE FROM DBA_TAB_COLUMNS
WHERE
OWNER=OWNER_NAME
AND TABLE_NAME = TBL_NAME;

V_EXECUTE_MODIFY BOOLEAN := FALSE; --'TRUE' FOR TABLE MODIFY

BEGIN
FOR T IN T1('OPMAN') LOOP
--DBMS_OUTPUT.PUT_LINE(T.TABLE_NAME||'----'||T.TNAME);
FOR C IN C1('OPMAN',T.TABLE_NAME) LOOP
IF (C.DATA_TYPE='NUMBER' AND C.DATA_PRECISION=6 AND C.DATA_SCALE=0) THEN

DBMS_OUTPUT.PUT_LINE(T.TABLE_NAME || ' COLUMN NAME -->'||C.COLUMN_NAME);
--T_YR_MTH
IF (V_EXECUTE_MODIFY) THEN
IF (C.COLUMN_NAME 'T_YR_MTH') THEN
/*INCLUDE ALL COLUMN NAMES THOSE SHOULDN'T BE AFFECTED BY THE PL/SQL BLOCK RUN! eg:NOT IN ('ABC','DEF','GHI')*/
EXECUTE IMMEDIATE 'ALTER TABLE '||T.TABLE_NAME||' MODIFY '||C.COLUMN_NAME||' NUMBER';
END IF;

END IF;
END IF;

END LOOP;

END LOOP;
END;

So what does this block do?


CURSOR T1(OWNER_NAME VARCHAR2) IS
SELECT TABLE_NAME FROM DBA_TABLES
WHERE
OWNER=OWNER_NAME;

This block fetches all table names from the DBA_TABLES for the owner that is passed in as a parameter, in this specific case ‘OPMAN’


CURSOR C1(OWNER_NAME VARCHAR2, TBL_NAME VARCHAR2) IS
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE FROM DBA_TAB_COLUMNS
WHERE
OWNER=OWNER_NAME
AND TABLE_NAME = TBL_NAME;

The above cursor takes in two parameters, OWNER name and the table name, that comes from the 1st cursor


FOR T IN T1('OPMAN') LOOP
--DBMS_OUTPUT.PUT_LINE(T.TABLE_NAME||'----'||T.TNAME);
    FOR C IN C1('OPMAN',T.TABLE_NAME) LOOP
    IF (C.DATA_TYPE='NUMBER' AND C.DATA_PRECISION=6 AND C.DATA_SCALE=0) THEN

        DBMS_OUTPUT.PUT_LINE(T.TABLE_NAME || ' COLUMN NAME -->'||C.COLUMN_NAME);
        --T_YR_MTH
        IF (V_EXECUTE_MODIFY) THEN
        IF (C.COLUMN_NAME 'T_YR_MTH') THEN
        /*INCLUDE ALL COLUMN NAMES THOSE SHOULDN'T BE AFFECTED BY THE PL/SQL BLOCK RUN! eg:NOT IN ('ABC','DEF','GHI')*/
        EXECUTE IMMEDIATE 'ALTER TABLE '||T.TABLE_NAME||' MODIFY '||C.COLUMN_NAME||' NUMBER';
        END IF;

        END IF;
        END IF;

        END LOOP;

END LOOP;

Let us analyze this part of the block now.

The first cursor passes the table name to 2nd cursor and the loop goes through all columns matching data type, precision and scale. I’ve checked for “Number”, precision 6 & scale 0 which are common for all columns those were defined for document numbers. However there were few instances where YearMonth (YYYYMM) values captured for month end processes, which shouldn’t be checked. As our mini ERP is a standard business application, it was designed following same nomenclature across tables while columns defined. Thus it become easier for me to isolate the columns whose were matching same data type, precision and scale & to limit the alter commands modifying only those columns which were defined for document numbers.

The above example could be further developed to easily alter tables while the entire application is modernized and to easily modify columns with bigger sizes.

Interesting? Why don’t you give it a try and post your comments?

regards,

rajesh

Advertisements

ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET

January 7, 2018

Hi guys

Recently I was trying to understand the AUTOMATIC MEMORY MANAGEMENT feature of 11g onwards database & ended up in a drastic situation where I changed MEMORY_TARGET parameter with a value higher than MEMORY_MAX_TARGET

This has blocked the database from starting & a quick googling brought me to correct solution

As sys, connect to idle database

#sqlplus / as sysdba

SQL> create pfile from spfile;

–Applies to where spfile is used instead of pfile

Edit the pfile & comment/delete the MEMORY_MAX_TARGET parameter, get back to SQL>

SQL>create spfile from pfile;

SQL>startup;

This should resolve the error ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET

Hope this helps few folks out there!

regards,

rajesh


Oracle EBS R12(12.0.x) | Disable concurrent programs executing immediately after a clone

November 28, 2017

Hi guys

One of the annoyances suffered by business is the alerts and concurrent programs those start sending highly confusing messages to users immediately after a cloning completion. Application tier cloning finishes by trying to start all related services after a successful cloning. Hence the best method to stop the concurrent manager/alerts from firing up is NOT to let the cloning process start application tier level services, so that you can disable alerts/concurrent programs those are NOT required by the cloned instance.

First step is to alter the $COMMON_TOP/clone/bin/adcfgclone.pl (perl script, please refer the image)

image

Change the portion in the highlighted block as shown below. This will stop the clone script from kick starting the application services after a successful clone.

elsif (!($PLATFORM eq "win32")) {
print "\n Not Starting application Services for $s_dbSid:\n";
#bugfix:5838814 subgovin
#runPipedCmd("$s_config_home/admin/scripts/adstrtal.$scrExt -nopromptmsg", "$s_apps_user", "$gConfig{'appspass'}");
}

Now, you may proceed to disable all or selected alerts/concurrent programs using backend before starting the Application services.

Please note, this workaround is ONLY tested on 12.0.x releases of Oracle Applications. Please adjust to your environments.

References:

http://onlineappsdba.blogspot.com/2008/06/prevent-concurrent-requests-executing.html

http://oracle-latest-technology.blogspot.com/2013/11/how-to-disable-all-scheduled-concurrent.html

regards,

rajesh


Oracle Backup to Google Drive?

June 6, 2017

Hi guys

This is a follow up post to my previous post about using a simple batch script for creating a dump export file on regular basis for Oracle database.

Backup. The most essential, however many times highly ignored element of the digital world even today as many small scale industries find the investments made to this particular mechanism hardly comes in effect, unless a disaster arises. My personal experiences with convincing the management to go for sophisticated backup solutions were always the toughest, until we had a HUGE disaster.

As a thumb rule, the first thing I always did for an Oracle database was to setup a dump export every night (if the database is truly small in size), after the normal working hours, in addition to RMAN backups. These export files are kept in a different partition & regularly monitored and purged by the beginning of a new month, keeping the last day backup for the previous month, which is deleted by the beginning of a new year.

Keeping the backup in the same hardware could prove fatal when the hardware fails, and almost all the servers are configured to use RAID, using different levels. In such scenarios even if the drives are intact, retrieving the data from raided volumes will be a professional job, costing investment and time.

For small databases, like the one I have mentioned with my previous post we can design multiple options like mapping a network folder & copying the files automatically once after a new dump file is created as the part of a backup script.

I have devised two methods for my party, and they were

  1. FTP the compressed latest dump file to another machine hosting FTP server
  2. Using google drive (Free 15GB), upload the latest compressed dump file

The first method was already explained here so I will go to 2nd method in which Google drive sync is used to insure that the party has a valid backup stored somewhere in the cloud

  • Database dump export size: 300MB approximately
  • Zipped dump file size: 50MB approximately

Install google drive on your Windows 2008 x, Windows 2012 server machine. You may need to install corresponding Visual C++ Redistributable packages in order to come across python related errors. Please read more here for solutions.

Once the google drive starts working fine, you can use the following script, which will create a dump file first, then create a zip file against the latest dump file created and then copy the zip file to google drive for cloud synching.

Please note, I have moved the google drive folder from the default location to somewhere else, like E:\Google_Drive to make sure that my batch file has shortest path entry for the copying. If you plan the same, you can change the default location for google drive by exiting the application first, then pointing google drive to your folder of choice when google drive complains about missing default location

Windows batch file for Creating, zipping & copying the files to Google Drive

@echo off
FOR /F "tokens=2-4 delims=/ " %%a IN ('date/t') DO exp system/password@connectionstring full = y file=d:\Orabackup\exp_%%b%%a%%c.dmp 

SETLOCAL
::Get the latest dump file name, generated using exp command
::Switch to the folder where the dump (.dmp) files are stored
CD D:\Orabackup\
:: D:\Orabackup is the folder where everyday dump files are stored.
for /f "tokens=*" %%a in ('dir *.dmp /o:-d /b') do set NEWEST=%%a&& goto :next
:next
REM echo The most recently created file is %NEWEST%
::http://stackoverflow.com/questions/15567809/batch-extract-path-and-filename-from-a-variable
FOR %%i IN ("%NEWEST%") DO (
REM ECHO filedrive=%%~di
REM ECHO filepath=%%~pi
SET ZIPNAME=%%~ni
REM ECHO fileextension=%%~xi
)

SET ZIPNAME=%ZIPNAME%.zip
::You can use built-in zip or 7-Zip to create archives
zip %ZIPNAME% %NEWEST%
::E:\Google_Drive is the folder used by the google drive in my setup
COPY %ZIPNAME% E:\Google_Drive

del %ZIPNAME%

::Exit

While this method looks pretty awesome for small size databases, please be noted that, may not be at all feasible for larger ones. I will OPT this method for a backup dump file that could be compressed to a size of 400-500MB maximum, including the possibilities of corrupt compressed files.

Whatever, as far the party has a reliable internet connection with decent bandwidth, based on the size of compressed file, will always have access to a recent backup dump file, stored free in the cloud!

Does it look decent? ;)

Tip: Running Google drive sync as Windows Service

regards,

rajesh


Oracle Applications R12 | error while loading shared libraries: libig.so.0…

December 11, 2016

 

applprod@erp-prodbak.my.home:/u01/applprod/PROD/apps/apps_st/appl/au/12.0.0/forms/US>frmcmp_batch module=$AU_TOP/forms/US/XXITMDTLS.fmb userid=apps/apps output_file=$ONT_TOP/forms/US/XXITMDTLS.fmx Module_Type=FORM compile_all=Special
frmcmp_batch: error while loading shared libraries: libig.so.0: cannot open shared object file: No such file or directory
applprod@erp-prodbak.my.home:/u01/applprod/PROD/apps/apps_st/appl/au/12.0.0/forms/US>

Recently I cloned our Production instance to check the feasibility of OEL 6.7 & as usual securely copied most of the bash profile for the Oracle and Application manager users from the production server.

I came across the “cannot open shared object file: No such file or directory” error while trying to compile an old form module & hadn’t have a clue for how to deal with this error, that happened to me first time in last 8 years with Oracle applications.

I crosschecked the folder permissions against production instance and everything was fine. All I could remember which could have gone wrong was narrowed down to the bash profile for the application user and after validating I realized that I was sourcing a wrong environment file!

Instead of sourcing the “/apps/apps_st/appl/APPSPRODBAK_hostname.env” file, I was sourcing “/apps/apps_st/appl/PRODBAK_hostname.env”. I realized it while trying to echo the $ORACLE_HOME variable as application manager user.

So, if you come across “error while loading shared libraries” error while trying to compile, before start dismantling your instance, try to see whether you have sourced the environment variables correctly.

regards,

rajesh


Configure Oracle database for Virdi fingerprint machines

May 9, 2016

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!

1

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”

image

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

image

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"
 QUOTA UNLIMITED ON "UNIS_DB";

GRANT "CONNECT" TO unisuser;
GRANT "DBA" TO unisuser ;
GRANT "RESOURCE" TO unisuser ;
ALTER USER unisuser DEFAULT ROLE "CONNECT", "DBA", "RESOURCE";

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
);
*/
--Altered tEnter table script
--Rajesh
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

  1. 64Bit Windows Server 2008 r2 or later with ONE STATIC IP address
  2. Oracle 10g R2 64Bit (You can try 11g or 12c databases,provided you are pretty sure aware of the password complexity & expiry parts)
  3. 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

image

image

image

image

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)

 

image

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

image

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

image

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.

image


Java runtime 1.8.0.91 | Oracle EBS R12 (12.0.6)

May 2, 2016

Hi guys

You could run into a situation after the latest Java client runtime update, your R12 instance failing to load forms, complaining about the lower version of JRE. This is mainly due to the fact that, if your Windows OS is 64Bit, the latest java runtime update installs both 64Bit & 32Bit and most of the R12 environments run against 32Bit JRE.

Internet explorer (the only one browser that runs the JRE properly for EBS R12) loads the 64Bit java runtime by default from a 64Bit Windows OS & eventually the forms will fail to load.

At present the only possible solution to address this situation is to uninstall the 64bit version of java runtime, using “Programs and features” or “Add remove programs” based on the version of Windows you are using.

Hope this helps few out there :)

regards,

rajesh