GeekInterview.com
Series: Subject: Topic:

PL/SQL Interview Questions

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

What is meant by instead of cursor?

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

Answered by: Kumar on: Jul 25th, 2014

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

How to reverse string without using string function?

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

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.  

Answered by: anandkiran on: Sep 10th, 2013

Use simple decremented for loop with an variable

Code
  1.             String x = "";
  2.             String s = "test";
  3.             for (int i = s.Length - 1; i >= 0; i--)
  4.             {
  5.  
  6.                 x = x + s[i];
  7.             }
  8.  Console.WriteLine(x);

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

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

Answered by: Pradipta on: Feb 7th, 2014

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

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

Can we insert multiple nulls through unique constraints ?

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

Answered by: siva on: Jul 7th, 2014

By using single bit mechanism 1 null is big then 2nd null......

Answered by: Dharmendra Patel on: Jul 20th, 2012

Yes we can insert multiple nulls as each nulls have different ROWID

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

Debugging PL/SQL programs

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

How can we debug stored procedures in PL/SQL?

Answered by: prasadkrish on: Jun 10th, 2014

In procedure function can be able to use ?if yes means give example

Answered by: ram on: Jul 31st, 2013

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

What is the main purpose of return in functions?

Asked By: sudnic | Asked On: Apr 10th, 2012

What is the main purpose of return in functions when we can calculate a value and pass it through using out mode in procedure or functions?

Answered by: Deepika S Verma on: Mar 14th, 2014

Its a myth that function returns value whereas procedure does not, actually procedure also return value (i.e. 0 or 1 ) at the time of exiting which indicates the success or error. Whereas Function ret...

Answered by: shivigupta on: Oct 9th, 2012

create or replace procedure proc1 as begin dbms_output.put_line(Before Return Clause); return; dbms_output.put_line(After Return Clause); end; set serveroutput on; begin proc1; dbms_output.put_line(I...

Flashback concept ? What is it?

Asked By: balanagi | Asked On: Apr 11th, 2013

Can any one help me on flashback concept ? What is it?

Answered by: Deepika S Verma on: Mar 13th, 2014

Its a feature introduced in oracle9i where oracle DB allows user to view the content of table quickly and easily the way it was at a particular time in the past, even when it is modified and committed...

Answered by: NAVEEN on: Mar 2nd, 2014

Execute dbms_flashback.enable_at_time(sysdate-5/1440);(for 5 minutes flash back )

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 Table1.ROW or STATEMENT 2.BEFORE or AFTER or INSTEAD OF Triggers3.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.

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

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.