What is Mutating Table Errors ?

A mutating table error occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement).

sql>CREATE OR REPLACE FUNCTION rec_call(sal NUMBER)

RETURN NUMBER IS

BEGIN

INSERT INTO employees(employee_id, last_name, email, hire_date, job_id, salary)

VALUES(1, 'Sharma', 'sharma@gmail.com',

SYSDATE, 'SA_REP', sal);

RETURN (sal + 100);

END;

Function created.


sql>UPDATE employees

SET salary = Rec_call(2000)

WHERE employee_id = 197

Note:- The Rec_call function contains an INSERT statement that inserts a new record into the EMPLOYEES table and returns the input salary value incremented by 100. This function is invoked in the UPDATE statement that modifies the salary of employee 197 to the amount returned from the function.The UPDATE statement fails with an error indicating that the table is mutating (that is, changes are already in progress in the same table). In the following example, the query_call_sql function queries the SALARY column of the EMPLOYEES table

Read More

Rownum In Oracle

Example :- To Display First  5 top Record in  Oracle ,You can use Rownum Command because oracle does not support Top


select employee_id,last_name
from employees
where rownum <=5
/


EMPLOYEE_ID LAST_NAME
----------- -----------------------
        198 OConnell
        199 Grant
        200 Whalen
        201 Hartstein
        202 Fay

Read More

PL/SQL interview questions

1. What are the various types of database triggers ?

Answer: There are 12 types of triggers they are combination of:

Insert, delete and update triggers.

Before and after triggers .

Row and statement triggers

2.What is advantage of a stored procedure over a database trigger?

3. What is the use of control files ?

Answer: contains pointers to locations of various data files, redo log files, etc.

Answer: we have control over the firing of stored procedure but we have no control over the firing of trigger 

4.What is maximum no. of statements that can be specified in trigger statement?

Answer: one 

5.Can views be specified in trigger statement?

Answer: no

6. --WRITE A PROGRAM TO PRINT EVEN NUMBERS FROM 1 TO 100

 DECLARE

 N NUMBER(3) :=0;

 BEGIN

 WHILE N<=100

 LOOP

 N :=N+2;

 DBMS_OUTPUT.PUT_LINE(N);

 END LOOP;

 END;

---

7. --Write a PL_SQL block to insert numbers into temp table  Using For Loop

Example :-

Insert the numbers 1..10 excludining 6

BEGIN

FOR i in 1..10 LOOP

  IF i = 6  THEN

    null;

  ELSE

    INSERT INTO  temp(results)

    VALUES (i);

 END IF;

END LOOP;

 COMMIT;

END;

Execute a  PLSQL statement to verify that your PLSQL BLock Worked.

Select * from temp;

8. Using If condition write a pl sql block that to find a number wheather it even or odd

declare

  num number(5);

begin

  num:=&num;

  if num mod 2 =0 then

    dbms_output.put_line('Accepted Number '||num||' is even');

  else

    dbms_output.put_line('Accepted Number '||num||' is odd');

  end if;

end;

9. What is the differance between a procedure and Function.

Both Procedures and functions have the capability of accesing parameters but function returns a value
to the PLSQL block where as procedures do not return any value.
You can use a function in a normal SQL where as you cannot use a procedure in SQL statements.

Read More

Sql-Basic_Queries

1.Get the first_day of the Month
SQL> SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month"
2 FROM DUAL;


First day
---------
01-AUG-14


2.Get the Last_day of the Current Month


SELECT TRUNC (LAST_DAY (SYSDATE)) "Last day of current month"
FROM DUAL;


3.Write a query that check if a table exists in the current database schema.
SELECT table_name
FROM user_tables
WHERE table_name = 'EMPLOYEES'


4. Write a query that shows a Table structure.
Desc Employees;


5.Write a query to display Current User.


Show User;


6.Write a query to find last_record from a table.


SELECT employee_id,last_name,job_id
FROM employees
WHERE ROWID IN (SELECT MAX (ROWID) FROM employees)
/
7. To Check table has any data


select count(*) from employees


Or select 1 from employees


where Rownum=1


8. How to find out table name for which this constraint applied

sql> select owner_table-name from user_constraints
where constraints_name =<<your constraint_name>>

if you have access to the dba_constraints
sql> select owner_table-name from dba_constraints
where constraints_name =<<your constraint_name>>

9. How to get list of all tables in Oracle

sql> select owner,table_name from all_tables
NOte:- If you are only concerned with tablesthat you own,not those that you have access to, you could use user_table.
/
sql> select owner,table_name from user_tables


10.1. How can you convert a number into words using Oracle Sql Query?

Please see the query below:-

SELECT TO_CHAR (TO_DATE (523, 'j'), 'jsp') from dual


Output:
TO_CHAR(TO_DATE(523,'J'),
-------------------------
five hundred twenty-three

11. Display the details of employees salary in round value.

select last_name ,salary,round(salary,3) from employees
/





12.Explain Various Types of Objects In Oracle?

1. View
2. Tables
3.Synonyms
4.Indexes
5. Tablespaces


13.Display Employees details with Department name & manager name .
SELECT e.employee_id Worker_name ,DEPARTMENT_NAME, FIRST_NAME Manager_name FROM DEPARTMENTS D JOIN EMPLOYEES E ON (D.MANAGER_ID=E.EMPLOYEE_ID)
/



14.How to check if table contains any data

select count(*) from table name to know number of rows.
select count(*) from employees;




15.How to find the last record from the table


SELECT *
FROM employees
WHERE ROWID IN (SELECT MAX (ROWID) FROM employees)
/


16. write a query to show table names from current database schema
select table_name from user_tables
SQL> SELECT table_name
FROM user_tables;


17.How is the primary key different from a unique key ?


Both these keys are uniquely identifying a record in a database table.
But one difference is that you can have more than one unique per table,
But only one primary key. Also ,the primary key does not any null value while

the unique key allows null value.


18: Get all employee details from the employees table

Sql> select * from employees


19: Get the position of 'g' in the last_name 'King' from employees table.
Sql> select (instr(last_name,'g') ) from employees
where last_name='King';

(INSTR(LAST_NAME,'G'))
----------------------
4
4
20. select first three characters from last_name from employees table

SQL>  select substr(last_name,0,3) from employees
 where department_id=30;




21. You can find maximum salary for each department by grouping all records by department_id
& then use max function to calculate maximum salary in each department.

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

22.Write a  query to display procedure name in Oracle 

SELECT * FROM USER_OBJECTS
WHERE OBJECT_TYPE='PROCEDURE';

SELECT *
FROM USER_PROCEDURES;

Read More

Top Ten Queries In Sql

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. What is a Self Join In Oracle ?

A self join  a join  in which a table is joined with itself .Specially when the

table has a foreign key which references its own primary key.To join a table itself

means that each row of the table is combind with itself and with every other row of

the table.the self join can be viewied as a join of two coppies of  the some table

the table is not actually copied but sql performs the command as though it were.

the syntax of the command for joining a table to itself is almost same as that for

joining two different tables . to distinguish the column names from one anther

aliases are for the actual the table name are used since both the tables have same

name.

Table name aliases are defined in the FROM clause of the SELECT statement see

the syntax:

select a column_name, b. column_name...FROM tablel a, tablel a, tablel b where a

common_filed =b.common_fileld;

Manager_id in the worker table is equal to Employee_id in the Manager table.



SELECT e.last_name emp, m.last_name mgr
FROM   employees e JOIN employees m
ON    (e.manager_id = m.employee_id);




16 How to check if table contains any data

select count(*) from table name  to know number of rows.
select count(*) from employees;

17.How to find the last record from the table

SELECT *
  FROM employees
 WHERE ROWID IN (SELECT MAX (ROWID) FROM employees)


18. Get number of days between two dates.
select round((months_between('01-feb-2014','01-mar-2012') *30,0) num_of_days
from dual;

19. How to get 3 maximum salaries  from employees table.

select distinct salary from employees a where 3>=(select count(distinct sal) from employees b where

a.salary<=b.salary) order by a.salary desc.

Read More

What is PLSQL

Anonymous blocks

In this tutorial, you will learn about the PL/SQL block structure and how to write and execute the first PL/SQL block in SQL*PLUS.Let’s examine the PL/SQL block structure in Detail

[DECLARE]

   Declaration statements;

BEGIN

   Execution statements;

  [EXCEPTION]

      Exception handling statements;

END;

The declaration section allows you to define data types, structures, and

variables. You can declare variables in the declaration section by giving them

names, data types and initial values.

Execution section is required in a block structure and it must have at leastone statement. The execution section is the place where you put the execution code or business logic code. You can use both procedural and SQL statements.inside the execution section.The exception handling section is starting with the EXCEPTION keyword.The exception section is where you  can write  the code to handle exceptions.You can either catch or handle exceptions in the exception section.

Let’s take a Simple  Example .First, you need to login to the Oracle database via command

prompt by providing a username and password as shown the following picture.

sql > DECLARE

  v_bonus NUMBER(8,2);

BEGIN

  SELECT salary*0.10

  INTO   v_bonus

  FROM   employees

  WHERE  employee_id=&empid;

  DBMS_OUTPUT.PUT_LINE(v_bonus);

END;

Set Serveroutput on command to instruct sql *Plus to display database ouput.

Use DBMS_OUTPUT.PUT_LINE procedure to output a string on a screen.

Here you will get message Enter value for empid:

Enter any employee_id  for example 200

Output you can see :-

old   7:   WHERE  employee_id=&empid;

new   7:   WHERE  employee_id=200;

280

Example:- 2.   Let us take another example using Bind Variable.

Write a PLSQL block to display monthly salary by accepting annual salary using local variable with in block.

VARIABLE g_monthly_sal NUMBER     --  We declare a variable from Host environment.

DECLARE

  v_sal NUMBER(9,2) := &p_annual_sal;

BEGIN

  :g_monthly_sal := v_sal/12;

END;

/

To  Print the value of Bind Variable you can give below this command.

PRINT g_monthly_sal

Example :- 3 Write a PLSQL block to display monthly salary by accepting annual salary using

DEFINE variable

DEFINE p_annual_sal = 60000

DECLARE

  v_sal number(9,2) := &p_annual_sal;

BEGIN

  v_sal := v_sal/12;

  DBMS_OUTPUT.PUT_LINE('The monthly salary is '||v_sal);

END;

/

Pl Sql is a block structure language.A pl Sql block contains three keywords Begin,Exception,End which break up the block into three sections.

Declare :- In this section we declare variables,constants,other code language which can be used
within that block.

Executable section that run when the block is exceuted.
Exceptional handling section you can trap any exception that are raised when executable
section run.

Here are some example
Begin
dbms_output.put_line('Hello');
End;
Once you have written a pl sql block you can execute it Enter a slash(/) to tell sql> to execute this plsql block
or you can enter run command.

An anonymous block is a PL/SQL program unit that has no name and it does not require the explicit presence of the BEGIN and END keywords to enclose the executable statements. An anonymous block consists of an optional declarative part, an executable part, and one or more optional exception handlers.

The declarative part declares PL/SQL variables, exceptions, and cursors. The executable part contains PL/SQL code and SQL statements, and can contain nested blocks. Exception handlers contain code that is called when the exception is raised, either as a predefined PL/SQL exception (such as NO_DATA_FOUND or ZERO_DIVIDE) or as an exception that you define.

Running The PLSQL block:-

Once you have written a block of PLSQL code ,you can execute(run) it.

There are many tools are available for executing PLSQL code.

A Command Line interface for executing SQL statements as well as PLSQL blocks are shown below.

To tell it to execute ,a block must be followed by a "/" use the following code

The following short example of a PL/SQL anonymous block prints the names of all employees in department 20 in the Emp_tab table, using the DBMS_OUTPUT package:

DECLARE
   Emp_name    VARCHAR2(10);
   Cursor      c1 IS SELECT Ename FROM Emp_tab
                  WHERE Deptno = 20;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO Emp_name;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(Emp_name);
   END LOOP;
END;


Example 2:

VARIABLE g_char varchar2(30)
VARIABLE g_num number

DECLARE
  v_char varchar2(30) := '42 is the answer';
  v_num  number := substr(v_char,1,2);
BEGIN
  :g_char := v_char;
  :g_num := v_num;
END;
/
PRINT g_char
PRINT g_num 


Example 3:-


DECLARE
  v_bonus NUMBER(8,2);
BEGIN
  SELECT salary*0.10
  INTO   v_bonus
  FROM   employees
  WHERE  employee_id=&empid;
  DBMS_OUTPUT.PUT_LINE(v_bonus);
END; 

Example 4 :- Write a PLSQL Block to display employee_id of given person.

DECLARE
  employee_id NUMBER(6);
BEGIN
  SELECT employee_id
  INTO   employee_id
  FROM   employees
  WHERE  last_name='Kochhar';
  DBMS_OUTPUT.PUT_LINE(employee_id);
END;

Read More

Basic Commands in Sql

1.Group By Clause

1.Group By Clause
2. Months_between 
3. Select Clause
4. Create User In Oracle
5.View In Oracle
6. Conversion Function In Oracle
7.Add Comments On a Table
8.Alter Table
9. Substring Function
10. concatenate Function
11.Distinct Clause
12 Replace Function

13 Grant Previliges 
14.Create table using Existing table

Read More

Select Clause & Hr Schema In Oracle

SQL (Structured Query Language) is used to modify and access data or information from a storage area called database.

This beginner sql tutorial website teaches you the basics of SQL and how to write SQL queries. I will be sharing my

knowledge on SQL and help you learn SQL better. The sql concepts discussed in this tutorial can be applied to most of

database systems. The syntax used to explain the concepts is similar to the one used in Oracle database.

Some Of the most important Sql Commands are as follwes:

SQL SELECT Statement

The most commonly used SQL command is SELECT statement. The SQL SELECT statement is used to query or retrieve data

from a table in the database. A query may retrieve information from specified columns or from all of the columns in the

table. To create a simple SQL SELECT Statement, you must specify the column(s) name and the table name. The whole

query is called SQL SELECT Statement.

Note: SQL is not case sensitive. SELECT is the same as select.

Syntax of SQL SELECT Statement:

SELECT column_list FROM table-name
[WHERE Clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause];

Table-name is the name of the table from which the information is retrieved.
    column_list includes one or more columns from which data is retrieved.

The SQL SELECT Statement

The SELECT statement is used to select data from a database.

The result is stored in a result table, called the result-set.

SELECT * Example

Now we want to select all the columns from the "Employees" table.

SQL> select * from employees;

Now we want to select the content of the columns named

"Employee_id", "LastName" and "FirstName" from the table above.

SQL> select employee_id,first_name,last_name from employees;


Where Clause:-

SQL HAVING Clause

Having clause is used to filter data based on the group functions. This is similar to WHERE condition but is used with group

functions. Group functions cannot be used in WHERE Clause but can be used in HAVING clause.

For Example: If you want to select the department that has total salary paid for its employees more than 25000, the sql

query would be like;

SELECT departmet_id, SUM (salary)
FROM employees
GROUP BY departmets
HAVING SUM(salary) > 25000

When WHERE, GROUP BY and HAVING clauses are used together in a SELECT statement,
the WHERE clause is processed first, then the rows that are returned after the WHERE clause is executed are grouped

based on the GROUP BY clause. Finally, any conditions on the group functions in the HAVING clause are applied to the

grouped rows before the final output is displayed.
SQL ORDER BY

The ORDER BY clause is used in a SELECT statement to sort results either in ascending or descending order. Oracle sorts

query results in ascending order by default.

For Example: If you want to sort the employee table by salary of the employee, the sql query would be.

SELECT first_name, salary FROM employee ORDER BY salary;



The query first sorts the result according to name and then displays it.

You can also use more than one column in the ORDER BY clause.
he query first sorts the result according to name and then displays it.

You can also use more than one column in the ORDER BY clause.                                                                                                        



Alter Table :-
SQL ALTER TABLE Statement

The SQL ALTER TABLE command is used to modify the definition (structure) of a table by modifying the definition of its columns. The ALTER command is

used to perform the following functions.

1) Add, drop, modify table columns
2) Add and drop constraints
3) Enable and Disable constraints

Syntax to add a column

ALTER TABLE table_name ADD column_name datatype;

For Example: To add a column "experience" to the employee table, the query would be like

ALTER TABLE employees  ADD experience number(3);

Syntax to drop a column

ALTER TABLE table_name DROP column_name;

For Example: To drop the column "location" from the employees  table,
the query would be like


ALTER TABLE employees DROP location;

Syntax to modify a column

ALTER TABLE table_name MODIFY column_name datatype;

For Example: To modify the column salary in the employees  table, the query would be like

ALTER TABLE employees MODIFY salary number(15,2);


SQL RENAME Command

The SQL RENAME command is used to change the name of the table or a database object.

If you change the object's name any reference to the old name will be affected. You have to manually change the old name to the new name in every reference.
Syntax to rename a table

RENAME old_table_name To new_table_name;

For Example: To change the name of the table employees to my_employee, the query would be like

RENAME employees  TO my_emloyee;



The SQL SELECT DISTINCT Statement
In a table, some of the columns may contain duplicate values. This is not a problem, however,
 sometimes you will want to list only the different (distinct) values in a table.

The DISTINCT keyword can be used to return only distinct (different) values.

SQL SELECT DISTINCT Syntax
SELECT DISTINCT column_name(s)
FROM table_name

SELECT DISTINCT City FROM emp_info;


SQL WHERE Clause

The WHERE clause is used to extract only those records that fulfill a specified criterion

SELECT * FROM Emp_info
WHERE City='Mumbai'
Use Quotes Around Text Fields
SQL uses single quotes around text values (most database systems will also accept double quotes).

However, numeric values should not be enclosed in quotes....

Read More

C programming Example

-- Write a c Program to find largest among three numbers.

#include<stdio.h>
#include<conio.h>
void main()
{
int num1,num2,num3,highest;

clrscr();

printf("Enter first number");
scanf("%d",&num1);

printf("Enter second number");
scanf("%d",&num2);

printf("Enter third number");
scanf("%d",&num3);

if((num1>num2)&&(num1>num3))
{
printf("FIRST NO IS HIGHEST");
}
else if((num2>num1)&&(num2>num3))
{
printf("SECOND NO IS HIGHEST");
}
else
{
printf("THIRD NUMBER IS HIGHEST");
}
}






Example 2:- Write a program in c to calculate simple Interest .

/ /Function with argument and return value
//Calculate Simple Interest
#include<stdio.h>
#include<conio.h>
float calculatesi(int amt,float rate, float no)
{
float si;
si=(amt*no*rate)/100;
return si;
}
void main()
{
int p;
float n,r,si;
clrscr();
printf("\nEnter Principal Amount, no of years, rate of interest : ");
scanf("%d%f%f",&p,&n,&r);
si=calculatesi(p,n,r);
printf("\nSI=%f",si);
getch();
}



Example 3.

/*Write a program to print area of a circle. A(circle)= 3.142 * R * R in C language*/


#include<stdio.h>
#include<conio.h>

void main()

{

float AREA, R;
clrscr();

printf("Enter redius;");
scanf("%f",&R);

AREA = 3.14*R*R;
printf("AREA of the givan is %6.2f",AREA);

getch();

}



Example 4:-

/* write a program to print simple interest SI=(PNR)/100 in C lang */

#include<stdio.h>
#include<conio.h>
main()
{
float SI,P,N,R;
clrscr();
printf("\n enter Principle Amount:");
scanf("%f",&P);

printf("\n enter the number of years:");
scanf("%f",&N);

printf("\n enter the rate of interest:");
scanf("%f",&R);

SI=(P*N*R)/100;
printf(" \n Simple interest is : %6.2f",SI);
getch();
return(0);
}



Example 5:- /* Find a wheather number is a prime */

#include<stdio.h>
#include<conio.h>
void main()
{
int no,i,a=0;
printf("\n enter the numnber:");
scanf("%d",&no);
for(i=2;i<=no/2;i++)
{
if(no%i==0)
{
a=1;
break;
}
}
if(a==1)
printf("\n not a prime");
else
printf("\ entered number is a prime num:");
getch();
}


Example 6:- #include<stdio.h>
#include<conio.h>
main()
{
float AREA,B,H;
clrscr();
printf("Enter Base & Height: ");
scanf("%f%f",&B,&H);
AREA=0.5*B*H;
printf("Area of the given is : %6.2f",AREA);
getch();
}









Example 7 :-


•Write a program to print simple interest SI = (PNR)/100 in C language
#include<stdio.h>
#include<conio.h>
main()
{
float SI,P,N,R;
clrscr();
printf("Enter Principle Amount: ");
scanf("%f%f%f",&P,&N,&R);
SI=(P*N*R)/100;
printf("Simple Interest is : %6.2f",SI);
getch();
}


Example 8: /* Display the reverse of given number */
#include<stdio.h>
#include<conio.h>
void main()
{
int num,rem=0,rev=0;
clrscr();
printf("\n enter the number:");
scanf("%d",&num);
while(num>0)
{
rem=num%10;
num=num/10;
rev=rev*10+rem;
}
printf("\n the reverse of a number =%d",rev);
getch();
}


Example 9:- /*• Write a program to accept a number from user and print it’s square & cube in C language*/
#include<stdio.h>
#include<conio.h>
main()
{
int n,Square,Cube;
clrscr();
printf("Enter Number: ");
scanf("%d",&n);
Square=n*n;
Cube=n*n*n;
printf("\nSquare: %d\nCube: %d",Square,Cube);
getch();
}



Example 10:-

/*•Write a program to accept a number from user and print it’s square & cube in C language*/
#include<stdio.h>
#include<conio.h>
main()
{
int s,Square,Cube;
clrscr();
printf("Enter Number: ");
scanf("%d",&s);
Square=s*s;
Cube=s*s*s;
printf("\nSquare: %d\nCube: %d",Square,Cube);
getch();
return (0);
}


Example 11:- /* Write a program to accept a number and print if the number is Positive/Negative in C language*/
#include<stdio.h>
#include<conio.h>

main()
{
int num;
clrscr();
printf("Enter number..");
scanf("%d",&num);
if(num>0)
printf("Given number is positive");
else if(num<0)
printf("Given number is negative");
else
printf("Number is Zero");
getch();
return(0);
}


Example :-12 • Write a Simple Program to print numbers 1 to n using while loop in C language
#include<stdio.h>
#include<conio.h>
main()
{
int i=1,n;
clrscr();
printf("Enter n : ");
scanf("%d",&n);
while(i<=n)
{
printf("%d\t",i);
i++;
}
getch();
}

13:- /* Write a program to calculate the sum of n natural number given by user*/



#include<stdio.h>
#include<conio.h>


void main()
{
int n,i,sum=0;
clrscr(); //to clear the screen
printf("Enter the value of n:");
scanf("%d",&n);


for(i=1;i<=n;++i)
{
printf("\n%d",i);
sum+=i;
}
printf("\n\nSum of first n natural numbers is:%d",sum);
getch(); //to stop the screen
}

Read More

Working With Bfiles

Managing Bfiles

Manipulating Large Objects

A LOB is a data type that is used to store large, unstructured data such as text, graphic images, video clippings, and so

on. Structured data, such as a customer record, may be a few hundred bytes, but even small amounts of multimedia data can

be thousands of times larger. Also, multimedia data may reside in operating system (OS) files, which may need to be

accessed from a database.


There are four large object data types:
BLOB represents a binary large object, such as a video clip.
CLOB represents a character large object.
NCLOB represents a multibyte character large object.
BFILE represents a binary file stored in an OS binary file outside the database. The BFILE column or attribute stores a

file locator that points to the external file.

A table can have multiple LOB columns and object type attributes. A table can have only one LONG column.
The maximum size of LONGs is 2 GB; LOBs can be up to 4 GB.

There are two distinct parts to a LOB:
LOB value: The data that constitutes the real object being stored
LOB locator: A pointer to the location of the LOB value stored in the database.



What Are BFILEs?
BFILEs are external large objects (LOBs) stored in OS files that are external to database tables. The BFILE data type

stores a locator to the physical file. A BFILE can be in GIF, JPEG, MPEG, MPEG2, text, or other formats.

Securing BFILEs
Unauthenticated access to files on a server presents a security risk.Oracle Database 10g can act as a security mechanism

to shield the operating system from unsecured access while removing the need to manage additional user accounts on an

enterprise computer system.


A DIRECTORY is a nonschema database object that enables administration of access and usage of BFILEs in Oracle Database

10g.


Example :-


create table emp as select * from employees;
ALTER TABLE emp ADD (photo blob);
UPDATE emp
Empty_CLOB – It is a special function, which can be used in Insert or Update statement of SQL DML to initialize a NULL.
--The EMPTY_BLOB function returns an empty locator of type BLOB (binary large object).
--. It cannot be NULL. The locator might point to an empty BLOB value, but it will be a --valid BLOB locator.
SET photo = empty_blob( )
CREATE OR REPLACE DIRECTORY photo_folder AS 'F:\students:'
CONNECT sys as sysdba
GRANT CREATE ANY DIRECTORY TO Hr;

GRANT DROP ANY DIRECTORY TO hr;
connect hr/hr
CREATE OR REPLACE DIRECTORY photo_folder AS 'f:\students'

-----------------------------------------------
CREATE OR REPLACE PROCEDURE insert_photo
(p_empno NUMBER, p_photo VARCHAR2)
AS
f_photo BFILE;
b_photo BLOB;
BEGIN
-- Update the employee photo
UPDATE emp
SET photo = empty_blob()
WHERE employee_id = p_empno
RETURN photo into b_photo;
-- find where the photo's pointer is located.
f_photo := bfilename('PHOTO_FOLDER', p_photo);
-- open the photo as read-only option.
dbms_lob.fileopen(f_photo, dbms_lob.file_readonly);
-- load the photo into column photo.
dbms_lob.loadfromfile(b_photo,f_photo, dbms_lob.getlength(f_photo));
-- close the photo's pointer.
dbms_lob.fileclose(f_photo);
-- Save the loaded photo record.
COMMIT;
EXCEPTION
-- Check for your error messages
WHEN others THEN
dbms_output.put_line('*** ERROR *** Check you procedure.');
END;

EXECUTE insert_photo(198, '1.GIF')
EXECUTE insert_photo(124, '1.GIF')

SELECT employee_id,first_name,
dbms_lob.getlength(photo) "Photo Size"
FROM emp

Read More

Replace Function In Oracle

Trim :- Removes leading and /or trailing blanks(or other characters ) from a string.
Syntax:- TRim([[<trim_spec >] char ]

SQL> select trim(' Aptech Computer') from dual;


Returns the following result.

TRIM('APTECHCOM
---------------
Aptech Computer


Concate Function:--

The syntax for the oracle concat function is :
concat(string1,string2)

Parameters Or Arguments.

String1 is the first string to concatenate.
string2 is second string to concatnate....

SQL> select concat(trim(' 'from'Nikita_sharma '),'Good Morning') from dual;

CONCAT(TRIM(''FROM'NIKITA
-------------------------
Nikita_sharmaGood Morning


Length
Syntax
Length(char)
{fn length(char)}
Returns the length in characters of the string argument char.if the char has the datatype CHAR ,the length includes all trailing blanks.
if a char is null ,it returns null.

SQL> select length('Australia') from dual;

LENGTH('AUSTRALIA')
-------------------
9


SQL> select concat(trim(' 'from'Nikita_sharma '),'Good Morning') from dual;

CONCAT(TRIM(''FROM'NIKITA
-------------------------
Nikita_sharmaGood Morning


Replace function replaces a sequence of characters in a string with another set of characters.

The syntax for the oracle sql replace function is:-
Replace(string1,string_to_replace,[replacement_string])


SQL> select replace('software','o','@') from dual;

REPLACE(
--------
s@ftware

Read More

Sql substring Function in oracle

Substr: The oracle substr function allows you to extract a substring .
The syntax for the oracle substr is as follows:-
substr(string,start_position,[ length])


SQL> select substr('santosh chaurasia',2,6) from dual;

SUBSTR
------
antosh

sql> select substr('priyaseth',4) from dual;
SUBSTR
------
antosh

SQL> select substr('avinashkare',-4) from dual;
SQL> select instr('nikita_sharma','k') from dual;

INSTR('NIKITA_SHARMA','K')
--------------------------
3
SQL> select instr('america','e') from dual;
The instr function in sql used to find the starting locatin of a pattern in a string.
The syntax for instr(str) is as follows.
instr(str,pattern,[starting position,[nth locatin]])

INSTR('AMERICA','E')
--------------------
3
SQL> select instr('nikitasony','n',2) from dual;-- for 2nd occurance

INSTR('NIKITASONY','N',2)
-------------------------
9
SQL> select lpad('vikram',10,'*') from dual;

LPAD('VIKR
----------
****vikram

SQL> select rpad('nasim',10,'*') from dual;

RPAD('NASI
----------
nasim*****



Trim :- Removes leading and /or trailing blanks(or other characters ) from a string.
Syntax:- TRim([[<trim_spec >] char ]

SQL> select trim(' Aptech Computer') from dual;


Returns the following result.

TRIM('APTECHCOM
---------------
Aptech Computer


Concate Function:--

The syntax for the oracle concat function is :
concat(string1,string2)

Parameters Or Arguments.

String1 is the first string to concatenate.
string2 is second string to concatnate....

SQL> select concat(trim(' 'from'Nikita_sharma '),'Good Morning') from dual;

CONCAT(TRIM(''FROM'NIKITA
-------------------------
Nikita_sharmaGood Morning


Length
Syntax
Length(char)
{fn length(char)}
Returns the length in characters of the string argument char.if the char has the datatype CHAR ,the length includes all trailing blanks.
if a char is null ,it returns null.

SQL> select length('Australia') from dual;

LENGTH('AUSTRALIA')
-------------------
9


SQL> select concat(trim(' 'from'Nikita_sharma '),'Good Morning') from dual;

CONCAT(TRIM(''FROM'NIKITA
-------------------------

Nikita_sharmaGood Morning

Read More

Example Of Constraints in Sql

Primary Key:- YOU CAN NOW CREATE A PRIMARY KEY IN oracle with the CREATE TABLE STATEMENT.
SYNTAX
THE SYNTAX TO CREATE A PRIAMARY KEY USING THE CREATE TABLE STATEMENT IN ORACLE/PLSQL IS:

create table empl(employee_id number primary key,
last_name varchar2(50),
city varchar2(50))
/

Syntax

Alter table table_name
Add column column_defination
Add constraint clause
drop[column] column-name [cascade|Restrict]
drop{primary key}foreign key constraint_name | unique
constraint_name|check constraint-name|constraint constraint-name}
alter [column] column_alteration|locksize {Row |Table}

alter table emp1 add phone number;


In oracle Sql-PLSQL a foreign key is column that appears in one table and must appear in another table.

Create table table_name
(Column_name1 datatype Null/Not null,
Column_name2 datatype Null/Not null,
Column_name3 datatype Null/Not null,
constraint constraint_name
foreign key(column_name1,column_name2....column_namen)
On delete cascade;

Syntax to create a foreign key with on Delete Cascade in alter table statement is:

Alter Table Table_name
Add Constraint constraint_name
Foreign key (Column_name1,Column_name2,....Column_nameN)
On Delete Cascade;

Now we will take one example:-



create table dept(department_id number,
department_name varchar2(50),
location_id number,
city varchar2(50));

alter table dept add constraint pk10 primary key(department_id);

create table emp(employee_id number,last_name varchar2(50),
salary number(10,2),
department_id number )
/

alter table emp add constraint fk14 foreign key(department_id)
references dept(department_id) on delete cascade;

/

How to check user constraints from employees table

select constraint_name,constraint_type,index_name
from user_constraints
where table_name='EMPLOYEES'
/

Read More

How to create User in Oracle & assign grant revoke privileges

CREATE USER user in Oracle


Controlling User Access


In a multiple-user environment, you want to maintain security of the database access and use. With Oracle server database security, you can do the following:
Control database access.
Give access to specific objects in the database.
Confirm given and received privileges with the Oracle data dictionary.
Create synonyms for database objects.
Database security can be classified into two categories: system security and data security. System security covers access and use of the database at the system level such as the username and password, the disk space allocated to users, and the system operations that users can perform. Database security covers access and use of the database objects and the actions that those users can have on the objects.


Privileges


Privileges are the right to execute particular SQL statements. The database administrator (DBA) is a high-level user with the ability to create users and grant users access to the database and its objects. Users require system privileges to gain access to the database and object privileges to manipulate the content of the objects in the database. Users can also be given the privilege to grant additional privileges to other users or to roles, which are named groups of related privileges.
Schemas
A schema is a collection of objects such as tables, views, and sequences. The schema is owned by a database user and has the same name as that user.


System Privileges
More than 100 distinct system privileges are available for users and roles. System privileges typically are provided by the database administrator.
Typical DBA Privileges


CREATE USER user
IDENTIFIED BY password;


Creating a User
The DBA creates a user by executing the CREATE USER statement. The user does not have any privileges at this point. The DBA can then grant privileges to that user. These privileges determine what the user can do at the database level


CREATE USER hema
IDENTIFIED BY hema


MANAGING SCHEMA OBJECT


OBJECTIVES:
· Altering table
· Adding constraint
· Setting column unused
· Creating index at time of table creation
· Flashback technology


v ALTERING TABLE:


ADDING COLUMN:
Alter table <table name> add (<column name> <datatype> );


For eg:
SQL> alter table t45 add(employee_id number);
Table altered.


DROPPING COLUMN:
Alter table <table name> drop column <column name>;
For eg:
SQL> alter table t45 drop column dat1;
Table altered.


ADDING CONSTRAINT:
SQL> ALTER TABLE N3 MODIFY ID CONSTRAINT PK_ID PRIMARY KEY;


Table altered.
OR
SQL> ALTER TABLE N3 ADD CONSTRAINT EMAIL_UQ UNIQUE(EMAIL_ID);


Table altered.


DROPPING CONSTRAINT:
SQL> ALTER TABLE N3 DROP CONSTRAINT EMAIL_UQ ;


Table altered.




ON DELETE CASCADE:


SQL> ALTER TABLE N3 ADD CONSTRAINT DEPT_FK FOREIGN KEY(DEPT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID) ON DELETE CASCADE;


Table altered.


DEFERRRING CONSTRAINT:


· DEFERRABLE AND NON-DEFERRABLE
· INITIALLY DEFERRED AND INITIALLY IMMEDIATE


DEFERRABLE INITIALLY DEFERRED:
SQL> ALTER TABLE N3 ADD CONSTRAINT PK_ID PRIMARY KEY(ID) DEFERRABLE INITIALLY DEFERRED;
Table altered.
(CHEKS AFTER COMMIT)


NOTE:BY DEFAULT ALL CONSTRAINT, IF NOT SPECIFIED ,ARE INITIALLY IMMEDIATE.


TO SET A PERTICULER CONSTRAINT IMMEDIATE FOR A SESSION BEING:
SQL> SET CONSTRAINT PK_ID IMMEDIATE;
Constraint set.


TO SET ALL CONSTRAINTS IMMEDIATE FOR A SESSION BEING:


SQL> ALTER SESSION SET CONSTRAINTS=IMMEDIATE;
Session altered.


TO ALTER SEQUENCE:


ALTER SEQUENCE EMPLOYEES_SEQ INCREMENT BY 10;


SET UNUSED:


SQL> ALTER TABLE N3 SET UNUSED COLUMN DATE1;
Table altered.
NOTE:
ONCE U UNUSED A COLUMN, U CAN’T SEE IN DESC COMMAND,ALSO U CAN DROP THAT COLUMN AND ADD COLUMN WITH SAME NAME.
SQL> DESC N3;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
EMAIL_ID VARCHAR2(20)
DEPT_ID NUMBER


SQL> ALTER TABLE N3 ADD DATE1 DATE;


Table altered.


DROPPING UNUSED COLUMN


ALTER TABLE N4 DROP UNUSED COLUMN;






CREATING INDEX AT TIME OF TABLE CREATION


SQL> CREATE TABLE N4(ID NUMBER PRIMARY KEY USING INDEX (CREATE INDEX IND123 ON N4(ID)),NAME VARCHAR2(20));


Table created.


SQL> SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME='N4';


CONSTRAINT_NAME
------------------------------
SYS_C005613


SQL> SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME='N4';


INDEX_NAME
------------------------------
IND123


FLASHBACK TECHNOLOGY:


SQL> DROP TABLE N3;


Table dropped.


SQL> SELECT * FROM RECYCLEBIN WHERE ORIGINAL_NAME='N3';


OBJECT_NAME ORIGINAL_NAME OPERATION
------------------------------ -------------------------------- ---------
TYPE TS_NAME CREATETIME
------------------------- ------------------------------ -------------------
DROPTIME DROPSCN PARTITION_NAME CAN CAN
------------------- ---------- -------------------------------- --- ---
RELATED BASE_OBJECT PURGE_OBJECT SPACE
---------- ----------- ------------ ----------
BIN$R5uBkInYSsS4IT5jaZWaBg==$0 N3 DROP
TABLE USERS 2012-04-17:10:34:49
2012-05-03:10:00:25 2342497 YES YES
53369 53369 53369 8




SQL> FLASHBACK TABLE N3 TO BEFORE DROP;


Flashback complete.


SQL> SELECT * FROM RECYCLEBIN WHERE ORIGINAL_NAME='N3';


no rows selected


NOTE:
RECYCLEBIN EXIST ONLY FORM NON-SYSTEM TABLESPACES.


FOR NORMAL USER THERE IS PRIVATE RECYCLEBIN .

Read More

Basic Loop In Oracle

Basic Loop Statement:-

The Loops means run the same statements with a series of differant values.


Structure of basic loop is
[label ] loop
Stetements
end loop[label];

with each iteration of the loop statement run and control returns to the top of the loop.
To avoid an infinite loop ,we must use exit loop.exit statement exists the current iteration of a loop,eigher conditionally,or

unconditionally.

Example :-Write a pl sql block to print 1..10 numbers using basic loop.

DECLARE
NUM NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(NUM);
NUM := NUM+1;
EXIT WHEN NUM>10;
END LOOP;
END;


Example :- 2. List the Name,salary of an employees from Employee_id 100 ...To 109 using basic loop.

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.

Example 3:- List the Name,salary,bonus based on salary of an employees from Employee_id 100 ...To 109 using basic loop.

DECLARE
lNAME EMPLOYEES.LAST_NAME%TYPE;
V_SAL EMPLOYEES.SALARY%TYPE;
V_BONUS V_SAL%TYPE;
BEGIN
FOR EMPID IN 100..109 LOOP
SELECT LAST_NAME,SALARY
INTO lNAME,V_SAL
FROM EMPLOYEES
WHERE EMPLOYEE_ID=EMPID;
IF V_SAL >= 20000 THEN
V_BONUS := V_SAL*.2;
ELSIF V_SAL>=15000 THEN
V_BONUS := V_SAL*.3;
ELSIF V_SAL>= 10000 THEN
V_BONUS := V_SAL *.4;
ELSE
V_BONUS := V_SAL*.5;
END IF;
DBMS_OUTPUT.PUT_LINE(lNAME||' GETS '||V_SAL);
DBMS_OUTPUT.PUT_LINE('BONUS OF '||lNAME||' IS '||V_BONUS);
END LOOP;
END;
/




For Loop:-

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.



While Loop:-





The while statement again and again executes a block of statements while a particular condition is true.
it's sysntax can be expressed as
While(expression)
{
statements
}
while statement evaluate expression which must return a boolean value.if expression is true while statement executes statements in the while block & it continues testing the expression until the expression evaluates to false.

Example:-Print 1..10 numbers using while loop.

DECLARE
NUM NUMBER := 1;
BEGIN
WHILE NUM <=10 LOOP
DBMS_OUTPUT.PUT_LINE(NUM);
NUM := NUM+1;
END LOOP;
END;

Output

1
2
3
4
5
6
7
8
9
10

Read More

Invoke Procedure From Procedure

CREATE OR REPLACE PROCEDURE ANNSAL(P_SAL IN NUMBER,P_ANNSAL OUT NUMBER)
IS
BEGIN
  P_ANNSAL :=P_SAL*12;
END ANNSAL;
/
CREATE OR REPLACE PROCEDURE EMPSAL(P_ID IN NUMBER)
IS
  V_SAL NUMBER;
  V_ANNSAL NUMBER;
BEGIN
  SELECT SALARY INTO V_SAL FROM EMPLOYEES WHERE EMPLOYEE_ID=P_ID;
  ANNSAL(V_SAL,V_ANNSAL);
  DBMS_OUTPUT.PUT_LINE('SALARY IS '||V_SAL||' AND ANNSAL IS '||V_ANNSAL);
END EMPSAL;
/
SHO ERR


EXECUTE EMPSAL(103)

SQL> EXECUTE EMPSAL(103);
SALARY IS 9000 AND ANNSAL IS 108000

PL/SQL procedure successfully completed.

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

Differences Between Anonymous Blocks and Subprograms

1. Anonymous is unnamed plsql block
A stored procedure or a named block is a pl/sql block.
2. Anonymous cannot save in database,
oracle stores pl/sql block in the database.
3. We cannot call Anonymous
blocks. We can recall named block
Whenever program require it.
4 Anonymous Block cannot allow any mode of parameter.
Named block accepts the mode of
Parameter like in,inout,out.
5 Anonymous blocks are to be compiled each time when we
Require it.
But once we compile a named pl/sql block it is
Permanently stored as p-code after compilation in the
Shared pool of the system global area.
6. Anonymous block: Starts with
DECLARE or BEGIN.
Named block: Starts with Header Block.
Like name of block, type of block, parameter.
Named Block has NAME: like PROCEDURE, FUNCTION, and PACKAGES


7.The anonymous block statement is an executable statement
That can contain PL/SQL control statements and SQL
Statements. It can be used to implement procedural logic in
a scripting language.
named block or stored procedure is a pl/sql block that
oracle stores in the database and can be called by name
From any application examples are function, procedure and packages,etc..
8.Anonymous block are created on client and subprograms are stored on server. It is possible to call subprograms from anonymous block.
9.Anonymous blocks do not return values.
Subprograms called functions must return values.


10. Anonymous blocks cannot take parameters.
Subprograms can take parameters..


Syntax:-


Whenever you will execute the procedure nth time would appear the following message
and not showing any output anonymous block completed.

Anonymous Blocks
DECLARE(Optional)
Variables,Cursors,etc.
BEGIN(Mandatory)
SQL and PL/SQL cOMMANDS
EXCEPTION(Optional)
Exceptional_Handling actions;
END;(Mandatory)


11. example Anonymous blocks


DECLARE
l_message
VARCHAR2 (100) := 'Hello World!';
BEGIN
DBMS_OUTPUT.put_line (l_message);
END;




SUBPROGRAMS PROCEDURE
Create [Or Replace ] procedure name [parameters] is|as
(Mandatory)
Variables,Cursors,etc(Optional)
BEGIN (Mandatory)
SQL & plsql STATEMENTS;
EXCEPTION(Optional)
When Exception_handling actions;
END[NAME] ; (Mandatory)

Let us take one example of Named Block given below.


CREATE OR REPLACE FUNCTION FINDAREA(LEN IN NUMBER, WID IN NUMBER)
RETURN NUMBER
AS VAREA NUMBER;
BEGIN
VAREA := LEN * WID;
RETURN VAREA;
END;


SQL> SELECT FIND_AREA(25,15) AREA FROM DUAL;


AREA
375

Read More

Anonymous Blocks

Anonymous blocks






In this tutorial, you will learn about the PL/SQL block structure and how to write and execute the first PL/SQL block in SQL*PLUS.Let’s examine the PL/SQL block structure in Detail

[DECLARE]
Declaration statements;
BEGIN
Execution statements;
[EXCEPTION]
Exception handling statements;
END;
The declaration section allows you to define data types, structures, and
variables. You can declare variables in the declaration section by giving them
names, data types and initial values.
Execution section is required in a block structure and it must have at leastone statement. The execution section is the place where you put the execution code or business logic code. You can use both procedural and SQL statements.inside the execution section.The exception handling section is starting with the EXCEPTION keyword.The exception section is where you can write the code to handle exceptions.You can either catch or handle exceptions in the exception section.
Let’s take a Simple Example .First, you need to login to the Oracle database via command
prompt by providing a username and password as shown the following picture.
sql > DECLARE
v_bonus NUMBER(8,2);
BEGIN
SELECT salary*0.10
INTO v_bonus
FROM employees
WHERE employee_id=&empid;
DBMS_OUTPUT.PUT_LINE(v_bonus);
END;
Set Serveroutput on command to instruct sql *Plus to display database ouput.
Use DBMS_OUTPUT.PUT_LINE procedure to output a string on a screen.
Here you will get message Enter value for empid:
Enter any employee_id for example 200
Output you can see :-
old 7: WHERE employee_id=&empid;
new 7: WHERE employee_id=200;
280
Example:- 2. Let us take another example using Bind Variable.
Write a PLSQL block to display monthly salary by accepting annual salary using local variable with in block.
VARIABLE g_monthly_sal NUMBER -- We declare a variable from Host environment.
DECLARE
v_sal NUMBER(9,2) := &p_annual_sal;
BEGIN
:g_monthly_sal := v_sal/12;
END;
/
To Print the value of Bind Variable you can give below this command.
PRINT g_monthly_sal
Example :- 3 Write a PLSQL block to display monthly salary by accepting annual salary using
DEFINE variable
DEFINE p_annual_sal = 60000
DECLARE
v_sal number(9,2) := &p_annual_sal;
BEGIN
v_sal := v_sal/12;
DBMS_OUTPUT.PUT_LINE('The monthly salary is '||v_sal);
END;
/




Pl Sql is a block structure language.A pl Sql block contains three keywords Begin,Exception,End which break up the block into three sections.
Declare :- In this section we declare variables,constants,other code language which can be used
within that block.

Executable section that run when the block is exceuted.
Exceptional handling section you can trap any exception that are raised when executable
section run.

Here are some example
Begin
dbms_output.put_line('Hello');
End;
Once you have written a pl sql block you can execute it Enter a slash(/) to tell sql> to execute this plsql block
or you can enter run command.

An anonymous block is a PL/SQL program unit that has no name and it does not require the explicit presence of the BEGIN and END keywords to enclose the executable statements. An anonymous block consists of an optional declarative part, an executable part, and one or more optional exception handlers.

The declarative part declares PL/SQL variables, exceptions, and cursors. The executable part contains PL/SQL code and SQL statements, and can contain nested blocks. Exception handlers contain code that is called when the exception is raised, either as a predefined PL/SQL exception (such as NO_DATA_FOUND or ZERO_DIVIDE) or as an exception that you define.



Running The PLSQL block:-




Once you have written a block of PLSQL code ,you can execute(run) it.


There are many tools are available for executing PLSQL code.


A Command Line interface for executing SQL statements as well as PLSQL blocks are shown below.




To tell it to execute ,a block must be followed by a "/" use the following code

The following short example of a PL/SQL anonymous block prints the names of all employees in department 20 in the Emp_tab table, using the DBMS_OUTPUT package:



DECLARE
Emp_name VARCHAR2(10);
Cursor c1 IS SELECT Ename FROM Emp_tab
WHERE Deptno = 20;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO Emp_name;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(Emp_name);
END LOOP;
END;


Example 2:

VARIABLE g_char varchar2(30)
VARIABLE g_num number

DECLARE
v_char varchar2(30) := '42 is the answer';
v_num number := substr(v_char,1,2);
BEGIN
:g_char := v_char;
:g_num := v_num;
END;
/
PRINT g_char
PRINT g_num


Example 3:-


DECLARE
v_bonus NUMBER(8,2);
BEGIN
SELECT salary*0.10
INTO v_bonus
FROM employees
WHERE employee_id=&empid;
DBMS_OUTPUT.PUT_LINE(v_bonus);
END;

Example 4 :- Write a PLSQL Block to display employee_id of given person.

DECLARE
employee_id NUMBER(6);
BEGIN
SELECT employee_id
INTO employee_id
FROM employees
WHERE last_name='Kochhar';
DBMS_OUTPUT.PUT_LINE(employee_id);
END;

Read More