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.
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.
Give some examples of pseudo columns?
NEXTVAL, CURRVAL, LEVEL, SYSDATE
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.
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.
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.
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.
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.
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.
What is autonomous transaction?
Autonomous transaction means a transaction that is embedded in some other transaction, but functions independently.
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.
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.
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.
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.
What is cascading triggers?
When a trigger fires, a SQL statement within its trigger action potentially can fire other triggers, resulting in cascading triggers.
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.
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;
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
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.
How to compile Procedures and Packages?
ALTER <proc/package> <name>COMPILE;
LINK : http://oracleapps4u.blogspot.in/2011/07/oracle-apps-interview-questions-and.html
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.
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.
Give some examples of pseudo columns?
NEXTVAL, CURRVAL, LEVEL, SYSDATE
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.
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.
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.
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.
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.
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.
What is autonomous transaction?
Autonomous transaction means a transaction that is embedded in some other transaction, but functions independently.
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.
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.
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.
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.
What is cascading triggers?
When a trigger fires, a SQL statement within its trigger action potentially can fire other triggers, resulting in cascading triggers.
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.
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;
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
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.
How to compile Procedures and Packages?
ALTER <proc/package> <name>COMPILE;
LINK : http://oracleapps4u.blogspot.in/2011/07/oracle-apps-interview-questions-and.html
