91. What are the PL/SQL Statements used in cursor processing
?
DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record
types, CLOSE cursor name.
92. What are the cursor attributes used in PL/SQL ?
%ISOPEN - to check whether cursor is open or not. % ROWCOUNT - number of
rows featched/updated/deleted. % FOUND - to check whether cursor has fetched
any row. True if rows are featched. % NOT FOUND - to check whether cursor has
featched any row. True if no rows are featched. These attributes are proceded with
SQL for Implict Cursors and with Cursor name for Explict Cursors.
93. What is a cursor for loop ?
Cursor For Loop is a loop where oracle
implicitly declares a loop variable, the loop index that of the same record
type as the cursor's record.
Cursor for loop implicitly declares
%ROWTYPE as loop index, opens a cursor, fetches rows of values from active set
into fields in the record and closes when all the records have been processed.
eg. FOR emp_rec IN C1 LOOP salary_total := salary_total
+emp_rec sal; END LOOP;
94. What will happen after commit statement ?
Cursor C1 is Select empno, ename from emp; Begin open C1; loop Fetch C1
into
eno.ename; Exit When C1 %notfound;-----commit; end loop; end;
The cursor having query as SELECT .... FOR UPDATE gets closed after COMMIT/ROLLBACK.
The cursor having query as SELECT.... does not get closed even after COMMIT/ROLLBACK.
95. Explain the usage of WHERE CURRENT OF clause in cursors
?
WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the
latest row fetched from a cursor.
96. How many types of database triggers can be specified on
a table ? What are they ?
Insert Update Delete. Before Row o.k. o.k. o.k. After Row o.k. o.k. o.k.
Before Statement o.k. o.k. o.k. After Statement o.k. o.k. o.k. If FOR EACH ROW
clause is specified, then the trigger for each Row affected by the statement.
If WHEN clause is
specified, the trigger fires according to the retruned boolean value.
97. Is it possible to use Transaction control Statements
such a ROLLBACK or COMMIT in Database Trigger ? Why ?
It is not possible. As triggers are defined for each table, if you use
COMMIT or ROLLBACK in a trigger, it affects logical transaction processing.
98. What are two virtual tables available during database
trigger execution ?
The table columns are referred as OLD.column_name and NEW.column_name.
For triggers related to INSERT only NEW.column_name values only available. For
triggers related to UPDATE only OLD.column_name NEW.column_name values only
available. For triggers related to DELETE only OLD.column_name values only
available.
99. What happens if a procedure that updates a column of
table X is called in a database trigger of the same table ?
Mutation of table occurs.
100. Write the order of precedence for validation of a column
in a table ?
I. done using Database triggers. ii. done using Integarity Constraints.
I & ii. Exception :
101. What is an Exception ? What are types of Exception ?
Exception is the error handling part of PL/SQL block. The types are
Predefined and user_defined. Some of Predefined execptions are.
CURSOR_ALREADY_OPEN.DUP_VAL_ON_INDEX. NO_DATA_FOUND. TOO_MANY_ROWS.
INVALID_CURSOR.
INVALID_NUMBER. LOGON_DENIED. NOT_LOGGED_ON. PROGRAM-ERROR. STORAGE_ERROR.
TIMEOUT_ON_RESOURCE. VALUE_ERROR. ZERO_DIVIDE. OTHERS.
102. What does pragma mean to Oracle? OR What
is Pragma EXECPTION_INIT ? Explain the usage ?
A pragma is
simply a compiler directive, a method to instruct the compiler to perform some
compilation option.
The PRAGMA EXECPTION_INIT tells the
complier to associate an exception with an oracle error. To get an error
message of a specific oracle error. e.g. PRAGMA EXCEPTION_INIT (exception name,
oracle error number)
103. What is Raise_application_error ?
Raise_application_error is a procedure of package DBMS_STANDARD which
allows to issue an user_defined error messages from stored sub-program or
database trigger.
104. What are the return values of
functions SQLCODE and SQLERRM ? why are they important for PL/SQL developers?
SQLCODE returns the latest code of the
error that has occurred. SQLERRM returns the relevant error message of the
SQLCODE.
They can be used in exception handling
to report, or, store in an error log table, the error that occurred in the
code. These are
especially useful for the WHEN OTHERS
exception.
105. Where the Pre_defined_exceptions are stored ?
In the standard package.
Procedures, Functions & Packages ;
106. What are the modes of parameters that can be passed to
a procedure ?
IN,OUT,IN-OUT parameters.
107. What are the two parts of a procedure ?
Procedure Specification and Procedure Body.
108. Give the structure of the procedure ?
PROCEDURE name (parameter list.....)
is
local variable declarations
BEGIN
Executable statements.
Exception.
exception handlers
end;
109. Give the structure of the function ?
FUNCTION name (argument list .....)
Return datatype is
local variable declarations
Begin
executable statements
Exception
execution handlers
End;
110. Explain how procedures and functions are called in a
PL/SQL block ?
Function is called as part of an expression. sal := calculate_sal
('a822'); procedure is called as
a PL/SQL statement calculate_bonus ('A822');
111. What is Overloading of procedures ? OR
What is Over Loading and what are
its restrictions ?
OverLoading means an object performing
different functions depending upon the no. of
parameters or the data type of the
parameters passed to it.
The Same procedure name is repeated
with parameters of different datatypes and parameters
in different positions, varying number of
parameters is called overloading of procedures.
e.g. DBMS_OUTPUT put_line
112. What is difference between a Cursor declared in a
procedure and Cursor declared in a package specification ?
A cursor declared in a package specification is global and can be
accessed by other procedures or procedures in a package. A cursor declared in a
procedure is local to the procedure that can not be accessed by other
procedures.
113. How packaged procedures and functions are called from
the following?
a. Stored procedure or anonymous block b. an application
program such a PRC *C,
PRO* COBOL, c. SQL *PLUS
a. PACKAGE NAME.PROCEDURE NAME (parameters);
variable := PACKAGE NAME.FUNCTION NAME (arguments);
EXEC SQL EXECUTE
b. BEGIN
PACKAGE NAME.PROCEDURE NAME (parameters)
variable := PACKAGE NAME.FUNCTION NAME (arguments);
END;
END EXEC;
c. EXECUTE PACKAGE NAME.PROCEDURE if the procedures does not have any
out/in-out parameters. A function can not be called.
114. Name the tables where characteristics of Package,
procedure and functions are stored ?
User_objects, User_Source and User_error.
115. Give the two types of tables involved in producing a
star schema and the type of data they hold.
Fact tables and dimension tables. A fact table contains measurements
while dimension tables will contain data that will help describe the fact
tables.
116. What type of index should you use on a fact table?
A Bitmap index.
117. Give two examples of referential integrity constraints.
A primary key and a foreign key.
118. A table is classified as a parent table and you want to
drop and recreate it. How would you do this without affecting the children
tables?
Disable the foreign key constraint to the parent, drop the table,
re-create the table, enable the foreign key constraint.
119. What command would you use to encrypt a PL/SQL
application?
WRAP
120. Explain the use of table functions.
Table functions are designed to return a set of rows through PL/SQL
logic but are intended to be used as a normal table or view in a SQL statement.
They are also used to pipeline information
in an ETL process.
121. You have just compiled a PL/SQL
package but got errors, how would you view the errors?
SHOW ERRORS
122. How do you set the number of
lines on a page of output? The width?
The SET command in SQLPLUS is used to
control the number of lines generated per page and the width of those lines,
for example SET PAGESIZE 60 LINESIZE 80 will generate reports that are 60 lines
long with a line width of 80 characters. The PAGESIZE and LINESIZE options can
be shortened to PAGES and LINES.
123. How do you prevent output from
coming to the screen?
The SET option TERMOUT controls output
to the screen. Setting TERMOUT OFF turns off screen output. This option can be
shortened to TERM.
124. How do you prevent Oracle from
giving you informational messages during and after a SQL statement execution?
The SET options FEEDBACK and VERIFY
can be set to OFF.
125. How do you generate file output
from SQL?
By use of the SPOOL command
126. How can variables be passed to
a SQL routine?
By use of the & symbol. For
passing in variables the numbers 1-8 can be used (&1, &2,...,&8) to pass the values after the command into the
SQLPLUS session. To be prompted for a specific variable, place the ampersanded
variable in the code itself:
"select * from dba_tables where
owner=&owner_name;" . Use of double ampersands tells SQLPLUS to
resubstitute the value for each subsequent use of the variable, a single
ampersand will cause a reprompt for the value unless an ACCEPT statement is
used to get the value from the user.
127. You want to include a carriage
return/linefeed in your output from a SQL script, how can you do this?
The best method is to use the CHR()
function (CHR(10) is a return/linefeed) and the concatenation function
"||". Another method, although it is hard to document and isn?t
always portable is to use the return/linefeed as a part of a quoted string.
128. How can you call a PL/SQL
procedure from SQL?
By use of the EXECUTE (short form
EXEC) command.
129. How do you execute a host
operating system command from within SQL?
By use of the exclamation point
"!" (in UNIX and some other OS) or the HOST (HO) command.
130. You want to use SQL to build
SQL, what is this called and give an example
This is called dynamic SQL. An example
would be:
set lines 90 pages 0 termout off
feedback off verify off
spool drop_all.sql
select ?drop user ?||username||?
cascade;? from dba_users
where username not in
("SYS?,?SYSTEM?);
spool off
Essentially you are looking to see
that they know to include a command (in this case DROP USER...CASCADE;) and
that you need to concatenate using the ?||? the values selected from the database.
131. What SQLPlus command is used to
format output from a select?
This is best done with the COLUMN
command.
132. You want to group the following
set of select returns, what can you group on?
Max(sum_of_cost), min(sum_of_cost),
count(item_no), item_no
The only column that can be grouped on
is the "item_no" column, the rest have aggregate functions associated
with them.
133. What special Oracle feature
allows you to specify how the cost based system treats a SQL statement?
The COST based system allows the use
of HINTs to control the optimizer path selection. If they can give some example
hints such as FIRST ROWS, ALL ROWS, USING INDEX, STAR, even better.
134. You want to determine the
location of identical rows in a table before attempting to place a unique index
on the table, how can this be done?
Oracle tables always have one
guaranteed unique column, the rowid column. If you use a min/max function
against your rowid and then select against the proposed primary key you can squeeze
out the rowids of the duplicate rows pretty quick. For example:
select rowid from emp e where e.rowid
> (select min(x.rowid) from emp x where x.emp_no = e.emp_no);
In the situation where multiple
columns make up the proposed key, they must all be used in the where clause.
135. What is a Cartesian product?
A Cartesian product is the result of an
unrestricted join of two or more tables. The result set of a three table
Cartesian product will have x * y * z number of rows where x, y, z correspond
to the number of rows in each table involved in the join. It is causes by
specifying a table in the FROM clause without joining it to another table.
136. You are joining a local and a
remote table, the network manager complains about the traffic involved, how can
you reduce the network traffic?
Push the processing of the remote data
to the remote instance by using a view to pre-select the information for the
join. This will result in only the data required for the join being sent
across.
137. What is the default ordering of
an ORDER BY clause in a SELECT statement?
Ascending
138. What is tkprof and how is it
used?
The tkprof tool is a tuning tool used
to determine cpu and execution times for SQL statements. You use it by first
setting timed_statistics to true in the initialization file and then turning on
tracing for either the entire database via the sql_trace parameter or for the
session using the ALTER SESSION command.
Once the trace file is generated you run the tkprof tool against the trace file
and then look at the output from the tkprof tool. This can also be used to
generate explain plan
output.
139. What is explain plan and how is
it used?
The EXPLAIN PLAN command is a tool to
tune SQL statements. To use it you must have an explain_table generated in the
user you are running the explain plan for. This is created using the utlxplan.sql
script. Once the explain plan table exists you run the explain plan command
giving as its argument the SQL statement to be explained. The explain_plan
table is then queried to see
the execution plan of the statement.
Explain plans can also be run using tkprof.
140. What is a mutating table error
and how can you get around it?
This happens with triggers. It occurs
because the trigger is trying to update a row it is currently using. The usual
fix involves either use of views or temporary tables so the database is
selecting from one while updating the other.
141. When is a declare statement needed
?
The DECLARE statement is used in
PL/SQL anonymous blocks such as with stand alone, non stored PL/SQL procedures.
It must come first in a PL/SQL stand alone file if it is used.
142. In what order should a
open/fetch/loop set of commands in a PL/SQL block be implemented if you use the
%NOTFOUND cursor variable in the exit when statement? Why?
OPEN then FETCH then LOOP followed by
the exit when. If not specified in this order will result in the final return
being done twice because of the way the %NOTFOUND is handled by PL/SQL.
143. How can you find within a
PL/SQL block, if a cursor is open?
Use the %ISOPEN cursor status
variable.
144. How can you generate debugging
output from PL/SQL?
Use the DBMS_OUTPUT package. Another
possible method is to just use the SHOW ERROR command, but this only shows
errors. The DBMS_OUTPUT package can be used to show intermediate results from
loops and the status of variables as the procedure is executed. The new package
UTL_FILE can also be used.
145. The SELECT INTO Statement is
most often used to create backup copies of tables or for archiving records.
SELECT column_name(s) INTO newtable
[IN externaldatabase] FROM source
SELECT column_name(s) INTO newtable
[IN externaldatabase] FROM source WHERE column_name operator value
146. What is the use of CASCADE
CONSTRAINTS?
When this clause is used with the DROP
command, a parent table can be dropped even when a child table exists.
147. Which system tables contain
information on privileges granted and privileges obtained?
USER_TAB_PRIVS_MADE,
USER_TAB_PRIVS_RECD
148. What is the advantage of
specifying WITH GRANT OPTION in the GRANT command?
The privilege receiver can further
grant the privileges he/she has obtained from the owner to any other user.
149. Which command executes the
contents of a specified file?
START or @.
150. Which command displays the SQL
command in the SQL buffer, and then executes it?
RUN.
151. What command is used to get
back the privileges offered by the GRANT command?
REVOKE.
152. Which date function is used to
find the difference between two dates? OR
Which date function returns number
value?
MONTHS_BETWEEN.
153. What operator performs pattern
matching?
LIKE operator.
154. What are the wildcards used for
pattern matching.?
_ for single character substitution
and % for multi-character substitution.
155. What operator tests column for
the absence of data?
IS NULL operator.
156. What are the privileges that
can be granted on a table by a user to others?
Insert, update, delete, select,
references, index, execute, alter, all.
157. Which function is used to find
the largest integer less than or equal to a specific value?
FLOOR.
158. What is the use of DESC in SQL?
DESC has two purposes. It is used to
describe a schema as well as to retrieve rows from table in descending order.
Explanation : The query SELECT * FROM
EMP ORDER BY ENAME DESC will display the output sorted on ENAME in descending
order.
159. What command is used to create
a table by copying the structure of another table?
CREATE TABLE .. AS SELECT command
Explanation: To copy only the
structure, the WHERE clause of the SELECT command should contain a FALSE
statement as in the following.
CREATE TABLE NEWTABLE AS SELECT * FROM
EXISTINGTABLE WHERE 1=2;
If the WHERE condition is true, then
all the rows or rows satisfying the condition will be copied to the new table.
160. What's an SQL injection?
SQL Injection is when form data
contains an SQL escape sequence and injects a new SQL query to be run.
161. What is the parameter
substitution symbol used with INSERT INTO command?
&
162. Which Oracle supplied package
can you use to output values and messages from database triggers, stored
procedures and functions within SQL*Plus?
1. DBMS_DISPLAY 2. DBMS_OUTPUT 3.
DBMS_LIST 4. DBMS_DESCRIBE
163. What occurs if a procedure or
function terminates with failure without being handled?
1. Any DML statements issued by the
construct are still pending and can be committed or rolled back.
2. Any DML statements issued by the
construct are committed
3. Unless a GOTO statement is used to
continue processing within the BEGIN section, the construct terminates.
4. The construct rolls back any DML
statements issued and returns the unhandled exception to the calling
environment.
164. Under which circumstance must
you recompile the package body after recompiling the package specification?
1. Altering the argument list of one
of the package constructs
2. Any change made to one of the
package constructs
3. Any SQL statement change made to
one of the package constructs
4. Removing a local variable from the
DECLARE section of one of the package constructs
165. Procedure and Functions are
explicitly executed. This is different from a database trigger. When is a
database trigger executed?
1. When the transaction is committed
2. During the data manipulation
statement
3. When an Oracle supplied package
references the trigger
4. During a data manipulation
statement and when the transaction is committed
166. How to access the current value
and next value from a sequence? Is it possible to access the current value in a
session before accessing next value?
Sequence name CURRVAL, sequence name
NEXTVAL. It is not possible. Only if you access next value in the session,
current value can be accessed.
167. What is a database link?
Database link is a named path through
which a remote database can be accessed.
168. What is referential integrity
constraint?
Maintaining data integrity through a
set of rules that restrict the values of one or more columns of the tables based on the values of primary key
or unique key of the referenced table.
169. What are the pre-requisites to
modify datatype of a column and to add a column with NOT NULL constraint?
- To modify the datatype of a column
the column must be empty.
- To add a column with NOT NULL
constrain, the table must be empty.
170.Can we define exceptions twice
in same block ?
No.
171.What
are the various types of Exceptions ? or How many types of Exceptions are
there?
There
are 2 types of exceptions. They are
a)
System Exceptions: e.g. When
no_data_found, When too_many_rows
b) User
Defined Exceptions e.g. My_exception exception When My_exception then
172.Can you have two functions with
the same name in a PL/SQL block ?
Yes.
173.Can you have two stored
functions with the same name ?
Yes.
174.Can you call a stored function
in the constraint of a table ?
No.
175.Can functions be overloaded ?
Yes.
176.Can 2 functions have same name
& input parameters but differ only by return datatype
No.
177.Why Create or Replace and not
Drop and recreate procedures ?
So that Grants are not dropped.
178.Can you pass parameters in
packages ? How ?
Yes. You can pass parameters to
procedures or functions in a package.
Oracle only allows columns to
be added to the end of an existing table. Example:
SQL> CREATE TABLE tab1 ( col1 NUMBER );
SQL> ALTER TABLE tab1 ADD (col2 DATE);
SQL> DESC tab1
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER
COL2 DATE
Nevertheless, some databases
also allow columns to be added to an existing table after a particular column
(i.e. in the middle of the table). For example, in MySQL the following syntax
is valid:
ALTER TABLE tablename ADD columnname
AFTER columnname;
Oracle does not support this
syntax. However, it doesn't mean that it cannot be done.
Workarounds:
1. Create a new table and copy
the data across.
SQL> RENAME tab1 TO tab1_old;
SQL> CREATE TABLE tab1 AS SELECT 0 AS col1, col1 AS col2 FROM tab1_old;
2. Use the DBMS_REDEFINITION
package to change the structure on-line while users are workining.
Here is another workaround:
create table emptest as select empno, 1 as id, 'x' as
emp_name, ename from emp;
In the code above, replace 1 as
id, 'x' as emp_name with your new columns.
180. How does one prevent Oracle
from using an Index?
In certain cases, one may want
to disable the use of a specific, or all indexes for a given query. Here are
some examples:
Adding
an expression to the indexed column
SQL>select count(*) from t where empno+0=1000;
COUNT(*)
----------
1
Execution Plan
--------------------------------------------- ----- --------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=3)
Specifying
the FULL hint to force full table scan
SQL>select /*+ FULL(t) */ * from t where empno=1000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO GRADE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
1000 Victor DBA 7839 20-MAY-03 11000 0 10 JUNIOR
Execution Plan
--------------------------------------------- ----- --------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=41)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=41)
Specifying
NO_INDEX hint
SQL>select /*+ NO_INDEX(T) */ count(*) from t where empno=1000;
COUNT(*)
----------
1
Execution Plan
--------------------------------------------- ----- --------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=3)
Using
a function over the indexed column
SQL>select count(*) from t where to_number(empno)=1000;
COUNT(*)
----------
1
Execution Plan
--------------------------------------------- ----- --------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=3)
181. Map/ concatenate several rows
to a column
This FAQ will demonstrate how
row values can be concatenated into a single column value (similar to MySQL's
GROUP_CONCAT function).Start by creating this function:
SQL> CREATE OR REPLACE FUNCTION rowconcat(q in VARCHAR2) RETURN VARCHAR2 IS
2 ret VARCHAR2(4000);
3 hold VARCHAR2(4000);
4 cur sys_refcursor;
5 BEGIN
6 OPEN cur FOR q;
7 LOOP
8 FETCH cur INTO hold;
9 EXIT WHEN cur%NOTFOUND;
10 IF ret IS NULL THEN
11 ret := hold;
12 ELSE
13 ret := ret || ',' || hold;
14 END IF;
15 END LOOP;
16 RETURN ret;
17 END;
18 /
Function created.
This function returns a string
result with the concatenated non-NULL values from a SQL statement. It returns
NULL if there are no non-NULL values.
Here is an example of how to
map several rows to a single concatenated column:
SQL> SELECT rowconcat('SELECT dname FROM dept') AS departments FROM dual;
DEPARTMENTS
--------------------------------------------------------------------------------
ACCOUNTING,RESEARCH,SALES,OPERATIONS
This example is more
interresting, it concatenates a column across several rows based on an aggregation:
SQL> col employees format a50
SQL> SELECT deptno,rowconcat('SELECT ename FROM emp a WHERE deptno='||deptno) AS Employees
FROM emp GROUP BY deptno /
DEPTNO EMPLOYEES
---------- --------------------------------------------------
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
20 SMITH,JONES,SCOTT,ADAMS,FORD
10 CLARK,KING,MILLER
182. How to generate a text graphs
(histograms) using SQL
SELECT d.dname AS "Department", LPAD('+', COUNT(*), '+') as "Graph" FROM emp e, dept d
WHERE e.deptno = d.deptno GROUP BY d.dname;
Sample output:
Department Graph
-------------- --------------------------------------------------
ACCOUNTING +++
RESEARCH +++++
SALES ++++++
In the above example, the value
returned by COUNT(*) is used to control the number of "*" characters
to return for each department. We simply pass COUNT(*) as an argument to the
string function LPAD (or RPAD) to return the desired number of *'s.
183. How can one see if somebody modified any code?
Code for stored
procedures, functions and packages is stored in the Oracle Data Dictionary. One
can detect code changes by looking at the LAST_DDL_TIME column in the
USER_OBJECTS dictionary view. Example:
SELECT OBJECT_NAME, TO_CHAR(CREATED, 'DD-Mon-RR HH24:MI') CREATE_TIME,
TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') MOD_TIME,STATUS FROM USER_OBJECTS
WHERE LAST_DDL_TIME > '&CHECK_FROM_DATE';
184. How can one search PL/SQL code for a string/ key
value?
The following query is
handy if you want to know where a certain table, field or expression is
referenced in your PL/SQL source code.
SELECT TYPE, NAME, LINE FROM USER_SOURCE WHERE UPPER(TEXT) LIKE '%&KEYWORD%';
185. How can one keep a history of PL/SQL code changes?
One can build a history of
PL/SQL code changes by setting up an AFTER CREATE schema (or database) level
trigger (available from Oracle 8.1.7). This way one can easily revert to
previous code should someone make any catastrophic
changes. Look at this example:
CREATE TABLE SOURCE_HIST -- Create history table
AS SELECT SYSDATE CHANGE_DATE, USER_SOURCE.*
FROM USER_SOURCE WHERE 1=2;
CREATE OR REPLACE TRIGGER change_hist -- Store code in hist table
AFTER CREATE ON SCOTT.SCHEMA -- Change SCOTT to your schema name
DECLARE
BEGIN
if DICTIONARY_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
'PACKAGE', 'PACKAGE BODY', 'TYPE') then
-- Store old code in SOURCE_HIST table
INSERT INTO SOURCE_HIST
SELECT sysdate, user_source.* FROM USER_SOURCE
WHERE TYPE = DICTIONARY_OBJ_TYPE
AND NAME = DICTIONARY_OBJ_NAME;
end if;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END;
/
show errors
186. How can I protect my PL/SQL source code?
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.plb
187. Can one print to the screen from PL/SQL?
One can use the
DBMS_OUTPUT package to write information to an output buffer. This buffer can
be displayed on the screen from SQL*Plus if you issue
the SET SERVEROUTPUT ON; command. For example:
set serveroutput on
begin
dbms_output.put_line('Look Ma, I can print from PL/SQL!!!');
end;
/
DBMS_OUTPUT is useful for
debugging PL/SQL programs. However, if you print too much, the output buffer
will overflow. In that case, set the buffer size to a larger value, eg.: set
serveroutput on size 200000
If you forget to set
serveroutput on type SET SERVEROUTPUT ON once you remember, and then EXEC
NULL;. If you haven't cleared the DBMS_OUTPUT buffer with the disable or enable
procedure, SQL*Plus will display the entire contents of the buffer when it
executes this dummy PL/SQL block.
188. Can one read/write files from PL/SQL?
Included in Oracle 7.3 is an
UTL_FILE package that can read and write operating system 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. Copy this example to get started:
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/tmp', 'myfile', 'w');
UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!\n');
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file or path not in INIT.ORA.');
END;
/
189. Can one call DDL statements from PL/SQL?
One can call DDL statements
like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the "EXECUTE
IMMEDATE" statement. Users running Oracle versions below 8i can look at
the DBMS_SQL package (see FAQ about Dynamic SQL).
begin
EXECUTE IMMEDIATE 'CREATE TABLE X(A DATE)';
end;
NOTE: The DDL statement in
quotes should not be terminated with a semicolon.
190. Can one use dynamic SQL statements from PL/SQL?
Starting from Oracle8i one can
use the "EXECUTE IMMEDIATE" statement to execute dynamic SQL and
PL/SQL statements (statements created at run-time). Look at these examples.
Note that statements are NOT terminated by semicolons:
EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)';
-- Using bind variables...
sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
-- Returning a cursor...
sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
191. What is the result of comparing NULL with NULL?
NULL is neither equal to NULL,
nor it is not equal to NULL. Any comparison to NULL is evaluated to NULL. Look
at this code example to convince yourself.
declare
a number := NULL;
b number := NULL;
begin
if a=b then
dbms_output.put_line('True, NULL = NULL');
elsif a<>b then
dbms_output.put_line('False, NULL <> NULL');
else
dbms_output.put_line('Undefined NULL is neither = nor <> to NULL');
end if;
end;
192. How often should one COMMIT in a PL/SQL loop? /
What is the best commit strategy?
Contrary to popular believe,
one should COMMIT less frequently within a PL/SQL loop to prevent ORA-1555
(Snapshot too old) errors. The higher the frequency of commit, the sooner the
extents in the rollback segments will be cleared for new transactions, causing
ORA-1555 errors.
To fix this problem one can
easily rewrite code like this:
FOR records IN my_cursor LOOP
...do some stuff...
COMMIT;
END LOOP;
to
FOR records IN my_cursor LOOP
...do some stuff...
i := i+1;
IF mod(i, 10000) THEN -- Commit every 10000 records
COMMIT;
END IF;
END LOOP;
If you still get ORA-1555
errors, contact your DBA to increase the rollback segments.
193. I can SELECT from SQL*Plus but not from PL/SQL.
What is wrong?
PL/SQL
respect object privileges given directly to the user, but does not observe
privileges given through roles. The consequence is that a SQL statement can
work in SQL*Plus, but will give an error in PL/SQL. Choose one of the following
solutions:
- Grant direct access on the
tables to your user. Do not use roles!
· GRANT select ON scott.emp TO my_user;
- Define your procedures with
invoker rights (Oracle 8i and higher);
- Move all the tables to one
user/schema.
194. Can one pass an object/table as an argument to a
remote procedure?
The only way the same
object type can be referenced between two databases is via a database link.
Note that it is not enough to just use the same type definitions. Look at this
example:
-- Database A: receives a PL/SQL table from database B
CREATE OR REPLACE PROCEDURE pcalled(TabX DBMS_SQL.VARCHAR2S) IS
BEGIN
-- do something with TabX from database B
null;
END;
/
-- Database B: sends a PL/SQL table to database A
CREATE OR REPLACE PROCEDURE pcalling IS
TabX DBMS_SQL.VARCHAR2S@DBLINK2;
BEGIN
pcalled@DBLINK2(TabX);
END;
/
195. Is there a limit on the size of a PL/SQL block?
Yes, the max size is not
an explicit byte limit, but related to the parse tree that is created when you
compile the code. 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';
196. How does one code a matrix/crosstab/pivot report in
SQL?
Newbies frequently ask how one
can display "rows as columns" or "columns as rows". Look at
these example crosstab queries (also sometimes called matrix or pivot queries):
SELECT * FROM (SELECT job, sum(decode(deptno,10,sal)) DEPT10, sum(decode(deptno,20,sal)) DEPT20,
sum(decode(deptno,30,sal)) DEPT30, sum(decode(deptno,40,sal)) DEPT40 FROM scott.emp GROUP BY job)
ORDER BY 1;
JOB DEPT10 DEPT20 DEPT30 DEPT40
--------- ---------- ---------- ---------- ----------
ANALYST 6000
CLERK 1300 1900 950
MANAGER 2450 2975 2850
PRESIDENT 5000
SALESMAN 5600
Here
is the same query with some fancy headers and totals:
SQL> ttitle "Crosstab Report"
SQL> break on report;
SQL> compute sum of dept10 dept20 dept30 dept40 total on report;
SQL>
SQL> SELECT * FROM (SELECT job,sum(decode(deptno,10,sal)) DEPT10, sum(decode(deptno,20,sal)) DEPT20,
sum(decode(deptno,30,sal)) DEPT30,sum(decode(deptno,40,sal)) DEPT40, sum(sal) TOTAL FROM emp
GROUP BY job) ORDER BY 1;
Mon Aug 23 page 1
Crosstab Report
JOB DEPT10 DEPT20 DEPT30 DEPT40 TOTAL
--------- ---------- ---------- ---------- ---------- ----------
ANALYST 6000 6000
CLERK 1300 1900 950 4150
MANAGER 2450 2975 2850 8275
PRESIDENT 5000 5000
SALESMAN 5600 5600
---------- ---------- ---------- ---------- ----------
sum 8750 10875 9400 29025
Here's
another variation on the theme:
SQL> SELECT DECODE(MOD(v.row#,3) ,1, 'Number: ' ||deptno ,2, 'Name: ' ||dname,0, 'Location: '||loc
) AS "DATA" FROM dept, (SELECT rownum AS row# FROM user_objects WHERE rownum < 4) v WHERE deptno = 30 /
DATA
--------------------------------------- ---------
Number: 30
Name: SALES
Location: CHICAGO
197. Can one select a random collection of rows from a
table?
The following methods can be
used to select a random collection of rows from a table:
The SAMPLE Clause
From Oracle 8i, the easiest way
to randomly select rows from a table is to use the SAMPLE clause with a SELECT
statement. Examples:
SELECT * FROM emp SAMPLE(10);
In the above example, Oracle is
instructed to randomly return 10% of the rows in the table.
SELECT * FROM emp SAMPLE(5) BLOCKS;
This example will sample 5% of
all formatted database blocks instead of rows.
This clause only works for
single table queries on local tables. If you include the SAMPLE clause within a
multi-table or remote query, you will get a parse error or "ORA-30561:
SAMPLE option not allowed in statement with multiple table references".
One way around this is to create an inline view on the driving table of the
query with the SAMPLE clause. Example:
SELECT t1.dept, t2.emp FROM (SELECT * FROM dept SAMPLE(5)) t1, emp t2 WHERE t1.dep_id = t2.dep_id;
If you examine the execution
plan of a "Sample Table Scan", you should see a step like this:
TABLE ACCESS (SAMPLE) OF 'EMP' (TABLE)
ORDER BY
dbms_random.value()
This method orders the data by
a by a random column number. Example:
SQL> SELECT * FROM (SELECT ename FROM emp ORDER BY dbms_random.value()) WHERE rownum <= 3 /
ENAME
----------
WARD
MILLER
TURNER
The ORA_HASH() function
The following example retrieves
a subset of the data in the emp table by specifying 3 buckets (0 to 2) and then
returning the data from bucket 1:
SELECT * FROM emp WHERE ORA_HASH(empno, 2) = 1
198. How do I display
row number with records?
Use the row-num pseudocolumn
with query, like
SQL> select rownum, ename from
emp;
199. if
the commission field is null, then the text “Not Applicable” should be
displayed, instead of blank space. How do you write the query?
The NVL
function only allows the same data type. But here’s the task:
SQL>
select nvl(to_char(comm.),’Not Applicable’) from emp;
200. Explain explicit cursor attributes.
There are four cursor attributes used in Oracle:
cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT,
cursor_name%ISOPEN
Explicit
Cursor:
1. %ISOPEN:
Return TRUE if cursor is open, FALSE otherwise.
2. %FOUND:
Return TRUE if record was fetched successfully, FALSE otherwise.
3. %NOTFOUND:
Return TRUE if record was not fetched successfully, FALSE otherwise.
4.
%ROWCOUNT: Returns number of records processed from the cursor.
No comments:
Post a Comment