How to call stored procedure from within another......?

CREATE OR REPLACE PROCEDURE EMPANNSAL(P_SAL IN OUT NUMBER)
IS
BEGIN
  P_SAL := P_SAL*12;
END EMPANNSAL;
/
-- INVOKING FROM THE HOST

VARIABLE G_SAL NUMBER
BEGIN
  :G_SAL := 5000;
END;
/

PRINT G_SAL
EXECUTE EMPANNSAL(:G_SAL)
PRINT G_SAL


-- INVOKE FROM ANOTHER PROCEDURE
CREATE OR REPLACE PROCEDURE EMPANSAL(P_DEPT IN NUMBER)
IS
  CURSOR EMPCUR IS SELECT LAST_NAME,SALARY
                   FROM EMPLOYEES
                   WHERE DEPARTMENT_ID=P_DEPT;
  V_SAL NUMBER;
BEGIN
  FOR EMPREC IN EMPCUR LOOP
      DBMS_OUTPUT.PUT_LINE(EMPREC.LAST_NAME||' GETS ANNUALY ');
      V_SAL := EMPREC.SALARY;
      EMPANNSAL(V_SAL);
      DBMS_OUTPUT.PUT_LINE(V_SAL);
  END LOOP;
END EMPANSAL;
/
SHO ERR
EXECUTE EMPANSAL(50)
      

Read More

How to check if a stored procedure is exist........?

Oracle data dictionary also called as system catalogue.the data dictionary  which contain information about database.

1.The data dictionary contains defination of all schema objects in the database,that is tables,view,stored procedure,sequences,synonyms,indexes etc.

2.it also privides information about how much space has been allocated for these schema objects.


How to check if a stored procedure is exist........?

You can try this Example:-


How to find which procedure are  being used in database for example name..?

Example is as follows....

SELECT * FROM USER_OBJECTS
WHERE OBJECT_TYPE='PROCEDURE';


SELECT *
FROM USER_PROCEDURES;


When You need to get all defination of procedures like source code or text  to achive this task
we can enter this select statement.

SELECT * FROM USER_SOURCE

Read More

Procedure in Plsql

A procedure in oracle is program Unit that performs a particular task it also called as subprogram.this procedure can be

be invoked by another procedure or program which is called the calling program.

It can be created in pl sql block,also inside a package,and schema level.


Syntax

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
  < procedure_body >
END procedure_name;

The foll example creates a simple procedure that displays Good Morning On the screen when it executed.

Create or replace procedure pro1()
AS
begin

dbms_output.put_line('Good Morning');

End;



When above code will executed it will show the foll output.

procedure created.

Execute pro1;

Good Morning
Pl sql Procedure successfully completed




Example 2.

Here is an example of procedure that uses a Named Defined Procedure


Create [or Replace] Function  function_name
[(parameter[,Parameter])]
Return  return datatype
Is|As
[declaration_section]
Exception_name EXCEPTION;
Begin
Executable section
Raise exception_name;
Exception
When exception_name then
[statements]
When others Then
[statements]
End [function_name];


Syntax for Procedure:

Create [or Replace] Procedure Procedure_name
[(parameter [,parameter])]
is

[declaration_section]

exception_section]

exception_name EXCEPTION;

begin

executable_section
Raise exception_name;
EXCEPTION
when exception_name then
[statements]
when others Then
[statements]
End [Procedure_name];




CREATE OR REPLACE PROCEDURE add_new_emp
   (empno  IN NUMBER, fname in varchar2 )
IS
   no_emp EXCEPTION;
BEGIN
   IF empno = 0 THEN
      RAISE no_emp;
   ELSE
      INSERT INTO emp (employee_id, first_name )
      VALUES ( empno, fname );
   END IF;
EXCEPTION
   WHEN no_emp THEN
      raise_application_error (-20001,'You must have sales in order to subm
e order.');
   WHEN OTHERS THEN
      raise_application_error (-20002,'An error has occurred inserting an o
');
END;
/

Read More

For loop in oracle

Pl sql for loop is an iterative statement that allows to execute
a series of statements a fixed number of times.
The foll is a syntax for loop statement.

FOR loop_counter IN [REVERSE] lower_bound .. higher_bound
LOOP
   sequence_of_statements;
END LOOP;

SQL> declare
  2  n1 number;
  3  begin
  4  for  i in 1.. 10
  5  loop
  6  dbms_output.put_line(i);
  7  end loop;
  8  end;
  9  /

Output :-

1
2
3
4
5
6
7
8
9
10

in the

PL/SQL procedure successfully completed.

in the next example we will use the RESERVE keyword to print a list of integers in

descending order.

declare
n1 number;
begin
for  i in reverse 1.. 10
loop
dbms_output.put_line(i);
end loop;
end;
/
Output

SQL> /
10
9
8
7
6
5
4
3
2
1



Example 2.  write a PL/SQL block to display names and salary of employees.
.
 DECLARE
   V_NAME EMPLOYEES.LAST_NAME%TYPE;
   V_SAL  EMPLOYEES.SALARY%TYPE;
 BEGIN
   FOR V_ID IN 100..109 LOOP
       SELECT LAST_NAME,SALARY
       INTO   V_NAME,V_SAL
       FROM   EMPLOYEES
       WHERE  EMPLOYEE_ID=V_ID;
       DBMS_OUTPUT.PUT_LINE(V_NAME||' GETS '||V_SAL);
   END LOOP;
 END;

Output:-

King GETS 24000
Kochhar GETS 17000
De Haan GETS 17000
Hunold GETS 9000
Ernst GETS 6000
Austin GETS 4800
Pataballa GETS 4800
Lorentz GETS 4200
Greenberg GETS 12000
Faviet GETS 9000

PL/SQL procedure successfully completed.

Read More

Top Ten Sql Queries

1.Q. How is the primary key different from a unique key?
A. Both the primary and unique keys uniquely identify a record in a database table. main  difference is that you can have

more than one unique key per table, but only one primary key. Also, the primary key does not allow any null value, where

as  the unique key allows null value.

2.Q. What is the SQL syntax for sorting, and which is the default order?

A. Syntax:
The basic syntax of ORDER BY clause which would be used to sort result in ascending or descending order is as follows:

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

 The default sorting order is ascending.

select from  Employees order by Last_name

select  from employees order by hire_date Desc

For descending order simply desc

Following is an example, which would sort the result in ascending order by NAME and SALARY:
You can use More than one  column in order by clause.

SQL> SELECT * FROM Employees
     ORDER BY last_name, SALARY;



3. How to find third highest or second maximum salary of an Employee

Rownum is defined for ORACLE, while Top is defined for MS SQL  Both rownum and top functions the same,
i.e of selecting the top N tuples according to the query, where N is the number specified when both rownum .

 Select Rownum as rank, last_name, salary  from ( Select last_name, salary from employees order by sal desc) where rownum<=3;
So the above query is made for finding the three highest earners.

4. 1.What is the differance between Having & Where ?

Where Clause Is used For filtering Rows & Having Clause is for Filter Groups.
For Example to view the details of Employees who is working in department_id=50

select employee_id,salary,department_id
from employees
where department_id=50

Output :- EMPLOYEE_ID     SALARY DEPARTMENT_ID
----------- ---------- -------------
        198       2600            50
        199       2600            50
        120       8000            50
        121       8200            50
        122       7900            50
        123       6500            50
        124       5800            50
        125       3200            50
        126       2700            50
        127       2400            50
        128       2200            50


Group By Clause Example

select department_id,sum(salary)
from employees group by department_id

DEPARTMENT_ID SUM(SALARY)
------------- -----------
          100       51600
           30       24900
                     7000
           20       19000
           70       10000
           90       58000
          110       20300
           50      156400
           40        6500
           80      304500
           10        4400

DEPARTMENT_ID SUM(SALARY)
------------- -----------
           60       28800

12 rows selected.


5. Describe all aggregate Functions with example.

Aggregate functions return a single result row based on groups of rows, rather than on single rows.

select sum(salary) from employees
where department_id=40

select avg(salary) from employees
where department_id=40

select min(salary) from employees
where department_id=40

select max(salary) from employees
where department_id=40



6. What is the differance between truncate & delete command ?

 The delete command is used to remove rows from a table. a  where clause can be used to only remove some rows.

Truncate removes all records from a table and we can not be rolled back and no triggers will fired on truncate command. delete keeps

record into recycle bin(Buffer).

 SQL> delete from employee where employee_id=124;

1 row deleted.


SQL> truncate table employee;

Table truncated.


7. How to convert salary in words.

SQL> select to_char(to_date(salary,'j'),'jsp') from employees;


Output :-

TO_CHAR(TO_DATE(SALARY,'J'),'JSP')
------------------------------------------------
two thousand six hundred
two thousand six hundred
four thousand four hundred
thirteen thousand
six thousand
six thousand five hundred
ten thousand
twelve thousand
eight thousand three hundred
twenty-four thousand
seventeen thousand


8. What is the differance between Uniq key and primary key.?

You can have more than one unique key  per table but you can have onle one primary key per table.it creates an index for primary key automatically.

Sql does not allows null value for primary key but unique key has null value in a table.

9. To find max salary from each department.

select department_id ,max(salary) from employees
group by department_id


10.  Write a sql query to display current date & time.
select sysdate,systimestamp from dual.


11. How to view table comments

SELECT comments
FROM user_tab_comments
WHERE table_name='DEPARTMENTS'


12. How to add a comment to a Table.

COMMENT ON VIEW  V3
IS 'Employee Information data '



13. Write a sql query that list the names employees whose first_name starts with s or k

select employee_id,last_name,first_name from employees
where regexp_like(first_name,'^s','i');

EMPLOYEE_ID LAST_NAME                 FIRST_NAME
----------- ------------------------- ------------------
        203 Mavris                    Susan
        205 Higgins                   Shelley
        100 King                      Steven
        116 Baida                     Shelli
        117 Tobias                    Sigal
        123 Vollman                   Shanta
        128 Markle                    Steven
        138 Stiles                    Stephen
        161 Sewall                    Sarath
        166 Ande                      Sundar
        173 Kumar                     Sundita

EMPLOYEE_ID LAST_NAME                 FIRST_NAME
----------- ------------------------- ------------------
        192 Bell                      Sarah
        194 McCain                    Samuel


14.  How can i create en  empty table with same structure of employees?

create table temp as select * from employees where 1=2;



15. Display the last name, salary, and commission for all employees who earn commissions. Sort

data in descending order of salary and commissions.

SELECT last_name, salary, commission_pct

FROM employees

WHERE commission_pct IS NOT NULL

ORDER BY salary DESC, commission_pct DESC;

Read More

When Too_Many_Rows

Too_many exception is  ora error no ORA-01422 when you issue a select statement into single variable  that returns more than one row at that time this error will occur.if you want to handle this situation in your annonymous block or procedure use TOO_MANY_ROWS

exception.
The advantage of this approch is that you can customise your your messages user friendly rather
oracle  messages.

Example:- Write a pl sql block to accept first_name from user return his/her last_name.

declare
v_name  varchar2(20);
begin
select last_name into v_name from employees
where  first_name='&fname';
dbms_output.put_line('First_name of an employee is'||v_name);
Exception
when too_many_rows then
dbms_output.put_line('There are multiple employeesin this department');
end;

The Foll is the output
caught TOO_MANY_ROWS exception..

Enter value for fname: John
old   5: where  first_name='&fname';
new   5: where  first_name='John';
There are multiple employeesin this department

PL/SQL procedure successfully completed.

Read More

No_data_Found

When Pl sql block detects an error during the  program execution
normal execution  stops and an exception block is raised.

It is an pl sql error occur is raised during program execution, either implicitly by oracle server or explicitly by your program.

Example 1. Write a  Pl sql Block that to find an Employee Number   using Exception.

DECLARE
  lNAME EMPLOYEES.LAST_NAME%TYPE;
  SAL  EMPLOYEES.SALARY%TYPE;
  ANNSAL SAL%TYPE;
BEGIN
  SELECT LAST_NAME,SALARY
  INTO   lNAME,SAL
  FROM   EMPLOYEES
  WHERE  EMPLOYEE_ID=&EMPNO;
  ANNSAL := SAL*12;
  DBMS_OUTPUT.PUT_LINE(lNAME||' GETS '||SAL||' ANNUALLY '||ANNSAL);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE('NO SUCH EMPLOYEE...');
END;
/

Output:-

Enter value for empno: 100

old   9:   WHERE  EMPLOYEE_ID=&EMPNO;

new   9:   WHERE  EMPLOYEE_ID=100;

King GETS 24000 ANNUALLY 288000

PL/SQL procedure successfully completed.


The above program displays the name and address of a Employees Whose ID is given.
Since there is no Employees with ID value  100 in our database,
 the program raises the run-time exception NO_DATA_FOUND , which is caught  in EXCEPTION block.


Example :-  Exception Using Boolean Variables.

declare
   v_foundrows boolean := false;
begin
   for r in ( select * from sanemp where employee_id= &dno )
   loop
      dbms_output.put_line(r.last_name || ' ' || r.salary);
      v_foundrows := true;
   end loop;

   if not v_foundrows then
      raise no_data_found;
   end if;

exception
   when no_data_found then
      dbms_output.put_line('hello');
end;

Read More

Conditional Statement

Conditional selection statements, which run different statements for different data values.

The conditional selection statements are IF and and CASE.

The IF THEN statement has this structure:

IF condition THEN
  statements
END IF;
If the condition is true, the statements run; otherwise,
the IF statement does nothing. (For complete syntax, see "IF Statement".)

DECLARE
  grade CHAR(1);
BEGIN
  grade := 'B';

  IF grade = 'A' THEN
    DBMS_OUTPUT.PUT_LINE('Excellent');
  ELSIF grade = 'B' THEN
    DBMS_OUTPUT.PUT_LINE('Very Good');
  ELSIF grade = 'C' THEN
    DBMS_OUTPUT.PUT_LINE('Good');
  ELSIF grade = 'D' THEN
    DBMS_OUTPUT. PUT_LINE('Fair');
  ELSIF grade = 'F' THEN
    DBMS_OUTPUT.PUT_LINE('Poor');
  ELSE
    DBMS_OUTPUT.PUT_LINE('No such grade');
  END IF;
END;
/


OutPut:-

Very Good
PL/SQL procedure successfully completed.


case statement 


The case statement selects from sequence of conditions,and runs the corresponding statement.
it is simple & which evaluates a single expression & compares with several potential values.

The simple CASE statement has this structure:

CASE selector
WHEN selector_value_1 THEN statements_1
WHEN selector_value_2 THEN statements_2
...
WHEN selector_value_n THEN statements_n
[ ELSE
  else_statements ]
END CASE;]

Else clause is an optional if you do not specify an else clause and none of the result of the case

expression oracle  will raise an a case not found error.

The following is an example of a simple case statement that uses the grade type for selecting apprisal

of an candidate

DECLARE
  grade CHAR(1);
BEGIN
  grade := 'B';

  IF grade = 'A' THEN
    DBMS_OUTPUT.PUT_LINE('Excellent');
  ELSIF grade = 'B' THEN
    DBMS_OUTPUT.PUT_LINE('Very Good');
  ELSIF grade = 'C' THEN
    DBMS_OUTPUT.PUT_LINE('Good');
  ELSIF grade = 'D' THEN
    DBMS_OUTPUT. PUT_LINE('Fair');
  ELSIF grade = 'F' THEN
    DBMS_OUTPUT.PUT_LINE('Poor');
  ELSE
    DBMS_OUTPUT.PUT_LINE('No such grade');
  END IF;
END;

Output:

Very Good


LOOP Statements

Loop statements run the same statements with a series of different values. The loop statements are:

Basic LOOP
FOR LOOP
Cursor FOR LOOP
WHILE LOOP
The statements that exit a loop are:

EXIT
EXIT WHEN
The statements that exit the current iteration of a loop are:

CONTINUE
CONTINUE WHEN




EXIT WHEN Statement

The EXIT WHEN statement exits the current iteration of a loop when the condition in its WHEN clause is true, and transfers control to the end of either the current loop or an enclosing labeled loop.

Each time control reaches the EXIT WHEN statement, the condition in its WHEN clause is evaluated. If the condition is not true, the EXIT WHEN statement does nothing. To prevent an infinite loop, a statement inside the loop must make the condition true, as in Example 4-10.

In Example 4-10, the EXIT WHEN statement inside the basic LOOP statement transfers control to the end of the current loop when x is greater than 3. Example 4-10 is logically equivalent to Example 4-9.

Example 4-10 Basic LOOP Statement with EXIT WHEN Statement

DECLARE
  x NUMBER := 0;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE('Inside loop:  x = ' || TO_CHAR(x));
    x := x + 1;  -- prevents infinite loop
    EXIT WHEN x > 3;
  END LOOP;
  -- After EXIT statement, control resumes here
  DBMS_OUTPUT.PUT_LINE('After loop:  x = ' || TO_CHAR(x));
END;
/

Read More

Implicit Cursors

Implicit Cursors
Implicit cursors are automatically created by Oracle whenever an we  SQL statement is  process, when there is no explicit cursor for the statement.
Programmers cannot control the implicit cursors and the information in it.

Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.

In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has the attributes like %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement. The following table provides the description of the most used attributes:


The following program would update the table and increase salary of each customer by 500 an

d use the SQL%ROWCOUNT attribute to determine the number of rows affected:

DECLARE
Attribute Description
%FOUND Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.
%NOTFOUND The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.
%ISOPEN Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement.
%ROWCOUNT Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.
Any SQL cursor attribute will be accessed as sql%attribute_name as shown below in the example.

DECLARE
   total_rows number(2);
BEGIN
   UPDATE customers
   SET salary = salary + 500;
   IF sql%notfound THEN
      dbms_output.put_line('no customers selected');
   ELSIF sql%found THEN
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' customers selected ');
   END IF;
END;




When the above code is executed at SQL prompt, it produces the following result:

6 customers selected

PL/SQL procedure successfully completed.

Read More