Oracle tips and tricks

#1 Deploy Oracle REST Data Service (ORDS)

What is ORDS ?

ORDS is a Java application that enables developers with SQL and database skills to develop REST APIs for the Oracle Database, the Oracle Database 12c JSON Document store, and the Oracle NoSQL Database. Any application developer can use these APIs from any language environment, without installing and maintaining client drivers, in the same way they access other external services using the most widely used API technology: REST . ORACLE site

Required Software:

JDK (download from : https://www.oracle.com/technetwork/java/javaee/downloads/jdk8-downloads-2133151.html )

install to c:\Program Files\Java

Oracle REST (download from : https://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/index.html )

extract ords.war to c:\Oracle\ords\ords.war

Oracle database 12c or later

How to Enable ORDS ?

Uninstall if exists using :c:\Program Files\Java\jdk1.8.0_152\bin>java -jar c:\Oracle\ords\ords.war uninstall

Enter required data like SYS password

Download a fresh copy of ORDS from :
install using : c:\Program Files\Java\jdk1.8.0_152\bin>java -jar D:\Oracle\ords\ords.war install

Enter the location to store configuration data: D:\Oracle\ords\conf

Enter the name of the database server [localhost]: localhost
Enter the database listen port [1521]: 1521
Enter 1 to specify the database service name, or 2 to specify the database SID [1]: 1
Enter the database service name : ORCL
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]: 1
Enter the database password for ORDS_PUBLIC_USER:oracle
Confirm password:oracle
Enter the administrator username:SYS *
Enter the database password for SYS AS SYSDBA:123456
Confirm password:123456
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]: 2
Enter 1 if you wish to start in standalone mode or 2 to exit [1]: 1
Enter 1 if using HTTP or 2 if using HTTPS [1]:1
Enter the HTTP port [8080]:8080

IF you get the following message , this means ORDS is running :

2019-04-28 10:07:46.989:INFO:oejs.Server:main: Started @331997ms

* Important Notice: You can get this error when installing ORDS:
ords_grant_privs.sql Error: ORA-01031: insufficient privileges
This is a result of lack of current USER Privileges even if it was GRANTED DBA permission.
SO , it's very recommended to use SYS with a DIGIT password as Oracle ORDS has a built-in Bug which cannot identify passwords letters case.
You changed SYS password using :
ALTER USER SYS IDENTIFIED BY oracle;
To read more about this bug go here: https://community.oracle.com/thread/4120820

Auto Compile Multiple Forms and Reports without opening builder

Here's how to Compile Multiple Forms and Reports without opening forms builder or report builder .

open Notepad (or any text editor) , paste the following text:

@ECHO OFF
cls
Echo libraries compilation....
for %%f IN (*.pll) do ( frmcmp userid=user/pw@srv module=%%f batch=yes module_type=library compile_all=yes window_state=minimize & ECHO %%f )
ECHO libraries compilation finished ---
Echo menus compilation....
for %%f IN (*.mmb) do ( frmcmp userid=user/pw@srv module=%%f batch=yes module_type=menu compile_all=yes window_state=minimize & ECHO %%f )
ECHO menus compilation finished ---
Echo forms compilation....
for %%f IN (*.fmb) do ( frmcmp userid=user/pw@srv module=%%f batch=yes module_type=form compile_all=yes window_state=minimize & ECHO %%f )
ECHO forms compilation finished ---
Echo compiling reports ....
for %%f IN (*.rdf) do rwconverter userid=sit/sit@SIT batch=yes source=%%f stype=rdffile DTYPE=REPFILE compile_all=yes OVERWRITE=yes logfile=log.txt
window_state=minimize & ECHO %%f )
ECHO --------------------------------------------------------------------
ECHO Report Compilation complete

Then save it as "Compile.bat"
# replace userid=user/pw@srv with your own credentials
# place this bat file in folder which you need to compile forms in it

Whenever you need to compile forms and reports run this bat file.

Enjoy and learn others...

UPDATE :
If the previous batch file does not work try to replace frmcmp and rwconverter with the original EXE file in your Oracle folder , in my case it's :
d:\Oracle\Middleware\Oracle_Home\bin\frmcmp
d:\Oracle\Middleware\Oracle_Home\bin\rwconverter

It should work for all Oracle versions up to 12c

Thanks....

Forms offline editor

While we are working on a large project which is serving HMS and ERP sectors for hundreds of customers , we need to edit specific parts in forms and reports like replacing Program units or modifying it .

I found a very cool tool called : FormsAPI master.
It's not free , but it's very awesome as it has many features like :
it can compare two forms
searching and replacing in multiple forms
this all while forms are closed

you can download it from here http://www.orcl-toolbox.com/formsapimaster/download
or from https://drive.google.com/file/d/11cI4l6ep7VHvCp-5cEBsT8MrgXzIJUv4/view?usp=sharing

Enjoy and learn others...

Undo in Oracle forms

A good way to enable user to undo last changes , I found it by Mr. GERD blog.

Retrieving data from the database and changing the data is really easy. But what, if the user changes data and want to do an UNDO?

Doing a new query is the easiest way. The limitations are:

- in a multi-record-block you have to position in the correct record after the query
- if the query was executed via ENTER-QUERY mode you can't jump to the old record because the query-result has changed.

So you have to use a new technique.

The solution is this function. All database-items get their old values back:

PROCEDURE Undo IS
  V_Block  VARCHAR2 (30) := :SYSTEM.CURSOR_BLOCK;
  V_Field  VARCHAR2 (61);
  V_Item   VARCHAR2 (61);
BEGIN
  Validate (Item_Scope);
  IF :SYSTEM.RECORD_STATUS = 'CHANGED' THEN
    V_Field := Get_Block_Property (V_Block, FIRST_ITEM);
    V_Item := V_Block || '.' || V_Field;
    WHILE V_Field IS NOT NULL
    LOOP
      IF Get_Item_Property (V_Item, ITEM_TYPE) 
         IN ('DISPLAY ITEM', 'CHECKBOX', 'LIST', 
             'RADIO GROUP',  'TEXT ITEM')
      AND Get_Item_Property (V_Item, BASE_TABLE) = 'TRUE' 
      THEN
        COPY (Get_Item_Property (V_Item, DATABASE_VALUE), 
              V_Item);
      END IF;
      V_Field := Get_Item_Property (V_Item, NextItem);
      V_Item := V_Block || '.' || V_Field;
    END LOOP;
  END IF;
END;

Best practice is to start this undo-procedure from a menu (e.g. EDIT - UNDO) or handle it through a shortcut. ,,

Multi-Select from DUAL

Here's a hidden trick inside SQL .

An easy way to generate records from scratch is using an easy CONNECT BY against DUAL.

e.g. you need a Forms-LOV which shows the last 12 months.
So you have to create a record-group-select which gives you exactly 12 records. After that you combine it with sysdate. Let's see:

 SELECT Level LVL
   FROM Dual
CONNECT BY Level <= 12;

then you integrate the sysdate into the statement:


 SELECT add_months (trunc (sysdate, 'MM'), -1*Level) Month
   FROM Dual
CONNECT BY Level <= 12;    ,,


Thanks to

GERD VOLBERG

copy all files and folders using CMD

Copying files from one folder to another , may be hard for permissions or when using security software.

I found the following method to copy all files and subfolders too from one place to an other , using command prompt.

XCOPY D:\Source_Folder F:\Destination_Folder /h/i/c/k/e/r/y

I recommend using this method as it's really very fast and ram non-consuming

Source : StackOverFlow

ORACLE ly

My blogspot name was generated automatically using Shopify .