Sahaja Mallarapu

• Aug 26th, 2011

A Ref Cursor is a variable that points to cursor.A ref cursor allows you to store the result of a "bulk collect" from a table (or any PL/SQL cursor return set) into a PL/SQL table and then reference ...

Sadashiv

• Aug 8th, 2011

REF Cursor OR Dynamic Cursors: Query associated with cursor is not known at the time of compilation. The select statement associated with cursor is changing at the time of running the program Syntax...

Answer Question  Select Best Answer

SURESHMALLIPEDDI

 Profile Answers by SURESHMALLIPEDDI Questions by SURESHMALLIPEDDI

• Aug 9th, 2011

Select '('||substr(phone,1,3)||')'||substr(phone,4) "formated phone number",phone from phone

paresh Borad

• Aug 1st, 2011

Use this query

create table newphone as (select '(' || substr(phnumber, 1,3) || ')' || substr(phnumber,4,10) "phonenumber" from oldphone)

Answer Question  Select Best Answer

What are Composite Variable?

Rams

• Aug 7th, 2011

A composite variable has internal components, which you can access individually. You can pass entire composite variables to subprograms as parameters. PL/SQL has two kinds of composite variables, coll...

agitatedsoul10

 Profile Answers by agitatedsoul10 Questions by agitatedsoul10

• Aug 4th, 2010

A Variable holds a value in a memory location. The Association of a variable to other variables can be called as composite variables.

Answer Question  Select Best Answer

List at least three common ways to write logs in PL SQL?

yogesh

• Aug 4th, 2011

We can't return more than 1 value in function but by using OUT parameter we can get multiple values in function
which is not good programming, by using ref cursor we just save memory location of function doesn't return more than 1 value.

sububhaai

 Profile Answers by sububhaai Questions by sububhaai

• May 14th, 2009

1. Create a log table and insert your logging messages into this table.2. Write to a log file, using the DBMS_FILE package, but this is not recomended because there is lot of I/O.

Answer Question  Select Best Answer

saeed

• Jul 25th, 2011

Compare EXISTS and IN Usage with advantages and disadvantages.

be_viral

 Profile Answers by be_viral Questions by be_viral

• Jul 14th, 2008

We should use Exists or In based on the no of records inner and outer query is expected to return.Using Exists : Here the outer query is executed first and then inner query is excecuted for each value...

Answer Question  Select Best Answer

manoj_lanser

 Profile Answers by manoj_lanser Questions by manoj_lanser

• Jul 20th, 2011

%type is used when we have to define the datatype of a variable similar to another existing column data-type. eg- v_id employess.employee_id%type. In this example we have make a variable v_id similar...

Radha

• Jul 14th, 2011

We use %type and %rowtype in PL/SQL programming part. %Type: The variable will assign the datatype which contains in the column of that particular table we mentioned. Ex: v_eno employee.eno%type; ...

Answer Question  Select Best Answer

How to know the current database name in PL/SQL?

Saravanan Govindan

• Jul 20th, 2011

Select name from v$database;

Code
 

Saravanan Govindan

• Jul 20th, 2011

Select name from v$database;

Answer Question  Select Best Answer

harinath

• Jul 15th, 2011

Cursor is a static,
ref cursor is dynamic,
we can't use ref cursor directly, so we declare cursor variable
cursor variables can't be stored in the database

Rajeshwaran

• Oct 12th, 2006

Cursor is a structure which points to a memory locationswhile Ref-cursor is a datastructure which point to a object which inturn points to Memory locations..the advantage of having Ref-cursor is that we can pass dynamically the Ref-cursor as a parameter to a procedures..

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

Radha

• Jul 14th, 2011

Triggers fire when any condition on such trigger is satisfied...but we don't know when that trigger is going to be fired and if it is fired where can we see output of DBMS_output.put_line.

If we want to see output of trigger when it is fired we should mention Raise_application_error in trigger.

pawan_kr2007

 Profile Answers by pawan_kr2007 Questions by pawan_kr2007

• Oct 11th, 2009

If you 'set serveroutput on' and execute the statement which fires the trigger the message will display on prompt otherwise it will not.

Answer Question  Select Best Answer

Radha

• Jul 14th, 2011

Questions Like: Difference between Delete and Truncate Difference between Sub query and Correlated sub query Difference between Decode and Case Different Types of constraints Difference between...

Reks

• Jan 10th, 2007

Questions like:what is the advantage and disadvantage of packageswhat is the use of cursorsWhat are the exceptiions found in select queryhow to select duplicate rows,odd number of rowshow do we use de...

Answer Question  Select Best Answer

Radha

• Jul 13th, 2011

Flash Back query is used to get back the deleted rows & dropped tables from the database even they are committed. To get back the dropped table: Flashback table to before drop; To get back...

murthy 1053

 Profile Answers by murthy 1053 Questions by murthy 1053

• Jul 2nd, 2010

Flash back query is used to retrive the data.For ex if a table is dropped and we want the table again so at that time.syn:=FLASHBACK TABLE table_name TO BEFORE DROP.This will work only if recycled bin is not purged.

Answer Question  Select Best Answer

Radha

• Jul 13th, 2011

Yes we can add NOT NULL constraint for the column already containing data as: If the data doesn't have null values then - Alter table modify constraint ; If the data contains null values...

monoranjang

 Profile Answers by monoranjang Questions by monoranjang

• Jan 24th, 2011

Yes possible, try the below query:ALTER TABLE test add c1 VARCHAR2(10) DEFAULT ('a') not nullThanksMonoranjan

Answer Question  Select Best Answer

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

nade_rs

 Profile Answers by nade_rs Questions by nade_rs

• Jun 25th, 2011

We can also  make use of compound trigger (new feature in  11 g to avoid mutating error)

Baji Shaik

 Profile Answers by Baji Shaik Questions by Baji Shaik

• Jun 5th, 2011

The? row level trigger based on a table,the trigger body can't read thedata? from same table, if you are trying to this it gives mutating errorto avoid that you must use statement level trigger

Answer Question  Select Best Answer

muraligrandhi

 Profile Answers by muraligrandhi Questions by muraligrandhi

• Jun 23rd, 2011

A forward declaration means that modules (procedures and functions) are declared in advance of their actual body definition. For example, if program A calls program B and program B calls program A,&nb...

KD09714

 Profile Answers by KD09714 Questions by KD09714

• Jun 26th, 2008

Example of forward declaration in package : CREATE OR REPLACE PACKAGE pkg_CBRef_Seq AS PROCEDURE sp_CBRCreateDropSeq_scd ( p_cod_CountryCode IN country.country_code%TYPE, ...

Answer Question  Select Best Answer

sunshine60india

 Profile Answers by sunshine60india Questions by sunshine60india

• Jun 18th, 2011

Primary KEY is the combination of UNIQUE and NOT NULL constraint

neha.awasthi1

 Profile Answers by neha.awasthi1 Questions by neha.awasthi1

• May 19th, 2011

Both primary key and unique contain unique value.1: primary key cant be nullUnique key can be null.2: Multiple column of table van be defined as unique key.Only one column can defined as primary key

Answer Question  Select Best Answer

Baji Shaik

 Profile Answers by Baji Shaik Questions by Baji Shaik

• Jun 5th, 2011

Varchar2(80)  accept  upto 80  characters,varchar2(80 byte) accept upto 80*8=640 characters

psingla

 Profile Answers by psingla Questions by psingla

• Apr 28th, 2010

If system parameter NLS_LENGTH_SEMANTICS is set to defaultthen no differenceIf system parameter NLS_LENGTH_SEMANTICS is changed from default behavior  BYTE to CHAR.then VARCHAR2(80) means VARCHAR...

Answer Question  Select Best Answer

Baji Shaik

 Profile Answers by Baji Shaik Questions by Baji Shaik

• Jun 5th, 2011

Strong ref cursor return value, but seek ref cursor it can't return any value

askvenki

 Profile Answers by askvenki Questions by askvenki

• Jul 20th, 2007

           A strong REF CURSOR type definition specifies a return type, but a weak definition does not.DECLARE   TYPE EmpCurTyp IS REF ...

Answer Question  Select Best Answer

sandeshshinde000

 Profile Answers by sandeshshinde000 Questions by sandeshshinde000

• Jun 4th, 2011

begin  DML 1  '  '  '  '  '  DML 10exceptionwhen others thandbms_output('error at location '||dbms_utility.format_error_backtrace);e...

BakulPatel

 Profile Answers by BakulPatel Questions by BakulPatel

• May 7th, 2011

To simplify  Remove exception block. Oracle will show line number with error thrownOther method:beginlocation :=1; dml1 location :=2; dml2exception    when others than&nb...

Answer Question  Select Best Answer