Dynamic Side Navigation Menu in Oracle Apex

Image File store outside of Oracle Database and show image into Apex Application

Directory Create :

grant execute on utl_file to HRMS;

CREATE OR REPLACE DIRECTORY STOCK_DIRECTORY AS 'C:\Program Files (x86)\Apache Software Foundation\Tomcat 9.0\webapps\i\IMAGE_STORE';

 

GRANT read, write ON DIRECTORY STOCK_DIRECTORY TO HRMS;

Create Procedure In Database :

CREATE OR REPLACE PROCEDURE HRMS.blob_to_file (p_blob      IN OUT NOCOPY BLOB,

                                          p_dir       IN  VARCHAR2,

                                          p_filename  IN  VARCHAR2)

AS

  l_file      UTL_FILE.FILE_TYPE;

  l_buffer    RAW(32767);

  l_amount    BINARY_INTEGER := 32767;

  l_pos       INTEGER := 1;

  l_blob_len  INTEGER;

BEGIN

  l_blob_len := DBMS_LOB.getlength(p_blob);

  

  -- Open the destination file.

  l_file := UTL_FILE.fopen(p_dir, p_filename,'WB', 32767);

  -- Read chunks of the BLOB and write them to the file until complete.

  WHILE l_pos <= l_blob_len LOOP

    DBMS_LOB.read(p_blob, l_amount, l_pos, l_buffer);

    UTL_FILE.put_raw(l_file, l_buffer, TRUE);

    l_pos := l_pos + l_amount;

  END LOOP;

  

  -- Close the file.

  UTL_FILE.fclose(l_file);

  

EXCEPTION

  WHEN OTHERS THEN

   -- Close the file if something goes wrong.

    IF UTL_FILE.is_open(l_file) THEN

      UTL_FILE.fclose(l_file);

    END IF;

    RAISE;

/* WHEN UTL_FILE.invalid_operation THEN dbms_output.PUT_LINE('cannot open file invalid name');

WHEN UTL_FILE.read_error THEN dbms_output.PUT_LINE('cannot be read');

WHEN no_data_found THEN dbms_output.PUT_LINE('end of file');

UTL_FILE.fclose(l_file);

 RAISE;*/

END blob_to_file;

/

Create Process To Insert Image  

DECLARE

l_blob BLOB;

VID NUMBER;

BEGIN

SELECT NVL(MAX(NVL(EMPLOYEE_ID,0)),0)+ 1 INTO VID FROM                      EMPLOYEE_INFORMATION_IMAGE ;

 

 select BLOB_CONTENT INTO l_blob FROM apex_application_temp_files

where NAME= :P7_IMAGE ;

blob_to_file(p_blob => l_blob,

p_dir => 'STOCK_DIRECTORY',

p_filename => VID||'.png');

INSERT INTO EMPLOYEE_INFORMATION_IMAGE(EMPLOYEE_ID,EMPLOYEE_NAME,DESIGNATION,JOINING_DATE,FATHERS_NAME)

VALUES(VID,:P7_EMPLOYEE_NAME,:P7_DESIGNATION,:P7_JOINING_DATE,:P7_FATHER_NAME);

END;

 

Delete Specific Row From Interactive Grid In Oracle Apex

1. Create a New Report Column =>>Type=>>Link

     Target  ======>>  javascript:void(null);

     Link Text ====>>  <span class="t-Icon fa fa-trash-o" aria-hidden="true"></span>

    Link Attribute =>>  id='&DEDUCTION_ID.' class="delete t-Button t-Button--danger t-Button--simple                                     t-Button--small" title="Delete Allowance : &DEDUCTION."

2. Create New Item In Report Region :

    Create Dynamic Action : Event ==>> Click ; Selection Type ==>> jQuery Selector;

                                            jQuery Selector ==>> a.delete

   Create True Action: Confirm;

   Create True Action: Set Value ==>> Set Type ==>> JavaScript Expression

                                                              JavaScript Expression ==>> this.triggeringElement.id

   Create True Action: Execute PL/SQL Code 

            PL/SQL Code: UPDATE DEDUCTION_SETUP SET STATUS=0  WHERE                                                         DEDUCTION_ID=:P43_DEDUCTION_ID AND BRANCH_CODE=:BRANCH_CODE;

   Create True Action : Refresh Report Region.

How can use Apex Collection In Oracle Apex

Insert Data Into Apex Collection :

begin

if not apex_collection.collection_exists('EMPLOYEE_COLLECTION') then

apex_collection.create_collection('EMPLOYEE_COLLECTION');

end if;

apex_collection.add_member (

p_collection_name=>'EMPLOYEE_COLLECTION',

p_c001=>:P2_EMPLOYEE_ID,

p_c002=>:P2_EMPLOYEE_NAME,

p_c003=>:P2_FATHERS_NAME,

p_c004=>:P2_MOTHERS_NAME,

p_c005=> :P2_DESIGNATION

);

end;

Data Show Into Report From Apex Collection :

SELECT SEQ_ID,c001 EMPLOYEE_ID,c002 EMPLOYEE_NAME,c003 FATHERS_NAME,c004 MOTHERS_NAME,c005 DESIGNATION 

from APEX_COLLECTIONS where COLLECTION_NAME='EMPLOYEE_COLLECTION' ; 

 

Insert Data Into Database From Apex Collection : 

IF apex_collection.collection_exists('EMPLOYEE_COLLECTION') THEN

DECLARE

VID NUMBER;

 BEGIN

  

    INSERT INTO EMPLOYEE_DETAILS(EMPLOYEE_ID,EMPLOYEE_NAME,FATHERS_NAME,MOTHERS_NAME,DESIGNATION)

    SELECT c001,c002,c003,c004, c005 FROM  APEX_COLLECTIONS where COLLECTION_NAME='EMPLOYEE_COLLECTION';

END;

    begin

    apex_collection.delete_collection(p_collection_name =>'EMPLOYEE_COLLECTION');

    end;

   

END IF;

Delete Apex Member From Apex Collection : 

if  apex_collection.collection_exists('EMPLOYEE_COLLECTION') then

begin

    APEX_COLLECTION. DELETE_MEMBER( p_collection_name => 'EMPLOYEE_COLLECTION' , p_seq =>:P2_SELECTED_ID);

end;

end if;

Oracle Apex & ORDS Installation & Apache Tomcat Server Configuration

Apex Configuration :

1. Run--------@apexins.sql sysaux sysaux temp /i/

2. Go To Apex Location Folder => Run--------@apex_epg_config.sql  D:/Oracle/apex/apex <parent of apex directory> (@apex_epg_config.sql)

3. @apxconf.sql
4. alter user anonymous account unlock;

5. @apxconf.sql

6. @apex_rest_config.sql
7. Unlock All Locked User (

                select username,account_status from dba_users  where username like 'APEX%';

                select username,account_status from dba_users  where username like  'ORDS%';

       OR

        select username,account_status from dba_users where lock_date is not null

                 and username like 'APEX%'; )

Run Procedure For ACL Create

 DECLARE

  ACL_PATH  VARCHAR2(4000);

BEGIN

  -- Look for the ACL currently assigned to '*' and give APEX_050100----------( Or APEX_050000)

  -- the "connect" privilege if APEX_050100 does not have the privilege yet.

 

  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS

   WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

 

  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_050100',

     'connect') IS NULL THEN

      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,

     'APEX_050100', TRUE, 'connect');

  END IF;

 

EXCEPTION

  -- When no ACL has been assigned to '*'.

  WHEN NO_DATA_FOUND THEN

  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',

    'ACL that lets power users to connect to everywhere',

    'APEX_050100', TRUE, 'connect');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');

END;

/

COMMIT;

DECLARE

  ACL_PATH  VARCHAR2(4000);

BEGIN

  -- Look for the ACL currently assigned to 'localhost' and give APEX_050100

  -- the "connect" privilege if APEX_050100 does not have the privilege yet.

  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, 'APEX_050100',

     'connect') IS NULL THEN

      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,

     'APEX_050100', 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',

    'APEX_050100', TRUE, 'connect');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('local-access-users.xml','localhost');

END;

/

COMMIT;

ORDS Configuration :

1. Go To ORDS Location=======>>D:\Oracle\ords

         Connect SQL Plus & Execute===>>EXEC DBMS_XDB.SETHTTPPORT(0);

2. Copy Image Folder From Apex Folder To Ords Folder.

3. java -jar ords.war instal

 Enter the location to store configuration data:C:\Oracle\OracleApp\apex\ords\cofig
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 SID1]:1
Enter the database service name:orcl
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
Please login with SYSDBA privileges to verify Oracle REST Data Services schema.
Enter the username with SYSDBA privileges to verify the installation [SYS]: sys
Enter the database password for sys:
Confirm password:
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step:2
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:1
Enter the database password for APEX_PUBLIC_USER:
Confirm password:
   Enter 1 to specify passwords for Application Express RESTful Services database
   sers (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:1
   Enter the database password for APEX_LISTENER:
   Confirm password:
   Enter the database password for APEX_REST_PUBLIC_USER:
  Confirm password:
Enter 1 if you wish to start in standalone mode or 2 to exit [1]:1
Enter the APEX static resources location:C:\Oracle\OracleApp\apex\ords\images
Enter 1 if using HTTP or 2 if using HTTPS [1]:1

Internal Server Error (500):

alter system flush buffer_cache;

alter system flush shared_pool;

Apex ORDS Database Verification : (When Image Showing Problem then validate database) :

java -jar ords.war validate --database apex;

Apex ORDS 404 Error:(The request could not be mapped to any database. Check the request URL is correct, and that URL to database mappings have been correctly configured) :

1.SELECT username, account_status, created, lock_date, expiry_date FROM dba_users WHERE account_status != 'OPEN';

2.Then perform a bunch of “alter user xxx identified as xxxx account unlock”.

3.After you have open every necessary user account, perform the following in your console as administrator:

After Run Command(Java -jar ords.war setup)