-
LOCK Query
What is use of lock query in SQL Plus? Give its syntax and implementation?
sampra
- Mar 6th, 2012
Exclusive Lock: This is placed when Insert, Update or Delete command is performed. There can be only one exclusive lock on a record at a time.
hussasha
- Mar 17th, 2009
Locking protect table when several users are accessing the same table. Locking is a concurrency control technique in Oracle. It helps in data integrity while allowing maximum concurrency access to...
-
SELECT Statement in CASE
How to use select statement in Case?
sunraajkumar
- Jun 3rd, 2009
Example sample query: if then else conditionSELECT empno, ename,
job, sal,
CASE job WHEN 'manager' THEN sal *0.10
WHEN 'analyst' THEN sal *0.20
WHEN 'president' THEN sal *0.30
ELSE sal END "Revised Salary" from emp; -
Ref Cursors
How many ref cursors can be dynamically opened in stored procedures for a session?
kondapallikalyan
- Nov 24th, 2009
We have two types of ref cursors1. Week typed2. Strong typedEx: Type <Ref cursor Name> is ref cursor /* this is week typed*/Ex:Type <Ref cursor Name> is ref cursor return /* this is strong type
sunraajkumar
- Jun 10th, 2009
more then one query open same cursor name at same time (use unlimited query)
-
SQL Soundx
What is soundx in SQL?
sure_prince
- Aug 14th, 2009
This is Soundex Function is useful to select the data according to the sound of
the given value but not compare with the casing of that letters.For Example:
SQL> SELECT * FROM emp WHERE Soundex(Ename)=Soundex('Analyst');
It select all the Analyst details in emp table in anuy case...sunraajkumar
- Jul 1st, 2009
Soundex function is used to find the phonetic representation of stringSyntax----------Soundex(CHAR)Query--------SELECT Empname FROM Employee WHERE SOUNDEX(EMPNAME)=SOUNDEX('SMYTHE')
-
SQL System Time
How to get System time in SQL?
kondapallikalyan
- Nov 24th, 2009
We have Functions1. current_timestamp2. systimestampEx: Select current_timestamp from dual;Ex:Select systimestamp from dual;
-
Change Default Display
How to change the default display? For deptno 10 show "Financial Department", for deptno 20 show "Account Department", for deptno 30 show "Management Information System", for deptno 40 show "Electronic Data Processing"
kiran_marla
- Jun 20th, 2010
Use decoding to display names.select decode(deptno,10,'Financial Department',20,'Account Department',30,'Management Information System',40,'Electronic Data Processing'...
Kushy14
- Jun 19th, 2010
SELECT DECODE (deptno,10,'Financial Department',20,'Account Department',30,'Management Information System',40,'Electronic Data Processing') FROM dept;
-
Number of Constraints
How to count the number of constraints on a given table?
itoradeveloper
- Jan 6th, 2010
Select count(constraint_name) from user_constraints where table_name='TABLENAME'
-
Aggregation Key
What is the Aggregation Key?
AnithaLetchumi
- Aug 10th, 2011
Aggregation Key is nothing but composite or concatenated key. It's the primary key having more than one column. For example different books may have identical titles, authors. In this case we can take...
-
Create Index
How Create Index in Oracle? explain with example
sampra
- Mar 6th, 2012
Create index cr_ind on student(sid);
here i have created a index on student table on column sidprashant.baranwal
- Sep 16th, 2010
The CREATE INDEX statement is used to create indexes in tables. Indexes allow the database application to find data fast; without reading the whole table. CREATE INDEX index_name ON table_name (c...
-
Oracle query performance
A) I have 100 columns in emp table , which of the below queries will give good performance and WHY?1) Select * from emp;2)select col1,col2,col3,..............col100 from emp;B) we have 2 tables T1(100 records) and T2(1000) recordsWhen we use the below queries , it will generate Cartesian product , which query will give better performance and WHY?1)Selct * from t1,t2 ;2)select * from t2,t1;
DSADSF
- Sep 24th, 2011
SELECT * FROM T1,T2
WHERE T1.COLUM_NAME=T2.COLUM_NAME;
NOTE THAT COLUMN NAME AND DATA SHOULD BE SAMEnandha_spartan
- Aug 18th, 2011
Set time on
then
write the query then execute display output and timing..
then write set time off
write same query then execute display output ,compare both timing it is called query performance -
SQL Query to retrieve columns having a particular character
Table StructureID Name Salary1 Pratap 15002 Gayathri 25003 Anita 3000 4 Ram.C 4500Write a query to retrieve names which have the letter R in it irrespective of the case(small or caps).Output:ID Name Salary1 Pratap 15002 Gayathri 25004 Ram.C 4500
Sanjay Bajracharya
- Jul 13th, 2011
Code- span style="color: #ff0000;">'%R%';
-
Case and decode
can anyone help with a simple example of converting one from other like CASE to DECODE or DECODE to CASE.
Mark Haynes
- Oct 12th, 2011
Sorry about that seems some of the info got stripped out of my examples, so once again: CASE "expression" WHEN "search condition 1" THEN "return value 1" ELSE "return else ...
Mark Haynes
- Oct 12th, 2011
Both CASE and DECODE function as IF-THEN-ELSE statements: (limit your conversions to SQL statements in Oracle only) CASE WHEN THEN ELSE END converts to: DECODE(, , ,...
-
Outer Join
When will we use outer join? Explain with example
-
What is a cursor?
Oracle uses work area to execute SQL statements and store processing information PL/SQL construct called a cursor lets you name a work area and access its stored information A cursor is a mechanism used to fetch more than one row in a Pl/SQl block.
syam sundar
- Aug 17th, 2007
Oracle ueses the private area for excute SQL statements and store processing information. The private area is called as CONTEXT AREACURSOR: cursor is a handle or pointer to the context area.
rampratap409
- Dec 12th, 2006
cursor is a private sql working area.cursor could be two type implicit or explicit cursoronly explicit cursor are assigned private sql working area.
-
Difference between NO DATA FOUND and %NOTFOUND
NO DATA FOUND is an exception raised only for the SELECT....INTO statements when the where clause of the querydoes not match any rows. When the where clause of the explicit cursor does not match any rows the %NOTFOUND attribute is set to TRUE instead.
g_sidhu
- Feb 6th, 2008
NO DATA FOUND: Is an exception which is raised when no rows are retrieved from the database in a SELECT statement, then PL/SQL raises the exception NO_DATA_FOUND.%NOTFOUND: is a Boolean attribute that evaluates to TRUE if the most recent SQL statement does not affect any rows.
syam sundar
- Aug 17th, 2007
NO DATA FOUND is an exceptionWhen your?SELECT INTO?statement?returns no rows that time an error raised by oracle. The error is ORA-01403 NO DATA FOUNDWhereas %NOTFOUND?is a?cursor attributeIt ret...
-
What WHERE CURRENT OF clause does in a cursor?
LOOPSELECT num_credits INTO v_numcredits FROM classesWHERE dept=123 and course=101;UPDATE studentsSET current_credits=current_credits+v_numcreditsWHERE CURRENT OF X;END LOOPCOMMIT;END;
aravindpurohit
- Apr 4th, 2008
When referencing the current row from an explicit cursor, we use 'WHERE CURRENT OF'. it allows ...
g_sidhu
- Feb 6th, 2008
The WHERE CURRENT OF clause is used to reference the current row from an explicit cursor.
-
Can a function take OUT parameters. If not why?
No. A function has to return a value,an OUT parameter cannot return a value.
-
Difference between database triggers and form triggers?
Data base trigger(DBT) fires when a DML operation is performed on a data base table. Form trigger(FT) Fires when user presses a key or navigates between fields on the screenCan be row level or statement level No distinction between row level and statement level.Can manipulate data stored in Oracle tables via SQL Can manipulate data in Oracle tables as well as variables in forms.Can be fired from any...
rajakumar_na
- Nov 14th, 2007
Database Trigger: We can't invoke a Form Trigger in DatabaseFrom Trigger: We can invoke a Database Trigger in Forms
Mohit Dhamija
- Jul 3rd, 2006
Even 'Form Trigger' can be fired before and after the event. ex- Triggers like Pre Block / Pre Record / Post Block / Post Record ..
-
Where we use nested tables in our real time
Nicsmart
- Mar 31st, 2010
• Use when working with an unbounded list that needs to increase dynamically • Use when you need to store in the database and operate on elements individually
paul
- Sep 2nd, 2006
Hi,Nested Tables are collector objects (only attributes, i.e. no behaviour defined), that can be re-used in the same way as a class in java or c++ (without functions / procedures). In RDBMS...
-
Oracle SQL Interview Questions
Ans