RANK and DENSE_RANK in Oracle

 

dynamic interactive report in oracle apex

 1. Create 3 items -->  P1_SQL, P1_COLCOUNT and P1_HEADER

2. Create a region --> PL/SQL Dyanamic Content

declare

    l_query varchar2(32767) := nvl(:P1_SQL,'select empno,ename from emp');

    l_theCursor     integer default dbms_sql.open_cursor;

    l_columnValue   varchar2(4000);

    l_descTbl       dbms_sql.desc_tab;

    l_colCnt        number;

    l_sql varchar2(4000) := 'select /*smartcheck*/ ';

    l_sep     varchar2(1);

 v_sql     VARCHAR2 (32000) := NULL;

   v_dates   VARCHAR2 (10000) := NULL;

BEGIN

 

  IF APEX_COLLECTION.COLLECTION_EXISTS ( p_collection_name => 'EMP_SQL') THEN

     APEX_COLLECTION.DELETE_COLLECTION( p_collection_name => 'EMP_SQL');

  end if;

  APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (

      p_collection_name => 'EMP_SQL',

      p_query => l_query);

   dbms_sql.close_cursor(l_theCursor);

   l_theCursor     := dbms_sql.open_cursor;

   dbms_sql.parse(  l_theCursor, l_query, dbms_sql.native );

   dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

 

   :P1_COLCOUNT := l_colcnt;

   for i in 1 .. least(l_colCnt,2)

   loop

     apex_util.set_session_state( p_name  => 'P1_HEADER'||lpad(i,2,0)

                                , p_value =>  l_descTbl(i).col_name

                                );

   end loop;

end;

3. Create Interactive Report

select 

 c001 ,c002 

from apex_collections 

where collection_name = 'EMP_SQL';

JSON Parsing

 DECLARE
v_response      CLOB := NULL;
 v_status              VARCHAR2 (2000);
   v_message             VARCHAR2 (2000);
BEGIN
v_response :='{"status":"NOK","message":"ZOOM: Meeting 99711037631 is not found or has expired."}';
apex_json.parse (p_source => v_response);
 v_status              := apex_json.get_varchar2('status');
   v_message             := apex_json.get_varchar2('message');
dbms_output.put_line('v_status='||v_status);
dbms_output.put_line('v_message='||v_message);
END;

/

v_status=NOK
v_message=ZOOM: Meeting 99711037631 is not found or has expired.

Statement processed.

0.01 seconds

Connect to OCI via GIT Bash Tunneling

1. mkdir .ssh

2. cd .ssh

3. vi config

 

Host *
   ServerAliveInterval 240
   ServerAliveCountMax 20
   StrictHostKeyChecking=no
   ForwardAgent yes
Host P1
   Hostname 123.123.123.123
   LocalForward localhost:9021 abc000021801.a1-prodapp-ash1-a-i-vcn2.abc.com:1521
   KexAlgorithms +diffie-hellman-group14-sha1
Host P2
   Hostname 123.123.123.123
   LocalForward localhost:9022 abc000021802.a1-prodapp-ash1-a-i-vcn2.abc.com:1521
   KexAlgorithms +diffie-hellman-group14-sha1
Host S1
   Hostname 123.123.123.123
   LocalForward localhost:9023 abc000021841.a1-prodapp-phx1-a-i-vcn2.abc.com:1521
   KexAlgorithms +diffie-hellman-group14-sha1
Host S2
   Hostname 123.123.123.123
   LocalForward localhost:9024 abc000021842.a1-prodapp-phx1-a-i-vcn2.abc.com:1521
   KexAlgorithms +diffie-hellman-group14-sha1
Host D1
   Hostname 123.123.123.123
   LocalForward localhost:9025 abc00003324.a1-prodapp-phx1-a-i-vcn2.abc.com:1521
   KexAlgorithms +diffie-hellman-group14-sha1  

4. ssh P1

5. SQL Developer: 

    HostName: localhost

    Port: 9021

    Service Name: ORCL

Pivot

 CREATE TABLE TESTING (  ID VARCHAR2(100),SES VARCHAR2(100),AMOUNT NUMBER,CNT NUMBER);

INSERT INTO TESTING VALUES('abc123','njn-jn',10,9);
INSERT INTO TESTING VALUES('abc123','kjsdb-jj',20,3);
INSERT INTO TESTING VALUES('abc123','jskj-5g',30,6);
COMMIT;
INSERT INTO TESTING VALUES('abc345','njn-jn1',10,9);
INSERT INTO TESTING VALUES('abc345','kjsdb-jj1',20,3);
INSERT INTO TESTING VALUES('abc345','jskj-5g1',30,6);
COMMIT;

SELECT * FROM TESTING;

WITH SET_RN AS
(SELECT ID,SES,AMOUNT,CNT,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ROWID ASC) RN FROM TESTING)
SELECT S.ID,
(SELECT S1.SES FROM SET_RN S1 WHERE S1.ID=S.ID AND S1.RN=1) AS SES1 ,
(SELECT S1.AMOUNT FROM SET_RN S1 WHERE S1.ID=S.ID AND S1.RN=1) AS AMOUNT1, 
(SELECT S1.CNT FROM SET_RN S1 WHERE S1.ID=S.ID AND S1.RN=1) AS CNT1,
(SELECT S2.SES FROM SET_RN S2 WHERE S2.ID=S.ID AND S2.RN=2) AS SES2 ,
(SELECT S2.AMOUNT FROM SET_RN S2 WHERE S2.ID=S.ID AND S2.RN=2) AS AMOUNT2, 
(SELECT S2.CNT FROM SET_RN S2 WHERE S2.ID=S.ID AND S2.RN=2) AS CNT3,
(SELECT S3.SES FROM SET_RN S3 WHERE S3.ID=S.ID AND S3.RN=3) AS SES3 ,
(SELECT S3.AMOUNT FROM SET_RN S3 WHERE S3.ID=S.ID AND S3.RN=3) AS AMOUNT3, 
(SELECT S3.CNT FROM SET_RN S3 WHERE S3.ID=S.ID AND S3.RN=3) AS CNT3
FROM SET_RN S GROUP BY ID;

SQL query to view structure of the table in Oracle

  SELECT A.OWNER,
         A.TABLE_NAME,
         C.COLUMN_NAME,
         C.DATA_TYPE,
         C.DATA_LENGTH,
         C.DATA_PRECISION,
         C.DATA_SCALE,
         C.COLUMN_ID
    FROM ALL_TABLES A, ALL_TAB_COLUMNS C
   WHERE     A.TABLE_NAME = C.TABLE_NAME
         AND A.OWNER = 'SCOTT'
         AND C.OWNER = 'SCOTT'
         AND A.TABLE_NAME = 'EMP'
ORDER BY A.TABLE_NAME, C.COLUMN_ID;

Bar and Pie chart in Oracle APEX

CREATE TABLE PRODUCT (ID NUMBER, NAME VARCHAR2(100));
ALTER TABLE PRODUCT ADD CONSTRAINT PRODUCT_PK PRIMARY KEY(ID);
INSERT INTO PRODUCT VALUES(1, 'Iphone');
INSERT INTO PRODUCT VALUES(2, 'Samsung');
INSERT INTO PRODUCT VALUES(3, 'Xiami');
INSERT INTO PRODUCT VALUES(4, 'Lenovo');
INSERT INTO PRODUCT VALUES(5, 'Oppo');
INSERT INTO PRODUCT VALUES(6, 'OnePlus');
INSERT INTO PRODUCT VALUES(7, 'Nokia');
COMMIT; 

 

CREATE TABLE ORDERS (ID NUMBER, PRODUCT_ID NUMBER, QUANTITY NUMBER, ORDER_DATE DATE);
ALTER TABLE ORDERS ADD CONSTRAINT ORDERS_PK PRIMARY KEY(ID);
ALTER TABLE ORDERS ADD CONSTRAINT ORDERS_FK FOREIGN KEY(PRODUCT_ID) REFERENCES PRODUCT(ID);
INSERT INTO ORDERS VALUES (1,1,20,SYSDATE);
INSERT INTO ORDERS VALUES (2,3,10,'02/13/1992');
INSERT INTO ORDERS VALUES (3,1,40,'03/19/2010');
INSERT INTO ORDERS VALUES (4,1,10,'12/10/1998');
INSERT INTO ORDERS VALUES (5,4,10,'09/18/1992');
INSERT INTO ORDERS VALUES (6,3,50,'02/13/2020');
INSERT INTO ORDERS VALUES (7,1,10,'03/15/1992');
INSERT INTO ORDERS VALUES (8,2,30,'07/19/1992');
INSERT INTO ORDERS VALUES (9,7,10,'02/22/1992');
INSERT INTO ORDERS VALUES (10,7,80,'09/28/2017');
INSERT INTO ORDERS VALUES (11,2,10,'21/28/2019');
COMMIT;

 

PIE Chart SQL:
Application --> Create Page --> Chart --> Pie --> Page Name as Charts (anything) --> SQL Query --> Select LABEL and VALUE --> Create
SELECT P.NAME AS LABEL, COUNT(*) AS VALUE FROM PRODUCT P, ORDERS O WHERE P.ID = O.PRODUCT_ID GROUP BY P.NAME; 

 

BAR Chart SQL:
Application --> Create Page --> Chart --> Bar --> Page Name as Charts (anything) --> Soucre: Localdatabase ,Type: SQL Query --> Select LABEL and VALUE --> Create
SELECT P.NAME AS LABEL, COUNT(*) AS VALUE FROM PRODUCT P, ORDERS O WHERE P.ID = O.PRODUCT_ID GROUP BY P.NAME; 

 

Example: https://apex.oracle.com/pls/apex/royal/r/testing40/charts?session=112166733385562