Execute Immediate with function

CREATE OR REPLACE FUNCTION
single_number_value (
   table_in    IN VARCHAR2,
   column_in   IN VARCHAR2,
   where_in    IN VARCHAR2)
   RETURN NUMBER
IS
   l_return   NUMBER;
BEGIN
   EXECUTE IMMEDIATE
         'SELECT '
      || column_in
      || ' FROM '
      || table_in
      || ' WHERE '
      || where_in
      INTO l_return;
   RETURN l_return;
END;



BEGIN
   DBMS_OUTPUT.put_line (
      single_number_value (
                'employees',
                'salary',
                'employee_id=200'));
END; 

Execute Immediate with Procedure

CREATE OR REPLACE PROCEDURE

show_number_values (

   table_in    IN VARCHAR2,

   column_in   IN VARCHAR2,

   where_in    IN VARCHAR2)

IS

   TYPE values_t IS TABLE OF
NUMBER;



   l_values   values_t;

BEGIN

   EXECUTE IMMEDIATE

         'SELECT '

      || column_in

      || ' FROM '

      || table_in

      || ' WHERE '

      || where_in

      BULK COLLECT INTO
l_values;



   FOR indx IN 1 ..
l_values.COUNT

   LOOP

      DBMS_OUTPUT.put_line

      (l_values (indx));

   END LOOP;

END;



And when I call the
procedure for the standard employees table

BEGIN

   show_number_values (

      'employees',

      'salary',

      'department_id = 10

       order by salary
desc');

END;

Single Row Functions in ORACLE SQL

The single row functions are categorized into

Character Functions: Character functions accept character inputs and can return either character or number values as output.

Number Functions: Number functions accepts numeric inputs and returns only numeric values as output.

Date Functions: Date functions operate on date data type and returns a date value or numeric value.

Conversions Functions: Converts from one data type to another data type.

General Functions

Let see each function with an example:

Character Functions Example

1. LOWER

The Lower function converts the character values into lowercase letters.

SELECT lower('ORACLE') FROM DUAL;

2. UPPER

The Upper function converts the character values into uppercase letters.

SELECT upper('oracle') FROM DUAL;

3. INITCAP

The Initcap function coverts the first character of each word into uppercase and the remaining characters into lowercase.

SELECT initcap('LEARN ORACLE') FROM DUAL;

4. CONCAT

The Concat function coverts the first string with the second string.

SELECT concat('Oracle',' Backup) FROM DUAL;

5. SUBSTR

The Substr function returns specified characters from character value starting at position m and n characters long. If you omit n, all characters starting from position m to the end are returned.

Syntax: substr(string [,m,n])

SELECT substr('ORACLE DATA RECOVERY',8,4) FROM DUAL;

SELECT substr('ORACLE DATA PUMP',8) FROM DUAL;

You can specify m value as negative. In this case the count starts from the end of the string.

SELECT substr('ORACLE BACKUP',-6) FROM DUAL;

6. LENGTH

The Length function is used to find the number of characters in a string.

SELECT length('Oracle Data Guard') FROM DUAL;

7. INSTR

The Instr function is used to find the position of a string in another string. Optionally you can provide position m to start searching for the string and the occurrence n of the string. By default m and n are 1 which means to start the search at the beginning of the search and the first occurrence.

Syntax: instr('Main String', 'substring', [m], [n])

SELECT instr('oralce apps','app') FROM DUAL;

SELECT instr('oralce apps is a great application','app',1,2) FROM DUAL;

8. LPAD

The Lpad function pads the character value right-justified to a total width of n character positions.

Syntax: lpad(column, n, 'string');

SELECT lpad('100',5,'x') FROM DUAL;

9. RPAD

The Rpad function pads the character value left-justified to a total width of n character positions.

Syntax: rpad(column, n, 'string');

SELECT rpad('100',5,'x') FROM DUAL;

10. TRIM

The Trim function removes the leading or trailing or both the characters from a string.

Syntax: trim(leading|trailing|both, trim_char from trim_source)

SELECT trim('O' FROM 'ORACLE') FROM DUAL;

11. REPLACE

The Replace function is used to replace a character with another character in a string.

Syntax: replace(column, old_char,new_char)

SELECT replace('ORACLE DATA BACKUP', 'DATA','DATABASE') FROM DUAL;

Number Functions Example

1. ROUND

The Round function rounds the value to the n decimal values. If n is not specified, there won't be any decimal places. If n is negative, numbers to the left of the decimal point are rounded.

Syntax: round(number,n)

SELECT round(123.67,1) FROM DUAL;

SELECT round(123.67) FROM DUAL;

SELECT round(123.67,-1) FROM DUAL;

2. TRUNC

The Trunc function truncates the value to the n decimal places. If n is omitted, then n defaults to zero.

Syntax: trunc(number,n)

SELECT trunc(123.67,1) FROM DUAL;

SELECT trunc(123.67) FROM DUAL;

3. MOD

The Mod function returns the remainder of m divided by n.

Syntax: mod(m,n)

SELECT mod(10,5) FROM DUAL;

Date Functions Example

1. SYSDATE

The Sysdate function returns the current oracle database server date and time.

SELECT sysdate FROM DUAL;

2. Arithmetic with Dates

You can add or subtract the number of days or hours to the dates. You can also subtract the dates

SELECT sysdate+2 "add_days" FROM DUAL;

SELECT sysdate-3 "sub_days" FROM DUAL;

SELECT sysdate+3/24 "add_hours" FROM DUAL;

SELECT sysdate-2/24 "sub_hours" FROM DUAL;

SELECT sysdate-hire_date "sub_dates" FROM EMPLOYEES; -- returns number of days between the two dates.

3. MONTHS_BETWEEN

The Months_Between function returns the number of months between the two given dates.

Syntax: months_between(date1,date2)

SELECT months_between(sysdate,hire_date) FROM EMPLOYEES:

SELECT months_between('01-JUL-2000', '23-JAN-2000') FROM DUAL;

4. ADD_MONTHS

The Add_Months is used to add or subtract the number of calendar months to the given date.

Syntax: add_months(date,n)

SELECT add_months(sysdate,3) FROM DUAL;

SELECT add_months(sysdate,-3) FROM DUAL;

SELECT add_months('01-JUL-2000', 3) FROM DUAL;

5. NEXT_DAY

The Next_Day function finds the date of the next specified day of the week. The syntax is

NEXT_DAY(date,'char')

The char can be a character string or a number representing the day.

SELECT next_day(sysdate,'FRIDAY') FROM DUAL;

SELECT next_day(sysdate,5) FROM DUAL;

SELECT next_day('01-JUL-2000', 'FRIDAY') FROM DUAL;

6. LAST_DAY

The Last_Day function returns the last day of the month.

SELECT last_day(sysdate) FROM DUAL;

SELECT last_day('01-JUL-2000') FROM DUAL;

7. ROUND

The Round function returns the date rounded to the specified format. The Syntax is

Round(date [,'fmt'])

SELECT round(sysdate,'MONTH') FROM DUAL;

SELECT round(sysdate,'YEAR') FROM DUAL;

SELECT round('30-OCT-85','YEAR') FROM DUAL;

8. TRUNC

The Trunc function returns the date truncated to the specified format. The Syntax is

Trunc(date [,'fmt'])

SELECT trunc(sysdate,'MONTH') FROM DUAL;

SELECT trunc(sysdate,'YEAR') FROM DUAL;

SELECT trunc('01-MAR-85','YEAR') FROM DUAL;

Difference between Case and Decode in Oracle SQL

Databases before Oracle 8.1.6 had only the DECODE function. CASE was introduced in Oracle 8.1.6 as a standard, more meaningful and more powerful function.

Everything DECODE can do, CASE can. There is a lot else CASE can do though, which DECODE cannot. We’ll go through detailed examples in this article.

1. CASE can work with logical operators other than ‘=’

DECODE performs an equality check only. CASE is capable of other logical comparisons such as < > etc. It takes some complex coding – forcing ranges of data into discrete form – to achieve the same effect with DECODE.

An example of putting employees in grade brackets based on their salaries. This can be done elegantly with CASE.

SQL> select ename

        , case

             when sal < 1000

                  then 'Grade I'

             when (sal >=1000 and sal < 2000)

                  then 'Grade II'

             when (sal >= 2000 and sal < 3000)

                  then 'Grade III'

             else 'Grade IV'

          end sal_grade

   from emp

   where rownum < 4;

ENAME      SAL_GRADE

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

SMITH      Grade I

ALLEN      Grade II

WARD       Grade II

2. CASE can work with predicates and searchable subqueries

DECODE works with expressions that are scalar values only. CASE can work with predicates and subqueries in searchable form.

An example of categorizing employees based on reporting relationship, showing these two uses of CASE.

SQL> select e.ename,

           case

             -- predicate with "in"

             -- set the category based on ename list

             when e.ename in ('KING','SMITH','WARD')

                  then 'Top Bosses'

             -- searchable subquery

             -- identify if this emp has a reportee

             when exists (select 1 from emp emp1

                         where emp1.mgr = e.empno)

                 then 'Managers'

            else

                'General Employees'

          end emp_category

   from emp e

   where rownum < 5;

ENAME      EMP_CATEGORY

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

SMITH      Top Bosses

ALLEN      General Employees

WARD       Top Bosses

JONES      Managers

3. CASE can work as a PL/SQL construct

DECODE can work as a function inside SQL only. CASE can be an efficient substitute for IF-THEN-ELSE in PL/SQL.

SQL> declare

      grade char(1);

    begin

      grade := 'b';

      case grade

        when 'a' then dbms_output.put_line('excellent');

        when 'b' then dbms_output.put_line('very good');

        when 'c' then dbms_output.put_line('good');

        when 'd' then dbms_output.put_line('fair');

       when 'f' then dbms_output.put_line('poor');

       else dbms_output.put_line('no such grade');

     end case;

   end;

   /

PL/SQL procedure successfully completed.

CASE can even work as a parameter to a procedure call, while DECODE cannot.

SQL> var a varchar2(5);

SQL> exec :a := 'THREE';

PL/SQL procedure successfully completed.

SQL>

SQL> create or replace procedure proc_test (i number)

    as

    begin

      dbms_output.put_line('output = '||i);

    end;

    /

Procedure created.

SQL> exec proc_test(decode(:a,'THREE',3,0));

BEGIN proc_test(decode(:a,'THREE',3,0)); END;

                *

ERROR at line 1:

ORA-06550: line 1, column 17:

PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL

statement only

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

SQL> exec proc_test(case :a when 'THREE' then 3 else 0 end);

output = 3

PL/SQL procedure successfully completed.

4. Careful! CASE handles NULL differently

Check out the different results with DECODE vs NULL.

SQL> select decode(null

                , null, 'NULL'

                      , 'NOT NULL'

                 ) null_test

    from dual;

NULL

----

NULL

SQL> select case null

           when null

           then 'NULL'

           else 'NOT NULL'

           end null_test

    from dual;

NULL_TES

--------

NOT NULL

The “searched CASE” works as does DECODE.

SQL>  select case

           when null is null

           then 'NULL'

           else 'NOT NULL'

           end null_test

   from dual

SQL> /

NULL_TES

--------

NULL

5. CASE expects datatype consistency, DECODE does not

Compare the two examples below- DECODE gives you a result, CASE gives a datatype mismatch error.

SQL> select decode(2,1,1,

                   '2','2',

                   '3') t

    from dual;

         T

----------

         2

SQL> select case 2 when 1 then '1'

                when '2' then '2'

                else '3'

           end

    from dual;

            when '2' then '2'

                 *

ERROR at line 2:

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

6. CASE is ANSI SQL-compliant

CASE complies with ANSI SQL. DECODE is proprietary to Oracle.

7. The difference in readability

In very simple situations, DECODE is shorter and easier to understand than CASE.

SQL> -- An example where DECODE and CASE

SQL> -- can work equally well, and

SQL> -- DECODE is cleaner

SQL> select ename

         , decode (deptno, 10, 'Accounting',

                           20, 'Research',

                           30, 'Sales',

                               'Unknown') as department

    from   emp

    where rownum < 4;

ENAME      DEPARTMENT

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

SMITH      Research

ALLEN      Sales

WARD       Sales

SQL> select ename

         , case deptno

             when 10 then 'Accounting'

             when 20 then 'Research'

             when 30 then 'Sales'

             else         'Unknown'

             end as department

    from emp

    where rownum < 4;

ENAME      DEPARTMENT

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

SMITH      Research

ALLEN      Sales

WARD       Sales

Complicated logical comparisons in DECODE, even if technically achievable, are a recipe for messy, bug-prone code. When the same can be done more cleanly with CASE, go for CASE.

Execute Immediate with select Statement

declare
    type tmp_tbm is table of emp.ename%type;
    emp_tbl tmp_tbm;
    begin
    execute immediate 'SELECT ename FROM EMP'
    bulk collect INTO emp_tbl;
    for i in 1..emp_tbl.COUNT loop
    dbms_output.put_line(emp_tbl(i));
    end loop;
   end;

How to use execute immediate in PL/SQL Exception block

CREATE OR REPLACE PROCEDURE p1
IS
   l_id   NUMBER;
BEGIN
   SELECT employee_id
   INTO l_id
   FROM employees
   WHERE employee_id IN (100);
 --WHERE employee_id IN (100,200);

   DBMS_OUTPUT.put_line ('EMPLOYEE ID ==>  ' || l_id);
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DECLARE
         l_id1   NUMBER;
      BEGIN
         EXECUTE IMMEDIATE 'CREATE TABLE T1(ID NUMBER)';
         DBMS_OUTPUT.put_line ('EMPLOYEE ID NO_DATA_FOUND ==>  ');
      END;

      DBMS_OUTPUT.put_line ('EMPLOYEE ID ==>  NO_DATA_FOUND');
   WHEN TOO_MANY_ROWS
   THEN
      DECLARE
         l_id1   NUMBER;
      BEGIN
         SELECT employee_id
         INTO l_id1
         FROM employees
         WHERE employee_id IN (100);

         DBMS_OUTPUT.put_line ('EMPLOYEE ID TOO MANY ROWS ==>  ' || l_id1);
      END;
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('EMPLOYEE ID ==>  OTHERS');
END;

BEGIN
   p1;
END;





Thanks
Sajal Agarwal

How to create procedure and function in PL/SQL Exception block

CREATE OR REPLACE PROCEDURE p1
IS
   l_id   NUMBER;
BEGIN
   SELECT employee_id
   INTO l_id
   FROM employees
   --WHERE employee_id IN (100,200);
   WHERE employee_id IN (1);

   DBMS_OUTPUT.put_line ('EMPLOYEE ID ==>  ' || l_id);
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DECLARE
         l_id    NUMBER;
         l_id1   NUMBER;

         PROCEDURE p1
         IS
            l_id   NUMBER;
         BEGIN
            l_id   := 100;
            DBMS_OUTPUT.put_line ('PRO ID VALUE ==> ' || l_id);
         END p1;
      BEGIN
         l_id    := 200;
         l_id1   := 300;
         DBMS_OUTPUT.put_line(   'VALUE ID VALUE ==> '
                              || l_id
                              || CHR (10)
                              || 'SECOND ID VALUE ==> '
                              || l_id1);
         p1;
      END;

      DBMS_OUTPUT.put_line ('EMPLOYEE ID ==>  NO_DATA_FOUND');
   WHEN TOO_MANY_ROWS
   THEN
      DECLARE
         l_id1   NUMBER;
      BEGIN
         SELECT employee_id
         INTO l_id1
         FROM employees
         WHERE employee_id IN (100);

         DBMS_OUTPUT.put_line ('EMPLOYEE ID TOO MANY ROWS ==>  ' || l_id1);
      END;
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('EMPLOYEE ID ==>  OTHERS');
END;





Thanks
Sajal Agarwal

How to create procedure and function with in another procedure and function in declare block

-------------------------------------- IN DECLARE BLOCK CREATE NEW PROC AND FUNC AND CREATE FUN WITHIN PROC ---------------------------------------------


DECLARE
   l_id       NUMBER;
   l_id1      NUMBER;
   l_fun_id   NUMBER;

   PROCEDURE p1
   IS
      l_proc_id   NUMBER;
      l_proc_FUNC_id   NUMBER;
   FUNCTION PRO_F
      RETURN NUMBER
   IS
      l_PRO_fun_id   NUMBER;
   BEGIN
      l_PRO_fun_id   := 1111;
      DBMS_OUTPUT.put_line ('FUUNC ID VALUE ==> ' || l_PRO_fun_id);
      RETURN l_PRO_fun_id;
   END PRO_F;  
   BEGIN
      l_proc_id   := 100;
      l_proc_FUNC_id:= PRO_F;
      DBMS_OUTPUT.put_line ('PRO ID VALUE ==> ' || l_proc_id||CHR(9)|| 'PRO FUNCTION ID VALUE ==> '||l_proc_FUNC_id);
   END p1;

   FUNCTION f1
      RETURN NUMBER
   IS
      l_fun_id   NUMBER;
   BEGIN
      l_fun_id   := 1000;
      DBMS_OUTPUT.put_line ('FUUNC ID VALUE ==> ' || l_fun_id);
      RETURN l_fun_id;
   END f1;
BEGIN
   l_id       := 200;
   l_id1      := 300;
   DBMS_OUTPUT.put_line(   'VALUE ID VALUE ==> '
                        || l_id
                        || CHR (10)
                        || 'SECOND ID VALUE ==> '
                        || l_id1);
   p1;
   l_fun_id   := f1;
   DBMS_OUTPUT.put_line ('THIRD ID VALUE ==> ' || l_fun_id);  
END; 

How to create procedure and function in declare block

-------------------------------------- IN DECLARE BLOCK CREATE NEW PROC ---------------------------------------------

DECLARE
   l_id    NUMBER;
   l_id1   NUMBER;

   PROCEDURE p1
   IS
      l_id   NUMBER;
   BEGIN
      l_id   := 100;
      DBMS_OUTPUT.put_line ('PRO ID VALUE ==> ' || l_id);
   END p1;
BEGIN
   l_id    := 200;
   l_id1   := 300;
   DBMS_OUTPUT.put_line(   'VALUE ID VALUE ==> '
                        || l_id
                        || CHR (10)
                        || 'SECOND ID VALUE ==> '
                        || l_id1);
   p1;
END;

-------------------------------------- IN DECLARE BLOCK CREATE NEW PROC AND FUNC ---------------------------------------------


DECLARE
   l_id       NUMBER;
   l_id1      NUMBER;
   l_fun_id   NUMBER;

   PROCEDURE p1
   IS
      l_proc_id   NUMBER;
   BEGIN
      l_proc_id   := 100;
      DBMS_OUTPUT.put_line ('PRO ID VALUE ==> ' || l_proc_id);
   END p1;

   FUNCTION f1
      RETURN NUMBER
   IS
      l_fun_id   NUMBER;
   BEGIN
      l_fun_id   := 1000;
      DBMS_OUTPUT.put_line ('FUUNC ID VALUE ==> ' || l_fun_id);
      RETURN l_fun_id;
   END f1;
BEGIN
   l_id       := 200;
   l_id1      := 300;
   DBMS_OUTPUT.put_line(   'VALUE ID VALUE ==> '
                        || l_id
                        || CHR (10)
                        || 'SECOND ID VALUE ==> '
                        || l_id1);
   p1;
   l_fun_id   := f1;
   DBMS_OUTPUT.put_line ('THIRD ID VALUE ==> ' || l_fun_id);
END;