GeekInterview.com
Series: Subject: Topic:

PL/SQL Interview Questions

Showing Questions 141 - 160 of 290 Questions
First | Prev | | Next | Last Page
Sort by: 
 | 

GLobal temporary table

Asked By: ravishing_sid | Asked On: Apr 21st, 2008

What is GLobal temporary table? And what are the benefits of it?

Answered by: vanishavadlya on: Oct 24th, 2009

The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for t...

Answered by: taya_ronak on: Nov 3rd, 2008

Global temporary table is kind of temporary table managed by system. it'll keep in temporary tablespace till the session remain. there are two methods available i.e.1) create global temporary table t1...

What can be the maximum size of a PL/SQL block?

Asked By: Natesh | Asked On: Jul 18th, 2006

Answered by: jabir.mkk on: Oct 16th, 2009

The maximum parsed/compiled size of a PL/SQL block is 64K and the maximum code size is 100K. You can run the following select statement to query the size of an existing package or procedure. SQL> select * from dba_object_size where name = 'procedure_name'

Answered by: malik.aman on: Dec 1st, 2007

select * from dba_object_size where name = 'procedure_name';

What is the difference between all_ and user_ tables ?

Asked By: Shweta_faqs | Asked On: Feb 15th, 2006

Answered by: pawan_kr2007 on: Oct 11th, 2009

ALL_OBJECTS: Objects accessible to the user.
USER_OBJECTS: Describes all objects owned by the current user.
DBA_OBJECTS: Describes all objects in the database.

Answered by: krishnaindia2007 on: May 7th, 2008

The information available in data dictionary tables is very difficult to understand. So it provides views in a form that is easily  understood by users. All these views are owned by sys.Oracle provid...

Oracle extract records from temporary table

Asked By: Ramesh Venkatesan | Asked On: Oct 29th, 2007

Assume that I am using a temporary table in a procedure and I am inserting records and updating another set of records through merge statement. If I use cursor in that temporary table, how can I extract all of the records from temporary table at the end of the stored procedure. Can you please give coding...

Answered by: johnjerry on: Oct 7th, 2009

Make sure you do not COMMIT* in the middle of the procedure. You can do any number of insertion into and deletion from temporary table and use "Select * from temp_table" to fetch a...

Answered by: priyanka jolly on: Nov 24th, 2007

Create Global temporary tables and say on commit preserve rows. The global temporary tables have data available till the session is active. So you if you insert it at one procedure and want to access it somewhere else you can do that very easily till the session is active

Thanks
Priyanka

Referenced and dependent objects

Asked By: KD09714 | Asked On: Jun 8th, 2008

How Oracle manages dependency between referenced and dependent objects ?

Answered by: johnjerry on: Oct 7th, 2009

We can set REMOTE_DEPENDENCIES_MODE inside init.ora parameter file to TIMESTAMP or SIGNATURE.

ALTER SYSTEM SET REMOTE_DEPENDENCIES_MODE = [TIMESTAMP|SIGNATURE]

Answered by: YugundharK on: Oct 13th, 2008

Oracle manages dependency between referenced and dependent objects by using 2 modes
1. Timestamp mode
2. Signature mode

What is a cursor ? Why cursor is required ?

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

 cursor is a named priVATe SQL area from where information can be accessed. Cursors  are  required  to  process rows individually for queries returning multiple rows.

Answered by: rahulg5211 on: Oct 4th, 2009

Cursor is an small memory allocation or workstation where operation or processing is done on the data.

Types of attribute of cursor are-:
1)ROWCOUNT
2)ISOPEN
3)FOUND
4)NOTFOUND

Answered by: krishnaindia2007 on: May 6th, 2008

Oracle uses work areas called private SQL area to Execute Sql statements and   store  information. A cursor is a mechanism by which we can assign name to that private sql area , th...

 what is PL/SQL ?

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

PL/SQL  is  a  procedural  language  that  has  both  interactive  SQL  and procedural  programming  language constructs such as iteration, conditional branching.

Answered by: surya72446 on: Sep 30th, 2009

PL/SQL stands for procedural languages extensions to SQL and it's theme is to hold a bunch of SQL statements as a single function and return a single output.PL/SQL is advantageous compared to other pr...

Answered by: g_sidhu on: Feb 16th, 2008

PL/SQL is the procedural extension to SQL with design features of programming languages. Data manipulation and query statements of SQL are included within procedural units of code. PL/SQL offers moder...

Indexed select statement

Asked By: bmsrao | Asked On: Aug 18th, 2008

How can we find the select statement is indexed or not?

Star Read Best Answer

Editorial / Best Answer

Answered by: promisinganuj

View all answers by promisinganuj

Member Since Sep-2009 | Answered On : Sep 13th, 2009

1. using AUTOTRACE
SQL> SET AUTOTRACE ON TRACEONLY
SELECT *
    FROM emp
 WHERE emp_name = 'ABC';

2. Using EXPLAIN PLAN
SQL> EXPLAIN PLAN for
SELECT *
    FROM emp
 WHERE emp_name = 'ABC';

The advantage of using EXPLAIN PLAN over the AUTOTRACE is that former does not require the query to be actually run. The TRACEONLY option in AUTOTRACE just supress the query ouput but the query still runs before the explain plan is displayed.

Answered by: promisinganuj on: Sep 13th, 2009

1. using AUTOTRACESQL> SET AUTOTRACE ON TRACEONLYSELECT *    FROM emp WHERE emp_name = 'ABC';2. Using EXPLAIN PLANSQL> EXPLAIN PLAN forSELECT *    FROM...

Answered by: praveen_geek123 on: Sep 4th, 2008

Use "Explain Plan" command

Retriving multiple records

Asked By: bmsrao | Asked On: Aug 17th, 2008

Why PL/SQL does not support retriving multiple records?

Star Read Best Answer

Editorial / Best Answer

Answered by: promisinganuj

View all answers by promisinganuj

Member Since Sep-2009 | Answered On : Sep 13th, 2009

Retriving multiple records is a basic feature of SQL where we fetch the data using SELECT statement.
SELECT *
  FROM emp;
This itself gives us multiple records. There is no additional feature required to do the same in PL/SQL.
At the same time, PL/SQL goes one step further and enable us to process these fetched rows one by one (unlike the update statements which updates all the rows selected).
There are several options for fethcing data in different PL/SQL constructs:
1. Using Ordinary Cursor
2. Using Ref Cursor
3. Using PL/SQL bulk collect
4. Using PL/SQL arrays

But the important thing here is that irrespective of the way we fetch the data, we act upon them row-by-row.

Answered by: promisinganuj on: Sep 13th, 2009

Retriving multiple records is a basic feature of SQL where we fetch the data using SELECT statement.SELECT *  FROM emp;This itself gives us multiple records. There is no additional feature requir...

Answered by: Mad Hatter on: Nov 4th, 2008

Multiple records at a time could be retreved in PL/SQL using BULK COLLECT.To do that you define PL/SQL table, and load it using SELECT ... BULK COLLECT INTO <pl/sql table>FROM ..Optionaly could ...

What are the file utilit comands used in PL/SQL procedures?

Asked By: kartheek | Asked On: Apr 19th, 2007

Answered by: samareshp on: Aug 3rd, 2009

UTL_FILEThe Oracle supplied package UTL_FILE can be used to read and write files that are located on the server. It cannot be used to access files locally, that is on the computer where the client is ...

Answered by: g_sidhu on: Feb 5th, 2008

With the Oracle-supplied UTL_FILE package, you can read from and write to operating system files. It Provides security for directories on the server through the init.ora file. UTL_FILE Procedures and ...

What is the difference between using is and as while creating a procedure, function package and package body?

Asked By: Rohith | Asked On: Jul 12th, 2007

Answered by: Lakshmi84 on: Aug 4th, 2009

There is no difference btwn IS and AS while creating procedure,function,package

Answered by: samareshp on: Aug 3rd, 2009

Don't think there is any difference

What are the cursor attributes used in PL/SQL ?

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

 %isopen  - to check whether cursor is open or not   % rowcount - number of rows fetched/updated/deleted.   %  found - to check whether cursor has fetched any row. True if rows are fetched.     % not found - to check whether cursor has fetched any...

Answered by: Lakshmi84 on: Jul 31st, 2009

%ISOPEN - To check whether cursor is open or not.Returns true if cursor or cursor variable is open or not otherwise false.%FOUND - To check wheteher cursor is found or not. Returns true if fetch re...

Answered by: samareshp on: Jul 30th, 2009

There are five cursor attributes: %isopen, %found, %notfound, %rowcount and %bulk_rowcount.%isopenWith %isopen it is possible to test whether a cursor was opened: %found%found returns true when the la...

Function return more than one value

Asked By: satyam_Ora | Asked On: May 14th, 2008

Hi,my questions is " can function return more than one value".I have gone through the all answers relevant to my question was been posted earlier. But all answers are confusing me.Few people said directly that function can't return more than one value and few are telling that function can return more...

Answered by: sen_sam86 on: Jul 23rd, 2009

Yes this can be possible by TABLE FUNCTION STEP - 1    CREATE TYPE ex_tabl_fun_obj AS OBJECT (emp_name varchar2(20), emp_id NUMBER(2));STEP - 2     CREATE TYPE ex_tabl_fu...

Answered by: javedans on: Jul 8th, 2009

Its a universal truth that function can return only one value, but there are some tricks by using that you can get more than one value , while function returning only one value.When you define functio...

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

Answered by: mosam on: May 20th, 2008

ans is both values are not equal  char(20)='name' covered in memorey areea is 20 but varchar2(20)='name' covered in memorey areea is 4 becoze char is fixed lenth char data type but varchar2 is variable lenth char data type

What is p-code and sourcecode ?

Asked By: Naresh | Asked On: Mar 3rd, 2006

Answered by: grajeshkumar82 on: Jun 9th, 2009

PCode is the parsed Code of the Procedure which is ready for
execution... This is the code that is loaded into the shared pool and
executed for you.

Answered by: g_sidhu on: Jan 31st, 2008

Source code is the Text of the procedure.It is accessed from USER_SOURCE data dictionary view.

P-code is Compiled object code which is not accessible.

Rollforward

Asked By: kondla | Asked On: Jun 30th, 2008

What is the rollforward in PL/SQL

Answered by: Madhu_Das on: May 21st, 2009

Rollforward is to Redo, as Rollback is to undo.

Answered by: goelumesh on: Jul 1st, 2008

Roll forward refers to the process Oracle goes through to apply changes contained in the redo log files (both online and archive). The database clock (as measured by the system change number) is moved...

Return statement and out parameters

Asked By: ramyaselvi16 | Asked On: Jun 26th, 2008

What are return statement and out parameter in PL/SQL function?

Answered by: Database Steve on: May 20th, 2009

Functions always return a value.

Procedures do not.

The use of IN, OUT, and IN_OUT parameters as scalar values or ref cursors is dependent on your need and overall performance not on a pseudo-standard as answered above.

Answered by: KD09714 on: Jun 28th, 2008

Function must have return statement by which it returns one value.Though we can use out parameter in function(function not getting called from select statement or DML), it is not good programming prac...

Why functions are used in Oracle ?Can functions return more than 1 values?Why procedures are used in Oracle ?What are the disadvantages of packages?What are the GLobal variables in packages?

Asked By: sandeep kelkar | Asked On: Dec 16th, 2005

Answered by: ravivenkat1234 on: Apr 28th, 2009

Functions can have multiple out Parameters one with Return Parameter and others can be IN OUT Parameter to the function. this way a function can return multiple out parameters.Eg : Function ( a in num...

Answered by: krishnaindia2007 on: May 7th, 2008

1.Functions are used to computer a value.2.Function can return more than one value using out parameter. But it is not a good practice to return more than one value in functions.3.Proced...

Explain, is it possible to have same name for package and the procedure in that package.

Asked By: sri | Asked On: Feb 25th, 2007

Answered by: samareshp on: Apr 22nd, 2009

yes its possible.

Answered by: bhushan_nemade on: Feb 26th, 2007

Yes, its possible to have same name for package and the procedure in that package.

What is an exception ? What are types of exception ?

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

 exception  is  the  error  handling  part  of  PL/SQL  block. The types are predefined and user defined. Some of predefined exceptions are.      cursor_already_open     dup_val_on_index     no_data_found    ...

Answered by: samareshp on: Apr 17th, 2009

Hi all, Exception is nothing but Error. Exception can serve as an ALERT message also. (using RAISE_APPLICATION_ERROR)There are two types of exceptions: 1> Pre-defineddefine exception (2&g...

Answered by: Lavanya Chowdary on: May 6th, 2007

Exception is nothing but error in the PL/SQL program. If any error occured in the PL/SQL program that terminates from the program. To handle that exceptions we are using exception handling part in the...

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.