kool.bird9

 Profile Answers by kool.bird9 Questions by kool.bird9

• Jul 9th, 2011

Oracle ROWID uniquely identifies where a row resides on disk.  The information in a ROWID gives Oracle everything it needs to find your row, the disk number, the cylinder, block and offset ...

RajivPrasad

 Profile Answers by RajivPrasad Questions by RajivPrasad

• May 8th, 2007

Also as mentioned by our friend rowid is a unique hexadecimal format integer which takes up the representation as below      File: Block: Row One can retrieve rowid by using t...

Answer Question  Select Best Answer

What is Global temporary table? and what are the benefits of it?

Prabhat Sahu

 Profile Answers by Prabhat Sahu Questions by Prabhat Sahu

• Oct 24th, 2017

Global Temporary Table(GTT): --------------------------------------- --> GTT operation perform in-memory(RAM) so reading data from GTT is a "Logical Read" -> hence its faster. --> GTT are of 2 type...

vanishavadlya

 Profile Answers by vanishavadlya Questions by vanishavadlya

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

Answer Question  Select Best Answer

Why PL/SQL does not support retriving multiple records?

Read Best Answer

Editorial / Best Answer

promisinganuj  

 Profile Answers by promisinganuj Questions by promisinganuj

• Member Since Sep-2009 | 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.

promisinganuj

 Profile Answers by promisinganuj Questions by promisinganuj

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

Mad Hatter

 Profile Answers by Mad Hatter Questions by Mad Hatter

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

Improve Answer

What are all the different ways available to find the null value in a table

NSingh

 Profile Answers by NSingh Questions by NSingh

• May 22nd, 2010

select * from tab_namewhere col_name is null

psingla

 Profile Answers by psingla Questions by psingla

• May 6th, 2010

In pl/sql:"is null" and "is not null" are the best way to find null and not null values resp.In t-sqlif ansi_nulls is off then(set ansi_nulls off)"=" will work to search the null values

Answer Question  Select Best Answer

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

samareshp

 Profile Answers by samareshp Questions by samareshp

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

Lavanya Chowdary

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

Answer Question  Select Best Answer

 Procedure Specification and Procedure Body.

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by 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

Answer Question  Select Best Answer

Suppose if i have 8 triggers declared for a table..how to disable a single trigger from a table...i don't want to disable all triggers for a table. I know we can disable all triggers by using "alter table disable all triggers;". just i want to know how to disable single trigger from a table.

Pavan

• Apr 3rd, 2007

Thanks parweej, alter trigger <trigger-name> disable worked for me..

subbaraovangalli

 Profile Answers by subbaraovangalli Questions by subbaraovangalli

• Mar 27th, 2007

To disable a perticular trigger on a table             alter table disable trigger

Answer Question  Select Best Answer

samareshp

 Profile Answers by samareshp Questions by samareshp

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

g_sidhu

 Profile Answers by g_sidhu Questions by g_sidhu

• Feb 6th, 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 ...

Answer Question  Select Best Answer

Shiyamala Devi

• Jan 6th, 2012

Code
 

This query will display 4th highest salary from the player table

sunshine60india

 Profile Answers by sunshine60india Questions by sunshine60india

• Jun 18th, 2011

SELECT TOP 1 * FROM (    SELECT TOP 4 * FROM EMPLOYEE ORDER BY CTC desc)AORDER BY CTC

Answer Question  Select Best Answer

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• May 3rd, 2008

To add  primary key or unique key constraint Alter table tablename add constraint constraintname  primary key / unique (column name)To add check constraint Alter table tablename add constrai...

hindol_saha

 Profile Answers by hindol_saha Questions by hindol_saha

• Apr 9th, 2007

ALTER TABLE STUDENT  ADD CONSTRAINT PK_STD_ID PRIMARY KEY (STUDENT_ID);

Answer Question  Select Best Answer

ANIL KUMAR PASUPULETI

• Mar 27th, 2017

Use SQL%NOTFOUND to check whether the update is success or not and then take the action accordingly

ddkdhar

 Profile Answers by ddkdhar Questions by ddkdhar

• Mar 7th, 2007

Question was not clearsorrythis is my questionupdate a table A using cursor if it updated then insert into another table B.if not insert into same table A and insert into B.handle every exceptions in the code.nikhil ok

Answer Question  Select Best Answer

ddkdhar

 Profile Answers by ddkdhar Questions by ddkdhar

• Mar 7th, 2007

One thing nikhil handle sqlerrm with variables .that increases perfmance ok.

ddkdhar

 Profile Answers by ddkdhar Questions by ddkdhar

• Mar 6th, 2007

Thank you very mech nikhil

Answer Question  Select Best Answer

Habib Ali

• May 10th, 2016

Normal Cursor > Your SELECT Query is determined already before BEGIN i.e. Table, Column Names
Ref Cursor > Your SELECT Query is determined at runtime after BEGIN i.e.Table, Column Names

syed_ansar

• Oct 18th, 2006

We can use Ref cursor as an IN OUT parameter .

Answer Question  Select Best Answer

Radha

• Jul 14th, 2011

We use view to be confidential about the data and to avoid the complexity of a query. But Mviews are used for performance problem, we use mviews for faster execution of a query. But we need to do regular refresh for mviews.

pawan_kr2007

 Profile Answers by pawan_kr2007 Questions by pawan_kr2007

• Oct 11th, 2009

View: If a query is being used again and again then its better to create a view with that query. The query gets stored in database as an object.Materialized view: When you want you replicate the main ...

Answer Question  Select Best Answer

pawan_kr2007

 Profile Answers by pawan_kr2007 Questions by pawan_kr2007

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

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

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

Answer Question  Select Best Answer

grajeshkumar82

 Profile Answers by grajeshkumar82 Questions by grajeshkumar82

• Jun 9th, 2009

PCode is the parsed Code of the Procedure which is ready forexecution... This is the code that is loaded into the shared pool andexecuted for you.

g_sidhu

 Profile Answers by g_sidhu Questions by g_sidhu

• Feb 1st, 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.

Answer Question  Select Best Answer

Atiqur Rahman

• May 18th, 2017

There is a tables called ALL_TRIGGERS which contains column STATUS (ENABLE/DISABLE). This column tells us that whether a trigger is enable or disable. Also current user can modify these values if user have CREATE ANY TRIGGER privilege.

Kumar Prateek

• May 4th, 2017

To know weather the Trigger status is Active/Inactive AND which trigger is used on which Table, SELECT T.[name] as TableName, TR.[Name] as TriggerName, CASE WHEN 1=OBJECTPROPERTY(TR.[id], ExecIsTri...

Answer Question  Select Best Answer

g_sidhu

 Profile Answers by g_sidhu Questions by g_sidhu

• Feb 1st, 2008

Force View: creates the view regardless of whether or not the base tables exist or whether the user has privileges on them. The user still can’t execute the view, but he or she can create it. No...

Lavanya Chowdary

• May 6th, 2007

Generally we are not supposed to create a view without base table. If you want to create any view without base table that is called as Force View or invalid view.?Syntax: CREATE FORCE VIEW AS < SELEC...

Answer Question  Select Best Answer

KSPRDEEP

• Oct 17th, 2012

In parameter means it will insert the values
OUT parameters means it will print out put (pass values to output). If we not assigning any value to out parameter it will take (print) NULL value.

INOUT means it take values as input and out put

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• May 7th, 2008

1.There is no hard and fast rule about this. Whether to use INOUT or OUT depends upon the situation.2. If not assigned anything to OUT parameter it retuns NULL value.

Answer Question  Select Best Answer

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by 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

Answer Question  Select Best Answer