Create PDB , Alter/Open , TNSNAMES Configer, Drop PDB

CREATE PDB(Plaggable Database)
================================
create pluggable database NAME
admin user 
   app_admin identified by Password
file_name_convert = ('/pdbseed/', '/NAME/');



alter pluggable database NAME open;

TNSNAMES.ORA
================================
Path:
cd /u01/app/oracle/product/12.1.0/db_1/network/admin/

NAME =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.022)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = NAME)
    )
  )


DROP PDB
================================

alter pluggable database NAME close;

drop pluggable database NAME including datafiles;

Source: http://www.dba-oracle.com/t_pluggable_database.htm

Text Generation by PL/SQL

-- Create directory 
create or replace directory EXPIMP_DIR  as 'E:\hira';

--PL/SQL Block

DECLARE
v_file  UTL_FILE.FILE_TYPE;
BEGIN
  v_file := UTL_FILE.FOPEN(location     => 'EXPIMP_DIR',
                           filename     => 'New_Card_Test.txt',
                           open_mode    => 'w',
                           max_linesize => 32767);
                           
  FOR cur_rec IN (select sl,data_desc from tt order by sl) LOOP
  
    UTL_FILE.PUT_LINE(v_file,cur_rec.data_desc);
  
  END LOOP;
  UTL_FILE.FCLOSE(v_file);
    
EXCEPTION
  WHEN OTHERS THEN UTL_FILE.FCLOSE(v_file); RAISE;
END;

Global Back Button in Oracle Apex Using JavaScript

Back Button in Oracle Apex by doing this you can go back your immediate previous page.

1. Create a button with name "Back"

2. Button Properties >> Action When Button Clicked >>Action>> Redirect to URL >> URL Target>>    javascript:history.go(-1) 

And Enjoy 

Call Process From Dynamic Action in Oracle Apex 5.1

Some time we have need long PL/SQL block or raise_applicaiton_error and many other things which we can do in Apex Process level but can not do in Dynamic Action.

Now here I am show how to call a Process from item  Dynamic Action.

1. Create a Item and create Dynamic Action on this Item. 

2. This Dynamic Action Event can be anything but Action will Execute JavaScript Code and right this JavaScript Code >> Settings.

apex.submit({  request:"ID", showWait:true});

3.  Here "ID" is a reference to call a process. 

4.  Now a Create One or multiple Process with what ever as your wish.

5.  Go to Process >>Properties >> Conditions>> Condition Type >>Select :- Request= Expression 1 >> Expression 1 >> ID (reference)

That's It. Now enjoy Process form Dynamic Action.

Make a Oracle Apex 5.1 Item Readonly

Make a Oracle Apex 5.1 Item Readonly. It's not mean Display Only item type. By doing this you can just read a input text field but can not edit or remove.

1. Make a Item in a Region.

2. Go to Item properties >> Element >>HTML Form Element Attributes >> readonly

and Save.   

RESTful Get API Example in Oracle Apex

DECLARE
   l_param_list      VARCHAR2 (512);
   l_http_request    UTL_HTTP.req;
   l_http_response   UTL_HTTP.resp;
   l_response_text   VARCHAR2 (32767);
   l_response        VARCHAR2 (32767);

   v_url             VARCHAR (500);
   url_param_list    VARCHAR2 (512);

   p_userid          VARCHAR2 (500) := '100140';
   p_sessionid       VARCHAR2 (500) := '123456';
   p_companyid       VARCHAR2 (500) := '101';
   p_executionmode   VARCHAR2 (500) := 'S';
   p_studentid       VARCHAR2 (500) := '9005';
   p_authkey         VARCHAR2 (500) := 123456;
   ts                VARCHAR2 (32000)
      := '{"std_info_node":[{"errormessage":"Invalid ID . Please input valid ID","studentname":"Qaium","accountno":"0487878778","mobileno":"01234","errorcode":"1"}]}';

   l_data_count      NUMBER := 0;
   l_values          apex_json.t_values;

   t_timeout         INTEGER;
BEGIN
   v_url := 'http://10.11.150.12/PmsWebService/StudentInfo?';
   url_param_list :=
         'userid='
      || p_userid
      || '&sessionid='
      || p_sessionid
      || '&companyid='
      || p_companyid
      || '&executionmode='
      || p_executionmode
      || '&studentid='
      || :P180_STUDENT_ID                                        
      || '&authkey='
      || p_authkey;

   /*begin
   l_http_request :=  UTL_HTTP.begin_request (v_url, 'GET', 'HTTP/1.1');
   UTL_HTTP.write_text (l_http_request, l_param_list);
   --  raise_application_error(-20007,'1');
   l_http_response := UTL_HTTP.get_response (l_http_request);
   raise_application_error(-20007,'2 : '||l_http_response.status_code);
   end ; */

   UTL_HTTP.get_transfer_timeout (t_timeout);
   UTL_HTTP.set_transfer_timeout (10);
   UTL_HTTP.get_transfer_timeout (t_timeout);

   l_param_list := NULL;
   l_http_request :=
      UTL_HTTP.begin_request (v_url || url_param_list, 'GET', 'HTTP/1.1');
   UTL_HTTP.write_text (l_http_request, l_param_list);
   l_http_response := UTL_HTTP.get_response (l_http_request);

   UTL_HTTP.read_text (l_http_response, l_response_text);

   IF l_response_text IS NULL
   THEN
      raise_application_error (-20003,
                               'Request Does Not Get Any Response !!!');
   END IF;

   apex_json.parse (l_response_text);

   SELECT    'DCMC'
          || REPLACE (TO_CHAR (SYSDATE, 'DD-MM-YY'), '-', '')
          || DCMC_PAYMENT.NEXTVAL
     INTO :P180_REF_NO
     FROM DUAL;

   :P180_NAME :=
      apex_json.get_varchar2 (p_path => 'std_info_node[1].studentname');
   :P180_MOBILE :=
      apex_json.get_varchar2 (p_path => 'std_info_node[1].mobileno');
   :P180_AC_NUMBER :=
      apex_json.get_varchar2 (p_path => 'std_info_node[1].accountno');
   --raise_application_error(-20003,:P180_MOBILE);

   /*
    FOR i IN 1 ..3

    LOOP
   :P180_NAME:= apex_json.get_varchar2 (p_path => 'std_info_node[' || i || '].studentname') ;
   :P180_MOBILE:= apex_json.get_varchar2 (p_path => 'std_info_node[' || i || '].mobileno') ;
   :P180_AC_NUMBER :=apex_json.get_varchar2 (p_path => 'std_info_node[' || i || '].accountno') ;

    -- DBMS_OUTPUT.put_line ('a is '|| apex_json.get_varchar2 (p_path => 'std_info_node[' || i || '].studentname')|| l_data_count);

    END LOOP; */

   UTL_HTTP.end_response (l_http_response);
EXCEPTION
   WHEN UTL_HTTP.end_of_body
   THEN
      UTL_HTTP.end_response (l_http_response);
END;

PL/SQL To JSON Conversion

SET SERVEROUTPUT ON
DECLARE
  l_cursor SYS_REFCURSOR;
BEGIN
  
  OPEN l_cursor FOR
    SELECT e.empno AS "employee_number",
           e.ename AS "employee_name",
           e.deptno AS "department_number"
    FROM   emp e ;
   -- WHERE  rownum <= 2;

  APEX_JSON.initialize_clob_output;

  APEX_JSON.open_object;
  APEX_JSON.write('employees', l_cursor);
  APEX_JSON.close_object;

  DBMS_OUTPUT.put_line(APEX_JSON.get_clob_output);
  APEX_JSON.free_output;
END;

OUTPUT WILL LIKE :

{"employees":[{"employee_number":4,"employee_name":"ZAMAN","department_number":30}
,{"employee_number":2,"employee_name":"ZAMAN","department_number":30}
,{"employee_number":3,"employee_name":"ZAMAN","department_number":30}
,{"employee_number":1,"employee_name":"ZAMAN","department_number":30}
,{"employee_number":6,"employee_name":"ZAMAN","department_number":30}
,{"employee_number":7,"employee_name":"aa","department_number":30}
,{"employee_number":5,"employee_name":"jhjkhkj"}]}

Classic Report Scroll Bar in Oracle Apex

To add scroll bar on classic report .

Use this CSS on Region >> Region Attribute

<div style ="overflow-x: scroll;overflow-y: scroll;height:500px;">
<div style ="overflow:auto;height:220px;"> 

Region Print by JavaScript Clicking on Button And Save PDF in Oracle Apex

1. Create a Region and Do what ever you want.

2. Set a static id of the region. Region properties>>Advance>>Static ID >> exp: printableArea

3. Create a button and use this code. Button properties>>Advance>>Custom Attributes>> 

onclick="printDiv('printableArea')"

4. Use this code on. Region propertice>>Footer Text>>

<script>
function printDiv(printableArea) {
     var printContents = document.getElementById(printableArea).innerHTML;
     var originalContents = document.body.innerHTML;
    document.body.innerHTML = printContents;
     window.print();
     document.body.innerHTML = originalContents;
}
</script>

Now Enjoy Region Print by JavaScript clicking on print button ..

Auto New Row Generation In Interactive Grid Using Bar-code Reader or Manual Input

1. Create a page and a region type Interactive Grid form from a table.
  (using where 1=2 for showing empty grid).

2. Now create a Dynamic action with Change Event and Execute javaScript Action
on column, from where new row will create.

3. Then goto Region Properties Advanced>> Static ID >> type a id like: REG_ID

4.Use below JavaScript for create new from Upper side new row.

apex.region("REG_ID").widget().interactiveGrid("getActions").invoke("row-add-row")
OR

apex.region("REG_ID").widget().interactiveGrid("getActions").invoke("insert-record")
 (for down side new row)

(After making interactive grid if there any primary key problem then go to a primary key column>>
Sours>>Primary Key>>Yes)