Ontoor Solutions Pvt. Ltd.

Remove Special Character and Extra Space From a String ORACLE/PLSQL Removing special character using REGEXP: SELECT REGEXP_REPLACE('Sahay*)(^#@Pandey$ashish/Vikas','[^a-z_A-Z ]') FROM DUAL; Remove extra spaces between string using REGEXP: SELECT REGEXP_REPLACE ('Vikas                      Pandey from              Delhi', '[[:blank:]]+', ' ') FROM DUAL; Related Post:  How to Remove New line from a String Using PL/SQL/ORACLE

How to create Pipelined Functions in Oracle?  Pipeline functions are used to return data in tabular format. It returns the data in the nested table type. Pipeline functions are useful to dump data derived from the complex queries. Create the types  CREATE TYPE g_emp_row AS OBJECT (    empno NUMBER (4),    ename VARCHAR2 (10 BYTE),    job VARCHAR2 (9 BYTE),    mgr NUMBER (4),    hiredate DATE,    sal NUMBER (7, 2),    comm NUMBER (7, 2),    deptno NUMBER (2) ); / CREATE TYPE g_emp_tab IS TABLE OF g_emp_row; / Pipeline function  CREATE OR REPLACE FUNCTION get_employees (pi_deptno IN NUMBER)    RETURN g_emp_tab    PIPELINED AS    CURSOR c_get_emp (p_deptno NUMBER)    IS       SELECT empno,                         ename,                         job,                         mgr,                         hiredate,                         sal,                         comm,                         deptno         FROM emp em        WHERE deptno = p_deptno; BEGIN    FOR l_cnt IN c_get_emp (pi_dep

How to Change application language after login in ORACLE APEX? In the previous article , we have seen how to translate the application. But what if the user logs in and then want to change the language.  Create one dialog page with a select list which holds the list of language. Item Setting :  Page Action on Selection : Submit Page Create Entry in Navigation Bar as Language Go to Shared Components Navigation Bar List Desktop Navigation Bar Name as Language  Choose the Icon Set the Target page of the Dialog page

How to refresh Static region data in ORACLE APEX? I have been receiving couple of queries about the static region refresh. In this article, I'll try to get through it. Background: In the Interactive and classic reports data refresh from database is done by AJAX. APEX internally calls the AJAX callback to fetch the data and refresh the report. Reports are followed by their templates, meaning the data is being fetched and merged/replaced with the report templates. Partial refresh during the report search or applying filters also uses the AJAX callback to update and refresh data. Let's try out We need one AJAX callback, One layout, one refresh event and Static Region. Create an AJAX callback Process. There are two places to create it Shared component: Application process - Point AJAX Callback Page Process: Point- AJAX Callback Page Process Application Process Process Name : GET_EMP_DATA Processing Point : AJAX Callback Process code : BEGIN   APEX_JSON.initialize_clob_output;   APE