Oracle Apps Technical Interview Questions – Part 4

By | September 27, 2014

Following is List of articles for Oracle Apps Technical Questions

Alternatively, Go through the following Top 10 Oracle Apps Technical Questions


 

List of Oracle Apps Technical Interview Questions – Part 4

1. What is the SQL statement used to display the text of a procedure stored in database?
2. Can you use COMMIT in a trigger?
3. What is an autonomous transaction ?
4. What is Ref Cursor?
5. Can triggers be used on views? If so How?
6. How do you declare user defined Exception?
7. “UPDATE …..; CREATE TABLE E(….); ROLL BACK;” To which save point will the changes be Rolled Back?
8. what is External table?
9. What is global temporary table?
10. What is ROWID?

1. What is the SQL statement used to display the text of a procedure stored in database?
select text from dba_source where name = ‘Procedurename’

Back to top

2. Can you use COMMIT in a trigger?
Yes but by defining an autonomous transaction.

Back to top

3. What is an autonomous transaction ?
An autonomous transaction is an independent transaction that is initiated by another transaction (the parent transaction). An autonomous transaction can modify data and commit or rollback independent of the state of the parent transaction.

Back to top

4. What is Ref Cursor?
A: A ref cursor is a variable, defined as a cursor type, which will point to a cursor result. The advantage that a ref cursor has over a plain cursor is that is can be passed as a variable to a procedure or a function.

Ref Cursors are of 2 types: Weak and Strong.

Strong ref cursor (also called static structure type)

When return type included then it is called strong or static structure type
static ref cursor support different type of select statement but all of same structure ,but not necessary that the table should be same

Syntax:

type is ref cursor [return ];
syntax for open statement:

Weak ref cursor (also called dynamic structure type )

This ref cursor allows us to any type of select statement irrespective of data structure. i .e any table

Syntax:

open for select statement …….;

———–Strong Ref Cursor Example———————-
declare

type emprefcur is ref cursor return emp%rowtype;
ec emprefcur;
v_ec ec%rowtype;

begin

open ec for select * from emp;
loop

fetch ec into v_ec;
exit when ec%notfound;
dbms_output.put_line (v_Ec.empno);
dbms_output.put_line (v_ec.ename);

end loop;
close ec;

dbms_output.put_line (‘————————-‘):

open ec for select * from emp;
loop

fetch ec into v_ec;
exit when ec%notfound;
dbms_output.put_line (v_Ec.empno);
dbms_output.put_line (v_ec.ename);

end loop;
close ec;
end;

———–Weak Ref Cursor Example———————-

declare

type refcur is ref cursor;
xc refcur;
v_Ec emp%rowtype;
v_dc dept%rowtype;

begin

open xc for select * from emp;
loop

fetch xc into v_ec;
exit when xc%notfound;
dbms_output.put_line(v_ec.ename);
dbms_output.put_line (v_Ec.empno);

end loop;
close xc;
dbms_output.put_line (‘————————————–‘);

open xc for select * from dept;
loop

fetch xc into v_dc;
exit when xc%notfound;
dbms_output.put_line (v_dc.deptno);
dbms_output.put_line (v_dc.dname);
dbms_output.put_line (v_dc.loc);

end loop;
close xc;

end;

Back to top

5. Can triggers be used on views? If so How?
Yes only INSTEAD OF trigger can be used to modify a view.
CREATE OR REPLACE TRIGGER trigger_name
INSTEAD OF INSERT ON view name
begin … end;

Back to top

6. How do you declare user defined Exception?
DECLARE

ex_custom EXCEPTION;

BEGIN

RAISE ex_custom;

EXCEPTION  WHEN ex_custom THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

Back to top

7. “UPDATE …..; CREATE TABLE E(….); ROLL BACK;” To which save point will the changes be Rolled Back?
Updates done wouldn’t be Rolled Back as CREATE statement which is a DDL would issue a COMMIT after the creation of the table.

Back to top

8. what is External table?
External tables can be used to load flat files into the database.
Steps:

  • First create a directory say ext_dir
  • Second place the flat file (file.csv) in it
  • Third grant read/write access to it.

Then create the table as below:
create table erp_ext_table (

i Number,
n Varchar2(20),
m Varchar2(20)
)

organization external (

type oracle_loader
default directory ext_dir
access parameters (
records delimited by newline
fields terminated by ,
missing field values are null
)
location (file.csv)
)
reject limit unlimited;

Back to top

9. What is global temporary table?
Global temporary tables are session specific tables. The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction.

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
column1 NUMBER,
column2 NUMBER
) ON COMMIT DELETE ROWS;

In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved until the end of the session.
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
column1 NUMBER,
column2 NUMBER
) ON COMMIT PRESERVE ROWS;

Miscellaneous Features include:

* If the TRUNCATE statement is issued against a temporary table, only the session specific data is truncated. There is no affect on the data of other sessions.
* Data in temporary tables is stored in temp segments in the temp tablespace.
* Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
* Indexes can be created on temporary tables. The content of the index and the scope of the index is the same as the database session.
* Views can be created against temporary tables and combinations of temporary and permanent tables.
* Temporary tables can have triggers associated with them.
* Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.

Back to top

10. What is ROWID?
ROWID is a pseudo column attached to each row of a table. It is 18 characters long, blockno, rownumber are the components of ROWID

Back to top

Prev Oracle Apps Technical Interview Questions – Part 3                                             Next Oracle Apps Technical Interview Questions – Part 5

Related Posts:

Oracle HRMS Interview Questions
Question of the Day
9 Steps to become Oracle Apps Consultant
How to create backend logging mechanism
Steps for creating custom form in Oracle Apps
Clear Global Cache in Oracle Apps
Profile Values in Oracle Forms Personalization
2 simple Steps to make a responsibility read only
Step to Register Discoverer Report
Hierarchy of OLM Tables