GeekInterview.com
Series: Subject: Topic:

PL/SQL Interview Questions

Showing Questions 1 - 20 of 290 Questions
First | Prev | | Next | Last Page
Sort by: 
 | 

What is PL/SQL tables?Is cursor variable store in PL/SQL table?

Asked By: Dolly | Asked On: Apr 19th, 2006

Answered by: Pixie on: Feb 18th, 2014

PL/SQL tables are PL/SQL’s way of providing arrays. They are temporary tables in memory and thus are processed very quickly. They are not database tables, and DML statements cannot be issued against...

Answered by: sbagai2001 on: May 30th, 2006

DECLARE   TYPE EmpCurTyp IS REF CURSOR;   // Reference Cursor - weak    TYPE NumList IS TABLE OF NUMBER;  // Table of Number   TYPE NameList IS TABLE OF VA...

How many types of database triggers can be specified on a table ? What are they ?

Asked By: Interview Candidate | Asked On: Sep 9th, 2004

                insert         update          delete before  row    o.K.          ...

Star Read Best Answer

Editorial / Best Answer

Answered by: krishnaindia2007

View all answers by krishnaindia2007

Member Since Sep-2007 | Answered On : May 6th, 2008

A trigger may be a
1. DML Trigger on tables
2. Instead of triggers on views
3. System triggers on database or schema

Based on the way it executes statements  triggers are of two types
1. Statement leve trigger
2.  Row level trigger

A trigger fires for three actions
1. Insert
2. Delete 
3.Update

and the trigger can the fired
1. Before action
2. After action.

Answered by: tarini sankar das on: Feb 7th, 2014

Actual answer is we have 14 types of triggers in pl sql. For statement level :- 1-before insert 2-before update 3-before delete 4-after insert 5-after update 6-after delete For row level :- 7-before ...

Answered by: sandip.dhopat on: Jun 14th, 2011

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

Bind variabales

Asked By: kamesh | Asked On: Dec 16th, 2013

What are bind variables in SQL? Explain in detail?

Answered by: pradipta on: Feb 8th, 2014

The way to get Oracle to reuse the execution plans for "the statements having different hard coded value in where clause" there should use bind variables. Bind variables are «substitution» variable...

Answered by: Aarti on: Jan 15th, 2014

Bind variable is session specific variable. It is declared using variable keyword. Bind variable cant be used in named block. It cant have default value.

Query to retrieve one entire column data

Asked By: shankar | Asked On: Jul 24th, 2013

Write a query to retrieve one entire column data of multiple rows into one single column of single row?

Answered by: Pradipta on: Feb 7th, 2014

SELECT DBMS_LOB.SUBSTR(WM_CONCAT(Ename),3000) FROM Scott.Emp
ORDER BY Ename;

Answered by: oraclewx on: Jan 24th, 2014

use LISGAGG() function

What are the components of a PL/SQL block ?

Asked By: Interview Candidate | Asked On: Sep 9th, 2004

 a set of related declarations and procedural statements is called block.

Star Read Best Answer

Editorial / Best Answer

Answered by: Chandra Shekhar

Answered On : Oct 3rd, 2005

PL/SQL Block contains :

Declare : optional

Variable declaration

Begin  : Manadatory

Procedural statements.

Exception : Optional

any errors to be trapped

End :  Mandatory

So only begin and end keywords are required, as needed, to process a pl/sql block.

Answered by: saravanan on: Nov 29th, 2013

NIL

Answered by: prakashraj.v on: Jul 14th, 2008

Component of PL/SQL Block are Declare(Optional), Begin(Mandatory), Exception(Optional), END(Mandatory).

How can we insert into a table by fetching data from more then one table by using only single command

Asked By: Nitin kumar | Asked On: Jun 10th, 2013

I have 3 tables , same no of columns ,i want to insert whole the data from all these table into a single table,which also having same no of column and the same data type.

Answered by: J. Ali (PL/Sql) on: Nov 29th, 2013

INSERT INTO DYMMY (VAL)
SELECT COLUMN_VALUE val FROM table (tab_contents_nt (1, 2, 3))
UNION ALL
SELECT COLUMN_VALUE val FROM table (tab_contents_nt (4, 5, 6))
UNION ALL
SELECT COLUMN_VALUE val FROM table (tab_contents_nt (7, 8, 9))

Answered by: tinku981 on: Jun 16th, 2013

By using Insert and Select you will be able to achieve required results.

Reference cursor from a procedure & pass cursor as a paramater

Asked By: plsqlgeek | Asked On: Aug 16th, 2013

How to call/reference a cursor defined in a procedure a from another procedure b? how to pass a cursor as a parameter of a cursor?

Answered by: UTTAM GHOSH on: Oct 31st, 2013

How I can an Ac dynamo of a car/vehicle test with a12v dc battery good or bad

Answered by: Pavan on: Aug 27th, 2013

This can be done by using REFCURSORS.

How many number of triggers can be created on a table?

Asked By: vivek | Asked On: Mar 8th, 2006

Answered by: saravana kumar (PMS_SARA) on: Oct 11th, 2013

You can create n number of triggers, try the below trigger on a same table by giving different names with different when conditions.

Code
  1.  
  2. CREATE OR REPLACE TRIGGER trg_test_null_8
  3. before INSERT ON test_null
  4. FOR each ROW
  5. WHEN (NEW.a = 8)
  6.  
  7.   NULL;
  8.  

Answered by: AMANDEEP SINGH on: Jul 13th, 2013

There are total 28 possible types:
(3 statements + 4 combination statements) * 2 timing * 2 levels=28

Collections

Asked By: sdas84jobs | Asked On: Oct 6th, 2010

1-what is diff between varray and table?2-what is SQL support for nested table?3-what is inline storage and out of line stogare of the nested table?4-what are common exception related with collection?5-can u declare varray of varray of varray of table?6-give sample defination of varray of object and...

Answered by: subhashishchaki on: Oct 7th, 2013

Collections similarities and differences Collection Type Elements Limit Subscript Type Uninitialized Status Always Dense ----------------------------------------------------------...

Answered by: basukolur014 on: Feb 28th, 2011

exceptions related to collections are...

1.collection_is_null

2.collection subscript beyond count

3.collection subscript outside limit

4.no_data_found .

these are more common exceptions  related to collections.

How to update a rows with opposite gender in SQL?

Asked By: raiyaans | Asked On: Jun 14th, 2013

In emp table there is a column which consists of gender (male or female) ? How should one can update the values of male to female and female to male with a single query ?

Answered by: samy on: Oct 2nd, 2013

Decode(gender,M,F,F,M)

Answered by: tinku981 on: Jun 16th, 2013

Using decode should help! I am not able to paste the query, otherwise would have shown the example.

What is difference between % rowtype and type record ?

Asked By: Interview Candidate | Asked On: Sep 9th, 2004

 %  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 differenttable or views and variables.      e.G.  type  r_emp is record (eno emp.Empno%...

Answered by: Ram Seshadri on: Sep 20th, 2013

%rowtype holds all the rows of a table while you are fetching the rows using a cursor where as the type_rec object can hold the specific columns based on whatever the columns are defined in the type_record variable.

Answered by: RReid on: Sep 19th, 2013

%ROWTYPE can be used to declare a record of all columns in a table. They are the same.

Reference table from inside a procedure

Asked By: plsqlgeek | Asked On: Aug 16th, 2013

How do you reference a (updated) table from procedure a which is being updated by another procedure b?

Answered by: Lalit on: Sep 15th, 2013

By taking help of global temporary table.

Where would you use implicit & explicit cursors?

Asked By: sai | Asked On: Nov 24th, 2011

Answered by: Malar somu on: Sep 10th, 2013

Implicit Cursor:- It is Oracle implicit type.. when we fetch only one record then we need to use implicit cursor i.e when ever we performing select query or any dml operations termed as implicit curso...

Answered by: hira on: Aug 8th, 2013

When ever any query execute by the server in database sever itself create a implicit cursor ,which can not any control to the programmer but in case explicit cursor the programmer has to create it.and used for the data manipulation by following some rule......

PL/SQL table vs GLobal temporary table

Asked By: Sukanta2013 | Asked On: Aug 14th, 2013

Difference between PL/SQL table and GLobal temporary table. Why we use GLobal temporary table without using PL/SQL table.

Answered by: nivi on: Sep 3rd, 2013

1.Global Temporary table is stored in Temporary tablespace while PL/SQL table is stored in PGA. 2.SQL operations can not be performed on pl/sql tables whereas it can be performed on GTT as any normal...

Answered by: plsqlgeek on: Aug 16th, 2013

PL/SQL table or associated array is a collection (data type). Where as GTT is a temp table created to store/process data for a particular session. Once you log out of the session the data is gone, not the table structure.

What happens to the base table transactions when procedure has some error?

Asked By: plsqlgeek | Asked On: Aug 16th, 2013

Procedure a calls procedure b. A updates table t1, t2. B updates table t3. If some error happens to b what happens to the updates in all those tables? Whether they will rollback or commit? If commits how far it will commit?

Answered by: vishal.bhadange on: Aug 24th, 2013

CASE 1 (If you are not handling exception in Procedure B, none of the table gets updated) "sql PROCEDURE A BEGIN UPDATE TABLE t1; UPDATE TABLE t2; CALL PROCEDURE ...

Debugging PL/SQL programs

Asked By: sidd_130 | Asked On: Oct 6th, 2012

How can we debug stored procedures in PL/SQL?

Answered by: ram on: Jul 31st, 2013

can any one tell how to retrieve more than one table structure at a time?

Answered by: Ali on: Apr 9th, 2013

use following dbms package to get error line no in oracle 11g:

dbms_utility.format_error_backtrace

How to return more than one value from a function?What are the types of triggers?What are the features of Oracle 9i

Asked By: aseemnaithani | Asked On: Mar 15th, 2006

Answered by: anjan on: Jul 31st, 2013

if u create a object , and use this type to return type, then you return multiple value.

Answered by: hitesh on: Jul 29th, 2013

before , after, intead of

Overloading procedure - all parameters are default

Asked By: tinku981 | Asked On: Jun 14th, 2013

If there are 2 overloaded procedure, one among then have 1 in parameter and another have 2 parameters. Both procedures parameters are of default type. what will happen when you will call package.Procedure without any parameter?

Answered by: Khamar on: Jul 22nd, 2013

You will get an error message "PLS-0037: too many declarations of procedure_name match this call"

Why cant we use commit inside a trigger?

Asked By: Nitin kumar | Asked On: Jun 11th, 2013

Answered by: Varun Tiwari on: Jul 4th, 2013

Any data transaction control statement can not be used in triggers. As trigger invokes due to any DML event and the transaction may not be successfully commit later and may need to complete rollback.

Pragma_autonomous can be used as an alternate way to commit.

Answered by: prashant mhatre on: Jun 13th, 2013

can not used ...if we want to use it inside the trigger then use pragma_autonomous.

Can we use function inside a trigger

Asked By: Nitin kumar | Asked On: Jun 11th, 2013

Is it possible to use function inside a trigger.

Answered by: tinku981 on: Jun 16th, 2013

Code
  1. CREATE OR REPLACE TRIGGER emp_trig
  2.   before INSERT ON emp
  3.   FOR each row
  4. --referencing old as old and new as new
  5. declare
  6.   i pls_integer;
  7. begin
  8.   emp_pro;
  9.   i := emp_func;
  10.   dbms_output.put_line(TRIGGER called AND FUNCTION returned  || i);
  11.  
  12. end;
  13.  

Answered by: tinku981 on: Jun 16th, 2013

Yes, by using CALL statement.

First | Prev | | Next | Last Page

 

 

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.