Case example in shell script(SWTICH)

The below code snippet shows how can we use case statement in shell script. case is acting here like switch statement in programming languages.

-bash-3.2$ cat case_example
#!/bin/bash
# Testing ranges of characters.
echo; echo "Hit a key, then hit return."
read Keypress
case "$Keypress" in
  [[:lower:]]   ) echo "Lowercase letter";;
  [[:upper:]]   ) echo "Uppercase letter";;
  [0-9]         ) echo "Digit";;
  *             ) echo "Punctuation, whitespace, or other";;
esac      #  Allows ranges of characters in [square brackets],
exit 0
-bash-3.2$

OUTPUT:

-bash-3.2$ sh case_example
Hit a key, then hit return.
a
Lowercase letter
-bash-3.2$ sh case_example
Hit a key, then hit return.
B
Uppercase letter
-bash-3.2$ sh case_example
Hit a key, then hit return.
4
Digit
-bash-3.2$ sh case_example
Hit a key, then hit return.
!
Punctuation, whitespace, or other
Explanation :

read Keypress            --> used to get input from keyboard
[[:lower:]]                   --> it accept all lower case characters
[[:upper:]]                   --> it accept all upper case characters
[0-9]                          --> it accepts all digits

Arithmatic Expression in shell script

The below code snippet shows how do the arithmatic expression in shell script.

-bash-3.2$ cat arithmatic_sh

#!/bin/bash
let "z=5**3"    # 5 * 5 * 5
echo "z = $z"   # z = 125
expr 5 % 3      #2

n=1; echo -n "$n "
let "n = $n + 1"   # let "n = n + 1"  also works.
echo -n "$n "
(( n = n + 1 ))
#  A simpler alternative to the method above.
echo -n "$n "
n=$(($n + 1))
echo -n "$n "
(( n++ ))          # (( ++n ))  also works.
echo -n "$n "
echo
exit 0

Output :

-bash-3.2$ sh arithmatic_sh
z = 125
2
1 2 3 4 5
-bash-3.2$

let "z=5**3"        -->  5*5*5 assigned into variable z (5 will be multiply by 3 times)

Convert Unix file Creation date into Oracle date

The below code snippet is used to convert the file creation date into oracle date format in unix shell script.
we can get the file creation date of mytextfile.txt and assign into ctime variable. give the ctime variable in oracle insertion script/where clause.
ctime=`stat -c%y mytextfile.txt| sed 's/\..*$//'`

to_date('${lv_ctime}','YYYY-MM-DD HH24:MI:SS')

Create Simple Shell Script Function

The below code snippet shows how can call simple shell script function.

shell script name  : simple_function

#!/bin/ksh
simple_function_call()
{
 echo -e "Hello World!"
}
simple_function_call

Output :

-bash-3.2$ sh simple_function
Hello World!
-bash-3.2$

simple_function_call is the function name we can call the function simply call by function name like simple_function_call

Note : In generaly shell script function is worked bottom up aproach. dont specified any argument in the function delcaration statement.

PL/SQL Exception Handlings

Exceptions (PL/SQL runtime errors) can arise from design faults, coding mistakes, hardware failures, and many other sources. We cannot anticipate all possible exceptions, but can write exception handlers that let your program to continue to operate in their presence.

Any PL/SQL block can have an exception-handling part, which can have one or more exception handlers.For example, an exception-handling part could have this syntax:

EXCEPTION

  WHEN exception_name_1 THEN

                   statements_1                 -- Exception handler

  WHEN exception_name_2 OR ex_name_3 THEN

                   statements_2                 -- Exception handler

  WHEN OTHERS THEN

                   statements_3                -- Exception handler

END;

When an exception is raised in the executable part of the block, the executable part stops and control transfers to the exception-handling part. If ex_name_1 was raised, then statements_1 run. If either ex_name_2 or ex_name_3 was raised, then statements_2 run. If any other exception was raised, then statements_3 run.After an exception handler runs, control transfers to the next statement of the enclosing block. If there is no enclosing block, then:

• If the exception handler is in a subprogram, then control returns to the invoker, at the statement after the invocation.
• If the exception handler is in an anonymous block, then control transfers to the host environment (for example, SQL*Plus)

Types of Exceptions

Predefined PLSQL Exceptions:

An internal exception is raised implicitly whenever your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit. Every Oracle error has a number, but exceptions must be handled by name. So, PL/SQL predefines some common Oracle errors as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows.

Frequently found exceptions are

Exception Name	Error number	Error code	Reason
CURSOR_ALREADY_OPEN	ORA-06511	-6511	Your program attempts to open an already open cursor. A cursor must be closed before it can be reopened.
INVALID_CURSOR	ORA-01001	-1001	Your program attempts an illegal cursor operation such as closing an unopened cursor
INVALID_NUMBER	ORA-01722	-1722	In a SQL statement, the conversion of a character string into a number fails because the string does not represent a valid number
NO_DATA_FOUND	ORA-01403	100	when select ,,,into ,,, from table statement returns no rows found from the table
PROGRAM_ERROR	ORA-06501	-6501	PL/SQL has an internal problem.
TOO_MANY_ROWS	ORA-01422	-1422	A SELECT INTO statement returns more than one row
VALUE_ERROR	ORA-06502	-6502	An arithmetic, conversion, truncation, or size-constraint error occurs
ZERO_DIVIDE	ORA-01476	-1476	Your program attempts to divide a number by zero

Example : NO DATA FOUND & TOO MANY ROWS

DECLARE
   lv_name   VARCHAR2 (30);
BEGIN
   SELECT NAME
     INTO lv_name
     FROM student_table
    WHERE marks > 100;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line ('NO DATA FOUND');
END;

 output : NO DATA FOUND

DECLARE
   lv_name   VARCHAR2 (30);
BEGIN
   SELECT NAME
     INTO lv_name
     FROM student_table
    WHERE marks < 100; -- it extract more than one rows
EXCEPTION
   WHEN TOO_MANY_ROWS
   THEN
      DBMS_OUTPUT.put_line ('TOO MANY ROWS FOUND');
END;

output : TOO MANY ROWS FOUND

Pragma EXCEPTION_INIT

To handle error conditions (typically ORA- messages) that have no predefined name, you must use the OTHERS handler or the pragma EXCEPTION_INIT. A pragma is a compiler directive that is processed at compile time, not at run time.
In PL/SQL, the pragma EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle error number. That lets you refer to any internal exception by name and to write a specific handler for it. When you see an error stack, or sequence of error messages, the one on top is the one that you can trap and handle.

The pragma EXCEPTION_INIT in the declarative part of a PL/SQL block, subprogram, or package using the syntax

PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number);

where exception_name is the name of a previously declared exception and the number is a negative value corresponding to an ORA- error number. The pragma must appear somewhere after the exception declaration in the same declarative section, as shown in the following example:

DECLARE
   deadlock_detected EXCEPTION;
   PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
BEGIN
   ... -- Some operation that causes an ORA-00060 error
EXCEPTION
   WHEN deadlock_detected THEN
      -- handle the error
END;

User-defined Exception :

User can  caught their own exceptions in the declarative part of any PL/SQL anonymous block, subprogram, or package. You must raise user-defined exceptions explicitly.

For example, you might declare an exception named insufficient_funds to flag overdrawn bank accounts.

Raising Exceptions Explicitly

To raise an exception explicitly, use either the RAISE statement or RAISE_APPLICATION_ERROR procedure.

RAISE Statement

The RAISE statement explicitly raises an exception. Outside an exception handler, you must specify the exception name. Inside an exception handler, if you omit the exception name, the RAISE statement reraises the current exception.

Declaring, Raising, and Handling User-Defined Exception

CREATE PROCEDURE account_status (
  due_date DATE,
  today    DATE
) AUTHID DEFINER
IS
  past_due  EXCEPTION;  -- declare exception
BEGIN
  IF due_date < today THEN
    RAISE past_due;  -- explicitly raise exception
  END IF;
EXCEPTION
  WHEN past_due THEN  -- handle exception
    DBMS_OUTPUT.PUT_LINE ('Account past due.');
END;
/
BEGIN
  account_status ('1-JUL-10', '9-JUL-10');
END;
/

Result:

Account past due.

RAISE_APPLICATION_ERROR Procedure

You can invoke the RAISE_APPLICATION_ERROR procedure (defined in the DBMS_STANDARD package) only from a stored subprogram or method. Typically, you invoke this procedure to raise a user-defined exception and return its error code and error message to the invoker.

To invoke RAISE_APPLICATION_ERROR, use this syntax:

RAISE_APPLICATION_ERROR (error_code, message[, {TRUE | FALSE}]);

You must have assigned error_code to the user-defined exception with the EXCEPTION_INIT pragma. The syntax is:

PRAGMA EXCEPTION_INIT (exception_name, error_code)

The error_code is an integer in the range -20000..-20999 and the message is a character string of at most 2048 bytes.

Raising User-Defined Exception with RAISE_APPLICATION_ERROR

CREATE PROCEDURE account_status (
  due_date DATE,
  today    DATE
) AUTHID DEFINER
IS
BEGIN
  IF due_date < today THEN                   -- explicitly raise exception
    RAISE_APPLICATION_ERROR(-20000, 'Account past due.');
  END IF;
END;
/

DECLARE
  past_due  EXCEPTION;                       -- declare exception
  PRAGMA EXCEPTION_INIT (past_due, -20000);  -- assign error code to exception
BEGIN
  account_status ('1-JUL-10', '9-JUL-10');   -- invoke procedure
EXCEPTION
  WHEN past_due THEN                         -- handle exception
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLERRM(-20000)));
END;
/

Result:

ORA-20000: Account past due.



Assign String/Number to variable

The given script is shown assign value/string into variable. one important thing is dont give space before and after equal (=) sign.

#!/bin/bash
a=23              # Simple case
echo $a
b=$a
echo $b
# Now, getting a little bit fancier (command substitution).
a=`echo Hello!`   # Assigns result of 'echo' command to 'a' ...
echo $a
a=`ls -l|tail -n 3`         # Assigns result of 'ls -l' command to 'a'
echo $a           # Unquoted, however, it removes tabs and newlines.
echo
echo "$a"         # The quoted variable preserves whitespace.

strVar="Hello"
echo "$strVar"

exit 0

Here
a=23                   --> value 23 is assigned into variable a
echo $a               --> used to display value at a (@a)
b=$a                  --> can assign variable to another variable
a=`echo Hello!`  --> can execute any shell command and assign the output into variable
strVar="Hello"    --> assign the string into variable

Get the input from command line(STDIN) & Write into File & Display in STDOUT put

#!/bin/bash
echo -e "Enter the Rollno";
read rollNo;
echo -e "Enter the Name";
read name;
echo -e "Enter the Marks";
read marks;
echo "$rollNo|$name|$marks" > studentDb
echo -e " StudentDb is created....."
echo -e " StudentDb records :"
cat studentDb

OUTPUT :

-bash-3.2$ sh HelloWorld
Enter the Rollno
07mx10
Enter the Name
Chidambaram
Enter the Marks
100
 StudentDb is created.....
 StudentDb records :
07mx10|Chidambaram|100

Here
#!/bin/bash            --> the program will run in bash shell
read <variable>     --->  is used to get the input from keyboard, no need to define any data types.
>                           -->   is used to redirect the output into studentDb file
cat studentDb         -->  the file content is displayed in command line

Listagg String aggregate function

Oracle 11gR2 listagg built-in function allows for many table columns to be displayed within a single row, a non-first-normal form display: LISTAGG is a built-in function that enables us to perform string aggregation natively.

The LISTAGG function has the following syntax structure:
     LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]

LISTAGG is an aggregate function that can optionally be used as an analytic (i.e. the optional OVER() clause). The following elements are mandatory:

• the column or expression to be aggregated;
• the WITHIN GROUP keywords;
• the ORDER BY clause within the grouping.

SQL> select * from student_table;

ROLLNUMBER NAME                          MARKS SECTION RESULT            DATEOFJOIN
---------- ---------------------------------------------- ----- ------- ----------------- -----------
      1000 chidambaram                                   99 A                       Pass              12/24/2012
      1001 raja                                                  76 A                      Pass               12/24/2012
      1002 ram                                                  48 A                      Fail                12/24/2012
      1003 jaya                                                  36 A                     Fail                12/23/2012
      1004 bala                                                  99 A                     Pass               12/23/2012
      1005 sam                                                  76 A                     Pass               12/22/2012
6 rows selected

SQL> SELECT  LISTAGG(name, ',') WITHIN GROUP (ORDER BY name) AS StudentList
  2      FROM   student_table  3  ;
STUDENTLIST
--------------------------------------------------------------------------------
bala,chidambaram,jaya,raja,ram,sam

Implicit and Explicit Cursors

Implicit cursor :

   It is implemented directly in a execution block. we cannot control the cursor . In PL/SQL you also use implicit cursors for DML statements and single select statements. it must return only one row
     Implicit (static) cursor is commonly refers to the good old SELECT INTO, in which Oracle implicitly opens, executes and closes the cursor for you, depositing any selected values of a single row INTO program data structures.
Example :
CREATE OR REPLACE PROCEDURE select_student_name (rollnum IN rollno.student%TYPE)
IS
   lv_stud_name student.name%TYPE;
BEGIN
   SELECT name INTO  lv_stud_name
     FROM student
    WHERE rollno = rollnum;
END;



The single select is a simple solution, but insufficient to solve the following problems.

• You may need to process more general result sets which return more or less than one row
• You may need to process rows in a specific order
• You may need to control the execution of your program depending on the result set.

To address these problems you need to use explicit cursors.

Working with explicit cursors

 Explicit cursors work the same way implicit cursors work, but you control the execution explicitly. It must declared in declaration part. it returns more than one row.

 DECLARE
  v_ename VARCHAR2(12);
  v_empno NUMBER:=7839;
--Oracle allocates memory and processes the query it is declared in declaration part
  CURSOR ename_cursor IS
    SELECT ename
      FROM emp
      WHERE empno=v_empno;
BEGIN
  OPEN ename_cursor;  -- Oracle binds variables, and executes query identifying the active set.
  --Oracle fetches a row from the active set, sets the value of v_ename, and advances the pointer to the active set.  
  FETCH ename_cursor INTO v_ename;
  CLOSE ename_cursor;  -- release the memory
END;
/

 Cursor attributes:

Cursor Attributes
Attribute	Description
cur%ISOPEN	Returns TRUE if cursor is open.
cur%NOTFOUND	Returns FALSE if the last FETCH found a row.
cur%FOUND	Returns TRUE if the last FETCH found a row..  (Logical inverse of %NOTFOUND).
cur%ROWCOUNT	Returns the number of rows modified by the DML statement.
SQL%BULK_ROWCOUNT	Returns the number of rows processed for each execution of the bulk DML operation.

 

BULK Collect

     BULK COLLECT allows you to retrieve multiple rows of data directly into PL/SQL Collections. It will raise NO_DATA_FOUND if it doesn't find any rows, but it certainly doesn't raise TOO_MANY_ROWS if it finds more than one!
   Specifically, memory for collections is stored in the program global area (PGA), not the system global area (SGA). SGA memory is shared by all sessions connected to Oracle Database, but PGA memory is allocated for each session. Thus, if a program requires 5MB of memory to populate a collection and there are 100 simultaneous connections, that program causes the consumption of 500MB of PGA memory, in addition to the memory allocated to the SGA.Fortunately, PL/SQL makes it easy for developers to control the amount of memory used in a BULK COLLECT operation by using the LIMIT clause.
Suppose I need to retrieve all the rows from the employees table and then perform some compensation analysis on each row. I can use BULK COLLECT as follows:

PROCEDURE process_all_rows
IS
   TYPE employees_aat 
   IS TABLE OF employees%ROWTYPE
      INDEX BY PLS_INTEGER;
   l_employees employees_aat;
BEGIN
   SELECT *
   BULK COLLECT INTO l_employees
      FROM employees;
     
   FOR indx IN 1 .. l_employees.COUNT 
   LOOP
       analyze_compensation 
      (l_employees(indx));
   END LOOP;
END process_all_rows;

Very concise, elegant, and efficient code. If, however, my employees table contains tens of thousands of rows, each of which contains hundreds of columns, this program can cause excessive PGA memory consumption.
Consequently, you should avoid this sort of "unlimited" use of BULK COLLECT. Instead, move the SELECT statement into an explicit cursor declaration and then use a simple loop to fetch many, but not all, rows from the table with each execution of the loop body,
 

Using BULK COLLECT with LIMIT clause

PROCEDURE process_all_rows (limit_in IN PLS_INTEGER DEFAULT 100)
IS
    CURSOR employees_cur 
    IS 
        SELECT * FROM employees;

    TYPE employees_aat IS TABLE OF employees_cur%ROWTYPE
        INDEX BY PLS_INTEGER;

    l_employees employees_aat;
BEGIN   
    OPEN employees_cur;
    LOOP
        FETCH employees_cur 
            BULK COLLECT INTO l_employees LIMIT limit_in;
        EXIT WHEN table_with_227_rows_cur%NOTFOUND;
        FOR indx IN 1 .. l_employees.COUNT 
        LOOP
            analyze_compensation (l_employees(indx));
        END LOOP;

        EXIT WHEN l_employees.COUNT < limit_in;

   END LOOP;

   CLOSE employees_cur;
END process_all_rows; 

REF Cursor

                  Ref Cursor is the method to returns result sets to client applications (like C, VB, etc).
You cannot define ref cursors outside of a procedure or function in a package specification or body. Ref cursors can only be processed in the defining procedure or returned to a client application. Also, a ref cursor can be passed from subroutine to subroutine and a cursor cannot. To share a static cursor like that, you would have to define it globally in a package specification or body. Because using global variables is not a very good coding practice in general, Ref cursors can be used to share a cursor in PL/SQL without having global variables getting into the mix. 


Last, using static cursors—with static SQL (and not using a ref cursor) —is much more efficient than using ref cursors, and the use of ref cursors should be limited to

• Returning result sets to clients
• Sharing cursors across many subroutines (very similar to the above point, actually)
• Achieving your goal when there is no other efficient/effective means of doing so, such as when dynamic SQL must be used 

Example 1:

DECLARE
type refcursor is REF CURSOR;
stud_cursor refcursor;
stud_desc student%rowtype;
BEGIN
open stud_cursor for select * from student;
loop
    fetch stud_cursor into stud_desc;
    exit when stud_cursor%notfound;
    dbms_output.put_line(stud_desc.rollno ||'|'||stud_desc.name || '|' || stud_desc.total);
end loop;
END 

Example 2:

CREATE OR REPLACE PACKAGE CURSPKG AS 

TYPE T_CURSOR IS REF CURSOR;  
PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER,IO_CURSOR IN OUT T_CURSOR); 
PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR,DEPTCURSOR OUT T_CURSOR);
 
END  CURSPKG; / CREATE OR REPLACE PACKAGE BODY CURSPKG AS 
PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER,IO_CURSOR IN OUT T_CURSOR) IS V_CURSOR T_CURSOR;
BEGIN     
     IF N_EMPNO <> 0 THEN 

      OPEN V_CURSOR FOR SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME FROM EMP, DEPT           WHERE   EMP.DEPTNO = DEPT.DEPTNO AND EMP.EMPNO = N_EMPNO;

    ELSE 

    OPEN V_CURSOR FOR SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; 

      END IF;
    IO_CURSOR := V_CURSOR; 
END OPEN_ONE_CURSOR;


PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR, DEPTCURSOROUT T_CURSOR) IS

V_CURSOR1 T_CURSOR;
V_CURSOR2 T_CURSOR;
BEGIN 

 OPEN V_CURSOR1 FOR SELECT * FROM EMP; OPEN V_CURSOR2 FOR SELECT * FROM DEPT;
EMPCURSOR := V_CURSOR1;
DEPTCURSOR := V_CURSOR2;
END OPEN_TWO_CURSORS; END CURSPKG; 


                     Cursor variables are cursors opened by a pl/sql routine and fetched from by another application or pl/sql routine (in 7.3 pl/sql routines can fetch from cursor variables as well as open them). The cursor variables are opened with the privileges of the owner of the procedure and behave just like they were completely contained within the pl/sql routine. It uses the inputs to decide what database it will run a query on. Here is an example:


create or replace package types
as
    type cursorType is ref cursor;
end;
/

create or replace function sp_ListEmp return types.cursortype
as
    l_cursor    types.cursorType;
begin
    open l_cursor for select ename, empno from emp order by ename;
    return l_cursor;
end;
/
create or replace procedure getemps( p_cursor in out types.cursorType )
as
begin
      open p_cursor for select ename, empno from emp order by ename;
end;
/