GeekInterview.com
Series: Subject: Topic:

PL/SQL Interview Questions

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

What are Oracle hints and how do you use them ?

Asked By: Jaikumar D V | Asked On: Nov 24th, 2011

Explain the real-time situation where would you use Oracle hits

Answered by: kalpana on: Oct 28th, 2014

Optimizer hints are used with SQL statements to alter execution plans.We are suggesting oracle how the statement should be executed.
syntax : /*+RULE */

Answered by: Lokesh M on: Dec 7th, 2011

Oracle optimizer is responsible for handling different situations by making appropriate decisions based on database statistics. It will create route path that is most appropriate to execute the code. ...

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: sateeshchandra on: Oct 14th, 2014

"sql INSERT ALL INTO suppliers (supplier_id, supplier_name) VALUES (1000, IBM) INTO suppliers (supplier_id, supplier_name) VALUES (2000, Microsoft) INTO customers (customer_i...

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))

Web testing

Asked By: Ramu.B | Asked On: Oct 24th, 2012

While testing a website, an image that is supposed to appear on a page does not load (for example in ie, you would see an empty box with a small red cross icon). What type of investigation could you carry out to find out what the cause of the problem is? What information would you include when raising...

Answered by: harmeet rehal on: Oct 13th, 2014

Web testing is the name given to software testing that focuses on web applications. Complete testing of a web-based system before going live can help address issues before the system is revealed to th...

Recursive function

Asked By: manju_kambar | Asked On: Sep 12th, 2014

What is recursive function, explain with example?

Answered by: Vishant Gupta on: Oct 11th, 2014

It is function that calls itself to implement some repetitive task

Answered by: VIVEK on: Sep 24th, 2014

Function is calling the same function

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: Subalakshmi R on: Sep 24th, 2014

There should be some additional condition to be applied against another column apart from the gender column. otherwise, it is not possible.

Answered by: Dinesh on: Sep 12th, 2014

Here the question is how to update the Table not how you show the table

How to reverse string without using string function?

Asked By: vinit0180 | Asked On: May 9th, 2013

Answered by: harsh on: Sep 22nd, 2014

Answer:"plsql reverse a string in oracle: DECLARE V_string VARCHAR2(10):=SRIRAM; V_reverse VARCHAR2(10):=null; V_string1 VARCHAR2(10); v_count NUMBER; BEGIN v_count:=length(V_stri...

Answered by: PANKAJ on: Jul 23rd, 2014

Code
  1. #include<iostream>
  2. #include<conio.h>
  3. using namespace std;
  4. int main()
  5. {
  6.     int i;
  7.     char *name="pankaj";
  8.     for(i=0;*(name+i)!=NULL;i++)
  9.     {
  10.  
  11.     }
  12.     for(int j=i-1;j>=0;j--)
  13.     {
  14.         cout<<*(name+j);
  15.     }
  16.     return 0;
  17. }
  18.  

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: RanitB on: Sep 19th, 2014

WM_CONCAT: should not be used in production code as it is not documented by Oracle documentation.

This technique is called as String Aggregation. Use functions like - LISTAGG (>11g), XMLAGG, etc.

Check this - http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php

Answered by: Prem on: Jul 7th, 2014

Using cursor

Code
  1.  V_EName VARCHAR2(100);
  2.  FOR ECur IN (SELECT EName FROM Emp)
  3.   V_EName := V_EName|| ||ECur.EName;
  4.  DBMS_OUTPUT.PUT_LINE(V_EName);

Function purity

Asked By: manju_kambar | Asked On: Sep 12th, 2014

What is function purity level and why we need it in PL/SQL function?

Answered by: chaitanya on: Sep 19th, 2014

Any function called from an sql query will have certain side effects on database state and package variables..these side effects are classified as purity levels. there are different purity levels like 1. read no database package state 2. write no database package state e.t.c

Call external procedure

Asked By: sk.mca6 | Asked On: Feb 17th, 2011

How to call an external-procedure from a package?

Answered by: manju_kambar on: Sep 12th, 2014

Calling procedure from other package should have following format

package_name.procedure_name;

if you are calling package in other schema, you need to create synonyms for that particular

Can we insert multiple nulls through unique constraints ?

Asked By: sachinkshd | Asked On: Jul 17th, 2012

Answered by: sapna on: Sep 12th, 2014

Yes

Answered by: Ashok on: Sep 4th, 2014

We can insert multiple nulls , as one null value is not equal to another null value.

What is meant by instead of cursor?

Asked By: Nareshbtd3 | Asked On: Jul 10th, 2014

Answered by: Raviteja on: Aug 23rd, 2014

Instead of cursor is not there,, instead of trigger is used incase of complex views. when we use views with joins

Answered by: Kumar on: Jul 25th, 2014

Its instead of trigger not cursor and it is used to apply trigger on views.

Cursor advantages and disadvantages

Asked By: sri3241 | Asked On: Jul 27th, 2009

What are the uses of cursor? what are its advantage and disadvantage?

Answered by: Phani Kumar on: Jul 24th, 2014

Dis Advantage of Cursors A cursor is a memory resident set of pointers -- meaning it occupies memory from your system that may be available for other processes. Poorly written cursors can completely ...

Answered by: Balakrishna12207 on: Feb 2nd, 2011

Cursor advantages:--> Using cursor to getting multiple values.--> One cursor have multiple select statements--> Cursor parameters        i.e cursor cursor_name (param...

What is difference between a procedure & function ?

Asked By: Interview Candidate | Asked On: Sep 2nd, 2005

 a function is always returns a value using the return statement.        a  procedure   may  return one or more values through parameters or may not return at all.

Star Read Best Answer

Editorial / Best Answer

Answered by: krishnaindia2007

View all answers by krishnaindia2007

Member Since Sep-2007 | Answered On : May 3rd, 2008

1. Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter.
 
2. Function can be called from SQL statements where as procedure can not be called from the sql statements

3. Functions are normally used for computations where as procedures are normally used for executing business logic.

4. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.

5. Function returns 1 value only. Procedure can return multiple values (max 1024).

6.Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution.

7.Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values

8. Stored procedure is precompiled execution plan where as functions are not.
 
9.A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller.

Answered by: Shalini on: Jul 23rd, 2014

function can be called from trigger. 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.begi...

Answered by: Md Jamshaid on: Dec 26th, 2012

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

Can we write commit or rollback in trigger ?

Asked By: sachinkshd | Asked On: Jul 17th, 2012

Answered by: Rakesh057 on: Jul 19th, 2014

NO.. triggers are auto commit no need of commit or rollback

Answered by: Rpradeep on: Jul 7th, 2014

Yes we can use commit or rollback statements in trigger by using AUTONOMOUS_TRANSACTION pragma.

Delete duplicate records without using sub query?

Asked By: Rakesh057 | Asked On: Jul 1st, 2014

Delete duplicate records without using sub query? Is that possible

Answered by: Raud Premananda on: Jul 7th, 2014

USING "ROWID" PSEUDO COLUMN YOU CAN DELETE DUPLICATE RECORDS

Char(20) = 'name' varchar2(20)='name' when comparing these two values, are the spaces padded in char are considered or not? Are both values equal?

Asked By: ricky | Asked On: Sep 12th, 2007

Answered by: Rpradeep on: Jul 7th, 2014

Yes but small difference
if we use char (20) it will padd the blank space
where as varchar 2(20) it will not padd blank space..

Answered by: javedans on: Jul 8th, 2009

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

What is Oracle 11g PL/SQL best feature?

Asked By: Shashank Gangimalla | Asked On: May 12th, 2011

Answered by: bhanu priya on: Jul 4th, 2014

1. function result cache : stores the retured value in a cache and can b used again n again instead of storing it in memory.
2. continue statement : passes controle from one iteration to other rather than exiting it.
3.regexp.count is introduced.
4.dbms_parallel_execute is introduced.

Why we have to perform calculation part in functions?

Asked By: Rakesh057 | Asked On: Jun 26th, 2014

Procedure also written the values using out parameter than why we need functions?

Answered by: Rakesh057 on: Jul 1st, 2014

Because procedure is pre compiler and function is not pre compiler so that the reason we go for an calculation part in function

Answered by: Madhav on: Jul 1st, 2014

Procedure also written in out parameter but we can not use procedure in Sql statement.

But if we wrote function and ita cant contain DML operation then we can access this function in SQL queries.

When use before and after trigger?

Asked By: Rakesh057 | Asked On: Jun 30th, 2014

When use before and after trigger? Please any help for this one

Answered by: chaithanya on: Jul 1st, 2014

Before trigger is used while performing DML operations and whereas After Trigger is used for after performing DML operations Real time Example for before trigger: in banking systems: ...

Answered by: Mrutyunjay Nayak on: Jul 1st, 2014

Before Trigger is used only when u want to restrict the invalid data entry in a table.After trigger is used when u perform the operation in one table that will effect on other table.

How to avoid using cursors? What to use instead of cursor and in what cases to do so?

Asked By: moviefan456 | Asked On: Aug 9th, 2006

Answered by: Ashish on: Jun 12th, 2014

You can use MERGE statements in Oracle to avoid looping through Conventional cursors. It will save lot of time

Answered by: KSPRADEEP on: Oct 17th, 2012

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

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.