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 ISBEGIN INSERT INTO employees(employee_id, last_name, email, hire_date, job_id, salary) VALUES(1, 'Sharma',...
Friday, January 23, 2015
Thursday, January 22, 2015
Rownum In Oracle
Example :- To Display First 5 top Record in Oracle ,You can use Rownum Command because oracle does not support Top
(adsbygoogle = window.adsbygoogle || []).push({});
select employee_id,last_name
from employees
where rownum <=5
/
EMPLOYEE_ID LAST_NAME
----------- -----------------------
198 OConnell
...
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...
Wednesday, January 21, 2015
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...
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...
Sunday, January 18, 2015
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...
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...
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...
Saturday, January 17, 2015
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))...
Working With Bfiles
Managing BfilesManipulating Large ObjectsA LOB is a data type that is used to store large, unstructured data such as text, graphic images, video clippings, and soon. Structured data, such as a customer record, may be a few hundred bytes, but even small amounts of multimedia data canbe thousands of times larger. Also, multimedia data may reside in...
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 ComputerConcate Function:--The syntax for the oracle concat function is :concat(string1,string2)Parameters...
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------antoshsql> select substr('priyaseth',4) from dual;SUBSTR------antoshSQL> select substr('avinashkare',-4) from dual;SQL>...
Example Of Constraints in Sql
Primary Key:- YOU CAN NOW CREATE A PRIMARY KEY IN oracle with the CREATE TABLE STATEMENT.SYNTAXTHE 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)) /SyntaxAlter table table_nameAdd column column_definationAdd constraint...
How to create User in Oracle & assign grant revoke privileges
CREATE USER user in OracleControlling User AccessIn 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...
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 ] loopStetementsend 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...
Invoke Procedure From Procedure
CREATE OR REPLACE PROCEDURE ANNSAL(P_SAL IN NUMBER,P_ANNSAL OUT NUMBER)ISBEGIN 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...
Procedure in Plsql
A procedure in oracle is program Unit that performs a particular task it also called as subprogram.this procedure can bebe 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.SyntaxCREATE [OR REPLACE] PROCEDURE procedure_name[(parameter_name [IN | OUT...
Differences Between Anonymous Blocks and Subprograms
1. Anonymous is unnamed plsql blockA 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 Anonymousblocks. We can recall named blockWhenever program require it.4 Anonymous Block cannot allow any mode of parameter.Named block accepts the mode ofParameter...
Anonymous Blocks
Anonymous blocksIn 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;BEGINExecution...
