Apex Recipe: May 2020

Sunday, May 10, 2020

How to expand navigation menu on hover in oracle apex

Oracle Apex application navigation menu has two types of position one is Top and another is Side. If your settings are like this image then after login at your application, navigation menu would be collapsed with icon because Collapse Mode is Icon(Default). It will expand after clicking the navigation icon from navigation menu.

How to expand navigation menu on hover

If you want to expand your navigation menu on hover (when you mouse pointer will come) then follow the simple steps of this video.

Click the Download link to get the code that are used in this video.

Download

Think it helps you. Good luck.

Friday, May 8, 2020

What happened on 20.1 API references in oracle apex

Slit your string with APEX_STRING_UTL

Think you have a large text phrase and now you have to separate web addresses or email addresses or links or specific phrases from here. What you will do now?

Don't worry! In new release 20.1 of Apex API reference introduce APEX_STRING_UTL as extension of APEX_STRING to do such complex task easily. APEX_STRING_UTL has the following functions:

REPLACE_WHITESPACE Function
FIND_FILE_EXTENSION Function
FIND_PHRASE Function
FIND_PHRASES Function
FIND_EMAIL_ADDRESSES Function
FIND_EMAIL_FROM Function
FIND_EMAIL_SUBJECT Function
FIND_TAGS Function
FIND_LINKS Function
GET_DOMAIN Function
FIND_IDENTIFIERS Function
TO_DISPLAY_FILESIZE Function
TO_SLUG Function

Example 1:

select APEX_STRING_UTIL.TO_DISPLAY_FILESIZE(13312312) from dual;
-> 12.7MB

Example 2:

select APEX_STRING_UTIL.FIND_FILE_EXTENSION('foo.xlsx') from dual
-> xlsx

There are very effective functions in APEX_STRING_UTL are waiting for you.

Manage Interactive Grid with APEX_IG

This is also brand new API reference for Oracle Apex developers just like APEX_IR. By this package you will be able to add filter, delete saved report as well as clear or reset the IG report. APEX_IG has the following procedures and functions:

ADD_FILTER Procedure Signature 1
ADD_FILTER Procedure Signature 2
CHANGE_REPORT_OWNER Procedure
CLEAR_REPORT Procedure Signature 1
CLEAR_REPORT Procedure Signature 2
DELETE_REPORT Procedure
GET_LAST_VIEWED_REPORT_ID Function
RESET_REPORT Procedure Signature 1
RESET_REPORT Procedure Signature 2

Others update at 20.1

APEX_AUTHENTICATION is updated in this release with new PERSISTENT_COOKIES_ENABLED function and update of SEND_LOGIN_USERNAME_COOKIE.

APEX_JSON has added new function GET_CLOB_OUTPUT for returning temporary CLOB.

APEX_INSTANCE_ADMIN add some parameters to get and set more control on workspace, instance, password and admin password. Here is the following parameters.

WORKSPACE_NAME_USER_COOKIE
PASSWORD_NOT_LIKE_USERNAME
PASSWORD_NOT_LIKE_WORDS
PASSWORD_NOT_LIKE_WS_NAME
PASSWORD_ONE_ALPHA
PASSWORD_ONE_LOWER_CASE
PASSWORD_ONE_NUMERIC
PASSWORD_ONE_PUNCTUATION
PASSWORD_ONE_UPPER_CASE
SERVICE_ADMIN_PASSWORD_MIN_LENGTH
SERVICE_ADMIN_PASSWORD_NEW_DIFFERS_BY
SERVICE_ADMIN_PASSWORD_ONE_ALPHA
SERVICE_ADMIN_PASSWORD_ONE_NUMERIC
SERVICE_ADMIN_PASSWORD_ONE_PUNCTUATION
SERVICE_ADMIN_PASSWORD_ONE_UPPER_CASE
SERVICE_ADMIN_PASSWORD_ONE_LOWER_CASE
SERVICE_ADMIN_PASSWORD_NOT_LIKE_USERNAME
SERVICE_ADMIN_PASSWORD_NOT_LIKE_WS_NAME
SERVICE_ADMIN_PASSWORD_NOT_LIKE_WORDS

Deprecated and desupported at 20.1

You have to use OAUTH_AUTHENTICATE_CREDENTIAL intead of OAUTH_AUTHENTICATE Procedure Signature 2. Because OAUTH_AUTHENTICATE Procedure Signature 2 is deprecated from 20.1.

NB: You need minimum 20.1 version of Oracle Apex to run these API references. For more you can visit API documentation.

Think its help you. Good Luck.

Tuesday, May 5, 2020

Importance of PREPARE_URL for redirecting in oracle apex

An application which developed by Oracle Apex, is a web application with bunch of pages for presenting data and for communicating with oracle database server. Within this application an end-user travels one page to another page.

When an end-user redirects one page to another, we must have to make it secure as we prevent unwanted DML request. PREPARE_URL function of APEX_UTIL package helps to make secure redirecting when we pass value from page item of one page to page item of another page.

Firstly we have to understand how oracle apex URL construct. See the example before 20.1

f?p=App:Page:Session:Request:Debug:ClearCache:itemNames:itemValues:PrinterFriendly

After launching friendly URL from 20.1, URL looks like.

https://apex.oracle.com/pls/apex/zigzag/r/apex-recipe/home?session=714253588663910

The construction of URL is still same, it just changes appearance.

If we want to communicate with database server from any page of our application then we have to set page access protection "Arguments Must Have Checksum" from page properties.

What checksum does?
Checksum make our actual redirecting value to &cs=<large hex value>. to restrict user from changing actual value of URL.

Have a look at the messages about page access protection from oracle apex.

Unrestricted

The page may be requested using a URL, with or without session state arguments, and without having to have a checksum.
Arguments Must Have Checksum: If Request, Clear Cache, or Name/Value Pair arguments appear in the URL, a checksum must also be provided.
The checksum type must be compatible with the most stringent Session State Protection attribute of all the items passed as arguments.
No Arguments Supported: A URL may be used to request the page, but the URL can not contain Request, Clear Cache, or Name/Value Pair arguments.
No URL Access: The page may not be accessed using a URL.
However, the page may be the target of a Branch to Page branch type, as this does not perform a URL redirect.

Let's hear a good news. All the default redirecting scopes from oracle apex provide checksum redirecting. So, LINK or branching or redirecting from attributes are safe to use. But when we make a URL manually to redirect we must have to be cautious.

In the end, use APEX_UTL.PREPARE_URL to redirect with valid 'SESSION' level arguments.

DECLARE
    l_url varchar2(2000);
    l_app number := v('APP_ID');
    l_session number := v('APP_SESSION');
BEGIN
    l_url := APEX_UTIL.PREPARE_URL(
        p_url => 'f?p=' || l_app || ':10:'||l_session||'::NO::P10_ITEM:ABC',
        p_checksum_type => 'SESSION');
END;

You can use APEX_UTL.PREPARE_URL within anchor tag (<a> tag) like,

<a href="'|| APEX_UTIL.PREPARE_URL(
   p_url => 'f?p=' || V('APP_ID') || ':27:'|| V('APP_SESSION') ||'::NO::P27_GUEST_ID:'|| ID ||'',
   p_checksum_type => 'SESSION')||'">
Link Text</a>

And if your user is public user and page is public page then use this code.

APEX_UTIL.PREPARE_URL(p_url => 'f?p=' || :APP_ID || ':10:'|| :APP_SESSION
||'::NO::P10_ITEM:ABC');

Think it helps you. Good luck.

Sunday, May 3, 2020

Complete guide to change or reset internal admin password of oracle apex

There is an authentication mechanism of Oracle Apex for confirming security of its content. For securing, Oracle Apex has one main workspace named INTERNAL with username ADMIN and it's password.

If you forget the password of ADMIN's username for INTERNAL workspace, then you are in a big trouble.

Don't worry! here are two ways to change or reset internal admin password:

By executing apxchpwd.sql script.
By executing some manual script

Follow these steps to execute apxchpwd.sql script.

1. Open command prompt (cmd) or terminal.

2. Go to your apex (cd e:\oraclexe\apex) directory.

3. Login to database as sysdba.

4. Type @apxchpwd.sql and press enter. Then follow the steps that comes.

Follow these steps to execute some manual script.

1. Open command prompt (cmd) or terminal.

2. Login to database as sysdba.

3. Change schema to your apex schema. Script to help you,

ALTER SESSION SET CURRENT_SCHEMA = APEX_190200;

NB: Here, "APEX_190200" is your apex schema.

4. Then run this query to get userid of admin username.

SELECT RTRIM (MIN (user_id)) user_id
  FROM wwv_flow_fnd_user
 WHERE security_group_id = 10 AND user_name = UPPER ('ADMIN');

5. To change or reset password of ADMIN username,

UPDATE wwv_flow_fnd_user
   SET web_password = 'Apexpassword1#'
 WHERE user_name = 'ADMIN' AND user_id = '1820122815444026';

NB: Here, "1820122815444026" is the userid that you get from previous query.

6. To unlock the ADMIN username execute the script below.

BEGIN
   WWV_FLOW_SECURITY.g_security_group_id := 10;
   WWV_FLOW_FND_USER_API.unlock_account ('ADMIN');
   COMMIT;
END;

If you use Toad or SQL Developer then you can start from step 4 by direct login your apex schema.

Tested on 5.0, 5.1, 18.1, 18.2, 19.1 and 19.2 version of Oracle Apex.

Think it helps you. Good Luck.

How to import data from excel's csv file into oracle database

If you want to import data from external file like excel's .csv or .xlsx and when you have to consider more then 50 columns, then collection or apex_collection don't help you to do it.

At this point, UTL_FILE package will help you to do it very easily. Let's see how this amazing package works.

Firstly, you have to create a directory at your server. Here are scripts to help you.

CREATE OR REPLACE DIRECTORY CSV_DIR AS 'c:\oracle\csv_files';
GRANT READ, WRITE ON DIRECTORY CSV_DIR TO HR;

Then, write a procedure at HR schema to place the .csv blob file to the CSV_DIR directory.

CREATE OR REPLACE PROCEDURE pro_save_csv_to_dir (
   p_csv_blob   IN OUT NOCOPY BLOB,
   p_dir          IN            VARCHAR2,
   p_filename     IN            VARCHAR2)
AS
   v_file       UTL_FILE.file_type;
   v_buffer     RAW (32767);
   v_amount     BINARY_INTEGER := 32767;
   v_pos        INTEGER := 1;
   v_blob_len   INTEGER;
BEGIN
   v_blob_len := DBMS_LOB.getlength (p_csv_blob);

   v_file :=
      UTL_FILE.fopen (p_dir,
                      p_filename,
                      'wb',
                      32767);

   WHILE v_pos <= v_blob_len
   LOOP
      DBMS_LOB.read (p_csv_blob,
                     v_amount,
                     v_pos,
                     v_buffer);
      UTL_FILE.put_raw (v_file, v_buffer, TRUE);
      v_pos := v_pos + v_amount;
   END LOOP;

   UTL_FILE.fclose (v_file);
EXCEPTION
   WHEN OTHERS
   THEN
      UTL_FILE.fclose (v_file);
      RAISE;
END;

NB: Here, "p_csv_blob" is your .csv blob file, "p_dir" is your created directory name.

After successfully placing your .csv blob file to the directory, write another procedure to insert data from file into a database table.

CREATE OR REPLACE PROCEDURE pro_insert_from_dir (
   p_dir          IN     VARCHAR2,
   p_file_name    IN     VARCHAR2,
   p_table_name   IN     VARCHAR2,
   p_out_msg         OUT VARCHAR2)
AS
   v_values        VARCHAR2 (20000);
   v_values2       VARCHAR2 (20000);
   sqlstmt         VARCHAR2 (20000);

   acc_open_file   UTL_FILE.file_type;
BEGIN
   acc_open_file :=
      UTL_FILE.fopen ('' || p_dir || '', '' || p_file_name || '', 'R');

   LOOP
      BEGIN
         UTL_FILE.get_line (acc_open_file, v_values);
         DBMS_OUTPUT.put_line (v_values);

         SELECT REPLACE (v_values, ',', ''',
                         ''')
           INTO v_values2
           FROM DUAL;

         sqlstmt :=
               'insert into '
            || p_table_name
            || ' (col1, col2, col3) values('''
            || v_values2
            || ''')';

         EXECUTE IMMEDIATE sqlstmt;

         DBMS_OUTPUT.put_line (sqlstmt);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            EXIT;
      END;
   END LOOP;

   UTL_FILE.fclose (acc_open_file);
END;

NB: Here "col1, col2, col3" is the column from table to maintain the sequence of .csv file's column.

You must have to remember,

In this procedure, comma (,) is used as separator. So, no other comma (,) is allowed at your .csv file.
Each and every row of .csv file would consider as a record. So, cut the heading column from .csv file before uploading.

If you don't want to consider above terms, then you should upload and import .xlsx file.

Saturday, May 2, 2020

How to import data from excel's xlsx file into oracle database

Think, if we can consider a row of an excel file as a record or row of table and a column as a table column. Is it possible at all? Yeap, Now it's possible. Anton Scheffer's as_read_xlsx package helps me to make it possible.

You will be able to direct query from excel's xlsx file in oracle database by using as_read_xlsx package. And then, manipulate your data as you want. Download this package from the following link.

Download

After downloading, there are two ways to use this xlsx file.

Read xlsx from a directory.
Read xlsx from a blob column of database.

Follow these steps to read xlsx from a directory:
1. Execute the script to create package in oracle database.
2. To contain the xlsx file, create a directory object. Here are scripts to help you.

CREATE OR REPLACE DIRECTORY XLSX_DIR AS 'c:\oracle\xlsx_files';
GRANT READ, WRITE ON DIRECTORY XLSX_DIR TO HR;

3. Place xlsx file to this directory.
4. You can write a query like this,

SELECT * 
FROM TABLE(as_read_xlsx.read(as_read_xlsx.file2blob('XLSX_DIR', 'file.xlsx')));

The result would be viewed as like as a query from a table. Now you can use this query in PL/SQL procedure as a cursor and insert data into database table.

Follow these steps to read xlsx from a blob column of database:
1. Execute the script to create package in oracle database.
2. Upload xlsx file into a blob column in table.
3. You can write query like this,

      WITH xlsx
           AS (SELECT ROW_NR,
                      COL_NR,
                      CASE CELL_TYPE
                         WHEN 'S' THEN STRING_VAL
                         WHEN 'N' THEN TO_CHAR (NUMBER_VAL)
                         WHEN 'D' THEN TO_CHAR (DATE_VAL, 'MM/DD/YYYY')
                         ELSE FORMULA
                      END
                         CELL_VAL
                 FROM (SELECT *
                         FROM TABLE (as_read_xlsx.read (
                                        (SELECT blob_column
                                           FROM xlsx_files
                                          WHERE xlsx_id = 1),
                                        'Sheet1'))))
      SELECT xl.ID,
             xl.FIRST_NAME,
             xl.LAST_NAME,
             xl.FATHER_NAME
        FROM xlsx PIVOT (MAX (CELL_VAL)
                  FOR COL_NR
                  IN  (1 AS ID,
                      2 AS FIRST_NAME,
                      3 AS LAST_NAME,
                      4 AS FATHER_NAME)) xl
       WHERE row_nr > 1;

NB: Here, "xlsx_files" is database table, "blob_column" is blob column of table, "xlsx_id" is unique key of table, "Sheet1" is the sheet name of excel's xlsx file and "row_nr > 1" is used to cut the row from header of excel's xlsx file.

Again, the result would be viewed as like as a query from a table. Now you can use this query in PL/SQL procedure as a cursor and insert data into database table.

Remember,

If you have lot of data at your .xlsx file then it will take some time. But if you can upload and import file from .csv then it would more faster and easier.

Think it helps you. Good luck.

You may also like:

How to import data from excel's csv file into oracle database