Unix Shell Script to connect Oracle database

[oracle@ODIGettingStarted practice]$ vi conoracle.sh
[oracle@ODIGettingStarted practice]$ cat conoracle.sh
user="scott"
pass="tiger"
sqlplus -s $user/$pass@ORCL <<EOF
select * from student;
exit;
EOF
[oracle@ODIGettingStarted practice]$ sh conoracle.sh

ID NAME
---------- --------------------
2 sekhar
1 raj

[oracle@ODIGettingStarted practice]$

Verifying Result from Database:

[oracle@ODIGettingStarted ~]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 26 23:42:26 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from student;

ID NAME

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

2 sekhar

1 raj

SQL> 

Data Warehousing - Slowly Changing Dimension (SCD)

Types of Dimensions:

1. Slowly Changing Dimension
2. Conformed Dimension
3. Degenearte Dimension
4. Junk Dimension
5. Role Playing Dimension

Slowly Changing Dimension (SCD):

Dimension attributes that change slowly over a period of time rather than changing regularly is group as SCD's.

Let's consider an example of a person changing his/her city from city 1 to city 2 and this change does not happen at regular intervals.

Examples: Name, Job, Address, Phone Number

There are 3 ways in which these types of attributes are handled.

SCD Type - 1: Overwrite with Old value
SCD Type - 2: Add a new Row/Record
SCD Type - 3: Add a new Column

SCD Type - 1: Overwrite with Old value
*******************************************************************************
Table 1: Before Change

Suppose Raj has changed his city from Tirupati to Bangalore in 2014, the values of the Table 1 is overwritten as shown below.

Table 2: After Change

The Advantage of above approach is that is very easy to follow and results in huge space savings and hence cost savings.

The Disadvantage is that no history is maintained.

SCD Type - 2: Add a new Row/Record
********************************************************************************
Table 1: Before Change

Suppose Raj has changed his city from Tirupati to Bangalore in 2014, new row is added as shown in Table 2.

Table 2: After Change

The Advantage of above approach is that complete history is maintained.

The disadvantage is that it results in huge space allocation because the entire history from the start has to be maintained.

It is relatively difficult to determine which record is the most recent.
A sub query has to be written to fetch the most recent record for each ID
To overcome these, there are 2 approaches to indicate most c current record.

Approach 1 - Use the Start Date and End Date

Suppose Raj started living in Tirupati from 1st June 2011, hence for the very first time the record will look as below:

If  Raj moves to Bangalore on 1st April 2014, then the record as per 1st approach will look as below:

Hence the most record can be now identified by using the condition End date > Current Date

Approach 2 - Use an Indicator

First time when a record arrives with Tirupati as the city, the indicator will be set as Y

When the person changes the city then the indicator of the previous record is changed to  N and the indicator of the current record is changed to Y

Hence to get the most recent record, just use the condition WHERE current_flag='Y'

SCD Type - 3: Add a new Column
********************************************************************************
Sometimes it is not required to maintain the entire history of a slowly changing dimension.

We might want to retain the current record and the immediate previous record. In such cases instead of adding new rows, it is beneficial to add columns as illustrated below.

For the first time when Raj moves to Tirupati the old city column will be blank.

When he moves to Bangalore, the city column becomes Bangalore and the old city column will be Tirupati

The Disadvantage of the above approach is clearly seen in the table above.
For each attribute, there must be 2 columns, one indicating the current value and the other indicating the previous value.

Redirect to another apex application using APEX_UTIL.REDIRECT_URL

Application --> Shared Components --> Application Processes --> Create
Name : p_redirection
Point: On Load: Before Header
PL/SQL Code:

BEGIN
   HTP.init;
   OWA_UTIL.redirect_url ('https://rrr-abc.xyz.com/pls/apex/f?p=1234:1:&APP_SESSION.');
END;


Opening in new window:

javascript:void(window.open('/pls/apex/f?p=&APP_ID.:22:&APP_SESSION.::::P22_MODE,P22_TESTING_ID,P22_REG_NO:14,&G_TESTING_ID.,#REGISTRATION_NUMBER#', '_blank'));

SQL Interview Questions Part - 2

SQL> WITH split_val AS (SELECT 'A,B,C,D,E' AS VAL FROM DUAL)
  2      SELECT REGEXP_SUBSTR (VAL,
  3                            '[^,]+',
  4                            1,
  5                            ROWNUM)
  6                AS result
  7        FROM split_val
  8  CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (VAL, '[^,]+')) + 1;

RESULT
------------------------------------
A
B
C
D
E

SQL>

ETL Testing Interview Questions Part -1

Typical first round interview questions:

Give me details about your QA back ground

What is fact table and dimension table

What validation you do when data passes from source to target

What is slowly changing dimension

What are the different kinds of SCDs (slowly changing dimensions) we have. Which one did you follow in your project?

List of questions previously asked our hires:

They will be asking about schemas (star and snowflake)

They will ask about what volume of data you are used to working with

They will ask how you validate from source to target

The will be testing your knowledge of SQL

They will be asking your opinion about relational databases vs. Hadoop

They will ask about the functions related the relational databases and then how they would change in a Hadoop environment

They might do a resume walk through or ask about specific roles you have had

What kind of data have you worked with?

What kind of method she is conducting the data validation

(1) Can you write SQL query to retrieve 2nd latest coverage from claim table, containing member name from member table?

SELECT DISTINCT COVERAGE
  FROM (SELECT COVERAGE, DENSE_RANK () OVER (ORDER BY COVERAGE DESC) RNK FROM CLAIM C, MEMBER M WHERE C.MEMBER_NAME=M.MEMBER_NAME)
     WHERE RNK = 2;

(2) Difference between Rownum(), Denserank() and rank() functions?

The ROW_NUMBER() function always generates a unique ranking even with duplicate records.

The RANK function will give the non-sequential ranking.

The DENSE_RANK function is similar to RANK function however the DENSE_RANK function does not skip any ranks if there is a tie between the ranks of the preceding records.
i.e DENSE_RANK function will give the sequential ranking.

SQL> WITH emp_sal
  2       AS (SELECT 101 id, 5000 AS salary FROM DUAL
  3           UNION ALL
  4           SELECT 102 id, 4000 AS salary FROM DUAL
  5           UNION ALL
  6           SELECT 103 id, 4000 AS salary FROM DUAL
  7           UNION ALL
  8           SELECT 104 id, 3000 AS salary FROM DUAL
  9           UNION ALL
 10           SELECT 105 id, 2000 AS salary FROM DUAL)
 11  SELECT id,
 12         salary,
 13         ROW_NUMBER () OVER (ORDER BY salary DESC) AS rnm,
 14         DENSE_RANK () OVER (ORDER BY salary DESC) AS d_rnk,
 15         RANK () OVER (ORDER BY salary DESC) AS rnk
 16    FROM emp_sal;

        ID     SALARY        RNM      D_RNK        RNK
---------- ---------- ---------- ---------- ----------
       101       5000          1          1          1
       102       4000          2          2          2
       103       4000          3          2          2
       104       3000          4          3          4
       105       2000          5          4          5

SQL>

(3) Real time example of Informatica DDO?

Data Validation Option for PowerCenter (DVO) is used for Data Validation Option, automatic test generation, reporting, and trouble-shooting.

(4) Did you use IBM WebSphere Datastage?

Yes/No.
Datastage is an ETL tool which extracts data, transform and load data from source to the target.
The data sources might include sequential files,relational databases, external data sources.
Datastage is an interface between different systems. It takes care of extraction, translation, and loading of data from source to the target destination.

(5) How did you execute jobs in Abnitio?

(6) How do you delete duplicate records from a table?

we can remove duplicates using rowid.
Below query is used to delete duplicates from EMP_COPY table based duplicate column i.e sal
DELETE FROM EMP_COPY WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM EMP_COPY GROUP BY SAL);
(or)
DELETE FROM EMP_COPY A WHERE ROWID <> (SELECT MAX(ROWID) FROM EMP_COPY B WHERE A.SAL=B.SAL);

(7) Data is coming from multiple source files, and data is loaded from files to tables? What is the validation steps to validate data between file and table?

we can create a temp table and import file data into it .
will perform validations b/w temp (source and actual table (target) -- count,duplicate,data validation

(8) how do you count number of records in a file in unix?

wc -l filename

Example:
oracle@ODIGettingStarted ~]$ cat student.txt
101|Raj|Bangalore
102|Siva|Hyderabad
103|Ram|Chennai
[oracle@ODIGettingStarted ~]$ wc -l student.txt
3 student.txt
[oracle@ODIGettingStarted ~]$


(9) How to check width of a record in a file in unix?

awk '{ print (length($0))}' student.txt

Example:
[oracle@ODIGettingStarted ~]$ cat student.txt
101|Raj|Bangalore
102|Siva|Hyderabad
103|Ram|Chennai
[oracle@ODIGettingStarted ~]$ awk '{ print (length($0))}' student.txt
17
18
15
[oracle@ODIGettingStarted ~]$ awk '{ print (length($0)); exit;}' student.txt
17
[oracle@ODIGettingStarted ~]$

(10) Do you have experience in code deployment?

No. But am aware of the deployment process.
Once developers finish coding for a project, they need to place or move that code to stage box for QA. It is called code deployment.
It can include code that fixes bugs, adds new features, or upgrades the underlying platform.
Code deployment flow: DEV -> STAGE -> UAT -> PROD

(11) Share your experience in Agile?

Agile methodology is a practice that promotes continuous iteration of development and testing throughout the software development lifecycle of the project.
Both development and testing activities are concurrent unlike the Waterfall mode.
Agile focuses on untangling the complications of Software development and break it down to simple, clean and smaller user stories.

(12) Give an example of Left outer join?

Suppose, we want to join two tables A and B. SQL left outer join returns all rows in the left table (A) and all the matching rows found in the right table (B).
It means the result of the SQL left join always contains the rows in the left table.

(13) Give an example of complex SQL you have written for Data validation?

SCD Type - 2 data validation

NVL and NVL2 in Oracle

SQL> WITH ADDRESS
  2       AS (SELECT 10 AS ID,
  3                  'BANGALORE' AS ADDRESS1,
  4                  'HYDERABAD' AS ADDRESS2,
  5                  'CHENNAI' AS ADDRESS3
  6             FROM DUAL
  7           UNION ALL
  8           SELECT 20 AS ID,
  9                  '' AS ADDRESS1,
 10                  'HYDERABAD' AS ADDRESS2,
 11                  'CHENNAI' AS ADDRESS3
 12             FROM DUAL)
 13  SELECT ID,
 14         ADDRESS1,
 15         ADDRESS2,
 16         ADDRESS3,
 17         NVL (ADDRESS1, ADDRESS2) AS NVL,
 18         NVL2 (ADDRESS1, ADDRESS2, ADDRESS3) AS NVL2
 19    FROM ADDRESS;

        ID ADDRESS1  ADDRESS2  ADDRESS NVL       NVL2
---------- --------- --------- ------- --------- ---------
        10 BANGALORE HYDERABAD CHENNAI BANGALORE HYDERABAD
        20           HYDERABAD CHENNAI HYDERABAD CHENNAI

SQL>

How to send an email using PL/SQL

CREATE OR REPLACE PROCEDURE send_mail (
   p_msg_to        IN VARCHAR2,
   p_msg_subject   IN VARCHAR2,
   p_msg_text      IN VARCHAR2,
   p_msg_from      IN VARCHAR2 DEFAULT NULL,
   p_mailhost      IN VARCHAR2 DEFAULT NULL,
   p_mailport      IN VARCHAR2 DEFAULT '25')
IS
   v_conc       UTL_SMTP.connection;
   v_msg_from   VARCHAR2 (50);
   v_mailhost   VARCHAR2 (200);
   v_mailport   VARCHAR2 (5);
   v_messages   VARCHAR2 (2000);
   v_action     VARCHAR2 (60);
BEGIN
   v_messages := NULL;
   v_action := 'START';

   IF p_mailhost IS NULL
   THEN
      v_mailhost := 'internal-xxx-router.gmail.com';
   END IF;

   IF p_mailport IS NULL
   THEN
      v_mailport := '25';
   END IF;

   v_conc :=
      UTL_SMTP.open_connection (NVL (p_mailhost, v_mailhost),
                                TO_NUMBER (NVL (p_mailport, v_mailport)));

   UTL_SMTP.helo (v_conc, NVL (p_mailhost, v_mailhost));
   UTL_SMTP.mail (v_conc, NVL (p_msg_from, v_msg_from));

   BEGIN
      FOR one_recipient IN (WITH DATA AS (SELECT p_msg_to str FROM DUAL)
                                SELECT TRIM (REGEXP_SUBSTR (str,
                                                            '[^,]+',
                                                            1,
                                                            LEVEL))
                                          str
                                  FROM DATA
                            CONNECT BY INSTR (str,
                                              ',',
                                              1,
                                              LEVEL - 1) > 0)
      LOOP
         UTL_SMTP.rcpt (v_conc, one_recipient.str);
      END LOOP;
   END;

   UTL_SMTP.open_data (v_conc);

   UTL_SMTP.write_data (
      v_conc,
         'From: '
      || v_msg_from
      || '  '
      || UTL_TCP.crlf
      || 'To: '
      || p_msg_to
      || UTL_TCP.crlf
      || 'Subject: '
      || p_msg_subject
      || UTL_TCP.crlf
      || p_msg_text);

   UTL_SMTP.close_data (v_conc);
   UTL_SMTP.quit (v_conc);
   v_messages := 'Success';
   v_action := 'END';
EXCEPTION
   WHEN UTL_SMTP.invalid_operation
   THEN
      v_messages :=
            'Invalid Operation in Mail attempt using UTL_SMTP.'
         || SUBSTR (TO_CHAR (SQLCODE) || '-' || SQLERRM, 1, 2000);

      v_action := 'ERROR';

      DBMS_OUTPUT.put_line (v_action || '~' || v_messages);
   WHEN UTL_SMTP.transient_error
   THEN
      v_messages :=
            'Temporary e-mail issue - try again '
         || SUBSTR (TO_CHAR (SQLCODE) || '-' || SQLERRM, 1, 2000);

      v_action := 'ERROR';

      DBMS_OUTPUT.put_line (v_action || '~' || v_messages);
   WHEN UTL_SMTP.permanent_error
   THEN
      v_messages :=
            'Permanent Error Encountered. '
         || SUBSTR (TO_CHAR (SQLCODE) || '-' || SQLERRM, 1, 2000);

      v_action := 'ERROR';

      DBMS_OUTPUT.put_line (v_action || '~' || v_messages);
   WHEN OTHERS
   THEN
      v_messages := SUBSTR (TO_CHAR (SQLCODE) || '-' || SQLERRM, 1, 2000);

      v_action := 'ERROR';

      DBMS_OUTPUT.put_line (v_action || '~' || v_messages);
END send_mail;
/



BEGIN
   send_mail (p_msg_to        => 'testsmtp@gmail.com',
              p_msg_subject   => 'Test Email',
              p_msg_text      => 'Dear Rajasekhar,..',
              p_msg_from      => 'refresh_alerts@xyz.com',
              p_mailhost      => NULL,
              p_mailport      => NULL);
END;

emp and dept script

DROP TABLE EMP;
DROP TABLE DEPT;
DROP TABLE BONUS;
DROP TABLE SALGRADE;
DROP TABLE DUMMY;

CREATE TABLE EMP
(
   EMPNO      NUMBER (4) NOT NULL,
   ENAME      VARCHAR2 (10),
   JOB        VARCHAR2 (9),
   MGR        NUMBER (4),
   HIREDATE   DATE,
   SAL        NUMBER (7, 2),
   COMM       NUMBER (7, 2),
   DEPTNO     NUMBER (2)
);

INSERT INTO EMP VALUES (7369, 'SMITH',  'CLERK',     7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN',  'SALESMAN',  7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMP VALUES (7521, 'WARD',   'SALESMAN',  7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO EMP VALUES (7566, 'JONES',  'MANAGER',   7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN',  7698, TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE',  'MANAGER',   7839, TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK',  'MANAGER',   7839, TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT',  'ANALYST',   7566, TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING',   'PRESIDENT', NULL, TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN',  7698, TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500,    0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS',  'CLERK',     7788, TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES',  'CLERK',     7698, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD',   'ANALYST',   7566, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK',     7782, TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE DEPT
(
   DEPTNO   NUMBER (2),
   DNAME    VARCHAR2 (14),
   LOC      VARCHAR2 (13)
);

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE BONUS
(
   ENAME   VARCHAR2 (10),
   JOB     VARCHAR2 (9),
   SAL     NUMBER,
   COMM    NUMBER
);

CREATE TABLE SALGRADE
(
   GRADE   NUMBER,
   LOSAL   NUMBER,
   HISAL   NUMBER
);

INSERT INTO SALGRADE VALUES (1,  700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

CREATE TABLE DUMMY(DUMMY CHAR(1));

INSERT INTO DUMMY VALUES ('X');

COMMIT;

UTL_FILE in oracle

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> CREATE DIRECTORY CDD_FILES AS 'D:\Oracle_2019\CDD';

Directory created.

SQL> GRANT READ,WRITE ON DIRECTORY CDD_FILES TO SCOTT;

Grant succeeded.

SQL> SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME='CDD_FILES';

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ ----------------------------
SYS                            CDD_FILES                      D:\Oracle_2019\CDD

SQL> GRANT EXECUTE,DEBUG ON UTL_FILE TO SCOTT;

Grant succeeded.

SQL>

SQL> conn scott/tiger
Connected.
SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DECLARE
  2     l_handler     UTL_FILE.file_type;
  3     l_file_name   VARCHAR2 (100)
  4        :=    LOWER (SYS_CONTEXT ('USERENV', 'DB_NAME'))
  5           || '_'
  6           || TO_CHAR (SYSDATE, 'YYYYMMDD')
  7           || '.log';
  8  BEGIN
  9     -- open the file for input
 10     l_handler := UTL_FILE.fopen ('CDD_FILES', l_file_name, 'w');
 11     -- write a line into file
 12     UTL_FILE.put_line (l_handler, 'Hello...');
 13     UTL_FILE.put_line (l_handler, 'Rajasekhar...');
 14     -- close the file
 15     UTL_FILE.fclose (l_handler);
 16  EXCEPTION
 17     WHEN OTHERS
 18     THEN
 19        DBMS_OUTPUT.PUT_LINE ('Oracle Error Message ~ ' || SQLERRM);
 20        UTL_FILE.fclose (l_handler);
 21  END;
 22  /

PL/SQL procedure successfully completed.

SQL>

SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DECLARE
  2     l_handler     UTL_FILE.file_type;
  3     l_read_line   VARCHAR2 (32765);
  4  BEGIN
  5     -- open the file for output
  6     l_handler := UTL_FILE.fopen ('CDD_FILES', 'xe_20190915.log', 'r');
  7
  8     LOOP
  9        -- read a line from file
 10        UTL_FILE.get_line (l_handler, l_read_line);
 11        DBMS_OUTPUT.PUT_LINE (l_read_line);
 12     END LOOP;
 13  EXCEPTION
 14     WHEN NO_DATA_FOUND
 15     THEN
 16        -- close the file
 17        UTL_FILE.fclose (l_handler);
 18     WHEN OTHERS
 19     THEN
 20        DBMS_OUTPUT.PUT_LINE ('Oracle Error Message ~ ' || SQLERRM);
 21        UTL_FILE.fclose (l_handler);
 22  END;
 23  /
Hello...
Rajasekhar...

PL/SQL procedure successfully completed.

SQL>


SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DECLARE
  2     l_handler     UTL_FILE.file_type;
  3     l_file_name   VARCHAR2 (100)
  4        :=    LOWER (SYS_CONTEXT ('USERENV', 'DB_NAME'))
  5           || '_'
  6           || TO_CHAR (SYSDATE, 'YYYYMMDD')
  7           || '.log';
  8  BEGIN
  9     -- open the existing file for input
 10     l_handler := UTL_FILE.fopen ('CDD_FILES', l_file_name, 'a');
 11     -- write a line into file
 12     UTL_FILE.put_line (l_handler, 'How are you...?');
 13     -- close the file
 14     UTL_FILE.fclose (l_handler);
 15  EXCEPTION
 16     WHEN OTHERS
 17     THEN
 18        DBMS_OUTPUT.PUT_LINE ('Oracle Error Message ~ ' || SQLERRM);
 19        UTL_FILE.fclose (l_handler);
 20  END;
 21  /

PL/SQL procedure successfully completed.

SQL>

File output should be:

Hello...

Rajasekhar...

How are you...?

SQL> SET SERVEROUTPUT ON;
SQL> BEGIN
  2     -- remove an existing file from the directory
  3     UTL_FILE.fremove (location=>'CDD_FILES',filename=>'xe_20190915.log');
  4  EXCEPTION
  5     WHEN OTHERS
  6     THEN
  7        DBMS_OUTPUT.PUT_LINE ('Oracle Error Message ~ ' || SQLERRM);
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL>


SET SERVEROUTPUT ON

DECLARE
   l_handler    UTL_FILE.file_type;
   l_handler1   UTL_FILE.file_type;
BEGIN
   l_handler := UTL_FILE.fopen ('CDD_FILES', 'xe_20190915.log', 'R');
   l_handler1 := UTL_FILE.fopen ('CDD_FILES', 'xe_20190916.log', 'W');

   --IF UTL_FILE.is_open (l_handler)
   IF UTL_FILE.is_open (l_handler) = TRUE
   THEN
      DBMS_OUTPUT.put_line ('l_handler is open and in read mode');
   END IF;

   --IF UTL_FILE.is_open (l_handler1)
   IF UTL_FILE.is_open (l_handler1) = TRUE
   THEN
      DBMS_OUTPUT.put_line ('l_handler1 is open and in write mode');
   END IF;

   -- closes all open file handles for the session
   UTL_FILE.fclose_all;
   -- UTL_FILE.fclose (l_handler);
   -- UTL_FILE.fclose (l_handler1);

   IF (UTL_FILE.is_open (l_handler1) OR UTL_FILE.is_open (l_handler1))
   THEN
      DBMS_OUTPUT.put_line ('l_handler or l_handler is still open');
   ELSE
      DBMS_OUTPUT.put_line ('both l_handler and l_handler1 are closed');
   END IF;
END;
/

Unix Shell Scripting Part - 2

if statement:

[oracle@ODIGettingStarted practice]$ vi ifstatement.sh
[oracle@ODIGettingStarted practice]$ cat ifstatement.sh
#If statement example
echo -n Enter first number:
read a
echo -n Enter second number:
read b
if [ $a -eq $b ]
then
   echo $a "is equal to" $b
else
   echo $a is not equal to $b
fi
[oracle@ODIGettingStarted practice]$ sh ifstatement.sh
Enter first number:5
Enter second number:5
5 is equal to 5
[oracle@ODIGettingStarted practice]$ sh ifstatement.sh
Enter first number:5
Enter second number:6
5 is not equal to 6
[oracle@ODIGettingStarted practice]$

ifelif statement:

[oracle@ODIGettingStarted practice]$ vi elifstatemnt.sh
[oracle@ODIGettingStarted practice]$ cat elifstatemnt.sh
#Purpose: Compare given two numbers
#Version: 1.0
#Created Date: Sat Sep 14 07:42:10 PM IST 2019
#Modified Date:
#Author: Rajasekhar Royal
# START #
echo -n Enter first number:
read a
echo -n Enter second number:
read b
if [ $a -eq $b ]; then
   echo $a is equal to $b
elif [ $a -gt $b ]; then
   echo $a is greater than $b
else
   echo $a is less than $b
fi
# END #
[oracle@ODIGettingStarted practice]$
[oracle@ODIGettingStarted practice]$ sh elifstatemnt.sh
Enter first number:35
Enter second number:20
35 is greater than 20
[oracle@ODIGettingStarted practice]$

while

[oracle@ODIGettingStarted practice]$ vi whiletest.sh
[oracle@ODIGettingStarted practice]$ cat whiletest.sh
myvar=1
while [ $myvar -le 10 ]
do
echo $myvar
        myvar=$(( $myvar + 1 ))
        sleep 0.5

done

[oracle@ODIGettingStarted practice]$ sh whiletest.sh
1
2
3
4
5
6
7
8
9
10
[oracle@ODIGettingStarted practice]$
[oracle@ODIGettingStarted practice]$ vi whiletest.sh
[oracle@ODIGettingStarted practice]$ cat whiletest.sh
myvar=1
while [ $myvar -le 5 ]
do
echo $myvar
        #myvar=$(( $myvar + 1 ))
        myvar=` expr $myvar + 1 `
        sleep 0.5
done
[oracle@ODIGettingStarted practice]$ sh whiletest.sh
1
2
3
4
5
[oracle@ODIGettingStarted practice]$

for loop

[oracle@ODIGettingStarted practice]$ vi forloop.sh
[oracle@ODIGettingStarted practice]$ cat forloop.sh
echo way-1
for i in 0 1 2 3 4 5
do
  echo $i
done
echo way-2
for i in {0..5}
do
  echo $i
done
echo way-3
max=5
for((i=0;i<=$max;i++));
do
  echo $i
done
[oracle@ODIGettingStarted practice]$ sh forloop.sh
way-1
0
1
2
3
4
5
way-2
0
1
2
3
4
5
way-3
0
1
2
3
4
5

[oracle@ODIGettingStarted practice]$

Determine whether the given is numeric , alphanumeric and hexadecimal in oracle SQL

^[^a-zA-Z]*$ or ^[[:xdigit:]]+$  => matches strings that don't contain letters
^[^g-zG-Z]*$                                => matches strings that don't contain letters after g/G

SQL> WITH vals
  2       AS (SELECT '1234567890' str FROM DUAL
  3           UNION ALL
  4           SELECT 'xyz123' str FROM DUAL
  5           UNION ALL
  6           SELECT '1234567890abcdef' str FROM DUAL)
  7  SELECT str,
  8         CASE
  9            WHEN REGEXP_LIKE (str, '^[^a-zA-Z]*$') THEN 'number'
 10            WHEN REGEXP_LIKE (str, '^[^g-zG-Z]*$') THEN 'hex'
 11            ELSE 'string'
 12         END
 13            typ
 14    FROM vals;

STR                        TYP
---------------- ---------------------------
1234567890              number
xyz123                      string
1234567890abcdef   hex

Jobs (dbms_job) in Oracle

Create a job:

Method 1: (job id generated by system)

SET SERVEROUTPUT ON;
DECLARE
   X   NUMBER;
BEGIN
   SYS.DBMS_JOB.SUBMIT (job         => X,
                        what        => 'BEGIN  EMP_DATA_REFRESH; END;',
                        next_date   => (TRUNC (SYSDATE) + 1) + (22 / 24),
                        interval    => '(TRUNC(SYSDATE)+1)+(22/24)', --10pm daily
                        no_parse    => FALSE);

   SYS.DBMS_OUTPUT.PUT_LINE ('Job Number is: ' || TO_CHAR (x));

   SYS.DBMS_JOB.BROKEN (job => X, broken => FALSE);
   COMMIT;
END;
/

Method 2: (job id should be passed)

DECLARE
   X   NUMBER := 888;
BEGIN
   SYS.DBMS_JOB.isubmit (job         => X,
                         what        => 'BEGIN  EMP_DATA_REFRESH; END;',
                         next_date   => (TRUNC (SYSDATE) + 1) + (22 / 24),
                         interval    => '(TRUNC(SYSDATE)+1)+(22/24)', --10pm daily
                         no_parse    => FALSE);
   COMMIT;
END;
/

Submit a job to run once:

BEGIN
   DBMS_JOB.isubmit (job         => 12345,
                     what        => ' BEGIN EMP_DATA_REFRESH; END;',
                     next_date   => SYSDATE + 1 / 1400);
   --SYSDATE + 1 / 1400 for 1 min
   COMMIT;
END;
/

Alter a existing job:

BEGIN
   DBMS_JOB.CHANGE (job         => 789,
                    what        => 'BEGIN OUDATA.EMP_DATA_REFRESH; END;',
                    next_date   => SYSDATE + 15 / 1400,
                    interval    => 'SYSDATE + 15 / 1400');
   COMMIT;
END;
/

BEGIN
   DBMS_JOB.WHAT (job => 789, what => 'BEGIN EMP_DATA_REFRESH;   END;');
   COMMIT;
END;
/

Broke the job:

BEGIN
   DBMS_JOB.BROKEN (789, TRUE);
   COMMIT;
END;

(or)

 EXEC DBMS_JOB.BROKEN (789, TRUE);
 COMMIT;


View the job details:

DBA_JOBS --> Lists all jobs in the database.
ALL_JOBS --> Lists all jobs accessible to the connected user.
USER_JOBS  --> Lists all jobs owned by the connected user.

Materialized View in Oracle

Create Materialized View:

CREATE MATERIALIZED VIEW EMP_MVIEW
AS
SELECT * FROM EMP;

Refresh Materialized view:

EXEC DBMS_MVIEW.REFRESH ('EMP_MVIEW', method => 'C');

Drop Materialized View:

DROP MATERIALIZED VIEW EMP_MVIEW;

Analyze tables in oracle

C:\Users\rajam>sqlplus system/admin

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Sep 14 00:32:02 2019

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL>
SQL> SELECT table_name,
  2         num_rows,
  3         logging,
  4         last_analyzed
  5    FROM user_tables
  6   WHERE table_name = 'EMP';

TABLE_NAME                       NUM_ROWS LOG LAST_ANAL
------------------------------ ---------- --- ---------
EMP                                    14 YES 02-JUL-19

SQL> EXEC dbms_stats.gather_table_stats('SYSTEM','EMP',estimate_percent=>100,cascade=>true,degree=>4);

PL/SQL procedure successfully completed.

SQL> SELECT table_name,
  2         num_rows,
  3         logging,
  4         last_analyzed
  5    FROM user_tables
  6   WHERE table_name = 'EMP';

TABLE_NAME                       NUM_ROWS LOG LAST_ANAL
------------------------------ ---------- --- ---------
EMP                                    14 YES 14-SEP-19

SQL>

Recompiling or Altering Invalid objects in Oracle

-- returns invalid objects and alter script to re-compile

  SELECT object_name,
         object_type,
         DECODE (
            object_type,
            'PACKAGE BODY', 'ALTER PACKAGE ' || OBJECT_NAME || ' COMPILE BODY;',
            'ALTER ' || OBJECT_TYPE || ' ' || OBJECT_NAME || ' compile;')
            AS COMPILE_SCRIPT
    FROM user_objects
   WHERE status = 'INVALID'
ORDER BY object_type;



SQL> COLUMN OBJECT_NAME FORMAT A30
SQL> COLUMN OBJECT_TYPE FORMAT A30
SQL> COLUMN COMPILE_SCRIPT FORMAT A60
SQL> SET LINES 150
SQL> SET PAGES 50
SQL>   SELECT object_name,
  2           object_type,
  3           DECODE (
  4              object_type,
  5              'PACKAGE BODY', 'ALTER PACKAGE ' || OBJECT_NAME || ' COMPILE BODY;',
  6              'ALTER ' || OBJECT_TYPE || ' ' || OBJECT_NAME || ' compile;')
  7              AS COMPILE_SCRIPT
  8      FROM user_objects
  9     WHERE status = 'INVALID'
 10  ORDER BY object_type;

OBJECT_NAME                    OBJECT_TYPE                    COMPILE_SCRIPT
------------------------------ ------------------------------ ------------------------------------------------------------
F_GETEMP_COUNT                 FUNCTION                       ALTER FUNCTION F_GETEMP_COUNT compile;

SQL>

Test Web Service in Oracle

HTTPS call :

SELECT APEX_WEB_SERVICE.make_rest_request (
          p_url              => 'https://wsctt.xyz12345.com/cxfws2/services/Ping',
          p_proxy_override   => 'http://www-xyz-abc7.us.sss.com:80',
          p_http_method      => 'GET',
          p_wallet_path      => 'file:/u01/app/oracle/dba/rdbms/orcl/wallet7',
          p_wallet_pwd       => 'Welcome123')
  FROM DUAL;


SELECT UTL_HTTP.request (
          url               => 'https://wsctt.xyz12345.com/cxfws2/services/Ping',
          proxy             => 'http://www-xyz-abc7.us.sss.com:80',
          wallet_path       => 'file:/u01/app/oracle/dba/rdbms/orcl/wallet7',
          wallet_password   => 'Welcome123')
  FROM DUAL;

Note: certificate has to be installed in a wallet and that wallet path is required for https calls

HTTP call:

SELECT UTL_HTTP.request (
                         url               => 'http://www.google.com',
                         proxy             => NULL,
                         wallet_path       => NULL,
                         wallet_password   => NULL)
  FROM DUAL;


Example:

SQL> SELECT UTL_HTTP.request (url               => 'http://www.google.com',
                         proxy             => NULL,
                         wallet_path       => NULL,
                         wallet_password   => NULL)
  FROM DUAL;  2    3    4    5

UTL_HTTP.REQUEST(URL=>'HTTP://WWW.GOOGLE.COM',PROXY=>NULL,WALLET_PATH=>NULL,WALL
--------------------------------------------------------------------------------
<!doctype html><html itemscope="" itemtype="http://schema.org/WebPage" lang="en-
IN"><head><meta content="text/html; charset=UTF-8" http-equiv="Content-Type"><me
ta content="/logos/doodles/2019/hans-christian-grams-166th-birthday-489939712815
9232-l.png" itemprop="image"><meta content="Hans Christian Gram&#8217;s 166th Bi
rthday" property="twitter:title"><meta content="Hans Christian Gram&#8217;s 166t
h Birthday! #GoogleDoodle" property="twitter:description"><meta content="Hans Ch
ristian Gram&#8217;s 166th Birthday! #GoogleDoodle" property="og:description"><m
eta content="summary_large_image" property="twitter:card"><meta content="@Google
Doodles" property="twitter:site"><meta content="https://www.google.com/logos/doo
dles/2019/hans-christian-grams-166th-birthday-4899397128159232-2x.jpg" property=
"twitter:image"><meta content="https://www.google.com/logos/doodles/2019/hans-ch

UTL_HTTP.REQUEST(URL=>'HTTP://WWW.GOOGLE.COM',PROXY=>NULL,WALLET_PATH=>NULL,WALL
--------------------------------------------------------------------------------
ristian-grams-166th-birthday-4899397128159232-2x.jpg" property="og:image"><meta
content="1200" property="og:image:width"><meta content="393" property="og:image:
height"><title>Google</title><script nonce="1rUWEIjDIfCXdUPikzk5jw==">(function(
){window.google={kEI:'h-J7XY6EJNqmwgPhpLiwCw',kEXPI:'0,18167,1335579,5663,731,22
3,510,19,1046,3151,379,206,1017,53,1431,577,2,124,10,713,226,112,538,245,1131683
,1197921,246,302938,26305,1294,12383,4855,32692,15247,867,12163,6381,854,2481,2,
2,6801,369,3314,5505,224,2215,5945,1119,2,579,727,2431,1362,284,4039,3698,1269,7
74,2247,1410,2487,1996,9,981,982,6196,669,1050,1808,1478,7,3,472,16,2044,5762,1,
3146,5072,223,2018,38,920,873,1217,1336,28,1611,2736,1558,1503,2,215,1,415,1137,
1,1265,837,7194,252,620,2883,19,1,319,234,455,429,904,1150,975,1,371,2777,517,40
0,992,509,196,580,9,108,2687,885,82,48,553,11,14,1279,2212,202,37,130,157,4,68,1

UTL_HTTP.REQUEST(URL=>'HTTP://WWW.GOOGLE.COM',PROXY=>NULL,WALLET_PATH=>NULL,WALL
--------------------------------------------------------------------------------
184,327,513,324,194,793,680,48,820,2620,818,109,151,52,1132,4,3,2063,606,1839,18
4,595,366,959,377,361,141,184,1261,749,59,368,44,207,1,781,113,328,1284,16,84,33
6,81,507,267,1510,142,2247,100,146,227,11,1328,29,700,501,557,669,2215,210,13


SQL>
  

Create Database link (dblink) in Oracle

PRIVATE:

Creates link in a specific schema of the local database. Only the owner of a private database link or PL/SQL subprograms in the schema can use this link to access database objects in the corresponding remote database.

PUBLIC:

Creates a database-wide link. All users and PL/SQL subprograms in the database can use the link to access database objects in the corresponding remote database.

GLOBAL:

Creates a network-wide link. When an Oracle network uses a directory server, the directory server automatically create and manages global database links (as net service names) for every Oracle Database in the network. Users and PL/SQL subprograms in any database can use a global link to access objects in the corresponding remote database.

To get TNS details using cmd prompt :

C:\Users\rajam>tnsping XE

Create Database Link:

Method 1:

CREATE DATABASE LINK ORCL_TO_XE CONNECT TO scott IDENTIFIED BY "tiger" USING 'XE';

Method 2:

CREATE DATABASE LINK ORCL_TO_XE
CONNECT TO scott
IDENTIFIED BY "tiger"
USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = RAJAM-IN.in.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))';


Dictionay tables:

DBA_DB_LINKS --> Lists all database links in the database.
ALL_DB_LINKS --> Lists all database links accessible to the connected user.
USER_DB_LINKS --> Lists all database links owned by the connected user.

Dropping a Database Link: 


Private:
The following statement drops the private database link named ouapp_to_fapap.
DROP DATABASE LINK ouapp_to_fapap; 

Public:
We must specify PUBLIC to drop a PUBLIC database link.
The following statement drops the public database link named ouapp_to_fapap.
DROP PUBLIC DATABASE LINK ouapp_to_fapap; 

Unix Shell Scripting Part - 1

Scripting:
  
-- Weakly or Loosely typed Programming Language
-- Interpreter Based
-- Less libraries available to perform installation
-- Special environment is not required for programming
-- Javascript, LiveScript, VBScript ,Shell Script, Perl Script

Programming:

-- Strictly typed Programming Language
-- Cimpiler Based
-- More libraries available and header files are mandatory to perform installation
-- Special environment and set up required for programming
-- Java,PL/SQL

Types of Shell:

Bourne Shell:  Steven Bourne Shell Prompt is $ and execution command is sh
C Shell: University of California Bill Joy Shell Prompt is % and execution command is Csh
Korn Shell: AT&T David Korn Shell Prompt is $ and execution command is ksh

Default Shell is : bash

Type of shell scripting:

1) Interactive shell script - user has to pass inputs to program
2) Non-Interactive shell script - don't take inputs from user

[oracle@ODIGettingStarted ~]$ pwd
/home/oracle
[oracle@ODIGettingStarted ~]$ cd practice
[oracle@ODIGettingStarted practice]$ pwd
/home/oracle/practice
[oracle@ODIGettingStarted practice]$

To get the name of the shell:

[oracle@ODIGettingStarted practice]$ echo $0
bash

To switch to Korn (ksh) shell:

[oracle@ODIGettingStarted practice]$ ksh
$echo $0
ksh
$exit

User defined Variables :

[oracle@ODIGettingStarted practice]$ x=2019
[oracle@ODIGettingStarted practice]$ echo $x
2019
[oracle@ODIGettingStarted practice]$ echo $y

[oracle@ODIGettingStarted practice]$ y=2020
[oracle@ODIGettingStarted practice]$ echo $y
2020
[oracle@ODIGettingStarted practice]$

Declaring Global variable: (here x is accessible even if we switch to kourn/c shell)

[oracle@ODIGettingStarted practice]$ export x

System defined Variables:

[oracle@ODIGettingStarted practice]$ echo $HOME
/home/oracle
[oracle@ODIGettingStarted practice]$ echo $LOGNAME
oracle
[oracle@ODIGettingStarted practice]$ echo $USER
oracle
[oracle@ODIGettingStarted practice]$ echo $PS1
[\u@\h \W]\$
[oracle@ODIGettingStarted practice]$ echo $PS2
>

Why Scripting is Loosely or Weakly Typed language?

[oracle@ODIGettingStarted practice]$ vi MyFirstScript.sh
[oracle@ODIGettingStarted practice]$ cat MyFirstScript.sh
#It is My First Shell Script
echo "Welcome to the world of Shell Scripting"
echo 'Default Script is BASH'
echo Thank You..!!
[oracle@ODIGettingStarted practice]$ sh MyFirstScript.sh
Welcome to the world of Shell Scripting
Default Script is BASH
Thank You..!!
[oracle@ODIGettingStarted practice]$

Non-Interactive Shell Scripting Examples:

Why Scripting is Interpreter based?

[oracle@ODIGettingStarted practice]$ vi MyFirstScript.sh
[oracle@ODIGettingStarted practice]$ cat MyFirstScript.sh
#It is My First Shell Script
echo "Welcome to the world of Shell Scripting"
cho 'Default Script is BASH'
echo Thank You..!!
[oracle@ODIGettingStarted practice]$ sh MyFirstScript.sh
Welcome to the world of Shell Scripting
MyFirstScript.sh: line 3: cho: command not found
Thank You..!!
[oracle@ODIGettingStarted practice]$

Number of users logged in:

[oracle@ODIGettingStarted practice]$ users
oracle oracle oracle
[oracle@ODIGettingStarted practice]$ users | wc -w
3
[oracle@ODIGettingStarted practice]$ who | wc -l
3
[oracle@ODIGettingStarted practice]$ who -q
oracle oracle oracle
# users=3
[oracle@ODIGettingStarted practice]$ vi users.sh
[oracle@ODIGettingStarted practice]$ cat users.sh
#Number of users
echo Currently logged in users count is:` who | wc -l `
echo currently logged in users are: ` who -q `
echo logged in users count is : ` users | wc -w `
echo "Success"
[oracle@ODIGettingStarted practice]$
[oracle@ODIGettingStarted practice]$ sh users.sh
Currently logged in users count is:3
currently logged in users are: oracle oracle oracle # users=3
logged in users count is : 3
Success
[oracle@ODIGettingStarted practice]$

Number of files in the current directory that are existed:

[oracle@ODIGettingStarted practice]$ pwd
/home/oracle/practice
[oracle@ODIGettingStarted practice]$ ls
MyFirstScript.sh  users.sh
[oracle@ODIGettingStarted practice]$ ls -ltr
total 8
-rw-rw-r--. 1 oracle oracle 124 Sep 13 11:44 MyFirstScript.sh
-rw-rw-r--. 1 oracle oracle 185 Sep 13 13:43 users.sh
[oracle@ODIGettingStarted practice]$ ls | wc -w
2
[oracle@ODIGettingStarted practice]$ ls | wc -l
2
[oracle@ODIGettingStarted practice]$ vi files.sh
[oracle@ODIGettingStarted practice]$ cat files.sh
#Number of files
echo "Number of files in the current directory:" `ls | wc -l`
echo "Success"
[oracle@ODIGettingStarted practice]$ sh files.sh
Number of files in the current directory: 3
Success
[oracle@ODIGettingStarted practice]$ ls -ltr
total 12
-rw-rw-r--. 1 oracle oracle 124 Sep 13 11:44 MyFirstScript.sh
-rw-rw-r--. 1 oracle oracle 185 Sep 13 13:43 users.sh
-rw-rw-r--. 1 oracle oracle  94 Sep 13 13:48 files.sh
[oracle@ODIGettingStarted practice]$

Is file extension is mandatory in Shell Scripting? Answer is : No

[oracle@ODIGettingStarted practice]$ vi noexten
[oracle@ODIGettingStarted practice]$ cat noexten
#script without file extension
echo "Welcome to BASH shell scripting"
echo Thank You!!
[oracle@ODIGettingStarted practice]$ sh noexten
Welcome to BASH shell scripting
Thank You!!
[oracle@ODIGettingStarted practice]$


Interactive Shell Scripting Examples:

[oracle@ODIGettingStarted practice]$ read a
5
[oracle@ODIGettingStarted practice]$ read b
10
[oracle@ODIGettingStarted practice]$ echo $a
5
[oracle@ODIGettingStarted practice]$ echo $b
10
[oracle@ODIGettingStarted practice]$ echo $a+$b
5+10
[oracle@ODIGettingStarted practice]$ echo $[a+b]
15
[oracle@ODIGettingStarted practice]$ read x
Raj
[oracle@ODIGettingStarted practice]$ echo $x
Raj
[oracle@ODIGettingStarted practice]$

echo

[oracle@ODIGettingStarted practice]$ vi read1.sh
[oracle@ODIGettingStarted practice]$ cat read1.sh
#Reading and displaying values
echo Enter your name:
read name
echo "Hello Mr./Mrs " $name
[oracle@ODIGettingStarted practice]$ sh read1.sh
Enter your name:
Rajasekhar
Hello Mr./Mrs  Rajasekhar
[oracle@ODIGettingStarted practice]$

echo -n

[oracle@ODIGettingStarted practice]$ vi read1.sh
[oracle@ODIGettingStarted practice]$ cat read1.sh
#Reading and displaying values
echo -n Enter your name:
read name
echo "Hello Mr./Mrs " $name
[oracle@ODIGettingStarted practice]$ sh read1.sh
Enter your name:Rajasekhar
Hello Mr./Mrs  Rajasekhar
[oracle@ODIGettingStarted practice]$

Arithmetic Operations:

[oracle@ODIGettingStarted practice]$ vi arithmetic.sh
[oracle@ODIGettingStarted practice]$ sh arithmetic.sh
Enter first number:10
Enter Second number:5
Sum of two numbers : 15
Difference of two numbers : 5
Product of two numbers: 50
[oracle@ODIGettingStarted practice]$ cat arithmetic.sh
#Arithmetic Operations
echo -n Enter first number:
read fno
echo -n Enter Second number:
read sno
echo Sum of two numbers : $[fno+sno]
echo Difference of two numbers : $[fno-sno]
echo Product of two numbers: $[fno*sno]

[oracle@ODIGettingStarted practice]$ 

SQL Interview Questions Part - 1

Query to find Second Highest Salary of Employee?

SQL> SELECT DISTINCT SAL
  2    FROM EMP E1
  3   WHERE 2 = (SELECT COUNT (DISTINCT sal)
  4                FROM emp E2
  5               WHERE E1.sal <= E2.sal);

       SAL
----------
      3000

SQL> SELECT DISTINCT SAL
  2    FROM (SELECT SAL, DENSE_RANK () OVER (ORDER BY SAL DESC) RNK FROM EMP)
  3   WHERE RNK = 2;

       SAL
----------
      3000

SQL> SELECT DISTINCT SAL
  2    FROM (SELECT SAL, ROW_NUMBER () OVER (ORDER BY SAL DESC) RNK FROM EMP)
  3   WHERE RNK = 2;

       SAL
----------
      3000

SQL> SELECT MAX (E1.SAL) AS SAL
  2    FROM EMP E1
  3   WHERE SAL < (SELECT MAX (SAL)
  4                  FROM EMP E2);

       SAL
----------
      3000

What is the Query to fetch last record from the table?

SQL> SELECT * FROM EMP WHERE ROWID=(SELECT MAX(ROWID) FROM EMP);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

SQL> SELECT E.*
  2    FROM EMP E,
  3         (SELECT EMPNO, ROW_NUMBER () OVER (ORDER BY ROWID DESC) RNK FROM EMP)
  4         R
  5   WHERE E.EMPNO = R.EMPNO AND R.RNK = 1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

What is Query to display first 5 Records from Employee table?

SQL> SELECT * FROM EMP WHERE ROWNUM <= 5;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

What is Query to display last 5 Records from Employee table?

SQL> SELECT * FROM (SELECT * FROM EMP E ORDER BY ROWID DESC) WHERE ROWNUM <=5;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

What is Query to display Nth Record from Employee table? ( in below example displaying 6th record)

SQL> SELECT E.*
  2    FROM EMP E,
  3         (SELECT EMPNO, ROW_NUMBER () OVER (ORDER BY ROWID) RNK FROM EMP)
  4         R
  5   WHERE E.EMPNO = R.EMPNO AND R.RNK = 6;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

How to Display Even rows in Employee table?

SQL> SELECT * FROM (SELECT ROWNUM AS RN,E.* FROM EMP E) WHERE MOD(RN,2)=0;

        RN      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
         2       7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
         4       7566 JONES      MANAGER         7839 02-APR-81       2975                    20
         6       7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
         8       7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
        10       7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
        12       7900 JAMES      CLERK           7698 03-DEC-81        950                    30
        14       7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

7 rows selected.

How to Display Odd rows in Employee table?

SQL> SELECT * FROM (SELECT ROWNUM AS RN,E.* FROM EMP E) WHERE MOD(RN,2)=1;

        RN      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
         1       7369 SMITH      CLERK           7902 17-DEC-80        800                    20
         3       7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
         5       7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
         7       7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
         9       7839 KING       PRESIDENT            17-NOV-81       5000                    10
        11       7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
        13       7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

7 rows selected.

How Can i create table with same structure with data of Employee table?

SQL> CREATE TABLE EMP_COPY AS SELECT * FROM EMP;

Table created.

SQL> SELECT COUNT(*) FROM EMP;

  COUNT(*)
----------
        14

SQL> SELECT COUNT(*) FROM EMP_COPY;

  COUNT(*)
----------
        14

How Can i create table with same structure without data of Employee table?

SQL> CREATE TABLE EMP_COPY2 AS SELECT * FROM EMP WHERE 1=2;

Table created.

SQL> SELECT COUNT(*) FROM EMP_COPY2;

  COUNT(*)
----------
         0


Display first 50% records from Employee table?

SQL> SELECT * FROM EMP WHERE ROWNUM<=(SELECT COUNT(*)/2 FROM EMP);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

7 rows selected.

Display last 50% records from Employee table?

SQL> SELECT ROWNUM,E.* FROM EMP E
  2  MINUS
  3  SELECT ROWNUM,E.* FROM EMP E WHERE ROWNUM<=(SELECT COUNT(*)/2 FROM EMP);

    ROWNUM      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
         8       7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
         9       7839 KING       PRESIDENT            17-NOV-81       5000                    10
        10       7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
        11       7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
        12       7900 JAMES      CLERK           7698 03-DEC-81        950                    30
        13       7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
        14       7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

7 rows selected.

How to get distinct records (jobs) from the table (EMP)

SQL> SELECT DISTINCT JOB FROM EMP;

JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST

How to get distinct records (jobs) from the table (EMP) without using distinct keyword.

SQL> SELECT JOB
  2    FROM EMP a
  3   WHERE ROWID = (SELECT MAX (ROWID)
  4                    FROM EMP b
  5                   WHERE a.JOB = b.JOB);

JOB
---------
MANAGER
PRESIDENT
SALESMAN
ANALYST
CLERK

how to write sql query for the below scenario
I/p:ORACLE

O/p:
O
R
A
C
L
E
i.e, splitting into multiple columns a string using sql.

SQL>     SELECT SUBSTR ('ORACLE', LEVEL, 1) AS OUT
  2        FROM DUAL
  3  CONNECT BY LEVEL <= LENGTH ('ORACLE');

OUT
----
O
R
A
C
L
E

6 rows selected.

How to find count of duplicate rows?

SQL> SELECT EMPNO FROM EMP_COPY WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM EMP_COPY GROUP BY SAL);

     EMPNO
----------
      7788
      7521

SQL> SELECT A.EMPNO FROM EMP_COPY A WHERE ROWID <> (SELECT MAX(ROWID) FROM EMP_COPY B WHERE A.SAL=B.SAL);

     EMPNO
----------
      7521
      7788

How to remove duplicate rows from table?

SQL> DELETE FROM EMP_COPY WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM EMP_COPY GROUP BY SAL);

2 rows deleted.

SQL> ROLLBACK;

Rollback complete.

SQL> DELETE FROM EMP_COPY A WHERE ROWID <> (SELECT MAX(ROWID) FROM EMP_COPY B WHERE A.SAL=B.SAL);

2 rows deleted.

Imagine a single column in a table that is populated with either a single digit (0-9) or a single character (a-z,A-Z).Write a SQL query to print 'Fizz' for numeric value or 'Buzz' for alphabitical values for all values in that column

SQL> WITH vals
  2       AS (SELECT 'd' str FROM DUAL
  3           UNION ALL
  4           SELECT 'x' str FROM DUAL
  5           UNION ALL
  6           SELECT 't' str FROM DUAL
  7           UNION ALL
  8           SELECT '8' str FROM DUAL
  9           UNION ALL
 10           SELECT 'a' str FROM DUAL
 11           UNION ALL
 12           SELECT '9' str FROM DUAL
 13           UNION ALL
 14           SELECT '6' str FROM DUAL
 15           UNION ALL
 16           SELECT '2' str FROM DUAL
 17           UNION ALL
 18           SELECT 'V' str FROM DUAL
 19           )
 20  SELECT str,
 21         CASE
 22            WHEN REGEXP_LIKE (str, '^[^a-zA-Z]*$') THEN 'Fizz'
 23            ELSE 'Buzz'
 24         END
 25            typ
 26    FROM vals;

S TYP
- ----
d Buzz
x Buzz
t Buzz
8 Fizz
a Buzz
9 Fizz
6 Fizz
2 Fizz
V Buzz

9 rows selected.

SQL>

SQL> DROP TABLE FRUIT;

Table dropped.

SQL> CREATE TABLE FRUIT(ID NUMBER);

Table created.

SQL> INSERT INTO FRUIT VALUES(1);

1 row created.

SQL> INSERT INTO FRUIT VALUES(2);

1 row created.

SQL> INSERT INTO FRUIT VALUES(3);

1 row created.

SQL> INSERT INTO FRUIT VALUES(NULL);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> DROP TABLE FRUIT_DETAILS;

Table dropped.

SQL> CREATE TABLE FRUIT_DETAILS(ID NUMBER,NAME VARCHAR2(10));

Table created.

SQL> INSERT INTO FRUIT_DETAILS VALUES(1,'ORANGE');

1 row created.

SQL> INSERT INTO FRUIT_DETAILS VALUES(2,'APPLE');

1 row created.

SQL> INSERT INTO FRUIT_DETAILS VALUES(2,'GRAPE');

1 row created.

SQL> INSERT INTO FRUIT_DETAILS VALUES(3,'BANANA');

1 row created.

SQL> INSERT INTO FRUIT_DETAILS VALUES(NULL,'AVOCADO');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM FRUIT;

        ID
----------
         1
         2
         3


SQL> SELECT * FROM FRUIT_DETAILS;

        ID NAME
---------- ----------
         1 ORANGE
         2 APPLE
         2 GRAPE
         3 BANANA
           AVOCADO

SQL> SELECT * FROM FRUIT F LEFT JOIN FRUIT_DETAILS FD ON F.ID=FD.ID;

        ID         ID NAME
---------- ---------- ----------
         1          1 ORANGE
         2          2 APPLE
         2          2 GRAPE
         3          3 BANANA