-
1)any one can tell me,suppose we have 1000 of records,ok.then we want to update only 500 records,how can we solve this problem?2)how many types of "explicit cursors" we have?
satyam_Ora
- May 8th, 2008
Sql> update set col_name='value' where rownum<=500;Note: rowid will not work instead of rownum for updating the 500 records.
-
Can we declare a column having number data type and its scale is larger than pricesionex: column_name NUMBER(10,100), column_name NUMBAER(10,-84)
satyam_Ora
- May 8th, 2008
SQL> create table a2(a number(10,100)); Table created. SQL> create table a3(a number(10,-84));Table created.
Oracle_learner
- Sep 20th, 2006
Yes ,such declaration is possible .Explaining with example .1.number(9,11) means there are 11 digits after decimal .However as the max precision is 9 so the rest are zero padded .Like 0.009999999992.n...
-
What is difference between PL/SQL tables and arrays?
krishnaindia2007
- May 8th, 2008
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 ...
jaswanthi.p
- Mar 8th, 2007
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.
-
State the advatage and disadvantage of Cursor's
krishnaindia2007
- May 7th, 2008
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 ...
Venkat9999
- Sep 22nd, 2006
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...
-
How to trace the errors in pl/sql block code..
krishnaindia2007
- May 7th, 2008
You can useUSER_ERRORSorSHOW ERRORSYou may also use DBMS_OUTPUT.PUT_LINE
pari
- Mar 14th, 2007
The correct command isSHOW ERRORS orSHOW ERR
-
What is difference between varray and nested table.can u explain in brief and clear my these concepts.also give a small and sweet example of both these.
krishnaindia2007
- May 7th, 2008
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...
g_sidhu
- Feb 18th, 2008
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...
-
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 ?
krishnaindia2007
- May 7th, 2008
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.
g_sidhu
- Jan 31st, 2008
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...
-
Give the structure of the procedure ?
PROCEDURE name (parameter list.....) is local variable declarations BEGIN Executable statements. Exception. exception handlers end;
krishnaindia2007
- May 6th, 2008
[CREATE [OR REPLACE]] PROCEDURE procedure_name[(Optional Parameters)] {IS | AS} [PRAGMA AUTONOMOUS_TRANSACTION;] [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];
KiranKW
- May 3rd, 2007
Create or replace procedure proc_name (optional parameters)as beginexecutable statements;exception handlingend;end proc_name;
-
What are the two parts of a procedure ?
Procedure Specification and Procedure Body.
krishnaindia2007
- May 6th, 2008
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 ...
salmankhan
- May 26th, 2007
There Nothing like two parts of procedure..It is basically related to packages...Specification of package and body of package
-
Where the Pre_defined_exceptions are stored ?
In the standard package. Procedures, Functions & Packages ;
-
What are two virtual tables available during database trigger execution ?
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.
krishnaindia2007
- May 6th, 2008
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...
-
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.
krishnaindia2007
- May 6th, 2008
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 ...
Lavanya Chowdary
- May 6th, 2007
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...
-
In function and procedure the parameter pass is "call by value" or "call by reference"?
krishnaindia2007
- May 3rd, 2008
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...
ashwinee2
- Apr 11th, 2008
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.
-
What is diffrence between IS and AS in procedure?
-
Mention the differences between aggregate functions and analytical functions clearly with examples?
krishnaindia2007
- May 3rd, 2008
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.
dev
- Dec 19th, 2006
Thank u Rampratap.Dev
-
What are mutating tables?
When a table is in state of transition it is said to be mutating. eg :: If a row has been deleted then the table is said to be mutating and no operations can be done on the table except select.
krishnaindia2007
- May 3rd, 2008
Mutate means chage. Mutating table is a table that is being modified by update, delete or insert statement.
-
How to create a constraint for a tablecolumn which is already created
krishnaindia2007
- May 3rd, 2008
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...
-
Package and Module
What is the difference between a package and a module?
-
What is the difference between granting and creating a view?
amahanit
- Dec 1st, 2007
grand is to provide privilege on a specific object to a usereg. grand select on emp to uma;well view is a database object which is made from column of one or more tables.if u don't have ...
-
Nested and Normal Table
What is the difference between Nested Table and Normal Table?
garvi.1983
- Mar 5th, 2008
Normal Table : It contain rows and columns.Each Row-Column represente only one value i.e intersection of Row-Columncreate table t1(col1 number(10), col2 varchar2(9));------>Creates normal tabl...
PL/SQL Interview Questions
Ans