1. Difference b/w procedure and function?
A procedure may
return (one or more values using OUT & INOUT Parameters) or may not return
a value. But a function has to return a single value and has the return clause
in its definition. Function can be called in select statements but procedure
can only be called in a pl/sql block. Procedure's parameters can have IN
or OUT or INOUT parameters. But function's parameters can only have IN
parameters.
2. Difference b/w ROWID and ROWNUM?
ROWID : It gives the hexadecimal string representing the address
of a row.It gives the location in database where row is physically
stored. ROWNUM: It gives a sequence number in which rows are retrieved
from the database.
3. Give some examples of pseudo columns?
NEXTVAL, CURRVAL, LEVEL, SYSDATE
4. Difference b/w implicit cursor and explicit
cursor?
Implicit cursors are
automatically created by oracle for all its DML stmts. Examples of implicit
cursors: SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN; Explicit
cursors are created by the users for multi row select stmts.
5. How to create a table in a procedure or
function?
See the below piece of code: Since create stmt can be used
only at the sql prompt, we have used dynamic sql to create a table.
DECLARE
L_STMT VARCHAR2(100);
BEGIN
DBMS_OUTPUT.PUT_LINE('STARTING ');
L_STMT := 'create table
dummy1 (X VARCHAR2(10) , Y NUMBER)';
EXECUTE IMMEDIATE
L_STMT;
DBMS_OUTPUT.PUT_LINE('end ');
END;
The above piece of code can be written In procedure and function
DDL's can be used in function provided that function should be invoked in
Begin-End block not from Select statement.
6. Explain the usage of WHERE CURRENT OF clause in
cursors ?
Look at the following
pl/sql code:
DECLARE
CURSOR wip_cur IS
SELECT acct_no,
enter_date
FROM wip
WHERE enter_date < SYSDATE -7
FOR UPDATE;
BEGIN
FOR wip_rec IN wip_cur
LOOP
INSERT INTO acct_log
(acct_no, order_date)
VALUES (wip_rec.acct_no,
wip_rec.enter_date);
DELETE FROM wip
WHERE CURRENT OF wip_cur;
END LOOP;
END;
"WHERE CURRENT OF" has to be used in concurrence with
"FOR UPDATE" in the cursor select stmt.
"WHERE CURRENT OF" used in delete or update stmts means,
delete/update the current record specified by the cursor.
By using WHERE CURRENT OF, you do not have to repeat the WHERE
clause in the SELECT statement.
7. What is the purpose of FORUPDATE?
Selecting in FOR UPDATE mode locks the result set of rows in
update mode, which means that row cannot be updated or deleted until a commit
or rollback is issued which will release the row(s). If you plan on
updating or deleting records that have been referenced by a Select For Update
statement, you can use the Where Current Of statement.
8. What is RAISE_APPLICATION_ERROR?
The RAISE_APPLICATION_ERROR is a procedure defined by Oracle that
allows the developer to raise an exception and associate an error number and
message with the procedure other than just Oracle errors. Raising an Application Error With
raise_application_error
9.
DECLARE
num_tables NUMBER;
BEGIN
SELECT COUNT(*) INTO num_tables FROM USER_TABLES;
IF num_tables < 1000 THEN
/* Issue your own error code (ORA-20101) with your own error message.
Note
that you do not need to qualify raise_application_error with
DBMS_STANDARD
*/
raise_application_error(-20101, 'Expecting at least 1000 tables');
ELSE
NULL; -- Do the rest of the processing (for the non-error case).
END IF;
END;
/
The
procedure RAISE_APPLICATION_ERROR lets you issue
user-defined ORA- error messages from stored subprograms. That way,
you can report errors to your application and
avoid returning unhandled exceptions.
9. What is mutating error?
Mutating error occurs in the following scenario:
WHEN WE ARE UPDATING A TABLE
(TRIGGER WRITTEN ON A TABLE FOR UPDATE) AND AT THE SAME TIME TRYING TO
RETRIEVE DATA FROM THAT TABLE. IT WILL RESULT INTO MUTATING TABLE AND IT WILL
RESULT INTO MUTATING ERROR.
10. Can we have commit/rollback in DB
triggers?
Having Commit / Rollback inside a trigger defeats the standard of
whole transaction's commit / rollback all together. Once trigger execution is
complete then only a transaction can be said as complete and then only commit
should take place. If we still want to carry out some action which should be
initiated from trigger but should be committed irrespective of trigger
completion / failure we can have AUTONOMUS TRANSACTION. Inside Autonomous
transaction block we can have Commit and it will act as actual
commit.
11. Can we make the trigger an autonomous
transaction?
This makes all the difference because within the autonomous
transaction (the trigger), Oracle will view the triggering table as it was
before any changes occurred—that is to say that any changes are uncommitted and
the autonomous transaction doesn’t see them. So the potential confusion Oracle
normally experiences in a mutating table conflict doesn’t exist.
12. What is autonomous transaction?
Autonomous transaction means a transaction that is embedded in
some other transaction, but functions independently.
13. What is a REF Cursor?
The REF CURSOR is a data type in the Oracle PL/SQL
language. It represents a cursor or a result set in Oracle Database.
14. What is the difference between ref cursors and
normal pl/sql cursors?
Declare
type rc is
ref cursor;
cursor c is
select *
from dual;
l_cursor rc;
begin
if (
to_char(sysdate,'dd') = 30 ) then
open l_cursor
for select * from emp;
elsif (
to_char(sysdate,'dd') = 29 ) then
open l_cursor
for select * from dept;
else
open l_cursor
for select * from dual;
end if;
open c;
end;
Given that block of code you see perhaps the most "salient"
difference, no matter how many times you run that block The cursor C will
always be select * from dual. The ref cursor can be anything.
15. Is Truncate a DDL or DML statement? And why?
Truncate is a DDL
statement. Check the LAST_DDL_TIME on USER_OBJECTS after truncating your table.
TRUNCATE will automatically commit, and it's not rollback able. This changes
the storage definition of the object. That's why it is a DDL.
16. What are the actions you have to perform when
you drop a package?
If you rename a package, the other packages that use it will have
to be MODIFIED. A simple compilation of the new renamed package won't
do. If you have toad, go to the "used by" tab that will show you
the packages that call the package being renamed.
17. What is cascading triggers?
When a trigger fires, a SQL statement within its trigger action
potentially can fire other triggers, resulting in cascading triggers.
18. What are materialized views?
A materialized view is a database object that stores the results
of a query (possibly from a remote database). Materialized views are sometimes
referred to as snapshots.
19.
Example
If the materialized view will access remote database objects, we
need to start by creating a database link to the remote DB:
CREATE DATABASE LINK remotedb
CONNECT TO scott IDENTIFIED BY tiger
USING 'orcl';
Now we can create the materialized view to pull in data (in this
example, across the database link):
CREATE MATERIALIZED VIEW items_summary_mv
ON PREBUILT TABLE
REFRESH FORCE AS
SELECT a.PRD_ID, a.SITE_ID, a.TYPE_CODE, a.CATEG_ID,
sum(a.GMS) GMS,
sum(a.NET_REV) NET_REV,
sum(a.BOLD_FEE) BOLD_FEE,
sum(a.BIN_PRICE)
BIN_PRICE,
sum(a.GLRY_FEE)
GLRY_FEE,
sum(a.QTY_SOLD)
QTY_SOLD,
count(a.ITEM_ID) UNITS
FROM items@remotedb a
GROUP BY a.PRD_ID, a.SITE_ID, a.TYPE_CODE, a.CATEG_ID;
Materialized view logs:
Materialized view logs are used to track changes (insert, update
and delete) to a table. Remote materialized views can use the log to speed-up
data replication by only transferring changed records.
Example:
CREATE MATERIALIZED VIEW LOG ON items;
20.
Commonly occurring Errors in Reports?
Some of the errors are defined below
1. There Exists uncompiled unit: When the report is not compiled
before loading in the Oracle Applications.
2. Report File not found: When the rdf is not uploaded in proper
directory
3. Width or margin is zero: When the repeating frame is not within
proper frames
4. Not in proper group: When the repeating frame is not referred
to proper group
21.
What is the difference between Compile and
Incremental Compile in oracle reports?
In Full compile all the PL/SQL within the reports are compiled but
in incremental compile only the changed PL/SQL units are compiled.
When compiling the report for the first time, we should do the
full compilation and not the Incremental compile.
22.
How to compile Procedures and Packages?
ALTER <proc/package> <name>COMPILE;
No comments:
Post a Comment