Oracle tips and tricks: 2019

Install Oracle 12C as Windows Services

It's more professional to make Oracle services start as Windows services , So the end-user is not required to start services every time PC starts.

Moreover It's not user-friendly too.

1.Here's how to Add Weblogic to Windows service :

SETLOCAL
set DOMAIN_NAME=insight
set USERDOMAIN_HOME=D:\Oracle\Middleware\user_projects\domains\insight
set SERVER_NAME=AdminServer
set WL_HOME=D:\Oracle\Middleware\wlserver
set PRODUCTION_MODE=true
cd %USERDOMAIN_HOME%
call %USERDOMAIN_HOME%\bin\setDomainEnv.cmd
rem *** call "C:\Oracle\Middleware\wlserver_10.3\server\bin\installSvc.cmd"
call "%WL_HOME%\server\bin\installSvc.cmd"
ENDLOCAL

Save the file as Install_AdminServer.cmd

just double click the file to add it to Windows services

2.Here's how to Add WLS_FORMS to Windows service :

SETLOCAL
set DOMAIN_NAME=insight
set USERDOMAIN_HOME=D:\Oracle\Middleware\user_projects\domains\insight
set SERVER_NAME=WLS_FORMS
set WL_HOME=D:\Oracle\Middleware\wlserver
set PRODUCTION_MODE=true
set ADMIN_URL=http://localhost:9001
cd %USERDOMAIN_HOME%
call %USERDOMAIN_HOME%\bin\setDomainEnv.cmd
rem *** call "C:\Oracle\Middleware\wlserver_10.3\server\bin\installSvc.cmd"
call "%WL_HOME%\server\bin\installSvc.cmd"
ENDLOCAL

Save the file as InstallWLS_FORMS.cmd

just double click the file to add it to Windows services

3.Here's how to Add WLS_REPORTS to Windows service :

SETLOCAL
set DOMAIN_NAME=insight
set USERDOMAIN_HOME=D:\Oracle\Middleware\user_projects\domains\insight
set SERVER_NAME=WLS_REPORTS
set WL_HOME=D:\Oracle\Middleware\wlserver
set PRODUCTION_MODE=true
set ADMIN_URL=http://localhost:9002
cd %USERDOMAIN_HOME%
call %USERDOMAIN_HOME%\bin\setDomainEnv.cmd
rem *** call "C:\Oracle\Middleware\wlserver_10.3\server\bin\installSvc.cmd"
call "%WL_HOME%\server\bin\installSvc.cmd"
ENDLOCAL

Save the file as InstallWLS_REPORTS.cmd

just double click the file to add it to Windows services

UPDATE : Reports Server is nolonger available according to Oracle site link

UPDATE : Some services might not work because After Windows 10 updates and it may ask for of Node manager , so we need to start up Node Manger too.

4.Here's how to Add Node Manger to Windows service :
Go to D:\Oracle\Middleware\user_projects\domains\insight\bin\installNodeMgrSvc.cmd

You can manage services from :

Start Menu -> Run -> services.msc

Oracle services start with wlsvc...

Access Report Server 12C

Sometimes when browsing to http://localhost:9002/reports/rwservlet/showjobs , It prompts you to enter User Name and Password.

To prevent such prompts:

open Web Browser then Goto :
http://localhost:9002/reports/rwservlet/showjobs
If you prompted to enter User name and Password , then
** Stop WLS_REPORTS
** Edit rwserver.conf in this path :D:\Oracle\Middleware\user_projects\domains\insight\config\fmwconfig\servers\WLS_REPORTS\applications\reports_12.2.1\configuration\rwserver.conf** Remove line which has SECURITY
** Remove
word Security_Id
** Remove
securityId="rwJaznSec"
** Save and restart WLS_REPORTS

Also if When logging to localhost:9002/reports cannot login

It may be as a result of SSL , so you do have 7002 instead of 9002.

Configure Oracle 12C - Dual-Language interface Step-7

To Configure Oracle 12c Dual-Language Layout

In the Upper Left ,click the navigation Menu beside insight(server name)
It expands to display, multiple components ,
expand Forms , click forms1
In Upper left , click Forms then click Web Configuration
In Upper right , click yellow LOCK key
Select Lock & Edit
Select Default , then press Create Like to make English Interface :
*New Section Name :EN
then press Create
After you finish , click APPLY,
click yellow LOCK key
select APPLY changes
Select EN , then in Upper right , click yellow LOCK key
Select Lock & Edit
In the mid-left of the page at the Section : default
change Show to: all
Change the following PARAMETER VALUES:
envFile : EN.env
form : login.fmx
After you finish , click APPLY,

click yellow LOCK key

select APPLYchanges

Configure Oracle 12C - Reports Step-6

Oracle 12C Reports Configurations

Open Internet Browser and goto: http://localhost:7001/em
Enetr Username: weblogic
Password:OrAcLe_2016

click SIGN IN

In the Upper Right ,click Weblogic Domain
It expands to display, multiple componnets ,
click System MBean Browser
on the System MBean Browser page :
expand   : oracle.reportsApp.config
then   : Server: WLS_REPORTS
then : Application: reports
then   : ReportsApp
Select rwservlet on the right Application Defined MBeans: ReportsApp:rwservlet :
Change the following
PARAMETER VALUES:
WebcommandAccess L2
allowhtmltags yes
server Insight_Rep_Srvr
After you finish , click APPLY

ReportsApp:rwserver

From the left menu , choose rwserver
** from the right Application Defined MBeans: ReportsApp:rwserver
Choose Operations tab :
click addEnvironment
Value : AR
click Invoke
on the same page modify AR to EN
click Invoke
on the upper right , click Refresh circle arrow
on the left menu , expand ReportApp.Environment , you should find AR , En variables
On the left menu , expand ReportApp Engine (Application Defined MBeans:ReportsApp.Engine:rwURLEng):
click rwURLEng
change Value of DefaultEnvId : EN
change Value of JvmOptionsJvmOptions: -Xmx1024m (to avoid REP-69 : Java heap space)
After you finish , click APPLY,
On the left menu , expand ReportApp Engine (Application Defined MBeans: ReportsApp.Engine:rwEng):
click rwEng
change Value of DefaultEnvIdDefaultEnvId : AR
After you finish , click APPLY

Apply Arabic Display (Right to Left) :

on the left menu , expand ReportApp.Environment :
click AR
Choose Operations tab :
click addEnvVariableValue
NLS_LANG ARABIC_EGYPT.AR8MSWIN1256click Invoke
on the same page Change existing Value to USER_NLS_LANG
click Invoke
on the same page modify
Change existing Value to REPORTS_TMP
next line Value : C:\TMP
click Invoke
Goto C:\ and create TMP Folder
on the same page modify
Change existing Value to : REPORTS_BIDI_ALGORITHM
with new line value : UNICODE
click Invoke
on the same page modify
Change existing Value to : REPORTS_ENHANCED_BIDIHANDLING
with new line value : Yes
click Invoke
on the same page modify
Change existing Value to : REPORTS_ARABIC_NUMERAL
with new line value : HINDI
click Invoke
on the same page modify
Change existing Value to : REPORTS_PATH
with new line value : D:\Oracle\Middleware\reports\templates;D:\Oracle\Middleware\reports ; \printers;c:\Windows\Fonts;d:\WorkArea\REPORTS;
click Invoke**open Regedit, add the same path to the following KEY:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OracleHome1\REPORTS_PATH

Apply English Display (Left to Right) :

On the left menu , expand ReportApp.Environment :
click EN
Choose Operations tab :
click addEnvVariable
Value
NLS_LANG AMERICAN_AMERICA.AR8MSWIN1256click Invoke
on the same page Change existing Value to USER_NLS_LANG
click Invoke
On the same page Change existing Value to REPORTS_TMP
next line Value :C:\TMP
click Invoke
On the same page Change existing Value to : REPORTS_ARABIC_NUMERAL
with new line value : ARABIC
click Invoke
on the same page modify
Change existing Value to : REPORTS_PATH
with new line value : D:\Oracle\Middleware\reports\templates;D:\Oracle\Middleware\reports ; \printers;c:\Windows\Fonts;d:\WorkArea\REPORTS;
click Invoke
**open Regedit, add the same path to the following KEY:HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OracleHome1\REPORTS_PATH
On the same page modify
Change existing Value to : REPORTS_BIDI_ALGORITHM
with new line value : UNICODE
click Invoke
On the same page modify
Change existing Value to : REPORTS_ENHANCED_BIDIHANDLING
with new line value : Yes
click Invoke

Fixing Arabic Font doesn't display :

Replace uifont.ali in the following paths with attached uifont.ali:

D:\Oracle\Middleware\user_projects\domains\insight\config\fmwconfig\components\ReportsToolsComponent\INSIGHT_Rep_Tools\tools\COMMON
D:\Oracle\Middleware\user_projects\domains\insight\config\fmwconfig\components\ReportsToolsComponent\INSIGHT_Rep_Tools\guicommon\tk\admin

you're done :-)

Update #1:

Open Registry --> TK_PATH ( C:\oracle\Middleware\tools\common )
add uifont.ali file

Update #2 :

How To Access http://localhost:9002/reports/rwservlet/showjobs , it's very useful to see jobs queue , showenv, serverinfo etc....

open Web Browser then Goto : http://localhost:9002/reports/rwservlet/showjobs
If you prompted to enter User name and Passwrod , then

Stop WLS_REPORTS
Edit rwserver.conf in this path
D:\Oracle\Middleware\user_projects\domains\SitKSA\config\fmwconfig\servers\WLS_REPORTS\applications\reports_12.2.1\configuration\rwserver.conf
Remove line which has SECURITY
Remove word : Security_Id
Remove securityId="rwJaznSec"
Save and restart WLS_REPORTS

REP-52262: Diagnostic output is disabled.

FIX Web Webcommandaccess

When logging to localhost:9002/reports cannot login?

It may be as aresult of SSL , so you do have 7002 & 9002

Configure Oracle 12C - Webutil Step-5

Download The attached folder from here .

From the downloaded Folder Copy theses files : jacob.jar , ffisamp.dll , jacob-1.18-M2-x86.dll
to : D:\Oracle\Middleware\forms\java
Make sure you have installed JDK8u151 (download from oracle )
Open Internet Explorer and goto : http://localhost:9001/forms/frmservlet** You should have [ Installed successfully ] message
In Upper left , click Forms then click Web ConfigurationIn Upper right , click yellow LOCK key
Select Lock & Edit
In the mid-left of the page at the Section : default
change Show to: all
Change the following PARAMETER VALUES
baseHTML : webutilbase.htm
baseHTMLjpi : webutiljpi.htm
After you finish , click APPLY,
click yellow LOCK key
select Apply changes
In upper left , click the Add symbol to add parameter:
PARMETER NAME VALUE
WebUtilArchive   frmwebutil.jar,jacob.jar
WebUtilLogging   off
WebUtilLoggingDetail   normal
WebUtilErrorMode Alert
WebUtilDispatchMonitorInterval 5
WebUtilTrustInternal   true WebUtilMaxTransferSize 16384

Bugs and Fixes :

When runing form includes WEBUTIL , You may face any of :

bean not found. WEBUTIL_FILE_TRANSFER.getMaxTransfer will not work ,WUT-121 File transfer error,etc

You can fix by Making sure following steps are done:

Goto Control Panel --> Java -->Security --> Edit Sites List --> then add full address of your application, then restart browser

Goto Control Panel --> Java -->General--> Settings -->Delete Files then check all,then restart browser

Delete webutil.ABUOUF.32.PROPERTIES << it's located in : C:\Users\Administrator >> , then retest (to download Webutil files again)

Open Internet explorer --> Tools --> Options --> General --> Delete check all, then Apply , Ok, then restart browser

Make sure the CLIENT current Windows User is ADMINISTRATOR

edit webutil.cfg as followed<< which is located in :
D:\Oracle\Middleware\user_projects\domains\insight\config\fmwconfig\components\FORMS\instances\forms1\server\webutil.cfg>>

transfer.database.enabled=TRUE
transfer.appsrv.enabled=TRUE
transfer.appsrv.workAreaRoot=c:\srvr
transfer.appsrv.accessControl=FALSE
#List transfer.appsrv.read.<n> directories
transfer.appsrv.read.1=c:\TEMP
#List transfer.appsrv.write.<n> directories
transfer.appsrv.write.1=c:\srvr

Configure Oracle 12C - Forms Step-4

Forms configuration:

Open Internet Browser and goto: http://localhost:7001/em
Enetr Username: weblogic
Password:OrAcLe_2016
click SIGN IN

1.Font_Icon_Mapping

In the Upper Left ,click the navigation Menu beside insight (server name)
It expands to display, multiple componnets ,
expand Forms , click forms1
Click Font and Icon Mapping
In Upper right , click yellow LOCK key
select Lock & Edit
Edit the ICONS Path make sure folder is named sysicons (it's located in d:\Oracle\Middleware\forms):
default.icons.iconpath: /forms/java/sysicons
After you finish , click APPLY,
click yellow LOCK key
select APPLYchanges

2.Environment (Arabic Display)

In Upper left , click Forms then click Environment Configuration
In Upper right , click yellow LOCK key
Select Lock & Edit
In upper left , click the Add symbol to add parameter:
PARMETER NAME VALUE
NLS_LANG   ARABIC_EGYPT.AR8MSWIN1256
USER_NLS_LANG   ARABIC_EGYPT.AR8MSWIN1256
COMPONENT_CONFIG_PATH  D:\Oracle\Middleware\user_projects\domains\insight\config\fmwconfig\components\ReportsToolsComponent\Insight_Reports_Tools
FORMS_DATETIME_SERVER_TZ GMT
FORMS_DATETIME_LOCAL_TZ GMT
Add the value of the following Existings Parameters to include PLLL , OLB , Forms, Reports files at the end :
FORMS_PATH: ; D:\WorkArea\FORMS
After you finish , click APPLY,
click yellow LOCK key
select Apply changes

3.Environment (English Display)

In Upper right , click Duplicate File to create Another Languge Environment :
Environment File : default.env
*Name : EN.env
From Upper left , Select EN.env then
In Upper right , click yellow LOCK key
Select Lock & Edit
Change the following PARAMETER VALUES:
NLS_LANG AMERICAN_AMERICA.AR8MSWIN1256USER_NLS_LANG AMERICAN_AMERICA.AR8MSWIN1256
After you finish , click APPLY,
click yellow LOCK key
select Apply changes

4.Web Config

In Upper left , click Forms then click Web Configuration
In Upper right , click yellow LOCK key
Select Lock & Edit
In the mid-left of the page at the Section : default
change Show to: all
Change the following PARAMETER VALUES:
form : arlogin.fmx
userid : insight/insight@prod
pageTitle : Insight | Innovative Solutions.
width : 100%
height : 100%
separateFrame : true
highContrast : true
background : logo.jpg
After you finish , click APPLY,
click yellow LOCK key
select Apply changes
Copy TNSNAMES.ORA to Middleware :
from: D:\Oracle\database\product\12.2.0\dbhome_1\network\admin\tnsnames.oracopy
to: D:\Oracle\Middleware\user_projects\domains\insight\config\fmwconfig\tnsnames.ora

congrats , forms are fully configured.

Configure Oracle 12C - Starting Services Step-3

1.Start Node Manager:

After Creating Domain goto

D:\Oracle\Middleware\user_projects\domains\insight\bin\startNodeManager.cmd

If you got message:

<Secure socket listener started on port 5556 , host localhost/127.0.0.1>

then you are right

2.Weblogic :

D:\Oracle\Middleware\user_projects\domains\insight\bin\startWebLogic.cmd

enter Weblogic server User name & Password

Once server state changed to RUNNING

You are right

3.Weblogic (EM) :

Open Internet Browser and goto: http://localhost:7001/emEnetr Username: weblogic
Password:
click SIGN IN
In the Upper Left ,click the navigation Menu beside insight(server name)
It expands to display, multiple componnets ,
expand HTTP Server, then click ohs1
Under Servers part in middle of page :
Click on WLS_FORMS wait until it loads..... click start Up --upper left
wait until it loads.....click start Up --upper left
Once Oracle HTTP Server started successfully:
Browse to : C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Oracle FMW 12c Domain - insight- 12.2.1.3.0** send following Shortcuts to Desktop:
Start Node Manager
Stop Node Manager
Start Weblogic Admin Server
Stop Weblogic Admin Server
Inside folder Oracle Forms Services - WLS_FORMS :
** Send the following shortcuts to Desktop :
Start Weblogic Server - WLS_FORMS
Stop Weblogic Server - WLS_FORMS
** Make a Copy of these shortcuts on the Desktop :
Start Weblogic Server - WLS_FORMS and RENAME it to Start Weblogic Server - WLS_REPORTS
Stop Weblogic Server - WLS_FORMS and RENAME it to Stop Weblogic Server - WLS_REPORTS
** Change properties of Start Weblogic Server - WLS_REPORTS file :
Target :D:\Oracle\Middleware\user_projects\domains\insight\bin\startManagedWebLogic.cmd WLS_REPORTS** Change properties of Stop Weblogic Server - WLS_REPORTS file :
Target:D:\Oracle\Middleware\user_projects\domains\insight\bin\stopManagedWebLogic.cmd WLS_REPORTS
Run : d:\Oracle\Middleware\oracle_common\common\bin\wlst.cmd
Wait until it loads.... , then type:connect ("weblogic" , "OrAcLe_2016" , "localhost:7001")
--where "weblogic" is Server Name and "OrAcLe_2016" is the password press Enter to connect to Weblogic
Create reports tools by typing the following code [ becareful as it's case-sensitive] :
createReportsToolsInstance(instanceName='insight_Reports_Tools' , machine='AdminServerMachine')press Enter
****** files must be created in this path
D:\Oracle\Middleware\user_projects\domains\insight\config\fmwconfig\components\ReportsToolsComponent\***********************************************************
Name Report Server Name by typing the following code [ becareful as it's case-sensitive] :
createReportsServerInstance(instanceName="Insight_Rep_Srvr", machine="AdminServerMachine")
press Enter
Once you are finished , exit by typing:
exit()
Stop all services now from STOP shortcuts on the desktop , Enter User name and password if required
Open Notepad and type the following and save file as boot.properties :
username=weblogic
password=OrAcLe_2016
Copy the boot.properties file to these destinations:
** D:\Oracle\Middleware\user_projects\domains\insight\servers\AdminServercreate folder : security
Paste boot.properties file here and in the following folders too:
D:\Oracle\Middleware\user_projects\domains\insight\servers\WLS_FORMS\securityD:\Oracle\Middleware\user_projects\domains\insight\servers\WLS_REPORTS\securityNow , you can run Weblogic , Forms and Reports too without inserting User Name even Password.
Goto :D:\Oracle\Middleware\user_projects\domains\insight\reports\bin\rwbuilder.batsend it as a shortcut to Desktop to run report Builder
To change Icon , Goto :
D:\Oracle\Middleware\binand choose rwbuilder

Configure Oracle 12C - Create Domain Step-2

Create Domain:

Goto:D:\Oracle\Middleware\oracle_common\common\bin\config.cmd
Create a new domain : name it as insight (D:\Oracle\Middleware\user_projects\domains\insight) click NEXT
Select the following Templates

* Oracle Forms Application Deployment Service(FADS)-12.2.1.3.0[forms]

* Oracle Forms -12.2.1.3.0[forms]

* Oracle Reports Application-12.2.1[reports]

* Oracle Enterprise Manager-12.2.1.3.0[em]

* Oracle HTTP Server (Collaocated) -12.2.1.3.0[ohs]

* Oracle Reports Server -12.2.1 [ReportsServerComponent]

* Reports Bridge -12.2.1 [reportsBridgeComponenet]

* Oracle WSM Policy Manager -12.2.1.3 [oracle_common]

* Oracle JRF - 12.2.1.3.0 [oracle_forms]

* WebLogic Coherence Cluster Extension - 12.2.1.3.0 [wlserver] click NEXT

4.Click NEXT

5.Enter weblogicuser name : insight

Password : insight123456

Confirm Password : insight123456 ,click NEXT

6.Select Domain Mode : Production ,click NEXT

7.Eneter Host Name :localhost

DBMS/Service : IAS --the name of Database

Schema Password : insight123456

click : Get RCU Configuration

if it's successfully done , then

click NEXT

8.On JDBC Component Schema click NEXT

9.On JDBC Component Schema Test , wait until end of Test

click NEXT

10.On Advanced Configuration :

Check Only the following 3 items:

* Administration Server

* Topology

* System Components ,click NEXT

11.On Administration Server :

Check Enable SSL

Server Groups: WSMPM-MAN-SVR --it's the last one in drop-down list,click NEXT

12.On Managed Servers , You do have :

WLS_FORMS

WLS_REPORTS , click NEXT

13.On Clusters , You do have :

cluster_forms

cluster_reports , click NEXT

14.On Server Template page, just click NEXT

15.On Dynamic Servers, You do have :

cluster_forms

cluster_reports , click NEXT

16.On Assign Servers to Clisusters , just click NEXT

17.On Coherence Clusters , just click NEXT

18.On Machines , just click NEXT

19.On Assign Servers to Machines :

Add the left AdminServer TO the right Machines

Click on the right AdminServerMachine, Then

Click the small arrow in the middle of screen , click NEXT

20.On Virtual Targets , just click NEXT

21.On Partitions , just click NEXT

22.On System Components

add component: ohs

--from upper left on the screen Click the Green Add icon

-- Change new_SystemComponent_1 to : ohs1

--Select OHS from Component Type List , click NEXT

23.On OHS Server, Just click NEXT

24.On Domain Frontend Host , Just click NEXT

25.On Assign System Components to Machines :

Add ohs1 to the right Machines , click NEXT

26.On OHS Server:

enter in Listen Address: localhost

click NEXT

27.On Deployments Targeting , just click NEXT

28.On ServicesTargeting , just click NEXT

29.On Configuration Summary , just click Create

30.On Configuration Progress , wait until it finishes

Once it's finished Click NEXT, then Finish

Congrats , you are done...... [next step is to run Node Manager]

UPDATE : If it config.cmd does not load , then remove all instaled JAVA (containing JDK&JRE and install other version) in my case I removed jdk-8u152-windows-x64 and installed jdk-8u181-windows-x64 .

This happened when I dropped then reCreate the RCU . That RCU was firstly created using the version of jdk-8u181-windows-x64 .

Configure Oracle 12C - RCU Step-1

Install the following software :

Oracle DB 12c download link , Here's another link Archive.org
Forms and Reports 12.2.1.3.0 download link
Fusion Middleware Infrastructure installer 12.2.1.3 download link
Java 8u152 version(jdk-8u152-windows-x64) download link

Then start the RCU as followed.

After installation ,Create Repository using Repository Creation Utility (RCU):

GOTO D:\Oracle\Middleware\oracle_common\bin\rcu.bat
Enter required Credentials
Select ALL : ORACLE AS Repository Components

Note, You can drop an existing DOMAIN , by following these simple steps :

Goto D:\Oracle\Middleware\domain-registry.xmland delete the line contains Domain Name
Delete the folder D:\Oracle\Middleware\user_projects\applications\insight
Delete the folder D:\Oracle\Middleware\user_projects\domains\insight
Delete the file: D:\Oracle\Middleware\user_projects\domains\insight\servers\AdminServer\tmp\AdminServer.lok

Before Starting install Forms &Reports and Fusion:

Both of Fusion and Forms&Reports must have the same version
Install Fusion First then install Forms&Reports
Forms&Reports are downloaded in two zipped folders, so extract first one (larger than 1.5GB) and keep the other (small zip file) inside extracted folder as the following image

Publish your local folder over internet

Using HFS application you can download it from here:

Steps to publish your files

Download HFS (from here)
Right click desired folder to publish
Check if it's real or virtual folder
To change port from 8080 to 280

in Home screen of HFS
upper left , click Port:8080
change it to 280

You are done !

Parsing JSON Manually in oracle database

Json data is very useful when working with web services.

Here's an example of JSON structure :

To extract data from json use the follwing function:

CREATE FUNCTION PARSE_JSON ( P_Text  IN VARCHAR2,
                                 P_Field IN VARCHAR2,
                                 P_Occur IN NUMBER) RETURN VARCHAR2 IS
 V_Text_Ready    VARCHAR2(2000);
 V_Elemnt_Occur  NUMBER :=P_Occur;
 V_Elemnt_Name   VARCHAR2(100) :=P_Field;
 V_Elemnt_Value  VARCHAR2(200);
BEGIN
  --------------
  V_Text_Ready := REGEXP_SUBSTR( P_Text , '{(.*?)\}',1,V_Elemnt_Occur);
  V_Text_Ready := REGEXP_REPLACE(V_Text_Ready , '({|"|}|\]|\[)+');
  V_Elemnt_Value := REGEXP_REPLACE(REGEXP_SUBSTR(V_Text_Ready, V_Elemnt_Name||':[^,]+', 1 , 1), V_Elemnt_Name);
  --------------
  RETURN ( REGEXP_REPLACE(V_Elemnt_Value,':'));
  --------------
  EXCEPTION
    WHEN OTHERS THEN
      RETURN (NULL);
 END PARSE_JSON;

Calling Oracle Procedures from PHP

Using Oracle Functions or Procedures can be used to insert /update/delete data from oracle database.

While creating function ADD_INVOICE to insert data in AR_HEADER and AR_LINES,
I found when calling it from TOAD it works fine and insert data in the two levels (HEADER and LINES).

When using PHP it only insert data in one level (HEADER) only.

After some search , I got the concept of calling stored procedure using PHP.
So, I converted the preceding function (ADD_INVOICE ) to procedure which holds an OUT parameter.

Then when calling it, the insert was successfully in the two levels (HEADER and LINES).

here's the PHP get code :

<?php
    //Connect to oracle database 
    $db=oci_connect('pos','pos','prod', 'AL32UTF8');
    
    $query = " DECLARE  BEGIN ADD_INVOICE(:message); END;";     
    $stmt =oci_parse ($db , $query);  
    // Bind the output parameter
    oci_bind_by_name($stmt,':message',$message,32);    
    // execute query
    oci_execute($stmt); 
print "$message\n";

UPDATES:

The preceding $query in green , must be written in one line , to avoid erros!

Also when receiving JSON using $_POST methods , it's automatically add back slash to every character.

I send json_text : [{ "product_id": "60402", "price": "20.0", "tax_rate": "0.0" , "qty": "1", }{ "product_id": "60302", "price": "60.0", "tax_rate": "5.0" , "qty": "1", }]

It's converted to: [{ \"product_id\": \"60402\", \"price\": \"20.0\", \"tax_rate\": \"0.0\" , \"qty\": \"1\", }{ \"product_id\": \"60302\", \"price\": \"60.0\", \"tax_rate\": \"5.0\" , \"qty\": \"1\", }]

So We use Oracle REPLACE function to remove back slash ,then parse json.

Connect PHP and Oracle database 10g

I posted before how to create APIs (Web Services) from Database 10g using PLSQL Gateway in this post.

When Android developer reads API , Arabic fonts was not complete when using FLUTTER, but when he using Android Studio he reads without any losing characters.

I found using php server , it works fine.

Here are steps to create your first APIs to connect PHP and ORACLE 10g together:
1# HR schema is used in the following example
2# Download this Github repository from here (It includes AppServ and API Folders) APPSERV another link
3# Install AppServ to C:\AppServ
4#Extract ora_php-master.zip to C:\AppServ\www
5# Rename C:\AppServ\www\ora_php-master to C:\AppServ\www\api
Now your folders and files should looks like this:

6# Config Folder: Contains database.php ,

<?php

class Database{

 // specify your own database credentials

    private $host     = "localhost"; //DataBase Server

    private $db_name  = "PROD";    //DataBase Name

    private $username = "hr";            //User Name, which We'll Create API for

    private $password = "hr";            //User Password

    public  $conn;                              //Public Varaible

// get the database connection
    public function getConnection(){
  $this->conn = null;
  try{
   $this->conn = oci_connect($this->username, 
                             $this->password, 
           '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
                                      (HOST = '.$this->host.')
                 (PORT = 1521)) 
               (CONNECT_DATA = (SERVICE_NAME = '.$this->db_name.') 
                               (SID = '.$this->db_name.')))' , 'AL32UTF8');
   }
  catch(Exception $exception){
   echo "Connection error: " . $exception->getMessage();
   }
   return $this->conn;
   }

7# Objects Folder : Contains all Users, if we do have other users .

8# User Folder : For every object we create folder contains all types of API (GET, PUT, POST....)

9# To Test API : goto http://localhost/api/user/read.php

URL consists of : Machine_Ip/API_Folder(Which was ora_php-master)/APIS_FOLDER(USER)/API_METHOD(read.php or search.php?id=107)

10# Done , Now you can connect to database from any machine, Android, iOS etc......

UPDATE #1
You may face an error like Call to undefined function oci_connect .

Here's how to fix it :
# You need to enable OCI for php
# Edit php.ini

# enable extension=php_oci8.dll
BEFORE : ;extension=php_oci8.dll
AFTER : extension=php_oci8.dll

# Restart AppServ .

UPDATE #2

To make sure OCI works , Open browser and goto http://localhost/phpinfo.php
it must looks have oci like that :

UPDATE #3

Connecting to remote DataBase is some tricky , You just add connection string when calling OCI_CONNECT , here's a full example :

<?php
//Connect String
$db="(DESCRIPTION=
     (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=TCP)
         (HOST=192.168.1.25)(PORT=1521)
       )
    )
        (CONNECT_DATA=(SID=HR))
 )";

//Connect to DB

$conn = oci_connect('hr','hr',$db); 


//Run a sample query

$qry = oci_parse($conn, 'select SYSDATE from DUAL');


if (!qry){

    echo "Not connected";

}else{

    echo "Connected";

}

UPDATE #4

If you still cannot see OCI8 in http://localhost/phpinfo.php , here's final steps :

1# download oracle instant client (instantclient-basic-win32-10.2.0.5) from here

2# Extract all contents to the zipped file to :

- C:\AppServ\Apache2.2\bin

and

- C:\AppServ\php5\ext

3# Edit System Variables PATH

add C:\AppServ\php5\ext at the end of existing text

4# Restart the Apache server

5# You are done !

Create nested JSON Array

To query all EMPLOYEES below the main Departments , and get the result in JSON format .

Connect using SCOTT user

Following are steps:
#1 Create GET method
Type : QUERY
Pagination Size: 0

#2 Add the following code :

SELECT G.Deptno,
       G.DName,
       CURSOR ( SELECT V.Empno, 
                       V.Ename
                FROM   EMP V 
       WHERE  V.Deptno = G.Deptno )  EMPLOYEES
FROM   DEPT   G

Here's the output in PLSQL/Develoepr

Share Folders using FTP builtin windows service

File Transfer Protocol (FTP) is an easy way to access files remotely in the same network or over the web too.

Windows Server 2003 has a built-in feature to enable FTP, here's how to activate FTP.

Before we start we'll need Windows Server 2003 ISO .

Go to CONTROL PANEL, Add Or Remove Programs
On the left side , click: Add/Remove Windows Components
Select : Application Server ,
Click Details
Select Internet Information Services (IIS)
Click Details
Select File Transfer Protocol(FTP) Service
OK
Click Next

After previous steps are done, complete as followd:

Go to CONTROL PANEL, Administrative Tools
Click Internet Information Services (IIS) Manager
On the left tree side, expand FTP Sites node
Right Click Default FTP Site, Choose New , FTP Site...
Choose IP Address : 192.168.1.92
In The Path page, Click Browse to choose desired Folder you want to share it (Say it's srvr_dsk)
Open Browser go to : FTP://192.168.1.92/
You're done !

Share folders using Apache Tomcat

When it comes to accessing images, files and other media through internet, There are many solutions especially when using ORACLE APEX.

But when using Oracle 10g it's bit hard, so I used Apache Tomcat.

Steps to share folder and access it using http:// :

Downlaod and install Java6 Update 20 from here: https://www.oracle.com/technetwork/java/javase/downloads/java-archive-downloads-javase6-419409.html
Download and install Apache Tomact from here: http://tomcat.apache.org/

Remember well the PORT : 8080 or any other one
Remember well the user: ADMIN
Remember well the user Password: ******

Suppose you have installed Apache Tomcat in the following path: C:\Program Files\Apache Software Foundation\ :

Go to : C:\Program Files\Apache Software Foundation\Tomcat 7.0\conf\
Edit web.xml <param-name>listings</param-name> <param-value>false</param-value>
Change false to true :
<param-name>listings</param-name> <param-value>true</param-value>

Go to : C:\Program Files\Apache Software Foundation\Tomcat 7.0\webapps\ROOT

Remove all contents
Place here all your folders, files to make it shareable

Restart Apache Tomcat
Open Browser and Go to : http://127.0.0.1:8080/
you're done

UPDATE : To share any folder without copying it to ROOT folder :

Go to C:\Program Files\Apache Software Foundation\Tomcat 7.0\conf
Edit server.xml
Add the following line :

<Context docBase="c:\insight_dsk" path="/images" reloadable="true" source="c:\insight_dsk"/>

c:\insight_dsk : Folder you want to share it

images : Alias appears on the web page

4. Restart Apache Tomcat

To Display folder files goto: http://127.0.0.1:8080/images

Thanks https://www.youtube.com/watch?v=pJPOBKLYZYY ,
https://techanand.wordpress.com/2016/09/21/share-files-to-download-from-tomcat/

#9 REST Standalone batch

To Run ORDS ,REST services must be up all time.
This can be achieved by running ORDS in standalone, deploying on Weblogic, using Apache tomcat...etc

To run ORDS in standalone mode go to this previous post
Here's tiny batch file to manually starting ORDS service :

Suppose you have installed JDK in the path : c:\Program Files\Java\jdk1.8.0_152\bin>java
And you downloaded ORDS, and extracted to : d:\Oracle\ords\ords.war
Open Notepad and add the following code :


@echo off




CD c:\Program Files\

CD Java\jdk1.8.0_152\bin\

CALL java -jar D:\Oracle\ords\ords.war

pause

4. Save file as : Start_ORDS.bat

you are done, To run ORDS anytime you just double click this Start_ORDS.bat file

That's all it.

Generate JSON from SQL and PLSQL

There are many ways to generate JSON files from Oracle Database (11g, 12c, ...), but when it comes to Oracle DataBase 10g it's a bit of difficult.

PLSQL-utils is a method can convert SQL and REF_CURSOR to JSON.
You can download it from here: https://code.google.com/archive/p/plsql-utils/downloads

To install only the required Package:

create or replace type t_str_array as table of varchar2(4000);
/
create or replace package json_util_pkg
as

  /*

  Purpose:    JSON utilities for PL/SQL

  Remarks:

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     30.01.2010  Created

  */

  -- generate JSON from REF Cursor
  function ref_cursor_to_json (p_ref_cursor in sys_refcursor,
                               p_max_rows in number := null,
                               p_skip_rows in number := null) return clob;

  -- generate JSON from SQL statement
  function sql_to_json (p_sql in varchar2,
                        p_param_names in t_str_array := null,
                        p_param_values in t_str_array := null,
                        p_max_rows in number := null,
                        p_skip_rows in number := null) return clob;


end json_util_pkg;
/

create or replace package body json_util_pkg
as

  /*

  Purpose:    JSON utilities for PL/SQL

  Remarks:

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     30.01.2010  Created

  */


  g_json_null_object             constant varchar2(20) := '{ }';


function get_xml_to_json_stylesheet return varchar2
as
begin

  /*

  Purpose:    return XSLT stylesheet for XML to JSON transformation

  Remarks:    see http://code.google.com/p/xml2json-xslt/

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     30.01.2010  Created
  MBR     30.01.2010  Added fix for nulls

  */


  return '<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<!--
  Copyright (c) 2006, Doeke Zanstra
  All rights reserved.

  Redistribution and use in source and binary forms, with or without modification,
  are permitted provided that the following conditions are met:

  Redistributions of source code must retain the above copyright notice, this
  list of conditions and the following disclaimer. Redistributions in binary
  form must reproduce the above copyright notice, this list of conditions and the
  following disclaimer in the documentation and/or other materials provided with
  the distribution.

  Neither the name of the dzLib nor the names of its contributors may be used to
  endorse or promote products derived from this software without specific prior
  written permission.

  THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
  ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
  WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
  IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
  INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
  BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
  DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
  LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
  OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF
  THE POSSIBILITY OF SUCH DAMAGE.
-->

  <xsl:output indent="no" omit-xml-declaration="yes" method="text" encoding="UTF-8" media-type="text/x-json"/>
  <xsl:strip-space elements="*"/>
  <!--contant-->
  <xsl:variable name="d">0123456789</xsl:variable>

  <!-- ignore document text -->
  <xsl:template match="text()[preceding-sibling::node() or following-sibling::node()]"/>

  <!-- string -->
  <xsl:template match="text()">
    <xsl:call-template name="escape-string">
      <xsl:with-param name="s" select="."/>
    </xsl:call-template>
  </xsl:template>

  <!-- Main template for escaping strings; used by above template and for object-properties
       Responsibilities: placed quotes around string, and chain up to next filter, escape-bs-string -->
  <xsl:template name="escape-string">
    <xsl:param name="s"/>
    <xsl:text>"</xsl:text>
    <xsl:call-template name="escape-bs-string">
      <xsl:with-param name="s" select="$s"/>
    </xsl:call-template>
    <xsl:text>"</xsl:text>
  </xsl:template>

  <!-- Escape the backslash (\) before everything else. -->
  <xsl:template name="escape-bs-string">
    <xsl:param name="s"/>
    <xsl:choose>
      <xsl:when test="contains($s,''\'')">
        <xsl:call-template name="escape-quot-string">
          <xsl:with-param name="s" select="concat(substring-before($s,''\''),''\\'')"/>
        </xsl:call-template>
        <xsl:call-template name="escape-bs-string">
          <xsl:with-param name="s" select="substring-after($s,''\'')"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:call-template name="escape-quot-string">
          <xsl:with-param name="s" select="$s"/>
        </xsl:call-template>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>

  <!-- Escape the double quote ("). -->
  <xsl:template name="escape-quot-string">
    <xsl:param name="s"/>
    <xsl:choose>
      <xsl:when test="contains($s,''&quot;'')">
        <xsl:call-template name="encode-string">
          <xsl:with-param name="s" select="concat(substring-before($s,''&quot;''),''\&quot;'')"/>
        </xsl:call-template>
        <xsl:call-template name="escape-quot-string">
          <xsl:with-param name="s" select="substring-after($s,''&quot;'')"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:call-template name="encode-string">
          <xsl:with-param name="s" select="$s"/>
        </xsl:call-template>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>

  <!-- Replace tab, line feed and/or carriage return by its matching escape code. Can''t escape backslash
       or double quote here, because they don''t replace characters (&#x0; becomes \t), but they prefix
       characters (\ becomes \\). Besides, backslash should be seperate anyway, because it should be
       processed first. This function can''t do that. -->
  <xsl:template name="encode-string">
    <xsl:param name="s"/>
    <xsl:choose>
      <!-- tab -->
      <xsl:when test="contains($s,''&#x9;'')">
        <xsl:call-template name="encode-string">
          <xsl:with-param name="s" select="concat(substring-before($s,''&#x9;''),''\t'',substring-after($s,''&#x9;''))"/>
        </xsl:call-template>
      </xsl:when>
      <!-- line feed -->
      <xsl:when test="contains($s,''&#xA;'')">
        <xsl:call-template name="encode-string">
          <xsl:with-param name="s" select="concat(substring-before($s,''&#xA;''),''\n'',substring-after($s,''&#xA;''))"/>
        </xsl:call-template>
      </xsl:when>
      <!-- carriage return -->
      <xsl:when test="contains($s,''&#xD;'')">
        <xsl:call-template name="encode-string">
          <xsl:with-param name="s" select="concat(substring-before($s,''&#xD;''),''\r'',substring-after($s,''&#xD;''))"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise><xsl:value-of select="$s"/></xsl:otherwise>
    </xsl:choose>
  </xsl:template>

  <!-- number (no support for javascript mantise) -->
  <xsl:template match="text()[not(string(number())=''NaN'')]">
    <xsl:value-of select="."/>
  </xsl:template>

  <!-- boolean, case-insensitive -->
  <xsl:template match="text()[translate(.,''TRUE'',''true'')=''true'']">true</xsl:template>
  <xsl:template match="text()[translate(.,''FALSE'',''false'')=''false'']">false</xsl:template>

  <!-- item:null -->
  <xsl:template match="*[count(child::node())=0]">
    <xsl:call-template name="escape-string">
      <xsl:with-param name="s" select="local-name()"/>
    </xsl:call-template>
    <xsl:text>:null</xsl:text>
    <xsl:if test="following-sibling::*">,</xsl:if>
    <xsl:if test="not(following-sibling::*)">}</xsl:if> <!-- MBR 30.01.2010: added this line as it appeared to be missing from stylesheet -->
  </xsl:template>

  <!-- object -->
  <xsl:template match="*" name="base">
    <xsl:if test="not(preceding-sibling::*)">{</xsl:if>
    <xsl:call-template name="escape-string">
      <xsl:with-param name="s" select="name()"/>
    </xsl:call-template>
    <xsl:text>:</xsl:text>
    <xsl:apply-templates select="child::node()"/>
    <xsl:if test="following-sibling::*">,</xsl:if>
    <xsl:if test="not(following-sibling::*)">}</xsl:if>
  </xsl:template>

  <!-- array -->
  <xsl:template match="*[count(../*[name(../*)=name(.)])=count(../*) and count(../*)&gt;1]">
    <xsl:if test="not(preceding-sibling::*)">[</xsl:if>
    <xsl:choose>
      <xsl:when test="not(child::node())">
        <xsl:text>null</xsl:text>
      </xsl:when>
      <xsl:otherwise>
        <xsl:apply-templates select="child::node()"/>
      </xsl:otherwise>
    </xsl:choose>
    <xsl:if test="following-sibling::*">,</xsl:if>
    <xsl:if test="not(following-sibling::*)">]</xsl:if>
  </xsl:template>

  <!-- convert root element to an anonymous container -->
  <xsl:template match="/">
    <xsl:apply-templates select="node()"/>
  </xsl:template>

</xsl:stylesheet>';

end get_xml_to_json_stylesheet;


function ref_cursor_to_json (p_ref_cursor in sys_refcursor,
                             p_max_rows in number := null,
                             p_skip_rows in number := null) return clob
as
  l_ctx         dbms_xmlgen.ctxhandle;
  l_num_rows    pls_integer;
  l_xml         xmltype;
  l_json        xmltype;
  l_returnvalue clob;
begin


  /*

  Purpose:    generate JSON from REF Cursor

  Remarks:

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     30.01.2010  Created

  */

  l_ctx := dbms_xmlgen.newcontext (p_ref_cursor);

  dbms_xmlgen.setnullhandling (l_ctx, dbms_xmlgen.empty_tag);

  -- for pagination

  if p_max_rows is not null then
    dbms_xmlgen.setmaxrows (l_ctx, p_max_rows);
  end if;

  if p_skip_rows is not null then
    dbms_xmlgen.setskiprows (l_ctx, p_skip_rows);
  end if;

  -- get the XML content
  l_xml := dbms_xmlgen.getxmltype (l_ctx, dbms_xmlgen.none);

  l_num_rows := dbms_xmlgen.getnumrowsprocessed (l_ctx);

  dbms_xmlgen.closecontext (l_ctx);

  close p_ref_cursor;

  if l_num_rows > 0 then
    -- perform the XSL transformation
    l_json := l_xml.transform (xmltype(get_xml_to_json_stylesheet));
    l_returnvalue := l_json.getclobval();
  else
    l_returnvalue := g_json_null_object;
  end if;

  l_returnvalue := dbms_xmlgen.convert (l_returnvalue, dbms_xmlgen.entity_decode);

  return l_returnvalue;

end ref_cursor_to_json;


function sql_to_json (p_sql in varchar2,
                      p_param_names in t_str_array := null,
                      p_param_values in t_str_array := null,
                      p_max_rows in number := null,
                      p_skip_rows in number := null) return clob
as
  l_ctx         dbms_xmlgen.ctxhandle;
  l_num_rows    pls_integer;
  l_xml         xmltype;
  l_json        xmltype;
  l_returnvalue clob;
begin

  /*

  Purpose:    generate JSON from SQL statement

  Remarks:

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     30.01.2010  Created
  MBR     28.07.2010  Handle null value in bind variable value (issue and solution reported by Matt Nolan)

  */


  l_ctx := dbms_xmlgen.newcontext (p_sql);

  dbms_xmlgen.setnullhandling (l_ctx, dbms_xmlgen.empty_tag);

  -- bind variables, if any
  if p_param_names is not null then

    for i in 1..p_param_names.count loop
      dbms_xmlgen.setbindvalue (l_ctx, p_param_names(i), nvl(p_param_values(i), ''));
    end loop;

  end if;

  -- for pagination

  if p_max_rows is not null then
    dbms_xmlgen.setmaxrows (l_ctx, p_max_rows);
  end if;

  if p_skip_rows is not null then
    dbms_xmlgen.setskiprows (l_ctx, p_skip_rows);
  end if;

  -- get the XML content
  l_xml := dbms_xmlgen.getxmltype (l_ctx, dbms_xmlgen.none);

  l_num_rows := dbms_xmlgen.getnumrowsprocessed (l_ctx);

  dbms_xmlgen.closecontext (l_ctx);

  -- perform the XSL transformation
  if l_num_rows > 0 then
    l_json := l_xml.transform (xmltype(get_xml_to_json_stylesheet));
    l_returnvalue := l_json.getclobval();
    l_returnvalue := dbms_xmlgen.convert (l_returnvalue, dbms_xmlgen.entity_decode);
  else
    l_returnvalue := g_json_null_object;
  end if;

  return l_returnvalue;

end sql_to_json;


end json_util_pkg;
/

1.Here's how to Add Weblogic to Windows service :

2.Here's how to Add WLS_FORMS to Windows service :

3.Here's how to Add WLS_REPORTS to Windows service :

UPDATE : Reports Server is nolonger available according to Oracle site link

UPDATE : Some services might not work because After Windows 10 updates and it may ask for of Node manager , so we need to start up Node Manger too.

4.Here's how to Add Node Manger to Windows service :Go to D:\Oracle\Middleware\user_projects\domains\insight\bin\installNodeMgrSvc.cmd

To Configure Oracle 12c Dual-Language Layout

Oracle 12C Reports Configurations

ReportsApp:rwserver

Replace uifont.ali in the following paths with attached uifont.ali:

Bugs and Fixes :

Forms configuration:

1.Font_Icon_Mapping

2.Environment (Arabic Display)

3.Environment (English Display)

4.Web Config

1.Start Node Manager:

2.Weblogic :

3.Weblogic (EM) :

Create Domain:

After installation ,Create Repository using Repository Creation Utility (RCU):

4.Here's how to Add Node Manger to Windows service :
Go to D:\Oracle\Middleware\user_projects\domains\insight\bin\installNodeMgrSvc.cmd