Reverse the String without using reverse function

WITH t AS (
    SELECT 'WELCOME TO SQL CLASS' d
    FROM dual
) SELECT LISTAGG(s) WITHIN GROUP(ORDER BY l DESC)
  FROM
    (
        SELECT level l,substr(d,level,1) s
        FROM t
        CONNECT BY level <= length(d)
    );

Sort the NUMBERS in the comma separated list

select regexp_substr('A,BB,CCC,DDDD','[^,]+',1,1) from dual;
select regexp_substr('A,BB,CCC,DDDD','[^,]+',1,2) from dual;
select regexp_substr('A,BB,CCC,DDDD','[^,]+',1,3) from dual;

select regexp_substr('A,BB,CCC,DDDD','[^,]+',1,4) from dual;

-------------------------------------------------------------

WITH t AS (
    SELECT  '3,10,5,1,7,55,75,100,42,12,17' d
    FROM         dual
) 
  SELECT  LISTAGG(s,',') WITHIN GROUP(
    ORDER BY to_number(s))
  FROM(
        SELECT regexp_substr(d,'[^,]+',1,level) s
        FROM   t
        CONNECT BY level <= regexp_count(d,',') + 1
    );

-------------------------------------------------------------

WITH t AS (
    SELECT '1 5 3 6 2 4   7  99   10   76    101    55   15  33 18' d
    FROM dual
) SELECT LISTAGG(s,' ') WITHIN GROUP(ORDER BY to_number(rtrim(ltrim(s) ) ))
  FROM(
        SELECT regexp_substr(regexp_replace(d,'\D+',','),'[^,]+',1,level) s
        FROM t
        CONNECT BY level <= regexp_count(regexp_replace(d,'\D+',','),',') + 1
    );

    
-------------------------------------------------------------

WITH t AS (
    SELECT  'WELCOME' d
    FROM dual
) SELECT LISTAGG(d) WITHIN GROUP( ORDER BY d )
  FROM(
        SELECT substr(d,level,1) d
        FROM t

        CONNECT BY level <= length(d));

SQL to find list of combinations where the third dice roll equal to sum of first and second dice roll

Write a SQL to find the list of combinations, where the third rolled dice value is equal to sum of first and second rolled value

with t as (
select level r
from dual
connect by level <=6)
select t1.r ROLL1,t2.r ROLL2,t1.r + t2.r ROLL3
from t t1,t t2
where t1.r + t2.r in( 6,5,4,3,2)
order by t1.r + t2.r desc;

with t as (select level r
from dual
connect by level<=6)
select t1.r as ROLL1, t2.r as ROLL2, t3.r as ROLL3
from t t1, t t2, t t3
where (t3.r = t1.r+t2.r)
order by 3 desc;

with t as (
select level r
from dual
connect by level <=6)
select t1.r ROLL1,t2.r ROLL2,t1.r + t2.r ROLL3
from t t1,t t2
where t1.r + t2.r <= 6
order by t1.r + t2.r desc;

Write a SQL Find the list of combinations, where any roll value is equal to sum of other two rolled values

with t as (select level r

from dual
connect by level<=6)
select t1.r as ROLL1, t2.r as ROLL2, t3.r as ROLL3
from t t1, t t2, t t3
where (t3.r = t1.r+t2.r or t2.r = t1.r+t3.r or t1.r = t2.r+t3.r)
order by 3 desc;

profile PLSQL code using DBMS_PROFILER

Create the profiler tables from the table creation script available in oracle home directory

@C:\orclee18c\WINDOWS.X64_180000_db_home_ORACLE18C\rdbms\admin\proftab.sql

** Search for "proftab.sql" in your oracle home directory

---

Scripts to clear the profiler tables

truncate table plsql_profiler_data;
delete from plsql_profiler_units;
delete from plsql_profiler_runs;

---

Invoke "dbms_profiler.start_profiler" to start the profiler and "dbms_profiler.stop_profiler" to stop the profiler.Sample code give below,

exec dbms_profiler.start_profiler ('MY_TEST_PERFORMANCE_RUN1');

exec proc_a;

exec dbms_profiler.stop_profiler();

---

Query to find the line wise time taken

SELECT

plsql_profiler_runs.RUN_DATE, 

plsql_profiler_runs.RUN_COMMENT,

plsql_profiler_units.UNIT_TYPE, 

plsql_profiler_units.UNIT_NAME,

plsql_profiler_data.LINE#, 

plsql_profiler_data.TOTAL_OCCUR, 

plsql_profiler_data.TOTAL_TIME, 

plsql_profiler_data.MIN_TIME, 

plsql_profiler_data.MAX_TIME,

round(plsql_profiler_data.total_time/1000000000) total_time_in_sec,

trunc(((plsql_profiler_data.total_time)/(sum(plsql_profiler_data.total_time) over()))*100,2) pct_of_time_taken

FROM

    plsql_profiler_data,plsql_profiler_runs,plsql_profiler_units

where plsql_profiler_data.total_time > 0 

and plsql_profiler_data.runid = plsql_profiler_runs.runid

and plsql_profiler_units.UNIT_NUMBER = plsql_profiler_data.UNIT_NUMBER

and plsql_profiler_units.runid = plsql_profiler_runs.runid

ORDER BY

    plsql_profiler_data.total_time DESC;

Named Cursor Vs REF Cursor in Oracle

Named Cursor Vs REF Cursor in Oracle

--------------------------------------------------------------------
set serveroutput on
DECLARE
    CURSOR lc_emp_name IS SELECT ename 
                          FROM emp 
                          WHERE deptno=10;
                          
    lv_emp_name varchar2(30);
BEGIN
    OPEN lc_emp_name;
    LOOP
        FETCH lc_emp_name into lv_emp_name;
        EXIT WHEN lc_emp_name%notfound;
        dbms_output.put_line(lv_emp_name);
    END LOOP;
    CLOSE lc_emp_name;
END;
/

--------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE PRINT_LIST(p_in varchar2) as
    TYPE REF_CUR_TYPE IS REF CURSOR;
    lv_ref_cur REF_CUR_TYPE;
    lv_name   VARCHAR2(30);
BEGIN

    IF p_in='ENAME' THEN
       OPEN lv_ref_cur FOR SELECT ename FROM emp;
    ELSIF p_in='DNAME' THEN
       OPEN lv_ref_cur FOR 'SELECT dname FROM dept';
    ELSIF  p_in='JOB' THEN
       OPEN lv_ref_cur FOR SELECT distinct job FROM emp;
    ELSE
       OPEN lv_ref_cur FOR SELECT 'INVALID INPUT' FROM dual;
    END IF;
    
    LOOP
        FETCH lv_ref_cur INTO lv_name;
        EXIT WHEN lv_ref_cur%notfound;        
        dbms_output.put_line(lv_name);
    END LOOP;
END;
/

set serveroutput on
EXEC PRINT_LIST('ENAME');
EXEC PRINT_LIST('DNAME');
EXEC PRINT_LIST('JOB');
EXEC PRINT_LIST('ABC');

--------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE PROC_GET_LIST(p_in varchar2,p_out_cur out SYS_REFCURSOR) as
    lv_name   VARCHAR2(30);
BEGIN

    IF p_in='ENAME' THEN
       OPEN p_out_cur FOR SELECT ename FROM emp;
    ELSIF p_in='DNAME' THEN
       OPEN p_out_cur FOR 'SELECT dname FROM dept';
    ELSIF  p_in='JOB' THEN
       OPEN p_out_cur FOR SELECT distinct job FROM emp;
    ELSE
       OPEN p_out_cur FOR SELECT 'INVALID INPUT' FROM dual;
    END IF;

END;
/

CREATE OR REPLACE PROCEDURE PROC_PRINT_LIST(p_in varchar2) AS
    TYPE lc_ref_cur_type IS REF CURSOR;
    lc_ref_cur  lc_ref_cur_type;
    lv_name     VARCHAR2(30);
BEGIN
    PROC_GET_LIST(p_in,lc_ref_cur);
    
    LOOP
        FETCH lc_ref_cur INTO lv_name;
        EXIT WHEN lc_ref_cur%notfound;        
        dbms_output.put_line(lv_name);
    END LOOP;
END;
/

set serveroutput on
exec PROC_PRINT_LIST('ENAME');
exec PROC_PRINT_LIST('DNAME');
exec PROC_PRINT_LIST('JOB');
exec PROC_PRINT_LIST('ABC');

--------------------------------------------------------------------

CREATE OR REPLACE PACKAGE emp_pkg AS
    CURSOR emp_list IS SELECT *
                       FROM emp;

    CURSOR emp_list_10 IS SELECT *
                          FROM emp
                          WHERE deptno = 10;

    CURSOR emp_list_20 IS SELECT *
                          FROM emp
                          WHERE deptno = 20;

END emp_pkg;
/

DECLARE
    lv_emp_rec   emp%rowtype;
BEGIN
    OPEN emp_pkg.emp_list;
    FETCH emp_pkg.emp_list into lv_emp_rec;
    dbms_output.put_line('ENAME = '||lv_emp_rec.ename);
    dbms_output.put_line('SAL = '||lv_emp_rec.sal);
END;

--------------------------------------------------------------------