What should be the return type for a cursor
variable. Can we use a scalar data type as return type?
The return type for a cursor must be a record type.It
can be declared explicitly as a user-defined or %ROWTYPE
can be used. eg TYPE t_studentsref IS REF CURSOR RETURN
What are different Oracle database objects?
What is difference between SUBSTR and INSTR?
SUBSTR returns a specified portion of a string eg
SUBSTR('BCDEF',4) output BCDE INSTR provides character
position in which a pattern is found in a string. eg
INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-')
Display the number value in Words?
SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))
the output like,
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
If you want to add some text like, Rs. Three Thousand
SQL> select sal "Salary ",
(' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| '
"Sal in Words" from emp
Salary Sal in Words
800 Rs. Eight Hundred only.
1600 Rs. One Thousand Six Hundred only.
1250 Rs. One Thousand Two Hundred Fifty only.
What is difference between SQL and SQL*PLUS?
SQL*PLUS is a command line tool where as SQL and PL/SQL
language interface and reporting tool. Its a command
line tool that allows user to type SQL commands to be
executed directly against an Oracle database. SQL is a
language used to query the relational
database(DML,DCL,DDL). SQL*PLUS commands are used to
format query result, Set options, Edit SQL commands and
What are various joins used while writing SUBQUERIES?
Self join-Its a join foreign key of a table references
the same table. Outer Join--Its a join condition used
where One can query all the rows of one of the tables in
the join condition even though they don't satisfy the
Equi-join--Its a join condition that retrieves rows from
one or more tables in which one or more columns in one
table are equal to one or more columns in the second
What a SELECT FOR UPDATE cursor represent.?
The processing done in a fetch loop modifies the rows
that have been retrieved by the cursor. A convenient way
of modifying the rows is done by a method with two
parts: the FOR UPDATE clause in the cursor declaration,
WHERE CURRENT OF CLAUSE in an UPDATE or declaration
What are various privileges that a user can grant to
Display the records between two range?
select rownum, empno, ename from emp where rowid in
(select rowid from emp where rownum <=&upto minus select
rowid from emp where rownum<&Start);
minvalue.sql Select the Nth lowest value from a table?
select level, min('col_name') from my_table where
level = '&n' connect by prior ('col_name') < 'col_name')
group by level;
Given a table called emp with the following columns:
-- id number
-- name varchar2(20)
-- sal number
-- For the second lowest salary:
-- select level, min(sal) from emp
-- where level=2
-- connect by prior sal < sal
-- group by level
What is difference between Rename and Alias?
Rename is a permanent name given to a table or column
whereas Alias is a temporary name given to a table or
column which do not exist once the SQL statement is
Difference between an implicit & an explicit cursor.?
only one row. However,queries that return more than one
row you must declare an explicit cursor or use a cursor
FOR loop. Explicit cursor is a cursor in which the
cursor name is explicitly assigned to a SELECT statement
via the CURSOR...IS statement. An implicit cursor is
used for all SQL statements Declare, Open, Fetch, Close.
An explicit cursors are used to process multirow SELECT
statements An implicit cursor is used to process INSERT,
UPDATE, DELETE and single row SELECT. .INTO statements.
What is a OUTER JOIN?
Outer Join--Its a join condition used where you can
query all the rows of one of the tables in the join
condition even though they donít satisfy the join
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.
What is the purpose of a cluster?
Oracle does not allow a user to specifically locate
tables, since that is a part of the function of the
RDBMS. However, for the purpose of increasing
performance, oracle allows a developer to create a
CLUSTER. A CLUSTER provides a means for storing data
from different tables together for faster retrieval than
if the table placement were left to the RDBMS.
What is OCI. What are its uses?
Oracle Call Interface is a method of accesing database
from a 3GL program. Uses--No precompiler is required,PL/SQL
blocks are executed like other DML statements.
The OCI library provides
--functions to parse SQL statemets
--bind input variables
--bind output variables
--fetch the results
How you open and close a cursor variable. Why it is
OPEN cursor variable FOR SELECT...Statement
CLOSE cursor variable In order to associate a cursor
variable with a particular SELECT statement OPEN syntax
is used. In order to free the resources used for the
query CLOSE statement is used.
Display Odd/ Even number of records?
Odd number of records:
select * from emp where (rowid,1) in (select rowid,
mod(rownum,2) from emp);
Even number of records:
select * from emp where (rowid,0) in (select rowid,
mod(rownum,2) from emp)
What are various constraints used in SQL?
Can cursor variables be stored in PL/SQL tables. If yes
how. If not why?
No, a cursor variable points a row which cannot be
stored in a two-dimensional PL/SQL table.
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.
Can you use a commit statement within a database
What WHERE CURRENT OF clause does in a cursor?
SELECT num_credits INTO v_numcredits FROM classes
WHERE dept=123 and course=101;
WHERE CURRENT OF X;
There is a string 120000 12 0 .125 , how you will find
the position of the decimal place?
INSTR('120000 12 0 .125',1,'.')
What are different modes of parameters used in functions
-IN -OUT -INOUT
How you were passing cursor variables in PL/SQL 2.2?
In PL/SQL 2.2 cursor variables cannot be declared in a
package.This is because the storage for a cursor
variable has to be allocated using Pro*C or OCI with
version 2.2, the only means of passing a cursor variable
to a PL/SQL block is via bind variable or a procedure
When do you use WHERE clause and when do you use HAVING
HAVING clause is used when you want to specify a
condition for a group function and it is written after
GROUP BY clause. The WHERE clause is used when you want
to specify a condition for columns, single row functions
except group functions and it is written before GROUP BY
clause if it is used.
Difference between procedure and function.?
Functions are named PL/SQL blocks that return a value
and can be called with arguments procedure a named block
that can be called with parameter. A procedure all is a
PL/SQL statement by itself, while a Function call is
called as part of an expression.
Which is more faster - IN or EXISTS?
EXISTS is more faster than IN because EXISTS returns a
Boolean value whereas IN returns a value.
Page Numbers :