Remove unused Layouts in Oracle Apex

 Tables used :

APEX_XXXXXXX.WWV_FLOW_REPORT_LAYOUTS 

APEX_XXXXXXX.WWV_FLOW_SHARED_QUERIES




use the following query to delete unused Layouts.

make sure you do have backup before .




DELETE FROM APEX_220100.WWV_FLOW_REPORT_LAYOUTS ll

      WHERE NOT EXISTS

                (SELECT 1

                   FROM APEX_220100.WWV_FLOW_SHARED_QUERIES qq

                  WHERE qq.REPORT_LAYOUT_ID = ll.id);

Thanks : https://stackoverflow.com/questions/21549667/where-are-the-report-queries-and-layouts-stored-in-oracle-apex-3-2

Deploy Oracle APEX on weblogic

1- Make sure you do have Java installed on your machine ( if not , then download and install it from here https://www.oracle.com/java/technologies/downloads/#java8)

2- Download Weblogic from (https://www.oracle.com/middleware/technologies/fusionmiddleware-downloads.html)

3- Open CMD , change your current directory to this path 

~~~

CD C:\Program Files\Java\jdk1.8.0_152\bin\

C:\Program Files\Java\jdk1.8.0_152\bin\java -jar C:\fmw_14.1.1.0.0_wls_lite_Disk1_1of1\fmw_14.1.1.0.0_wls_lite_generic.jar

~~~

4- Follow onscreen steps until you finish installation

5-Once ins finished , Create new domain wil automatically appears if not , you can launch it from (C:\oracle\Middleware\Oracle_Home\oracle_common\common\bin\config.cmd)

6- Change domain name or keep it base_domain

7- choose Create Domain Using Product Templates

8- Next choose your credentials that will use to login later

user name : weblogic

password: Apex_123456

9-Click next , make sure 

Domain Mode : Development

JDK : using the same path you used above (by default it's checked)

10-in Advanced configuration , select Administrative Server only, then next

11- in Administration Server , you can change server name (to my_server eg)

12-Ince you arrived  Configuration summary , click create and you're done.

13- To login to the weblogic , you must run it first

14- Go to this path (C:\oracle\Middleware\Oracle_Home\user_projects\domains\smart\startWebLogic.cmd) where you installed weblogic

15- wait until it''s running

16- Go to http://localhost:7001/console , enter your credentials

17-Download ORDS from (https://www.oracle.com/database/technologies/appdev/rest-data-services-downloads.html)

18- extract to C:\oracle\Middleware\Oracle_Home\ords_wl

19-Install ORDS 

~~~

CD C:\Program Files\Java\jdk1.8.0_152\bin\

C:\Program Files\Java\jdk1.8.0_152\bin\java -jar C:\oracle\Middleware\Oracle_Home\ords_wl\ords.war install

#Don't start in standalone

~~~

20- copy images folder from APEX folder to ords_wl

21-

~~~~

 C:\oracle\Middleware\Oracle_Home\ords_wl\ords.war static C:\oracle\Middleware\Oracle_Home\ords_wl\images

~~~

###Note an i.war file will be generated move it to ords_wl folder 

22-Goto http://localhost:7001/console , enter your credentials

23- on Left panel , click Deployments

24-On main page click install , then click localhost , and browse until you see ords.war

25-select ords.war  then next

26-choose Install this deployment as an application , next

27-choose custom Roles , next

28-Yes, take me to the deployment's configuration screen.

29- just click save

30-on Left panel , click Deployments 

31-select i.war  then next

32-choose Install this deployment as an application , next

33-choose custom Roles , next

34-Yes, take me to the deployment's configuration screen.

35- just click save

36- now go to http:/localhost:7001/ords

37- to change listen port from 7001 to 80

37-A - Login to the console

37-B - on left  click Servers 

37-C - on main page click the server name my_server

37-D - change port from 7001 to 80 , save

37-E - your apex url now is localhost/ords , you're done !

Note : you may face 503 error after running apex :

ORDS was unable to make a connection to the database. This can occur if the database is unavailable, the maximum number of sessions has been reached or the pool is not correctly configured. The connection pool named: |apex|| had the following error(s): ORA-28001: the password has expired

This is because of expired password , just change it as follows :

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

alter user APEX_PUBLIC_USER         identified by Apex_1234 account unlock;

alter user APEX_LISTENER            identified by Apex_1234 account unlock;

alter user APEX_REST_PUBLIC_USER    identified by Apex_1234 account unlock;

alter user ORDS_PUBLIC_USER         identified by Apex_1234 account unlock;   

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Thanks to eng. Hesham Abu Elenain  ,this script was written based on his video 

Youtube channel : https://www.youtube.com/channel/UCWqY-RftJ0X4Y3CTR30V1Cg

video url : https://youtu.be/xVe2DG-aAR4

Calling https from db

To invoke web service from oracle database is a bit complicated.

From user : HR ,  we're going to  invoke a web service from https://www.oracle.com .

Here are the steps to connect your oracle db to ssl websites :

1-Add your site to the ACL  :

login as sysdba : 

DECLARE
  ACL_PATH  VARCHAR2(4000);
BEGIN
  -- Look for the ACL currently assigned to 'localhost' and give APEX_050100
  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
   WHERE HOST = 'localhost' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
   
  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'HR',
     'connect') IS NULL THEN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
     'HR', TRUE, 'connect');
  END IF;
  
EXCEPTION
  -- When no ACL has been assigned to 'localhost'.
  WHEN NO_DATA_FOUND THEN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('local-access-users.xml',
    'ACL that lets users to connect to localhost',
    'HR', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('local-access-users.xml','localhost');
END;
/
COMMIT;

--where

---------HR : the user will consume webservice

---------*.oracle.com : site we'll call

BEGIN

    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE (

        HOST   => '*.oracle.com',

        ace    =>

            xs$ace_type (privilege_list   => xs$name_list ('connect'),

                         principal_name   => 'HR',

                         principal_type   => xs_acl.ptype_db));

END;

COMMIT;

2-Modify the SQLNET.ORA and add the wallet location : 

wallet_location =source =(method = file)(method_data = (directory =c:\oracle\db_home\wallet\https_wallet)))

3- Download certificate files using firefox browser , you'll download two crt files  

4-Create empty wallet using Orapki tool :

5-Create Empty Wallet

orapki wallet create -wallet C:\oracle\db_home\wallet\https_wallet -pwd oracle123 -auto_login

6- Add first Certificate to Wallet

orapki wallet add -wallet C:\oracle\db_home\wallet\https_wallet  -cert 

C:\oracle\db_home\wallet\https_wallet\DigiCertTLS.crt -trusted_cert -pwd oracle123 

7- Add second Certificate to Wallet

orapki wallet add -wallet C:\oracle\db_home\wallet\https_wallet  -cert C:\oracle\db_home\wallet\https_wallet\DigiCertGlobalRootCA.crt -trusted_cert -pwd oracle123 

8- test your webservice now !

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:

1. open Web Browser then Goto :
   http://localhost:9002/reports/rwservlet/showjobs
2. 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

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

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

ReportsApp:rwserver

1. From the left menu , choose rwserver
   ** from the right Application Defined MBeans: ReportsApp:rwserver
2. Choose Operations tab :
   click addEnvironment
   Value : AR
   click Invoke
3. on the same page modify AR to EN
   click Invoke
4. on the upper right , click Refresh circle arrow
5. on the left menu , expand ReportApp.Environment , you should find AR , En  variables
6. 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)
7. After you finish , click APPLY,
8. On the left menu , expand ReportApp Engine (Application Defined MBeans: ReportsApp.Engine:rwEng):
   click rwEng
   change Value of DefaultEnvIdDefaultEnvId : AR
9. After you finish , click APPLY

Apply Arabic Display (Right to Left) :

1. on the left menu , expand ReportApp.Environment :
   click AR
2. Choose Operations tab :
   click addEnvVariableValue
   NLS_LANG  ARABIC_EGYPT.AR8MSWIN1256click Invoke
3. on the same page Change existing Value to  USER_NLS_LANG
   click Invoke
4. on the same page modify 
   Change existing Value to  REPORTS_TMP
   next line Value : C:\TMP
   click Invoke
5. Goto C:\ and create TMP Folder
6. on the same page modify 
   Change existing Value to  : REPORTS_BIDI_ALGORITHM
   with new line value : UNICODE
   click Invoke
7. on the same page modify 
   Change existing Value to  : REPORTS_ENHANCED_BIDIHANDLING
   with new line value : Yes
   click Invoke
8. on the same page modify 
   Change existing Value to  : REPORTS_ARABIC_NUMERAL
   with new line value : HINDI
   click Invoke
9. 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) :

1. On the left menu , expand ReportApp.Environment :
   click EN
2. Choose Operations tab :
   click addEnvVariable
   Value
   NLS_LANG  AMERICAN_AMERICA.AR8MSWIN1256click Invoke
3. on the same page Change existing Value to  USER_NLS_LANG
   click Invoke
4. On the same page Change existing Value to  REPORTS_TMP
   next line Value        :C:\TMP
   click Invoke
5. On the same page Change existing Value to : REPORTS_ARABIC_NUMERAL
   with new line value : ARABIC
   click Invoke
6. 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
7. On the same page modify 
   Change existing Value to  : REPORTS_BIDI_ALGORITHM
   with new line value         : UNICODE
   click Invoke
8. 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:

1. D:\Oracle\Middleware\user_projects\domains\insight\config\fmwconfig\components\ReportsToolsComponent\INSIGHT_Rep_Tools\tools\COMMON
2. 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....

1.  open Web Browser then Goto :  http://localhost:9002/reports/rwservlet/showjobs
2. 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
3. REP-52262: Diagnostic output is disabled.
• FIX Web Webcommandaccess
4. When logging to localhost:9002/reports cannot login?
• It may be as aresult of SSL , so you do have 7002 & 9002