What is difference between a PROCEDURE &
Functions
----------
www.allwalkin.blogspot.com
1) can be used with Select statement
2) Not returning output parameter but returns Table variables
3) You can join UDF
4) Cannot be used to change server configuration
5) Cannot be used with XML FOR clause
6) Cannot have transaction within function
Stored Procedure
-----------------
1) have to use EXEC or EXECUTE
2) return output parameter
3) can create table but won?t return Table Variables
4) you can not join SP
5) can be used to change server configuration
6) can be used with XML FOR Clause
7) can have transaction within SP
1. Functions must return a value(scalar, inline table or multi statement table) whereas stored proc may or may not return a value.
2. Functions can return a table whereas stored procs can create a table but can't return table.
3. Stored procs can be called independently using exec keyword whereas function are called using select statements.
4. Stored procs can be used to change server configuration(in terms of security-i.e. setting granular permissions of user rights) whereas function can't be used for this
5. XML and output parameters can't be passed to functions whereas it can be with sp's.
6. Transaction related statement can be handled in sp whereas it can't be in function.
7. Stored procedures can call a function or another stored proc similarly a function can call another function and a stored proc. The catch with function is that no user defined stored proc can be called. Only extended/system defined procs can be called.
A FUNCTION always returns a value using the return statement.
And a FUNCTION can also use OUT parameter to return a value. But Return statment must be there.
A PROCEDURE may return one or more values through parameters or may not return at all.
A function can be called from sql stmt and pl/sql stmt.
A procedure can be called within pl/sql stmt.
In case of function,it must have
return type.
In case of procedure, it don't need return keyword.
State the difference between implicit and explicit cursor's.
Implicit Cursor are declared and used by the oracle internally. whereas the explicit cursors are declared and used by the user. more over implicitly cursors are no need to declare oracle creates and process and closes autometically. the explicit cursor should be declared and closed by the user.
Implict cursor can be used to handle single record (i.e) the select query used should not yield more than one row.
if u have handle more than one record then Explict cursor should be used.
Posted by: Dinesh
Contact Dinesh
It is not mandatory that when the query retrieves more than one record then explicit cursor should be used. If the number of records is less than 100 then implicit cursor should be used.
e.g.
set serverout on;
begin
for i in (select * from emp) loop
dbms_output.put_line(i.empno);
end loop;
end;
/
www.allwalkin.blogspot.com
Posted by: Sukhamoy
Contact Sukhamoy
Cursor: It is automatically created by oracle for all sql dml statements including the query that returns one row.
Explicit cursor: These are created and managed by the user.And used for multi row select statement.
Posted by: josu
Contact josu
Implicit cursors are oracle created and used internally by oracle,the only implicit cursor is sql.
Explicit cursors are user created cursors.
Posted by: Ashish
Contact Ashish
Cursors are used for the purpose of storing the intermediate results when executing a SQL Query or a block of code...
Implicit Cursors are by default declared by ORacle itself and hence there is no scope of storing resultset with more than one record where as Explicit Cursors are declared by the User and hence can define the parameters with measure to store the no of records.
State the advantage and disadvantage of Cursor?
Advantage :
In pl/sql if you want perform some actions more than one records you should user these cursors only. bye using these cursors you process the query records. you can easily move the records and you can exit from procedure when you required by using cursor attributes.
disadvantage:
using implicit/explicit cursors are depended by sutiation. if the result set is les than 50 or 100 records it is better to go for implicit cursors. if the result set is large then you should use exlicit cursors. other wise it will put burdon on cpu.
Disadvantage:
Each time we fetch a row from the cursor, it result a network round trip, where as a normal select statement query make only one round trip.
Posted by: Praveen Jindal
Contact Praveen Jindal
The guy's answer at the top of this page about "Cursors" is probably not wrong but sounds wrong and it is hard to understand, it sounds like he has tried to type the answer in english but it is shameful, his english is totally broken, it is bad. You guys at coolinterview.com should do something about such posting to keep up the readership.
Posted by: good programmer
Contact good programmer
Advantage of cursor
1.Basically cursor is a logical place or it is active set.
when a single query returns more than one rows in that case we can use the cursor. we don't need hit the database several time. Cursor
always points the latest rows.
www.allwalkin.blogspot.com
Disadvantage of cursor
1.As such there is no disadvantage
of cursor.
What is difference between stored procedures and application procedures,stored function and application function?
Stored procedures are sub programs stored in the database and can be called & execute multiple times where in an application procedure is the one being used for a particular application same is the way for function
Stored Procedure/Function is a compiled database object, which is used for fast response from Oracle Engine.Difference is Stored Procedure must return multiple value and function must return single value .
Posted by: Himanshu Kumar Tripathi
Contact Himanshu Kumar Tripathi
Procedure:-
Execute as a PL/SQL
statement,No RETURN clause in
the header,Can return none, one,
or many values,Can contain a RETURN
statement
Function:-Invoke as part of an
expression,Must contain a RETURN
clause in the header,Must return a single value,Must contain at least one RETURN statement
Posted by: Dev
Contact Dev
Stored procedures are sub programs stored in the database and can be called & execute multiple times where in an application procedure is the one being used for a particular application same is the way for function
Stored Procedure/Function is a compiled database object, which is used for fast response from Oracle Engine.Difference is Stored Procedure must return multiple value and function must return single value .
How we can create a table in PL/SQL block. insert records into it??? is it possible by some procedure or function?? please give example...
CREATE OR REPLACE PROCEDURE ddl_create_proc (p_table_name IN VARCHAR2)
AS
l_stmt VARCHAR2(200);
BEGIN
DBMS_OUTPUT.put_line('STARTING ');
l_stmt := 'create table '|| p_table_name || ' as (select * from emp )';
execute IMMEDIATE l_stmt;
DBMS_OUTPUT.put_line('end ');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('exception '||SQLERRM || 'message'||sqlcode);
END;
We can create table in procedure as explained in above case. but we can't create or perform any DDL in functions.
Posted by: Vinod Pandit
Contact Vinod Pandit
the above given answer is correct.
but we can also create or perform any ddl in functions.
I have tested myself
Posted by: Mike
Contact Mike
we can create a table through pl/sql block with dbms_sql package and execute immediate statement.
CREATE OR REPLACE PROCEDURE PROC_CREATE_NEW_TABLE(STMT VARCHAR2)
IS
AUTHID CURRENT_USER
BEGIN
EXECUTE IMMEDIATE STMT;
END;
here AUTHID CURRENT_USER is used to create a table under current user privileges.
through this procedure we can add our own fields of a table on run time,even it is not depended on structure of an existing table.
Insertion record into the table, because it is DML operation so we can easily create a procedure with simple statements or execute immediate statement.
What is Raise_application_error ?
Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-program or database
trigger.
RAISE_APPLICATION_ERROR is a
procedure of package DBMS which
allows to issue user_defined error
message.
Posted by: sudipta das
Contact sudipta das
The RAISE_APPLICATION_ERROR is a procedure defined by Oracle that allows the developer to raise an exception and associate an error number and message with the procedure other than just Oracle errors.
Posted by: Moumita Nandi
Contact Moumita Nandi
It is Its a STANDARD procedure to communicate with a pre defined exception interactively by returning a nonstandard error code and error message.
error_number : between -20000 to -20999
message : 2048 bytes
www.allwalkin.blogspot.com
This can be used in both execution and exception setion;
Ex: 1 :
.....
BEGIN
...
delete from employees
where manager_id = v_mgr;
if SQL%NOTFOUND then
RAISE_APPLICATION_ERROR(-20020,'This is not a valid manager');
end if;
....
---------------------------
ex:2 :
......
EXCEPTION
when NO_DATA_FOUND then
RAISE_APPLICATION_ERROR(-20201,'manager is not a valid employee');
END;
Explian rowid,rownum?What are the pseduocolumns we have?
Answers:
ROWID - Hexa decimal number each and every row having unique.Used in searching
ROWNUM - It is a integer number also unique for sorting Normally TOP N Analysys.
Other Psudo Column are
NEXTVAL,CURRVAL Of sequence are some exampls
ROWID : It gives the hexadecimal string representing the address of a row.
It gives the location in database where row is physically stored.
ROWNUM: It gives a sequence number in which rows are retrieved from the database.
Posted by: jyothsna
Contact jyothsna
ROWID : Every record in a database is uniquely identified by system generated value called Rowid. It Is a 18 character hexma decimal value. These Rowid's are physically existence.
ROWNUM : It is a pseduocolumn which generates the sequence of numeric values based on the position of the records in the output. These ROWNUM'S logically generated.
NEXTVAL,CURRVAL,SYSDATE,LEVEL ARE PSEDUOCOLUMNS
Name the tables where characteristics of Package, procedure and functions are stored ?
Answers:
User_objects, User_Source and User_error.
user_source,user_objects
Posted by: madhavi
Contact madhavi
characteristics of DB objects are stored in data dictionary. Which can be accessed by Data dict. views like (user/all/DBA_objects/source).
Posted by: pankaj
Contact pankaj
user_source,user_objects
Explain the usage of WHERE CURRENT OF clause in cursors ?
Answers:
WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor. Database Triggers
Where CURRENT OF clause means
cursor
points to present row of
the cursor
Posted by: sudipta das
Contact sudipta das
WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor.
Posted by: Alagappan A M
Contact Alagappan A M
This clause use for update & delete most resent row with out help of ROWID and befour update lock the particular row in the base table.FOR UPDATE clause is manditory for this clause.
Posted by: siva kumar
Contact siva kumar
Take an example, Cursor is
Select * from EMP;
and in cursor we write
Update Emp set sal = sal + 100;
that means the fetching loops will execute as many times as rows returned by the cursor and the update will also be done for the same number of times.
Now suppose, If we put a where clause in the update statement, then the result would also not be predictable.
In the third case, we use the WHERE CURRENT OF clause in the where clause, this point out to the current row returned by the cursor. In that case, Oracle differentiate all the rows on the basis of RowID. But what happens if someone changes one row in the table.....that means the rowid would be changed. So we have to manually lock all the rows of that table by writing the FOR UPDATE/DELETE clause.
So In case of WHERE CURRENT OF clause, we should use the FOR UPDATE/DELETE clause also. This is known as the Pessimistic/Manual Locking and this the major difference between the Implicit Cursors and Explicit Cursors.
What will the Output for this Coding?
Declare
Cursor c1 is select * from emp FORUPDATE;
Z c1%rowtype;
Begin
Open C1;
Fetch c1 into Z;
Commit;
Fetch c1 in to Z;
end;
Answers:
By declaring this cursor we can update the table emp through z,means wo not need to write table name for updation,it may be only by "z".
selecting in FOR UPDATE mode locks the result set of rows in update mode, which means that row cannot be updated or deleted until a commit or rollback is issued which will release the row(s).
Posted by: Ash
Contact Ash
You will get an error. Since, the commit/rollback closes the cursor and release the lock if you use for update of, so trying to fetch out of a closed cursor will raise an error.
Posted by: Nattu
Contact Nattu
The procedure executes successfully.COMMIT will not close the cursor.
declare
b boolean;
cursor c is select * from emp for update;
rec c%rowtype;
begin
open c;
fetch c into rec;
dbms_output.put_line('NAME is'||rec.ename);
commit;
if c%isopen then
dbms_output.put_line('Cursor is not closed '||c%rowcount);
end if;
fetch c into rec;
dbms_output.put_line('NAME is'||rec.ename);
end;
O/P
****
NAME isSMITH
Cursor is not closed 1
NAME isALLEN
1)What is the starting "oracle error number"?
2)What is meant by forward declaration in functions?
Answers:
One must declare an identifier before referencing it. Once it is declared it can be referred even before defining it in the PL/SQL. This rule applies to function and procedures also
www.allwalkin.blogspot.com
ORACLE ERROR NO starts with ORA 00001
Posted by: Soujanya
Contact Soujanya
sql,pl/sql
Posted by: glakshmireddy
Contact glakshmireddy
Example of forward declaration error for better understanding
create or replace package pkg1 is
procedure a;
end;
create or replace package body pkg1 is
procedure a IS
begin
procedure b -->throws an error.because procedure b is not declared yet
end;
The solution for this error is, either delcare procedure b in specification or package body. better to declare it in specification so that it will be visible to all.
Posted by: hari
Contact hari
First we have to declare a attribut before using it anywhere in a programme otherwise it through error.This is called forward declaration in function.
How many types of database triggers can be specified on a table ? What are they ?
Answers:
Insert Update Delete
Before Row o.k. o.k. o.k.
After Row o.k. o.k. o.k.
Before Statement o.k. o.k. o.k.
After Statement o.k. o.k. o.k.
If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement.
If WHEN clause is specified, the trigger fires according to the returned Boolean value.
Row level trigger,Statement level trigger,Instead of trigger(created on views)
Posted by: shrinivas singh
Contact shrinivas singh
Baically there only 1 type of trigger which can be fired on the table. .i.e., DML TRIGGER.
There are 14 types of DML TRIGGER
But we can fire only 12 types of triggers, because remaining 2 types of triggers fire on View.
1. Before insert on ROW LEVEL TRIGGER
2. 1. AFTER insert on ROW LEVEL TRIGGER
3. Before insert on STATEMENT LEVEL TRIGGER
4. After insert on STATEMENT LEVEL TRIGGER
5. Before update on ROW LEVEL TRIGGER
6. After update on ROW LEVEL TRIGGER
7. Before update on STATEMENT LEVEL TRIGGER.
8. After update on STATEMENT LEVEL TRIGGER.
9. Before Delete on STATEMENT LEVEL TRIGGER.
10 After Delete on STATEMENT LEVEL TRIGGER.
11. Before Delete on ROW LEVEL TRIGGER.
12 After Delete on ROW LEVEL TRIGGER
Can Commit,Rollback ,Savepoint be used in Database Triggers?If yes than HOW? If no Why?With Reasons
Answers:
we cannot commit inside a trigger.
www.allwalkin.blogspot.com
As we all know that when a dml is complete one can issue a commit.
A trigger if created is fired before the dml completes.
so we cannot commit intermediately.
Posted by: kapil sawant
Contact kapil sawant
As workaround, one can use autonomous transactions. Autonomous transactions execute separate from the current transaction.
Unlike regular triggers, autonomous triggers can contain COMMIT and ROLLBACK statements
Posted by: Jobs
Contact Jobs
Having Commit / Rollback inside a trigger defeats the standard of whole transaction's commit / rollback al together. Once trigger execution is complete then only a transaction can be said as complete and then only commit should take place.
If we still want to carry out some action which should be initiated from trigger but should be commited irrespective of trigger completion / failure we can have AUTONOMUS TRANSACTION. Inside Automomus transaction block we can have Commit and it will act as actual commit.
Specific queries a most welcome.
D.
What is ref cursor?
Answers:
In PL/SQL ,pointer has a datatype REF X where
REF-Reference
X-class of objects
Cursor Variables has a datatype REF-CURSOR
where Cursor Varibales are like pointers which hold the memory location of some item instead of the item itself.
Posted by: shikha srivastava
Contact shikha srivastava
Ref Cursor is cursor variable. It is a pointer to a result set. It is useful in scenarios when result set is created in one program and the processing of the same in some other, might be written in different language, e.g. firing the select is done in PL/SQL and the processing will be done in Java.
Posted by: Sukhamoy
Contact Sukhamoy
Its a run time query binding with the cursor variable. Normal cursors are static cursors becaz they get acquited of query at the compile time.
Posted by: Rup
Contact Rup
Ref cursors are used when we want to use the cursor which is not bound to a specific query.
Cursor variables can be tied to many such type of compatible queries.
What is pl/sql?what are the advantages of pl/sql?
PL/SQL(a product of Oracle) is the 'programming language' extension of sql.
It is a full-fledged language although it is specially designed for database centric activities.
Posted by: Sukhamoy
Contact Sukhamoy
in sql we can manipulate the data we can't process over data through Sql command for doing that we need a programming language such as pl/sql ,its a extantion of sql used to manipulate as well as process the data.
Posted by: sunny talent09
Contact sunny talent09
PL/SQL is Very Usefully Language & Tools of Oracle to Manipulate,Restrict,Validate & Control the Unauthorized Access of Data From the Database.
We Can easily show multiple records of the multiple table at same time.
And Using control statement like Loops & If else & Select case we control the database.
Posted by: Md.Salim
Contact Md.Salim
pl/sql is a procedural extension to sql with design features of programming languages.
Plsql can improve the performance of an application and it is portable which can handle errors. PL/SQL applications can run on any platform or operating system on which oracle runs.
Procedures ,functions and triggers are different pl/sql constructs and you can invoke subprograms from different environment.
Explain how procedures and functions are called in a PL/SQL block ?
Function is called as part of an expression.
sal := calculate_sal ('a822');
procedure is called as a PL/SQL statement
calculate_bonus ('A822');
Function can be called from SQL query + explicitly as well
e.g 1)select empno,salary,fn_comm(salary)from employee;
2)commision=fn_comm(salary);
Procedure can be called from begin-end clause.
e.g.
Begin
(
proc_comm(salary);
)
end
Posted by: Hirenkumar
Contact Hirenkumar
Function:
Example
Select get_name(Emp_id) from dual.
Procedure:
Example
Begin
Process_employee(EMP_ID);
end;
How to debug the procedure ?
Answers:
You can use DBMS_OUTPUT oracle supplied package or DBMS_DEBUG pasckage.
Posted by: Nasir Ali
Contact Nasir Ali
put a dbms output_putline statment which will dislapay that your procedure is executing successfully up to which stage.
Posted by: sanjay
Contact sanjay
By using the sql developer tools and in that use breakpoints (the location from which u want to debug and execute line by line at the same time you will notice the current value of the executed statement below in another windows) and don't forgot to enable the dbms_output ).
In this way you can do the same things
Posted by: RAJESH KUMAR
Contact RAJESH KUMAR
We can also do line by line execution using tools such as Pl/SQL Developer, Toad etc
Posted by: Amit Gupta
Contact Amit Gupta
if you want to tune a package then you can use dbms_profiler. if you want to debug use dbms_debug procedure.
Thanks,
Sudipta
What will happen after commit statement ?
Cursor C1 is
Select empno,
ename from emp;
Begin
open C1; loop
Fetch C1 into
eno.ename;
Exit When
C1 %notfound;-----
commit;
end loop;
end;
The cursor having query as SELECT .... FOR UPDATE gets closed after COMMIT/ROLLBACK.
The cursor having query as SELECT.... does not get closed even after COMMIT/ROLLBACK.
After commit statement,all the transaction will be ended.
all the changes data are parmanently stored in the database.
Posted by: sudipta das
Contact sudipta das
After commit statement, all changed data will be saved in permenent database.
After commit all the locks on the database tables are leased.
Posted by: srinivas
Contact srinivas
after commit statement, all the changes that u hav made are permanently stored in the database.
What is trigger,cursor,functions in pl-sql and we need sample programs about it?
Trigger is an event driven PL/SQL block. Event may be any DML transaction.
Cursor is a stored select statement for that current session. It will not be stored in the database, it is a logical component.
Function is a set of PL/SQL statements or a PL/SQL block, which performs an operation and must return a value.
Posted by: Girish P
Contact Girish P
Cursor
cursor is a private sql work area.
Every sql statement executed
by oracle server has an individual
cursor associated with it.
Cursor are two types
1.Implicit cursor
2.Explicit cursor
Implicit cursor: Implicit cursors
are declared by pl/sql implicitly
at the time of DML statement and select statement in pl/sql including queries that returns single row.
cursor have four attributes
1. SQL%ROWCOUNT
2. SQL%ISOPEN
3. SQL%NOTFOUND
4. SQL%FOUND
SQL%ROWCOUNT-Basically it returns
number.
means number of rows
affected by present sql statement.
SQL%ISOPEN-Always evalutes false
because implicit cursor automatically closed after execution of sql statement.
SQL%FOUND-Always evalutes true
because one or more rows are affected by recent sql statement
SQL%NOTFOUND-Always evalutes true when no rows are affected by
present sql statement.
example of explicit cursor
DECLARE
v_empno employees.employee_id%TYPE;
v_name employees.last_name%TYPE;
CURSOR emp_cur IS
SELECT employee_id,last_name
FROM employees
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur INTO v_empno,v_name ;
EXIT WHEN emp_cur%ROWCOUNT>10 OR
emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('employee_id:'||TO_CHAR(v_empno) || 'employee_name:'||'v_name');
END LOOP;
CLOSE emp_cur;
END;
Trigger:-Trigger is pl/sql block or
procedure that is associated with table,view,schema and database.
Execute immidiately when particular event take place.
there are two types of trigger
1.Application trigger:fires automatically when event occurs with particular application.
2.Database trigger:Fires when
data such as DML oparation occured at that time.
DML triggers are two types
1.Statementlevel trigger
2.Rowlevel trigger
www.allwalkin.blogspot.com
statement level trigger-statement level trigger means trigger body
execute once for the triggering event.this is default.A statement level trigger fire once even no rows are affected at all.
Row level- Trigger body execute
once for each row affected by triggering event.if no rows are
affected in that case trigger body
not executed.
trigger example
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT ON employees
BEGIN
TO_CHAR(SYSDATE,'DY')IN('SUN','SAT') OR TO_CHAR((SYSDATE,'HH24:MI')NOT BETWEEN '08:00' AND '18:00')THEN
RAISE_APPLICATION_ERROR(-20253,'u may insert employee information at business hrs');
END;
Posted by: Sudipta Das
Contact Sudipta Das
Cursor:
cursor is temporary work area in
pl/sql.
Type of cusor:
1.Implict Cursor
2.Explicit Cursor
3.Cursor for loop
4.Cursor With Parametter
5.Cursor for Update
6.Ref Cursor
Curosr Attributes:
%foud
%notfound
%rowCount
%isopen
Trigger:
Trigger is asscoiated with table or database event(when a table affect like dml operation that time occurs fire)
Types of Trigger:
we can write 12 types trigger
Posted by: raja
Contact raja
Trigger is a stored procedure that invokes automatically when the event occurs.
We can write 12 types of triggers on table and 15 types of triggers on views.
Cursor pointer or handler to the context area . 2 types of cursors are there
implicit and explicit cursors.
Where the Pre_defined_exceptions are stored ?
In the standard package.
Procedures, Functions & Packages ;
It stores in standard_package
What is trigger,cursor,functions in pl-sql and we need sample programs about it?
Where the Pre_defined_exceptions are stored ?
In the standard package.
Procedures, Functions & Packages ;
It stores in standard_package.
What is difference between % ROWTYPE and TYPE RECORD ?
% ROWTYPE is to be used whenever query returns a entire row of a table or view.
TYPE rec RECORD is to be used whenever query returns columns of different
table or views and variables.
E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %type
);
e_rec emp% ROWTYPE
cursor c1 is select empno,deptno from emp;
e_rec c1 %ROWTYPE.
What are the Restrictions on Cursor Variables?
Answers:
Currently, cursor variables are subject to the following restrictions:You cannot declare cursor variables in a package spec. For example, the following declaration is not allowed:CREATE PACKAGE emp_stuff AS TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; emp_cv EmpCurTyp; -- not allowedEND emp_stuff;You cannot pass cursor variables to a procedure that is called through a database link.If you pass a host cursor variable to PL/SQL, you cannot fetch from it on the server side unless you also open it there on the same server call.You cannot use comparison operators to test cursor variables for equality, inequality, or nullity.You cannot assign nulls to a cursor variable.Database columns cannot store the values of cursor variables. There is no equivalent type to use in a CREATE TABLE statement.You cannot store cursor variables in an associative array, nested table, or varray.Cursors and cursor variables are not interoperable; that is, you cannot use one where the other is expected. For example, you cannot reference a cursor variable in a cursor FOR loop
u cannot declare cursor in package specification
not allowed when using db links
cannot use comparison operators
cannot assign NULL
cursor's values cannot be stored in table columns
cannot be used with associative array, nested tables and varray
cannot be use one where the other is expected
cannot reference a cursor variable in cursor FOR LOOP
cannot direclty goto any columns
uestion :
What are two virtual tables available during database trigger execution ?
Answers:
The table columns are referred as OLD.column_name and NEW.column_name.
For triggers related to INSERT only NEW.column_name values only available.
For triggers related to UPDATE only OLD.column_name NEW.column_name values only available.
For triggers related to DELETE only OLD.column_name values only available.
Two tables are: OLD and NEW.
Insert Trigger :
OLD - no value.
NEW - inserted value.
UPDATE TRIGGER -
OLD- old value.
NEW- new updated value.
www.allwalkin.blogspot.com
DELETE TRIGGER -
OLD - old value.
NEW - no value
Can we declare a column having number data type and its scale is larger than pricesion
ex: column_name NUMBER(10,100),
column_name NUMBAER(10,-84)
We can create the table like
create table as1(id number(10,11))
but we cant insert the data into the table.
it will give error
Posted by: Tilak
Contact Tilak
ID number(4,5)
Can store number between 0.00000 to 0.09999
Question :
How to disable multiple triggers of a table at at a time
ALTER TABLE
<><>DISABLE ALL TRIGGER ALTER TABLE TT_DCB DISABLE ALL TRIGGERS Posted by: NUKALAARU Contact NUKALAARU alter table table_name disable all triggers Posted by: sateesh L Contact sateesh L ALTER table table_name DISABLE ALL TRIGGERS Question : What is a stored procedure ? A stored procedure is a sequence of statements that perform specific function. A stored procedure is a named pl/sql block which performs an action.It is stored in the database as a schema object and can be repeatedly executed.It can be invoked, parameterised and nested. Question : How to avoid using cursors? What to use instead of cursor and in what cases to do so? Answers: just use subquery in for clause ex: for emprec in (select * from emp) loop dbms_output.put_line(emprec.empno); end loop; no exit statement needed implicit open,fetch,close occurs We have avoided declaration of cursor.the given example will create cursor with some system generated name. Posted by: shrinivas singh Contact shrinivas singh When the number of rows returned by query is small (around 100),
www.allwalkin.blogspot.com
then explicit cursor can be avoided safely since in those cases using explicit cursor is performance degrading(CPU overhead). What is a database trigger ? Name some usages of database trigger ? Answers: Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modifications, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex security authorizations. Maintain replicate tables. A database triggers is stored PL/SQL program unit associated with a specific database table or view. The code in the trigger defines the action the database needs to perform whenever some database manipulation (INSERT, UPDATE, DELETE) takes place. Unlike the stored procedure and functions, which have to be called explicitly, the database triggers are fires (executed) or called implicitly whenever the table is affected by any of the above said DML operations. Till oracle 7.0 only 12 triggers could be associated with a given table, but in higher versions of Oracle there is no such limitation. A database trigger fires with the privileges of owner not that of user A database trigger has three parts 1. A triggering event 2. A trigger constraint (Optional)
www.allwalkin.blogspot.com
3. Trigger action A triggering event can be an insert, update, or delete statement or a instance shutdown or startup etc. The trigger fires automatically when any of these events occur A trigger constraint specifies a Boolean expression that must be true for the trigger to fire. This condition is specified using the WHEN clause. The trigger action is a procedure that contains the code to be executed when the trigger fires. Posted by: Rahul Contact Rahul Trigger is a stored plsql program, which is fired automatically when an event occur on the database. Triggers can be created on 1.DML statements 2.DDL staments 3.Viwes(for views only insted of trigger is valid) 4.Any database event occures like database startup or shutdown. Advantages of triggers: 1.Data auditing. 2.Enforce complex Business rules. 3.providing security. What are the modes of parameters that can be passed to a procedure ? IN,OUT,IN-OUT parameters. IN parameter is the default mode which acts as a constant inside calling environment.value passed in this parameter can not be changed.OUT parameter mode is used to pass value from calling environment into main block,here we can change the value.It acts as a variable inside calling environment. INOUT parameter mode which pass value into calling environment and will get the value back in main block. IN parameter mode uses call by reference method to pass value from formal parameter to actual parameter. OUT & INOUT parameter mode uses call by value method to pass values. Posted by: kiran Contact kiran You can also give a compiler hint - 'NOCOPY' when defining function parameters. e.g. : create or replace function add(var1 in number,var2 in number, result inout nocopy number) Main purpose of using NOCOPY: Even if exception occured in function block your result state is maintained in defined variable (parameter: result). If you haven't declared NOCOPY then the processed value for 'result' parameter will gets copied only when function ends. How we can create a table through procedure ? create procedure p1 is begin EXECUTE IMMEDIATE 'CREATE TABLE temp AS SELECT * FROM emp ' ; END; / Posted by: sunny talent09 Contact sunny talent09 You can create table from procedure using Execute immediate command. create procedure p1 is begin EXECUTE IMMEDIATE 'CREATE TABLE temp AS SELECT * FROM emp ' ; END; Posted by: fucker Contact fucker create procedure p1 (p_table_name IN VARCHAR(50)) is begin EXECUTE IMMEDIATE 'CREATE TABLE '|| p_table_name ||' AS SELECT * FROM emp ' ; END; Posted by: Aveek Contact Aveek The above answer is not working. procedure is created but not table. how to call procedure? Question : What is PL/SQL ? PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching. PL/SQL is nothing but the procedural extension to sql query language. using plsql we can process iterative statements,looping statements and so on. Posted by: ananth Contact ananth PL/SQL IS A PROCEDURAL LANGUAGE that provide condition ,error checking facility , Posted by: tabish jamia hamdard Contact tabish jamia hamdard We are using sql query language for the fast access data from the database , this we can do by using any front language like java,.net but comparatively the speed will be less . so for the best access of data the sql is extended with the concepts of programming language is exactly pl sql. In pl/sql functions what is use of out parameter even though we have return statement With out parameters you can get the more than one out values in the calling program. It is recommended not to use out parameters in functions. If you need more than one out values then use procedures instead of functions. Posted by: Viney Contact Viney We can't use OUT paramter in function.We must have to use RETURN to pass values out of function otherwise use procs Posted by: Vinod Pandit Contact Vinod Pandit Correction to previous post. We can use OUT parameters in functions. Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing. we can use TCL commands in trigger by using autonomous transactions feature of oracle. Posted by: vnamrata Contact vnamrata It is possible. We can create a trigger without error by using 'PRAGMA AUTONOMOUS-TRANSACTION' in declare section. Posted by: srinivas Contact srinivas No, we can not use Commit or Rollback, within a trigger. To understand, take one simple example... Suppose two persons are using one common table and they both have written trigger on that table. If in the first trigger we are using commit/rollback, then the second use would get some unwanted result. That is the reason, why we don't use Commit/Rollback in Triggers. Yeah. If some type of failure/ breakdown occurs, in that case Oracle automatically Rollback the data. Posted by: Sachin Nagpal Contact Sachin Nagpal We can use COMMIT or ROLLBACK inside Trigger, with the help of PRAGMA AUTONOMUS.. Question : What is a cursor for loop ? Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed. eg. FOR emp_rec IN C1 LOOP salary_total := salary_total +emp_rec sal; END LOOP; Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed. eg. FOR emp_rec IN C1 LOOP salary_total := salary_total +emp_rec sal; END LOOP; Posted by: sunny talent09 Contact sunny talent09 In case of cursor for loop, we don't need to open the cursor explicitly, for loop open the cursor and fetch the value after fetching the value cursor is implicitly closed.here is no need to close explicitly Give the structure of the procedure ? PROCEDURE name (parameter list.....) is local variable declarations BEGIN Executable statements. Exception. exception handlers end; basically procedure has three parts 1.variable declaretion(optional) 2.body(mandetory) 3.Exception(optional) suppose ex CREATE OR REPLACEPROCEDURE emp_pro( p_id IN employees.employee_id%TYPE) IS v_name employees.last_name%TYPE; v_mail employees.email%TYPE; BEGIN SELECT last_name,email INTO v_name,v_mail FROM employees WHERE employee_id:=p_id; DBMS_OUTPUT.PUT_LINE('NAME:'||v_name ||'MAILID:'||v_mail); END; / Posted by: sudipta das Contact sudipta das PROCEDURE name IS BEGIN --statements [EXCEPTION] END; Syntex CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter1 [mode1] datatype1, parameter2 [mode2] datatype2, . . .)] IS|AS PL/SQL Block; Example IS v_city VARCHAR2(30); v_dname VARCHAR2(30); BEGIN DBMS_OUTPUT.PUT_LINE('Main Procedure p1_ins_loc'); INSERT INTO locations (location_id, city) VALUES (p_lid, p_city); SELECT city INTO v_city FROM locations WHERE location_id = p_lid; DBMS_OUTPUT.PUT_LINE('Inserted city '||v_city); DBMS_OUTPUT.PUT_LINE('Invoking the procedure p2_ins_dept ...'); p2_ins_dept(p_lid); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No such dept/loc for any employee'); END; Question : What are two parts of package ? The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY. Package Specification contains declarations that are global to the packages and local to the schema. Package Body contains actual procedures and local declaration of the procedures and cursor declarations. package has two parts 1.Package specification 2.Package body In the specification,where we declare variable,function,procedure that is global to the package and local to the schema. package body contains the defination of the function,procedure.we can also declare private function and procedure which is not accessble out side the package. Posted by: sudipta das Contact sudipta das 1)package specification 2)package body package specification : where the variables r global and all the packages can access with that variable.it contains the declaration of variables.(variables r global) package body: where the procedures and functions are collected in that packages.the variable declared with in package body of a function or procedure they are not used outside of that procedure.(the variables are private used by that specific procedure or function. Posted by: sugeetha Contact sugeetha package has two parts 1.Package specification 2.Package body In the specification,declare variable,function,procedure that is global to the package and local to the schema. package body contains the defination of the function,procedure.we can also declare private function and procedure which is not accessble out side the package. create or replace package taxes is tax number; ----declare all public procedure/funcation end taxes; create or replace package body taxes is ---declare all private variable ---declare all public/private procedure/funcations begin select rate_value into tax from tax_rates where rate_name='TAX'; end taxes; In a Distributed Database System Can we execute two queries simultaneously ? Justify ? As Distributed database system based on 2 phase commit,one query is independent of 2 nd query so of course we can run. Question : What is Pragma EXECPTION_INIT ? Explain the usage Answers: The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error. e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number) The PRAGMA_EXCEPTION_INIT tells the compiler to assosiate an exception with an oracle error. Posted by: sudipta das Contact sudipta das The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. For this you need to first declare the exception name... Eg: x exception; Pragma exception_int(x,error number) What is the basic structure of PL/SQL ? PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL. DECLARE --all the variables u use in ur program should be declared here --- BEGIN --application logic goes here -- EXCEPTION HANDLING --very imp END Posted by: VIVEK BANSAL Contact VIVEK BANSAL Anonymous blocks or nested blocks can be used in PL/SQL. What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes? % TYPE provides the data type of a variable or a database column to that variable. % ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor. The advantages are : I. Need not know about variable's data type ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly. %TYPE provides datatypes of the particular column of the table. %ROWTYPE attribute is useful When we need to fetch entire row from the table. secondly if we don't know the data type of some column %rowtype is useful for that. finally, if we change any datatypes of the column,%rowtype automatically change the datatypes for the variable which is created by user Question : Explain the two type of Cursors ? There are two types of cursors, Implicit Cursor and Explicit Cursor. PL/SQL uses Implicit Cursors for queries. User defined cursors are called Explicit Cursors. They can be declared and used. there are two types of cursor 1.implicit cursor 2. Explicit cursor Posted by: sudipta das Contact sudipta das Implicit cursor is a system defined cursor .Explicit cursor is a user defined cursor. What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ? A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package. A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures. One more differene is cursor declared in a package specification must have RETURN type Posted by: Madhavi Contact Madhavi A cursor declare in the package specification that can be accessed in the other procedure or procedures of the package. A cursor declare in the procedure that can't be accessed by other procedure. Question : What is Overloading of procedures ? The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures. e.g. DBMS_OUTPUT put_line What is a package ? What are the advantages of packages ? Overloading of procedure name of the procedure is same but the number of parameters should be different.In that case,procedure will be overloaded. 2. if the number of parameters are same in that case,data type should be different. if the two rules are satisfied in that case procedure will be overloaded. Posted by: sudipta das Contact sudipta das The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures. e.g. DBMS_OUTPUT put_line Posted by: Madhavi Contact Madhavi Same procedure name with different parameters called procedure overloading, parameters may different by their datatypes, sequence and position. eg1: get_employee(Emp_id number); get_employee(Emp_id Varchar(20)); eg2: get_employee(ID number, name char(20)) get_employee(ID Number, name char(20), dept char(20)) get_employee(name char(20), dept char(20),ID Number) What happens if a procedure that updates a column of table X is called in a database trigger of the same table ? Mutation of table occurs. What is NVL? NVL: Null value function converts a null value to a non-null value for the purpose of evaluating an expression. Numeric Functions accept numeric I/P & return numeric values. They are MOD, SQRT, ROUND, TRUNC & POWER. What are the two parts of a procedure ? Procedure Specification and Procedure Body. the above person have answerd wrong. Or rather the question may be wrong. It should be packages instead of procedures. What are the 2 parts of package ? But the question is what are the 2 parts for procedue!!! Posted by: hari Contact hari Procedures cannot have specification and body. Only packages have specification and body. Procedures have only sections which are declaration section(optional),executable section(mandatory) and exception handler (optional) between begin and end. Correct me if I'm wrong Write the order of precedence for validation of a column in a table ? I. done using Database triggers. ii. done using Integarity Constraints I & ii. Exception : First column should be validated by constraints and then Triggers Posted by: RISHI RAWAT Contact RISHI RAWAT 1. Before Statement Trigger 2. Integrity constraint 3. After statement trigger Question : What is Character Functions? Character Functions are INITCAP, UPPER, LOWER, SUBSTR & LENGTH. Additional functions are GREATEST & LEAST. Group Functions returns results based upon groups of rows rather than one result per row, use group functions. They are AVG, COUNT, MAX, MIN & SUM. What is Rollback? Rollback causes work in the current transaction to be undone. What are the cursor attributes used in PL/SQL ? Answers: %ISOPEN - to check whether cursor is open or not % ROWCOUNT - number of rows fetched/updated/deleted. % FOUND - to check whether cursor has fetched any row. True if rows are fetched. % NOT FOUND - to check whether cursor has fetched any row. True if no rows are featched. These attributes are proceeded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors. What is Intersect? Intersect is the product of two tables listing only the matching rows. What are advantages fo Stored Procedures Extensibility,Modularity, Reusability, Maintainability and one time compilation. Easy maintenance ? Improved data security and integrity ? Improved performance Modularity, Reusability, one time compilation. Posted by: anju saxena Contact anju saxena Faster in execution: As Procedure is a complied schema object and stored in database, hence it takes less time to execution. What is SPOOL? SPOOL command creates a print file of the report. spool command used for printing the out put of the sql statments in a file. Eg. spool /tmp/sql_out.txt select emp_name, emp_id from emp where dept='sales'; spool off; we can see the out on /tmp/sql_out.txt file. What is an Exception ? What are types of Exception ? Exception is the error handling part of PL/SQL block. The types are Predefined and user defined. Some of Predefined exceptions are. CURSOR_ALREADY_OPEN DUP_VAL_ON_INDEX NO_DATA_FOUND TOO_MANY_ROWS INVALID_CURSOR INVALID_NUMBER LOGON_DENIED NOT_LOGGED_ON PROGRAM-ERROR STORAGE_ERROR TIMEOUT_ON_RESOURCE VALUE_ERROR ZERO_DIVIDE OTHERS. An exception is an identifier/error that is handle by pl/sql block. Exception is two types 1.Predefind exception 2.Userdefined exception predefind exceptions are 1.TOO_MANY_ROWS 2.INVALID_CURSOR 3.NO_DATA_FOUND 3. What are the datatypes a available in PL/SQL ? Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN. Some composite data types such as RECORD & TABLE. 1. Character Datatypes: char(size), nchar(size), varchar2(size), nvarchar2(size), long, raw, longraw 2. Numeric Datatypes number(p,s), numeric(p,s), float, dec(p,s), decimal(p,s), integer, int, smallint,real, double precision 3. Date/Time Datatypes: date, timestamp, timestamp with local time zone, timestamp with time zone, interval year to month, interval day to seconds 4. LOB data types: bfile,blob,clob,nclob 5. Rowid Data types: rowid,urowid The INSERT INTO Statements in SQL? Answers: INSERT INTO table_name VALUES (value1, value2,....) INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....) What are the components of a PL/SQL block ? A set of related declarations and procedural statements is called block. components of PL/SQL are: declare: (optional) variable declare Begin: (Mandatory)Procedural statement Exception: (optional) error to be trapped End: (Mandatory) So BEGIN and END are required in PL/SQL block What is SQL*Loader? SQL*Loader is a product for moving data in external files into tables in an Oracle database. To load data from external files into an Oracle database, two types of input must be provided to SQL*Loader : the data itself and the control file. The control file describes the data to be loaded. It describes the Names and format of the data files, Specifications for loading data and the Data to be loaded (optional). Invoking the loader sqlload username/password controlfilename . What is PL/SQL table ? Objects of type TABLE are called "PL/SQL tables", which are modeled as (but not the same as) database tables, PL/SQL tables use a primary PL/SQL tables can have one column and a primary key. Cursors What are the components of a PL/SQL Block ? Declarative part, Executable part and Exception part. Datatypes PL/SQL What is a JOIN? JOIN is the form of SELECT command that combines info from two or more tables. Types of Joins are Simple (Equijoin & Non-Equijoin), Outer & Self join. Equijoin returns rows from two or more tables joined together based upon a equality condition in the WHERE clause. Non-Equijoin returns rows from two or more tables based upon a relationship other than the equality condition in the WHERE clause. Outer Join combines two or more tables returning those rows from one table that have no direct match in the other table. Self Join joins a table to itself as though it were two separate tables. What is Consistency? Consistency : Assures users that the data they are changing or viewing is not changed until the are thro' with it. Question : What is Indexes? Indexes are optional structures associated with tables used to speed query execution and/or guarantee uniqueness. Create an index if there are frequent retrieval of fewer than 10-15% of the rows in a large table and columns are referenced frequently in the WHERE clause. Implied tradeoff is query speed vs. update speed. Oracle automatically update indexes. Concatenated index max. is 16 columns. Give the structure of the function ? FUNCTION name (argument list .....) Return datatype is local variable declarations Begin executable statements Exception execution handlers End; create or replace function (arg1,arg2,....) return datatype as .... variable declaration begin ... program code ... return exception exception statement end; Posted by: shikha Contact shikha Structure of the fuction same as procedure it has three parts 1.variable declaration(optional) 2.function body(mandetory) 3.Exception part(optional) What is a cursor ? Why Cursor is required ? Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows. The oracle server uses works areas called private sql area.Here all the DML statement is executed and to processing statement.basically it's a implicit cursor. there are two types of cursor 1.Implicit cursor. 2.Explicit cursor. Implicit cursor is open for all DML statement.after execute the statement cursor is atomatically closed. Explicit cursor is created by programmer. explicit cursor is needed when query returns more than one rows. In that case,programmer creates explicit cursor.open the cursor. then fetch the value from the active set. after fetching all the value, cursor is closed by programmer. What is the use of CASCADE CONSTRAINTS? When this clause is used with the DROP command, a parent table can be dropped even when a child table exists. Integrity key is violated when one tried to delete parent key value when foreign key value. A parent key value can only be deleted after foreign key value. Cascade constraints has dual purpose. 1. Alter table command can be issued with "ON DELETE CASCADE" When this is done one can run delete command with cascade constriaint to delete parent key value. 2. To drop a table that has parent values. With cascade constraint,foreign key constraint on child table and parent table is dropped.The child table still exits. Posted by: jack Contact jack When we try do drop a table for which another table links through foreign key ( In simple words primary key of one table(parent) will be foreign key in other (child) ) it gives an error saying integrity constraint violation. this is because we can not drop a parent table when child table exists. But We are able to delete a parent table along with child table by using CASCADE clause while cretaing a child table. Eg: create table tab_name ( num NUMBER(3) constraint f_key is foreign key references tab_name2(num2) on delete CASCADE ); What is Date Functions? Date Functions are ADD_MONTHS, LAST_DAY, NEXT_DAY, MONTHS_BETWEEN & SYSDATE. What is COLUMN? COLUMN command define column headings & format data values. What is Commit? Commit is an event that attempts to make data in the database identical to the data in the form. It involves writing or posting data to the database and committing data to the database. Forms check the validity of the data in fields and records during a commit. Validity check are uniqueness, consistency and db restrictions. What is TTITLE and BTITLE? TTITLE & BTITLE are commands to control report headings & footers. ttitle and btitle used in sql * in reports . to show the header and footer of the reports. What is SET? SET command changes the system variables affecting the report environment. What is Union? Union is the product of two or more tables. Union is the product of two or more tables. Which is removed duplicate values from the query. Question : What is Minus? Question : What is Correlated Subquery? Correlated Subquery is a subquery that is evaluated once for each row processed by the parent statement. Parent statement can be Select, Update or Delete. Use CRSQ to answer multipart questions whose answer depends on the value in each row processed by parent statement. Question : What is Multiple columns? Multiple columns can be returned from a Nested Subquery. What is Sequences? Sequences are used for generating sequence numbers without any overhead of locking. Drawback is that after generating a sequence number if the transaction is rolled back, then that sequence number is lost. Sequence is a database object used to generate unique sequential integer values. Question : What is Synonyms? Synonyms is the alias name for table, views, sequences & procedures and are created for reasons of Security and Convenience. Two levels are Public - created by DBA & accessible to all the users. Private - Accessible to creator only. Advantages are referencing without specifying the owner and Flexibility to customize a more meaningful naming convention. What is Data types? Max. columns in a table is 255. Max. Char size is 255, Long is 64K & Number is 38 digits. Cannot Query on a long column. Char, Varchar2 Max. size is 2000 & default is 1 byte. Number(p,s) p is precision range 1 to 38, s is scale -84 to 127. Long Character data of variable length upto 2GB. Date Range from Jan 4712 BC to Dec 4712 AD. Raw Stores Binary data (Graphics Image & Digitized Sound). Max. is 255 bytes. Mslabel Binary format of an OS label. Used primarily with Trusted Oracle. What is Transaction? Transaction is defined as all changes made to the database between successive commits. What is Posting? Posting is an event that writes Inserts, Updates & Deletes in the forms to the database but not committing these transactions to the database. vWhat is Savepoint? Savepoint is a point within a particular transaction to which you may rollback without rolling back the entire transaction. What is Locking? Locking are mechanisms intended to prevent destructive interaction between users accessing data. Locks are used to achieve. locks are mechanisms which are intended to prevent destructive interaction between con current sessions. How packaged procedures and functions are called from the following? a. Stored procedure or anonymous block b. an application program such a PRC *C, PRO* COBOL c. SQL *PLUS Category a. PACKAGE NAME.PROCEDURE NAME (parameters); variable := PACKAGE NAME.FUNCTION NAME (arguments); EXEC SQL EXECUTE b. BEGIN PACKAGE NAME.PROCEDURE NAME (parameters) variable := PACKAGE NAME.FUNCTION NAME (arguments); END; END EXEC; c. EXECUTE PACKAGE NAME.PROCEDURE if the procedures does not have any out/in-out parameters. A function can not be called. What is SQL Deadlock? Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource. First user needs a resource locked by the second user and the second user needs a resource locked by the first user. To avoid dead locks, avoid using exclusive table lock and if using, use it in the same sequence and use Commit frequently to release locks. What is Mutating SQL Table Mutating Table is a table that is currently being modified by an Insert, Update or Delete statement. Constraining Table is a table that a triggering statement might need to read either directly for a SQL statement or indirectly for a declarative Referential Integrity constraints. Pseudo Columns behaves like a column in a table but are not actually stored in the table. E.g. Currval, Nextval, Rowid, Rownum, Level etc. What are the return values of functions SQLCODE and SQLERRM ? SQLCODE returns the latest code of the error that has occurred. SQLERRM returns the relevant error message of the SQLCODE. SQLCODE returns the latest code of the error that has occured. SQLERRM returns relevant massege of the error code Posted by: sudipta das Contact sudipta das SQLCODE returns error code that has occurred. SQLERRM returns error message with error code. The most important DDL statements in SQL are? Answers: CREATE TABLE - creates a new database table ALTER TABLE - alters (changes) a database table DROP TABLE - deletes a database table CREATE INDEX - creates an index (search key) DROP INDEX - deletes an index Operators used in SELECT statements are? = Equal <> or != Not equal > Greater than < Less than >= Greater than or equal <= Less than or equal BETWEEN Between an inclusive range LIKE Search for a pattern SELECT statements in SQL? ELECT column_name(s) FROM table_name SELECT DISTINCT column_name(s) FROM table_name SELECT column FROM table WHERE column operator value SELECT column FROM table WHERE column LIKE pattern SELECT column,SUM(column) FROM table GROUP BY column SELECT column,SUM(column) FROM table GROUP BY column HAVING SUM(column) condition value Note that single quotes around text values and numeric values should not be enclosed in quotes. Double quotes may be acceptable in some databases. What are the PL/SQL Statements used in cursor processing ? DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE cursor name. The SELECT INTO Statement is most often used to create backup copies of tables or for archiving records? SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source WHERE column_name operator value. The Update Statement in SQL. UPDATE table_name SET column_name = new_value WHERE column_name = some_value Question : What is the Delete Statements in SQL? DELETE FROM table_name WHERE column_name = some_value Delete All Rows: DELETE FROM table_name or DELETE * FROM table_name How to sort the rows in SQL? Sort the Rows: SELECT column1, column2, ... FROM table_name ORDER BY columnX, columnY, .. SELECT column1, column2, ... FROM table_name ORDER BY columnX DESC SELECT column1, column2, ... FROM table_name ORDER BY columnX DESC, columnY ASC The IN operator may be used if you know the exact value you want to return for at least one of the columns. SELECT column_name FROM table_name WHERE column_name IN (value1,value2,..)
www.allwalkin.blogspot.com
No comments:
Post a Comment