Code
ALTER TABLE emp RENAME COLUMN emp_id TO id;

Use this Script for nename particular ColumnName in SqlTable

sp_rename 'TableName.[ColumnNameOld]','[ColumnNameNew]','Column'

INSERT , UPDATE, DELETE operations implicity lock rows of a table that satisfies the condition.

Explicitly you may lock a table using for update of as follows

SELECT * FROM EMP
WHERE EMPNO = 7369
FOR UPDATE OF SAL NOWAIT

It will lock single row of emp table having employee number 7369.

create table #temptable(rowid int,colorname varchar(25),Hexa varchar(7),R tinyint,G tinyint,B tinyint) GO insert into #temptable values(1,'Violet','#8B00FF',139,0,255); insert into #te...

Use decode function.

Yes , we can. Here is simple example.

CREATE OR REPLACE PROCEDURE TEST_PROC(V_NUM OUT NUMBER) AS
BEGIN
V_NUM := 10;
END;
/

CREATE OR REPLACE FUNCTION TEST_FUNC RETURN NUMBER  AS
V_NUM NUMBER;
BEGIN
TEST_PROC(V_NUM);
RETURN V_NUM;
END;
/

SELECT TEST_FUNC() FROM DUAL;

OUTPUT
10

Yes this can be possible by TABLE FUNCTION STEP - 1    CREATE TYPE ex_tabl_fun_obj AS OBJECT (emp_name varchar2(20), emp_id NUMBER(2));STEP - 2     CREATE TYPE ex_tabl_fu...

Its a universal truth that function can return only one value, but there are some tricks by using that you can get more than one value , while function returning only one value.When you define functio...

select e.* from( select enmae,sal,   dense_rank()  over(partition by deptno           order by sal) ranksal from emp)e  where e.ra...

SELECT empno,ename,sal
FROM (
select empno,ename,sal
from emp
order by sal asc
)
WHERE rownum <=3

EMPNO SAL
--------- ---------
7369 800
7900 950
7876 1100

enjoy!! by HIASAT

Alias name it temporary name given to a tablen or  columns of a table.It has its existence until the satement is executed.  For example in the following example instead of typing tablenames ...

just to have more readability

To view tables in the current schemaSELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE'To view all the tables that you have access SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT...

SELECT TABLE_NAME FROM All_TABLES
Order by TABLE_NAME;

The above query will display all the table names in the database.

SELECT TABLE_NAME FROM USER_TABLES
Order by TABLE_NAME;

The above query will display all the table names in a particular schema.

In sub query the inner query is executed only once. Depeding upon the results of inner query , outer query is evaluated.

In correlated subquery the inner query is evaluated once for each row processed by the parent statement or outer query.

subquery: required in those cases where requisted data are not known                  and always depend on inner qu...

The first mistake in this code is savepoint 1;You can't give 1 as identifier. Identifier name must begin with a letter.insert into A values(i);savepoint x;Here you are defining save...

The flow of execution will be as below:step1: loop will execute 5 times and the variable value(i) will store in the table a in series of values from 1 to 5.step2: all 5 values in series from 1..5 w...

1> always avoid select * from
2> do not use in operator

You may also use triggers for this purpose. But specifying on delete cascade while defining foreign key constraint is better option.

Union --> All rows/cols from 1st select and 2nd select with no duplicates Union All -->All rows/cols from 1st Select and 2nd Select with duplicates Intersect --> Rows common in both select statements...

The UNION operator is used to combine the result-set of two or more SELECT statements.   SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.

When you are performing INSERT , DELETE , UPDATE operations on a table it implicity aquires row level locks.  If a transaction obtains a row lock for a row, the transaction also aquires a table l...

The job of optimizer is to decide the most efficient method to execute a query. There are two types of optimizers in oracle RBO or Rule Based Optimizer since v6 CBO or Cost Based Optimizer sinc...

optimization techniques

Left outer Join fetch all the records from Left side Table(Only Condition satisfied records from right side table) where as Right Outer Join fetch all the records from Right side Table(Only Condition satisfied records from Left side table).

Using Equi Join with Union we can do the outer join then why we need to go for Outer Join -- increase the performance

Simple example for predefined exception CREATE OR REPLACE PROCEDURE TEST_PROC AS A NUMBER ; BEGIN A := 'TEST'; DBMS_OUTPUT.PUT_LINE('THE VALUE OF A = '||A); EXCEPTION WHEN VALUE_ERROR ...

set serveroutput on;create or replace procedure proc1 (emp_id in emp.employee_id%type, emp_na out emp.last_name%type, emp_sal out emp.salary%type, emp_job out emp.job_id%type) is &...

all data are stored in o and 1 format means binary form

all data are stored in o and 1 format means binary form

- Pl/sql is in memory representation of a table.- It is just a data type. ( Collection type)- It may store any number of rows from table.Here is  a simple exampleCREATE OR REPLACE PROCEDURE TEST_...

INDEX By Tables composed of two components1.  Primary key of data type BINARY_INTEGER2.  Column of scalar or record data type.Since this table is unconstrained, it can increase its size dynamically....

1.Sql is a declarative language.It tells only what to do. PL/Sql is a procedural language.It tells what to do and how to do. 2.Sql executes DDL and DML statements . Pl/Sql executes triggers,func...

sql is structured query language and plsql is procedural sql. in sql we write a query to fetch the data from data base. in pl sql we write the set of sql command and execute it in one times. the benef...

We can use commit and rollback in trigers using autonomous transactions.- An atonomous transaction is an independent transaction initiated by another transaction.- When an atonomous transaction is cal...

we can use commit or rolback in trigger without error by using "PRAGMA AUTONOMOUS_TRANSACTION;" on declaration sectionCREATE TRIGGER anniversary_triggerBEFORE INSERT ON employees FOR EACH RO...

A ref cursor is a dynamic cursor in which the contents of cursor can be changed dynamically at run time depending upon our requirement.A ref cursor is basically a data type. A variable declared based ...

Cartesian product is an example for many to many reolationship in SQL.

For example

select a.ename, a.job, a.sal, b.dname from emp a, dept b

results in many to many relationship.

Employee
Department
lets say one employee can work in multiple department.
It needs many to many relationship
Create another table EmpDeptMap.
it will have foreign key with employee and Deptartment too.
In this table (empid+deptid) is the primary key

Dead lock is a unique situation in multiuser system that causes two or more users to wait indefinately for a locked resource. First user needs a resource locked by the second user and second user ...

A deadlock occurs when two or more users are waiting for data locked by each other. When this happens, these users are stuck (deadly embraced) and cannot continue processing. Oracle automatically dete...

create table ABC as (select * from XYZ where 1=2)

You can copy the definition of objects to text file using dmbs_metadata package.

This is the query to copy emp table definition to text file.

spool textfilename
select dbms_metadata.get_ddl('TABLE','EMP','CMSJAN') from dual;
spool off;

Using the following query you may copy table structure and data but you can't copy constraints.

Create table emp_copy as select * from emp;

below shown query will copy table with constraints and data into new table. here table_copy is the new table and old_table is the table whose data and constraints are to be copied.

create table table_copy as (select * from old_table)

Constraint  is  used to restrict invalid and inconsistent data entry  into the table.
Index is used  for faster retrieval of data.

Both are entirely different concepts.

Constraints are rules applied to Table/Columns of a table. Constraints are one type of restriction which are useful while data is manipulated.

Index are ordered list of rows of tables. Index are useful while faster retrival of data.

Hope this anwsers to your question.

Cheers,
Srini.

select sysdate from dual;

select sysdate from user_tables where rownum <=50;

Here you can use any table name that contains more than 50 rows.

Variable length character values up to 2 gigabytes. Only one LONG column is allowed per table. You cannot use LONG datatype in functions, WHERE clause of SELECT, in indexing and subqueries.

If text is not visible use 
SET  LONG  2000.

Kindly elobrate your issue. As per my concern you can use CONCAT or PIE || Example

Code
SELECT CONCAT (ename,',') FROM emp;
 
SELECT ename||',' FROM emp;

Code
SELECT DISTINCT X.id, (
Substring((SELECT Val + ',' FROM txtTable Y WHERE Y.id = X.Id ORDER BY X.ID FOR XML PATH(''))
,0,
LEN((SELECT Val + ',' FROM txtTable Y WHERE Y.id = X.Id ORDER BY X.ID FOR XML PATH(''))) ))
FROM txtTable X

Ans : A & D

A and D

The column numbers are used so that you can do the order by without keying in all the column names in the order by clause. For eg.select empno, ename from emp order by empno; -- qry 1could b...

There is no relation between column_id in user_tab_columns and column number in order by clause.For exampleSelect ename,job, sal from emp order by 1;Here it will arrange data in the order of employee ...

Pragma is a compiler directive. It passes information to compiler. These are processed at compile time not at runtime.             &nbs...

Pragma is a compiler directive. Using SQLCODE & SQLERRM we know that which ERROR code is associate to what error.

Constrains are the key word along with create table command. Different type of table constraints are primary key not null unique etc.

>>We have two type of constraints - Table Level and Column Level. Which is better and why..? Performance wise there will not be any difference between table level and column level constraints. G...

Nested table Unbounded ...

A Table is a basic unit of storage in oracle.A nested table is a collection type. The main advantage of collections is instead of processing data sequentially, we may process all the date&nb...

There are only two types of cursors in oracle Pl/sql.1. Implicit cursors2. Explicit cursors    We can define an explicit cursor as static or dynamic (REF CURSOR).   ...

1. Implicit Cursor
2. Explicit Cursor
3. Ref Cursor

Implicit Cursor like SQL%rowcount,SQL%rowtype...

Explicit   cursors are user defined cursor

Ref Cursor is object name of the cursor type. Its mainly used for dynamic purpose

A cursor variable is a variable that points to or refernces an underlying cursor.  Unlike an explicit cursor, which names the PL/SQL work area for the result set, a cursor variable is a reference to ...

I think, You can use SYS_Cursor.

There are 5 types of datatypes
1.Numeric Datatype
2.character Datatype
3.LargeobjectsDatatype
4.Rowiddatatype
5.Time/Datedatatype

There is no restiction on number of datatypes allowed except for long and LOB.
I table can contain only one long column.

Collection is an ordered group of elements all are of same time. Each element in the collection has unique subscript to identify its position in the collection.Pl/sql offers three collection type...

The following bullet points can be referred to when deciding what collection best suits a particular solution.VarrayUse to preserve ordered list Use when working with a fixed set, with a known number ...

Mutate means chage. Mutating  table is a table that is being modified by update, delete or  insert statement.

it's a table which is currently being modified by a trigger statement.

Spaces will be padded with y variable,its means y will store y='sudhir              ' . while x only store 6 six character...

ans is both values are not equal  char(20)='name' covered in memorey areea is 20 but varchar2(20)='name' covered in memorey areea is 4 becoze char is fixed lenth char data type but varchar2 is variable lenth char data type

Using wrap utility we can encrypt source code of a function  to byte code (Not a readable format).  Becareful there is no  decrypt utility to convert  source co...

Function can be encrypted using Oracle Wrap utility. this will prevent accessing from users without permission

insert is faster than delete because insert just add values to the table. but delete want to store the deleted data in the memory for future references

Both insert and delete are used for different purposes. Also inserts are not always faster than delete. If the table has got indexes then insert will be slow and delete will be fast (if the WHERE cla...

Both are same.outer join : We have 3 types of outer joins. Left Oute, Right Outer and Full Outer join.Left outer join:Select * from lefttable, righttable where lefttable.col1=righttable.col1(+)This wi...

Both are same. Right outer join returns all the reocrds that satisfy the join condtion + rest of the records from right (or second) table.

DUAL is a part data dictionary and owned by SYS. You should not make modifications to this   table. 
It contains only one row and  one column of VARCHAR2 datatype.
Used to refer an object which does not have any pysical reference in database table.
Ex:- Select sysdate from dual.

Because its a dummy table.

Would like to add one more point for your answer,

Calling a function from select stament is not recomended as it will degrade the performance.

>>Why the stored functions can not be called from DML Statements?  We can call a stored function from dml statement.Here is simple exampleCREATE OR REPLACE FUNCTION TEST_1 RETURN NUMBER ISB...

Stored procedures are stored in data base in complied form. In dynamic sql the statements are dynamically constructed at run time.

Stored Procedures are compiled and runs on the server. dynamic SQL are not compiled and runs on client.

Compare EXISTS and IN Usage with advantages and disadvantages.

We should use Exists or In based on the no of records inner and outer query is expected to return.Using Exists : Here the outer query is executed first and then inner query is excecuted for each value...

To add  primary key or unique key constraint Alter table tablename add constraint constraintname  primary key / unique (column name)To add check constraint Alter table tablename add constrai...

ALTER TABLE STUDENT  ADD CONSTRAINT PK_STD_ID PRIMARY KEY (STUDENT_ID);

GRANT SELECT ON table_name TO user_name

There are couple of ways 1) If there are user other than owner accessing this table then you can "grant select on TABLE_NAME" 2) Otherwise you DBA can create a role which will have only the privillege...

try this its working insert into tablename (vname)values(chr(39)||'name'||chr(39))

output:- 'name'

Here all have given the answer which is for hard coded value for some predefined word. He is asking and I am too looking for the this Question "ANY DATA".If user don't know whether the coming strin...

To join a table itself is called self join.

Join the table to itself is called self join. It is used when you need to join the data with the same table data.

Ex:- SELECT A.ENAME,A.SAL ,B.ENAME,B.SAL
       FROM EMP A,EMP B

      WHERE A.MGR = B.EMPNO

PL/SQL tables are temporary array like objects used in a PL/SQL Block. The size of pl/sql table is unconstrained. For varrays we need to specify upperbound.Pl/sql tables can not be stored in database ...

array is set of values of same datatype.. where as tables can store values of diff datatypes.. also tables has no upper limit where as arrays has.

First, let us understand why these errors cropup.1) NO_DATA_FOUND - is raised only for "select into" statements when  the where clause of the query does not match any rows.2)TOO_MANY_rows - is raised...

If you don't want an excpetion to be raised on a SELECT statement, you can avoid executing the statement by having a count just before the SELECT as shown belowSELECT count(rowid)into v_count from...

Error will be like "Cursor already open"

CURSOR_ALREADY_OPEN ORA-06511 An attempt was made to open a cursor that was already open.

IN :- actual parameter is passed by reference (a pointer to the value is passed in) OUT:- actual parameter is passed by value (a copy of the value is passed out) unless NOCOPY is specified I...

Take this:

IN parameters are passed by reference, so the value cannot be changed in the procedure and functions.
Out and IN-OUT parameters are passed by value, so the values can be changed inthe procedures and fucnctions.

A ref cursor is basically a data type. A variable created based on such data type is called a cursor variable.Dynamic cursors are declared using reference cursors.Differences:1. Cusor is static in nat...

Ref Cursor is used to retrieve different Active Set from different Table,
whereas Cursor can retrieve only active set from the same table.

select * from all_objects where object_type= 'PROCEDURE';

I am not too sure if I understand our question properly. If you wanted to find all the stored procedures defined to the database with a CREATE PROCEDURE command (let's say DB2), then run a query from ...

create table emp as select * from student where 1=2;

Hello,

here is the query,

CREATE TABLE EMP1 AS SELECT * FROM EMP WHERE 1=2;

Group functions returns one result per each group of the result setWhere as analytical functions  returns multi rows per each group i.e. using analytical functions we may display group results along with individual rows.

Thank u Rampratap.

Dev

1.Firing sequence of Database triggers on multiple rows   Before statement trigger  Before row trigger for first row  After row trigger for first row  Befo...

the one that is created last executes first

Use database link

You can use a single query to do it ...Use the ordinary INSERT INTO Table2 SELECT col1, col2 .. FROM Table1But only consideration should be done is the two different DataBases .. Suppose Say our appl...

Use DML Triggers to apply the same action to another table as well.

write trigger

The answer will be between 11-14 . You have to consider two extreme cases where 1. 3 rows with a=10 and 11 rows with b=20 are all distinct rows - then the answer will be 14 2. Where there are 11 rows...

The accurate answer is "The result can contain max of 14 records", but this not guaranteed. It can be less also. (Minimum is 10 rows and maximum is 14 rows). It is because there can be some overlpa in the same row between "a" and "b" column.

Spool the out put to text file as follows.

SET HEADING OFF;
SET FEEDBACK OFF;
SPOOL EXAMPLE.TXT
SELECT EMPNO || ','||ENAME||','||SAL FROM EMP;
SPOOL OFF;
SET FEEDBACK ON;
SET HEADING ON;

try below wiil resolve the problemspool c:a.txtselect tname||','||tabtype from tab;spool offopen a.txt

Select distinct TABLE_NAME
from user_tab_columns;

Synonym name  Synonym for Table CAT USER_CATALOG CLU USER_CLUSTERS DICT DICTIONARY IND USER_INDEXES ...

Alais names in where clause works with inline view only.

Lets examing the query as:select * from (select rownum rnum,a.* from testsal a) b where b.rnum=3;".The subquery here is used as another table for the main query and only its structure will b...

ROWNUM returns a number indicating the order in which the row was retrieved from the table, but this is not always the order in which a row is displayed. 

If you directly user rownum in the outer query it considers the rownum of outer query not that of inner query.

It will work for = operator
But it will work for the first row in the table pls check that

use

Code
NVL(a.sal,0)<= NVL(b.sal,0)

This will work:-

Code
SELECT DISTINCT a.salary FROM emp a, emp b
WHERE 9=(SELECT DISTINCT count(b.salary) FROM emp b WHERE a.salary<=b.salary);

Ref cursor is basically a data type. A variable declared based on that data type is called cursor variable. The main advantage of using this variable is we can dynamically assosiate with different  queries at runtime depending upon  our requirement.

1. This is a dynamic cursor which can holds multiple value at run time
2. Actually this is nothing but a data type of cursor variable

ref cursor is dynamic and no need to declare variables.ref cursor as two types
1.stong cursor(it has return value)
2.week cursor (it has no return value)

I know about REF cursor..it is one datatype , which by we return data to web pages fetch by java.. but REP cursor is new foe me..

You cannot use =, >, >= on rownum operator. Instead you can have,

select employee_id from
(select row_number() over(order by employee_id asc) as rn, employee_id from employees) where rn=2;

select employee_id from
(select rownum as rn, employee_id from employees) where rn=2;

We should not use >, >=,  =  with rownum.It do not show any error message but returns 0 rows.Here select ename,job,salfrom emp where rownum = 2;Rownum always assigns rownum 1 to first ...

>>what purpose we are using cursors? why you need cursor?Whenever we are processing sql statement oracle allots PRIVATE SQL AREA OR WORK AREA to store porcessed information. Cursor is a pointer ...

in stored procedure & function we can retrive the data sequentially we use cursor

Alter trigger trigger_name disable/enable all triggers.

Alter trigger Disable;

Yes we can ..
Instead of cursor we using bulk collection or bulk binding or using %type and %rowtype

You can use bulk binding to avoid cursors. It improves performance by minimizing context switches sql and pl/sql. When you are hadling large volume of data then better to use bulk binding.

SELECT to_char(to_date(24,'J'),'JSP')||' DOLLARS' FROM DUAL

Please find the sample below.select 24||'$',       decode (substr('24$',1,1),1,'One',2,'Two',3,'Three',4,'Four','Others')||       decode (su...

SELECT TO_CHAR(TO_DATE(11111,'J'),'JSP')||' DOLLARS' FROM DUAL

HI Dev,Its working but can u explain it more clearly thanks,Radhi

A cusor can tabke out parameter. But never use OUT and IN OUT parameters in functions. The main purpose of function is to take zero or more parameters and return a single value.

function can take out parameter and in out parameter in that case it returns values  to calling program with return value also    accroding to me its not good programing style to u...

no data found is an exception where as %not found is a cursor attribute.

no data found is exception where as %notfound is retrn by exception

Whenever we execute DML statements oracle assigns a work area called private sql area to store information. Cursors meachanism allows us to name that private sql area there by access information ...

I think your implict and explict cursor concent needs a correction:EXPLICIT CURSORS are used, when you are getting more than a single row of data that needs to be processed further.  Explicit cur...

You can use

USER_ERRORS
or
SHOW ERRORS

You may also use DBMS_OUTPUT.PUT_LINE 

the correct command is
SHOW ERRORS or
SHOW ERR

%tye and %rowtype provide data independence and allows programs to adapt to database changes due to new business requirement.%type is used to declare a variable with same datatype as that of...

the %type will return datatyp attached to refernced column or itemex: v_a emp.empid%type;it will return datatype attched to empid of emp table. the %rowtype will return all datatype attached ever...

You can use either of them . Both are same.

Both are same

truncate is a ddl...delete is dml
cannot rollback data in truncate...rollback data in delete
truncate -delete entire data not structure...it delete specific datas

By using delete we have to delete particular records only..
If we use truncate we cannot retrieve the data permanently...

In parameter means it will insert the values
OUT parameters means it will print out put (pass values to output). If we not assigning any value to out parameter it will take (print) NULL value.

INOUT means it take values as input and out put

1.There is no hard and fast rule about this. Whether to use INOUT or OUT depends upon the situation.

2. If not assigned anything to OUT parameter it retuns NULL value.

Both are same.

There is no difference as such, We use AS when Stored Procedure is defined in a package if SP is outside pkg we use IS.  Here is the exampleEx: ASPakcage Headercreate package schema.testpkg ...

The following are the main differences between nested tables and varray1. Nested tables are unbounded where as varray has maximun limit.2. Nested tables are dense intially but can be sparse latte...

Nested tables are unbounded. Initially dense but can become sparse through deletions. Order is not preservedCan be indexed  VARRAYs are always bounded(varying arrays have a limited number of entr...

There are five types of Triggers 1) Data Definition Language triggers: These triggers fire when you make changes to the objects in the database like create, update or delete. They can be implemented...

The function always return a SINGLE value, which includes arrays. Check out the table functions (pipelined), BULK_COLLECT, arrays etc... You can find plenty of examples. "plsql set...

ALL_OBJECTS: Objects accessible to the user.
USER_OBJECTS: Describes all objects owned by the current user.
DBA_OBJECTS: Describes all objects in the database.

The information available in data dictionary tables is very difficult to understand. So it provides views in a form that is easily  understood by users. All these views are owned by sys.Oracle provid...

Function  having a return type as Boolean ,can't be used in select statement.

In functions we can use all three types of parametershere is a simple exampleCREATE OR REPLACE FUNCTION TEST_FUNC(A IN NUMBER, B IN OUT NUMBER,C  OUT NUMBER)  RETURN NUMBER IS   &n...

Functions can have multiple out Parameters one with Return Parameter and others can be IN OUT Parameter to the function. this way a function can return multiple out parameters.Eg : Function ( a in num...

1.Functions are used to computer a value.2.Function can return more than one value using out parameter. But it is not a good practice to return more than one value in functions.3.Proced...

SP 1-It is a subprogram that perform an action. 2-It does not contain any return clause. 3-Can return none,one or many values. 4-Can contain a return statement. SF 1-Invoke as a part of expression....

1. A function is used to compute a value. A porucedure is used to execute business logic.2. A functin must return a value. A procedure may not return a value or may return more than one value using ou...

Generally to process large number of records in pl/sql we use cursors . But cursors process records sequentially. It increases number of context switches between SQL and PL/SQL there by hampers perfor...

Hi ,Bulk Binding is used for avoiding the context switching between the sql engine and pl/sql engine. If we use simple For loop in pl/sql block it will do context switching between sql and pl/sql engi...

Procedures Can have Parameters. But parameters is optional .i.e. we may defined procedures without parameters also.

It can take three types of parameters
IN , OUT and INOUT.

Yes, Procedures can have parameters.

A warning or error condition is called an exception. An exception may raise in a pl/sql block due to designing faults, coding mistakes or hardware failure. If an exception raises in a block ...

You can handle exception in two ways:– Trap it with a handler : If the exception is raised in the executable section of the block, processing branches to the corresponding exception handler...

Definining two or more procedures with same name is called overloading of procedures. But they must differ in no or order or datatype family of formal arguments.

Overloading: Multiple subprograms of the same name• Enables you to use the same name for different subprograms inside a PL/SQL block, a subprogram, or a package• Requires the formal paramete...

[CREATE [OR REPLACE]] PROCEDURE procedure_name[(Optional Parameters)] {IS | AS}
[PRAGMA AUTONOMOUS_TRANSACTION;]
[local declarations]
 BEGIN
executable statements
[EXCEPTION exception handlers]
END [name];

Create or replace procedure proc_name (optional parameters)
as

begin

executable statements;

exception handling

end;
end proc_name;

This is from Oracle PL/SQL DocumentationA procedure has two parts: the specification (spec for short) and the body.The procedure spec begins with the keyword PROCEDURE and ends with the procedurename ...

There Nothing like two parts of procedure..It is basically related to packages...
Specification of package and body of package

What is the difference between system procedure and system function?.

Also Procedure can be called from triggers but Function cannot be called from trigger. As function returns value. And no value can be returned to trigger.

Predefined exceptions  are globally declared in standard package

PL/SQL declares predefined exceptions in the STANDARD package.

OLD and NEW are two virtual tables available during database trigger execution.UPDATE statement has access to  both old and new values.INSERT  statement has access only to new...

The two virtual table available are old and new.

Below mentioned triggers can be specified on Table

1.ROW or STATEMENT

2.BEFORE or AFTER or INSTEAD OF Triggers

3.DML (INSERT, UPDATE, or DELETE on table or view ) or DDL (CREATE, ALTER, or DROP on schema objects which includes all DB objetcs)

4.COMPOUND TRIGGERS

Triggers can be classified based on the below factors -

(1) Trigger Event:
INSERT
UPDATE
DELETE

(2) Trigger Type:
STATEMENT Level
ROW Level

(3) Trigger Timings:
BEFORE
AFTER
INSTEAD OF

Trigger is data base object .Trigger is block of code it is executed automatically when dml operations are fired .

by using trigger we can do the auditing and perform a operation(modifications) on a tables (using dml operation)

A database trigger is a named pl/sql block associated with a table and fires automatically when an event occurs or something happens.

 Data auditing , Implementing complex business rules, security are main uses of database triggers.

After committing first oracle will commit the transaction into redo log file and then Data file. The data block will remain into the memory until the logical reads are going on. Oracle use LRU algorit...

The data will be written from redo log buffer into redo log files.

Cursor for loop is the one by using this we need not to perform open, close, fetch operations of a cursor..

If we use explicit cursor we need to open the cursor and fetching the data and close the cursor.

If we use cursor for loop cursor will open the cursor and fetching data and close the cursor automatically.

%ISOPEN - To check whether cursor is open or not.Returns true if cursor or cursor variable is open or not otherwise false.%FOUND - To check wheteher cursor is found or not. Returns true if fetch re...

There are five cursor attributes: %isopen, %found, %notfound, %rowcount and %bulk_rowcount.%isopenWith %isopen it is possible to test whether a cursor was opened: %found%found returns true when the la...

Cursors are of two types1. Implicit Cursors: -  Whenever we execute sql statements oracle server assigns a work area called private sql area to store precessed infomation. The most recently used ...

There r two types of cursors in the pl/sql1.Implici 2.explicit For the select stmt which is retrieving single record or all the dml operations in the pl/sql program system automatically allocates...

Cursor is an small memory allocation or workstation where operation or processing is done on the data.

Types of attribute of cursor are-:
1)ROWCOUNT
2)ISOPEN
3)FOUND
4)NOTFOUND

Oracle uses work areas called private SQL area to Execute Sql statements and   store  information. A cursor is a mechanism by which we can assign name to that private sql area , th...

row type is used to hold the total row values

%rowtype:- %rowtype means it is will fetch entire row values.

Type record: Type record means we can fetch record from more than one table.

%rowtype is associated entire table(if we want to declare all columns then we need to declare %rowtype).

%type is associated with one column .(if we want to declare entire column then we need to declare %type).

if using %type and %rowtype , no need to know the the data type of the table column . and once the development is finished , the type of column is changed it will not effect the coding.

No we can not use a scalar data type as return type.
The return value of a strongly typed REF CURSOR must be a record which can be defined using the %ROWTYPE and %TYPE attributes or as a record structure.