• What are the various types of queries
?
Answer: The types of queries are:
Normal Queries
Sub Queries
Co-related queries
Nested
queries
Compound queries
• What is a transaction ?
Answer: A transaction is a set of SQL
statements between any two COMMIT and ROLLBACK
statements.
• What is implicit
cursor and how is it used by Oracle
?
Answer: An implicit cursor is a cursor
which is internally created by Oracle.It is created by Oracle for
each individual SQL.
• Which of the following is not a schema object : Indexes, tables, public synonyms,
triggers
and packages ?
Answer: Public synonyms
• What is
PL/SQL?
Answer: PL/SQL is Oracle's Procedural
Language extension to SQL.The
language includes object
oriented programming techniques such as
encapsulation, function overloading, information
hiding (all
but inheritance), and so, brings
state-of-the-art programming to the
Oracle database server and a variety
of Oracle
tools.
• Is there a PL/SQL Engine in SQL*Plus?
Answer: No.Unlike Oracle Forms, SQL*Plus
does not have a PL/SQL engine.Thus, all your
PL/SQL
are send directly to the database engine
for execution.This makes it much more efficient as SQL
statements are not stripped off and send
to the database individually.
• Is there a limit on the size of
a PL/SQL block?
Answer: Currently, the maximum
parsed/compiled size of a PL/SQL block is 64K and the maximum
code size is 100K.You can run the following select statement to query the size of an existing package
or procedure. SQL> select * from dba_object_size
where name = 'procedure_name'
• Can one read/write files from PL/SQL?
Answer: Included in Oracle 7.3 is a
UTL_FILE package that can read and write files.The directory you
intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=...parameter).Before
Oracle
7.3 the only means of writing a file was
to use DBMS_OUTPUT with the SQL*Plus
SPOOL
command.
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler :=
UTL_FILE.FOPEN('/home/oracle/tmp', 'myoutput','W');
UTL_FILE.PUTF(fileHandler, 'Value of func1
is %sn', func1(1));
UTL_FILE.FCLOSE(fileHandler);
END;
• How can I
protect my PL/SQL source code?
Answer: PL/SQL V2.2, available with
Oracle7.2, implements a binary
wrapper for PL/SQL programs
to protect the source code.This is done
via a standalone utility that transforms
the PL/SQL source code
into portable binary object code (somewhat
larger than the original).This way you
can distribute
software without having to worry about
exposing your proprietary algorithms
and methods.SQL*Plus
and SQL*DBA
will still understand and know how
to execute such scripts.Just be careful, there is no
"decode" command available.
The syntax is: wrap
iname=myscript.sql oname=xxxx.yyy
• Can one use dynamic SQL within PL/SQL? OR Can you use
a DDL in a procedure ? How
?
Answer: From PL/SQL V2.1 one can use the
DBMS_SQL package to execute dynamic
SQL
statements.
Eg: CREATE OR REPLACE PROCEDURE DYNSQL
AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,'CREATE TABLE X (Y
DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;
• What are the various types of Exceptions ?
Answer: User defined and Predefined Exceptions.
• Can we
define exceptions twice in same
block ?
Answer: No.
• What is the difference between a
procedure and a function ?
Answer: Functions return a single variable
by value whereas procedures do not return any variable by
value.Rather they return multiple variables by passing variables by
reference through their OUT
parameter.
• Can you have two functions with the same
name in a PL/SQL block ?
Answer: Yes.
• Can you have two stored functions with the
same name ?
Answer: Yes.
• Can you call a stored function in the constraint of
a table ?
Answer: No.
• What are the various types of parameter
modes in a procedure ?
Answer: IN, OUT AND INOUT.
• What is Over Loading and what
are its restrictions ?
Answer: OverLoading means an object performing different functions
depending upon the no.of
parameters or the data type of the parameters passed to it.
No comments:
Post a Comment