What
are the various types of queries ?
Answer: The types of queries are:
- Normal Queries
- Sub Queries
- Co-related queries
- Nested queries
- Compound queries
a.
What is a transaction ?
b.
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 ?
a.
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 ?
a.
Answer: Public synonyms
- What is PL/SQL?
a.
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?
a.
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?
a.
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?
a.
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;
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?
a.
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 ?
a.
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;
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 queries ?
Answer: The types of queries are:
- Normal Queries
- Sub Queries
- Co-related queries
- Nested queries
- Compound queries
- 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.
No comments:
Post a Comment