How to get every first letter from a string ?

SELECT REGEXP_REPLACE('KUMAR SHIVAM MYTHASS','(^| )([^ ])([^ ])*','\2') FIRST_LETTERS FROM DUAL;

FIRST_LETTERS
-------------
KSM

How to CREATE USER in Oracle 12C ?

In oracle 12c there is two types of users: common user and local user.

• Common User : The user is present in all containers (root and all PDBs).
• Local User : The user is only present in a specific PDB. The same username can be present in multiple PDBs, but they are unrelated.

Likewise, there are two types of roles.

• Common Role : The role is present in all containers (root and all PDBs).
• Local Role : The role is only present in a specific PDB. The same role name can be used in multiple PDBs, but they are unrelated.

Common users belong to Container Databases (CDB) as well as current and future Pluggable Databases (PDB). It means it can performed operation in Container or Pluggable according to Privileges assigned. For more information about common user.

Local users is purely database that belongs to only single PDB. This user may have administrative privileges but this only belongs to that PDB. For more information about local user.

Create Common Users

Create user start with C## and c##, as follows:

CREATE USER c##test_user IDENTIFIED BY password1; -- CONTAINER=ALL;

Create Local Users

ALTER SESSION SET CONTAINER = pdb1;

CREATE USER test_user IDENTIFIED BY password1; -- CONTAINER=CURRENT;

What is INSTR & SUBSTR in Oracle ?

SUBSTR

To extract the substring from any String we use SUBSTR
for e.g., mythass → thass

SUBSTR (STRING, START, LENGTH);

select SUBSTR('mythass', 3, 5) from dual;

------------------------
output: thass


INSTR

To get the position of any string we use INSTR
for e.g., mythass → thass


INSTR (STRING, SUBSTR, BEGIN_with_Nth_character_for_SUBSTR, RETRN_POS_Nth_OCCURENCE_OF_SUBSTR);
 /* N IS NUMBER */

SELECT INSTR('mythass','thass', 1, 1) FROM DUAL;
------------------------

output: 3

Some other examples-

SELECT INSTR('CORPORATE FLOOR FLOOR','OR', 1, 1) FROM DUAL;

-- starting from the 1st character of substring, for 1st occurence

SELECT INSTR('CORPORATE FLOOR FLOOR','OR', 1, 2) FROM DUAL;

-- starting from the 1st character of substring, for 2nd occurence

SELECT INSTR('CORPORATE FLOOR FLOOR','OR', 2, 2) FROM DUAL;         

-- starting from the 2nd character of substring, for 2nd occurence

-- same output as above query because it is getting the same position of OR

SELECT INSTR('CORPORATE FLOOR FLOOR','OR', 3, 2) FROM DUAL;

-- starting from the 3rd character of substring, for 2nd occurence

SELECT INSTR('CORPORATE FLOOR FLOOR','OR', 3, 3) FROM DUAL;

-- starting from the 3rd character of substring, for 3rd occurence

How to use & When to use Invoker Rights, Definer Rights in Stored Procedures & SQL methods ?

Sub-programs by default i.e., without AUTHID clause are called "Definer Rights" sub-programs.

Sub-programs with AUTHID clause are called "Invoker Rights" sub-programs.

How to use? Let see-

Assume you have two Schemas - MySchema_1, MySchema_2.

Both the Schemas are having table called EMP. Now, create a standalone procedure in MySchema_1.

CREATE PROCEDURE emp_details (

             p_emp_no NUMBER

            ,p_emp_name VARCHAR2

            ,p_emp_email VARCHAR2) AS

BEGIN

UDPATE EMP 

             SET emp_email = p_emp_email

             WHERE emp_no = p_emp_no;

END;

The above written is a "Definer Rights" sub-program. 

Assume that user MySchema_1 has granted the EXECUTE privilege on this procedure to user MySchema_2.

This will execute with the privileges of their owner (MySchema_1), not their current user (MySchema_2). So, it will update the EMP table of MySchema_1.

One way is to fully qualify references to the objects, as in

INSERT INTO MySchema_1.EMP...

CREATE PROCEDURE emp_details (

             p_emp_no NUMBER

            ,p_emp_name VARCHAR2

            ,p_emp_email VARCHAR2) AUTHID CURRENT_USER AS

BEGIN

UDPATE EMP 

             SET emp_email = p_emp_email

             WHERE emp_no = p_emp_no;

END;

The above written is an "Invoker Rights" sub-program.

Such invoker-rights subprograms are not bound to a particular schema.

When to use? Let see-

Invoker-rights subprograms let you reuse code and centralize application logic.

They are especially useful in applications that store data in different schemas. In such cases, multiple users can manage their own data using a single code base.

e.g.,

Consider a company that uses a definer-rights (DR) procedure to analyze sales. To provide local sales statistics, procedure analyze must access sales tables that reside at each regional site. So, the procedure must also reside at each regional site. This causes a maintenance problem.

To solve the problem, the company installs an invoker-rights (IR) version of procedure analyze at headquarters. Now, all regional sites can use the same procedure to query their own sales tables.

To restrict access to sensitive data, you can have an invoker-rights subprogram call a definer-rights subprogram. Suppose headquarters would like procedure analyze to calculate sales commissions and update a central payroll table.

That presents a problem because current users of analyze should not have direct access to the payroll table, which stores employee salaries and other sensitive data. The solution is to have procedure analyze call definer-rights procedure calc_comm, which in turn updates the payroll table.

How to find nth highest salary using rank and row_number?

2nd ..3rd highest salary using Aggregate Function MAX-

MAX returns maximum value of expr. You can use it as an aggregate or analytic function.

2nd highest salary-

select MAX(sal) from EMP 
WHERE 
sal <> ( select max(sal) from emp );    -- single value in a WHERE clause

3rd highest salary-

select MAX(sal) from EMP 
WHERE 
sal NOT IN            -- multiple values in a WHERE clause
( 
         (select max(sal) from emp)   -- till here >> return 2nd highest
       , (select max(sal) from emp where sal <> (select max(sal) from emp)) -- till here >> return 3rd highest
);

4th highest salary-

select MAX(sal) from EMP 
WHERE 
sal NOT IN         -- multiple values in a WHERE clause
( 
         ( select max(sal) from emp )   -- till here >> return 2nd highest
       , ( select max(sal) from emp where sal <> (select max(sal) from emp) ) -- till here >> return 3rd highest
       , ( select MAX(sal) from EMP WHERE sal NOT IN ( (select max(sal) from emp) , (select max(sal) from emp where sal <> (select max(sal) from emp))) )
);

Using this logic, you can find nth highest salary.
Note: The answer above is actually not optimal from a performance standpoint since it uses a subquery.

Find the nth highest salary in Oracle using ROWNUM (Best)

select * from
 (
    select EMPNO, Sal
    , row_number() over (order by Sal DESC) ROW_NUMBER
    from Emp
)
where ROW_NUMBER = n;  /*n is nth highest salary*/

Find the nth highest salary in Oracle using RANK

select * FROM 

(
    select EMPNO, Sal
    ,rank() over (order by Sal DESC) ranking
    from Emp
)
WHERE ranking = n;  /*n is nth highest salary*/

How to upload a text file and write into a table in Oracle APEX?

By default, all files using the “File Browse..” element will be uploaded to the “wwv_flow_files” table which has structure as follows:

?

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22

SQL> desc wwv_flow_files Name         Type           Nullable Default Comments ------------ -------------- -------- ------- -------- ID           NUMBER FLOW_ID      NUMBER NAME         VARCHAR2(90) FILENAME     VARCHAR2(400)  Y TITLE        VARCHAR2(255)  Y MIME_TYPE    VARCHAR2(48)   Y DOC_SIZE     NUMBER         Y DAD_CHARSET  VARCHAR2(128)  Y CREATED_BY   VARCHAR2(255)  Y CREATED_ON   DATE           Y UPDATED_BY   VARCHAR2(255)  Y UPDATED_ON   DATE           Y LAST_UPDATED DATE           Y CONTENT_TYPE VARCHAR2(128)  Y BLOB_CONTENT BLOB           Y LANGUAGE     VARCHAR2(30)   Y DESCRIPTION  VARCHAR2(4000) Y FILE_TYPE    VARCHAR2(255)  Y FILE_CHARSET VARCHAR2(128)  Y

Even though the text file contains character data, the uploaded file content will be stored in the “binary format” in a BLOB column named BLOB_CONTENT. Due to nature of BLOB, to read character out, the conversion using “chr” function is required. Please see a good article about this concept and conversion by Natalka Roshak on the orafaq at http://www.orafaq.com/node/895.

I modified the sample script provided in above article to suite my need – reading each line of the text file and insert into a target table.

?

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

DECLARE v_blob_data       BLOB; v_blob_len        NUMBER; v_position        NUMBER; v_raw_chunk       RAW(10000); v_char      CHAR(1); c_chunk_len   number       := 1; v_line        VARCHAR2 (32767)        := NULL; v_data_array      wwv_flow_global.vc_arr2; BEGIN -- Read data from wwv_flow_files select blob_content into v_blob_data from wwv_flow_files where name = 'F29800/Data_CSV.csv'; v_blob_len := dbms_lob.getlength(v_blob_data); v_position := 1; -- Read and convert binary to char WHILE ( v_position <= v_blob_len ) LOOP  v_raw_chunk := dbms_lob.substr(v_blob_data,c_chunk_len,v_position);  v_char :=  chr(hex_to_decimal(rawtohex(v_raw_chunk)));  v_line := v_line || v_char;  v_position := v_position + c_chunk_len; -- When a whole line is retrieved  IF v_char = CHR(10) THEN -- Convert comma to : to use wwv_flow_utilities    v_line := REPLACE (v_line, ',', ':'); -- Convert each column separated by : into array of data    v_data_array := wwv_flow_utilities.string_to_table (v_line); -- Insert data into target table    EXECUTE IMMEDIATE 'insert into TABLE_X (v1, v2, v3, v4)     values (:1,:2,:3,:4)'     USING       v_data_array(1),       v_data_array(2),       v_data_array(3),       v_data_array(4); -- Clear out    v_line := NULL;   END IF;  END LOOP; END; /