-
Example for calling procedure
Give an example for calling procedure with user and system exception
krishnaindia2007
- May 29th, 2008
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 ...
ravenkumar
- Mar 17th, 2008
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 &...
-
PL/SQL Block output
begin For i in 1..5 loop insert into A values(i); savepoint 1; end loop; rollback to savepoint 1; commitend;--initially there are no data in table A. So my question is after execution of this block what should be the data present in table A?
krishnaindia2007
- May 29th, 2008
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...
satyam_Ora
- May 6th, 2008
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...
-
How the execution will be done in exceptions?
ashishdixit
- May 28th, 2008
Exception is equivalent of an error in the code.This may be due to incorrect data or wrong coding techniques. What ever the reason may be,when an error occurs in the plsql code, the command terminates...
praveen.pinisetti
- Apr 6th, 2006
There is a lot to explain the execution of exceptions.Whenever error is occured,the corresponding exception will be raised and handled in Exception block.But in the case of nested blocks,The exception...
-
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 :
Srinatha Reddy
- May 21st, 2008
create table a ( b number, c number check (c >100) );create or replace trigger t1 before insert on a begindbms_output.put_line('this is before insert trigger'); end;create or replace trigge...
prashant
- Jul 6th, 2006
ii. done using Integarity Constraints
-
What is the difference between private packages and public package . what is the difference in declaration of these 2 packages.
mosam
- May 21st, 2008
U can not create thr private packages in oracle becoze packages can not be nasted
KiranKW
- May 3rd, 2007
hi , i dont think there is something called as public package / private package. But yes the methods defined in a package can either be public or private.Below is a simple expln.Variables and co...
-
What are the components of a PL/SQL Block ?
Declarative part, Executable part and Exception part. Datatypes PL/SQL
satyam_Ora
- May 14th, 2008
All has given the right answer and I am repeating the same.There are the three component of PL/SQL program block1) Declarative( Where you can decalre the variable which would be used as a part of exce...
g_sidhu
- Feb 16th, 2008
A PL/SQL block consists of up to three sections: declarative (optional), executable (required), and exception handling (optional).
-
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;
satyam_Ora
- May 8th, 2008
Please remove the commit from the begin statement and the it will execute properly.Thanks,
satyam_Ora
- May 8th, 2008
DeclareCursor c1 is select * from emp FORUPDATE;Z c1%rowtype;BeginOpen C1;Fetch c1 into Z;Commit;Fetch c1 in to Z;end; SQL> start e1 Declare * ERROR at line 1: ORA-01002: fetch out of sequence...
-
How do you encrypt the function to prevent accessing from users without specific permission. ?
krishnaindia2007
- May 13th, 2008
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...
nitingalande
- Jan 13th, 2008
Function can be encrypted using Oracle Wrap utility. this will prevent accessing from users without permission
-
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...
PL/SQL Interview Questions
Ans