Setter & Getter API in Oracle APEX

I have created a package in order to easy usage for set & get values in Oracle APEX.

Usage:

-Use the View with parameters

-Use Dynamic parameters in Query

-Set and get temporary data

Demo application: OAC

Download files and sample application via APEX-SetterGetterAPI

How to use this package in Database:

Run APEX_PKG_PARAMS.sql in database where you want to use it. Next to Follow below examples for clarifying the using of this API.

Sample 1

--One-dimensional
--Set two parameters
APEX_PKG_PARAMS.setparam(p_param=>'MANAGER',p_index=>1);
APEX_PKG_PARAMS.setparamnum(p_param=>10,p_index=>1);

--Get two parameters
--Use in query
SELECT "EMPNO",
          "ENAME",
          "JOB",
          "MGR",
          "HIREDATE",
          "SAL",
          "COMM",
          "DEPTNO"
FROM emp
WHERE     job = APEX_PKG_PARAMS.getparam (p_index =>1,p_seq=>1)
AND       deptno = APEX_PKG_PARAMS.getparamnum (p_index =>1,p_seq=>1)

Sample 2

--Multidimensional
--Set parameters
declare
    l_filecontent BLOB;
    l_filename VARCHAR2(400);
begin

    If :P28_UPLOAD_FILE Is Not Null Then
        Select FILENAME, BLOB_CONTENT
        Into   l_filename, l_filecontent
        From   apex_application_temp_files
        Where  NAME = :P28_UPLOAD_FILE;

        APEX_PKG_PARAMS.setparamnum(p_param => :P28_FILE_TYPE,
                                          p_count_seq => 4,
                                          p_seq  => :P28_SEQ);
        APEX_PKG_PARAMS.setparamblob(p_param => l_filecontent,
                                          p_seq  => :P28_SEQ);
        APEX_PKG_PARAMS.setparam(p_param => l_filename,
                                       p_seq  => :P28_SEQ);   
    End If;                                   
end;
----Get blob parameter
APEX_PKG_PARAMS.getparamblob (p_index =>1,p_seq=>1);

How to use this package in APEX:

We should use this packages according the instructions.

Step 1

In this step you must create a process and add pl/sql code

APEX_PKG_PARAMS.setparam(p_param=>'');
APEX_PKG_PARAMS.setparamnum(p_param=>null);

To add a sever-side Condition in order to control for calling this process

Step 2

Now, we created a view with parameters that usage in where clause.

CREATE OR REPLACE FORCE VIEW VIEW_EMP
(
   EMPNO,
   ENAME,
   JOB,
   MGR,
   HIREDATE,
   SAL,
   COMM,
   DEPTNO
)
AS
   SELECT "EMPNO",
          "ENAME",
          "JOB",
          "MGR",
          "HIREDATE",
          "SAL",
          "COMM",
          "DEPTNO"
     FROM emp
    WHERE     job = COALESCE (APEX_PKG_PARAMS.getparam (p_index => 1), job)
    AND       deptno = COALESCE (APEX_PKG_PARAMS.getparamnum (p_index => 1), deptno);

Next, we added three buttons in order to show the operation of setter & getter.

Step 3

we set the pl/sql code like a below code for first button

APEX_PKG_PARAMS.setparam(p_param=>'MANAGER',p_index=>1);
APEX_PKG_PARAMS.setparamnum(p_param=>10,p_index=>1);

we repeat it for another button

Step 4

So if we want to remove the value of parameters must be to add this code.

APEX_PKG_PARAMS.purgeparams;

I hope to use and enjoy it.