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

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

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

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

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

MURTHY

• Sep 7th, 2011

the question is very nice. yes we can have 2 triggers with same body but with different names. ex: create trigger trg1 .................... ............................... end; create tri...

Baji Shaik

 Profile Answers by Baji Shaik Questions by Baji Shaik

• Jun 5th, 2011

yes, we cant said upto 10g oracle server executes the triggers randomly,but 11g we are having a feature follows clause,by using this we have to control the execution of triggers explicitly by the deve...

Answer Question  Select Best Answer

pavankumar_greek

 Profile Answers by pavankumar_greek Questions by pavankumar_greek

• Sep 15th, 2011

Using packages we can run concurrently two programs .
place the programs in two files and create a package which is having the code can open those two files. next we have to register this package to apps.

Answer Question  Select Best Answer

Suppose Input table is COL1 COL2 COL 3A 1 -3B -2 6c 3 -4D 9 10In sql query I want the desired output as follows COL1 COL nA 1B 6C 3D 10Please write the query to get this output

fire_starter

 Profile Answers by fire_starter Questions by fire_starter

• Sep 15th, 2011

Hello, For my query example I have used the table test_tbl, and the columns col, col1, col2. It also does consider NULL value in comparison, so it will show value on comparing with NULL, and for ev...

BhushanNag

 Profile Answers by BhushanNag Questions by BhushanNag

• Aug 24th, 2011

SELECT COL1, GREATEST(COL2, COL3) FROM max_value_column

Regards
Bhushan

Answer Question  Select Best Answer

VITHAL

• Sep 16th, 2011

To handle this type of procedures we should use pragma autonomous trigger

Dilip Lakshetty

 Profile Answers by Dilip Lakshetty Questions by Dilip Lakshetty

• Mar 27th, 2011

Better You make your trigger body as autonomous transaction

Answer Question  Select Best Answer

satya

• Sep 20th, 2011

You can use "Decode" also inspite of Translate:
Ex: select decode( 'abcdefghijk','abcdefghijk','zyxwvutsrqp') enript from dual;

tolik

 Profile Answers by tolik Questions by tolik

• Aug 3rd, 2011

Wrap command

Answer Question  Select Best Answer

How to identify missing records in a single-column table ?Column consists of numbers in a ordered manner but the some numbers are deleted from the table in random manner and need to identify those rows.p.s. This table consist of only 1 row.

Satya

• Sep 20th, 2011

"sql SELECT CASE WHEN ConNum - prev_id = 2 THEN TO_CHAR( prev_id +1) ELSE '[' || NVL( prev_id +1 ,1 ) || ' , ' || (ConNum-1) || ']' END AS missing_range , ConNum - NVL...

geetha

• Aug 25th, 2011

Code
 

where the test table is like colA 2 4 6 7 the result will be the missing numbers..

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

g_sidhu  

 Profile Answers by g_sidhu Questions by g_sidhu

• Feb 1st, 2008

---

Before a PL/SQL program can be executed, it must be compiled. The PL/SQL compiler resolves references to Oracle objects by looking up their definitions in the data dictionary. Then, the compiler assigns storage addresses to program variables that will hold Oracle data so that Oracle can look up the addresses at run time. This process is called binding.

How a database language implements binding affects runtime efficiency and flexibility. Binding at compile time, called static or early binding, increases efficiency because the definitions of database objects are looked up then, not at run time. On the other hand, binding at run time, called dynamic or late binding, increases flexibility because the definitions of database objects can remain unknown until then.

Art11

 Profile Answers by Art11 Questions by Art11

• Sep 25th, 2011

This is the best answer I found:

Early binding is checking for existence of objects referred in the program unit during the compilation phase. Late binding will not check the existence of objects till the runtime

pramodh holla

 Profile Answers by pramodh holla Questions by pramodh holla

• Sep 21st, 2010

It uses deferred binding...in the sense it uses both static and dynamic binding.

Improve Answer

How to delete duplicate Keys through PL-SQL in single query.

anonymous

• Sep 27th, 2011

Code
 

Art11

 Profile Answers by Art11 Questions by Art11

• Sep 21st, 2011

Assuming that ename and job is the same or First and Last name...

SELECT * FROM (SELECT empno, ename, job ,RANK() OVER (PARTITION BY ename, job ORDER BY empno) AS SeqNo FROM emp1) WHERE SeqNo > 1

Answer Question  Select Best Answer

Art11

 Profile Answers by Art11 Questions by Art11

• Sep 27th, 2011

A REF Cursor is a datatype that holds a cursor value in the same way that a VARCHAR2 variable will hold a string value. It is not a cursor, but a variable that points to a cursor. It is not limited t...

sunil

• Jul 26th, 2011

CURSOR A cursor is a variable that runs through the tuples of some relation. This relation can be a stored table, or it can be the answer to some query. A cursor can be used in realtime when u nee...

Answer Question  Select Best Answer

Art11

 Profile Answers by Art11 Questions by Art11

• Sep 27th, 2011

A hint is an instruction to the optimizer. You can use optimizer hints with SQL statements to alter execution plans.

muraligrandhi

 Profile Answers by muraligrandhi Questions by muraligrandhi

• Jun 23rd, 2011

     The optimizer hints determines the most efficient way to execute a SQL statement after considering many factors related to the objects referenced and the conditions specified ...

Answer Question  Select Best Answer

Neeraj yadav

• Oct 3rd, 2011

Code
 

OR can pass clause also........................

Code
span style="color: #ff0000;">'CV/2011/12'

gsmanvi

 Profile Answers by gsmanvi Questions by gsmanvi

• Jun 27th, 2008

declarecursor cursor_name isselect col1,col2,col3from tab_name;ab_record cursor_name%rowtype;beginopen cursor_name;loopfetch ab_record into cursor_name;exit when cursor_name%notfound;insert into tab_n...

Answer Question  Select Best Answer

How to debug a procedure in SQL Prompt

srimanta sahoo

• Oct 4th, 2011

You can debug the procedure by writting following command :
alter procedure procedure name
debug ;

puneet.kush

 Profile Answers by puneet.kush Questions by puneet.kush

• Apr 17th, 2008

You can debug the procedure. Oracle gives a util programme DBMS_DEBUG by which you can debug a procedure.the full detail you can see at http://www.oracleutilities.com/wiki/index.php?title=Dbms_debugan...

Answer Question  Select Best Answer

Dharma

• Oct 14th, 2011

Code
 

mohan

• Oct 14th, 2011

SELECT * FROM TABLE_NAMEWHERE (ROWID, 0) IN (SELECT ROWID, MOD(ROWID, N) FROM TABLE_NAME);

Answer Question  Select Best Answer

What is Force View. Why Oracle Give Facility to create force view? What is the use of force View

sonam kanungo

• Oct 15th, 2011

As per my knowledge when view is created without base table.then the view is called as forced view.

in this type of view is created with compilation error.

mohan

• Oct 14th, 2011

If we create a view without having base table, that view called as forced view. it will create with compile error. it will be in invalid till base table create.

Answer Question  Select Best Answer

If a deadlock has occured and if you have given with the dump, how will you identify that where the deadlock has occured, how the deadlock occured and how to rectify the same.

kari

• Oct 16th, 2011

the steps necessary to identify and rectify code causing deadlocks are: * Locate the error messages in the alert log. * Locate the relevant trace file(s). * Identify the SQL statements i...

skumar.t15

 Profile Answers by skumar.t15 Questions by skumar.t15

• Aug 5th, 2010

Each user session might have one or more tasks running on its behalf where each task might acquire or wait to acquire a variety of resources. The following types of resources can cause blocking that c...

Answer Question  Select Best Answer