Oracle Apps Questions

  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.
  1. 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.
  1. Give some examples of pseudo columns? NEXTVAL, CURRVAL, LEVEL, SYSDATE
  1. 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.
  1. 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);

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.

  1. Explain the usage of WHERE CURRENT OF clause in cursors ? Look at the following pl/sql code:

SELECT acct_no, enter_date
WHERE enter_date < SYSDATE -7
INSERT INTO acct_log (acct_no, order_date)
VALUES (wip_rec.acct_no, wip_rec.enter_date);

WHERE CURRENT OF wip_cur;

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.

  1. 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.
  1. 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 
    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 procedureRAISE_APPLICATION_ERRORlets you issue user-definedORA-error messages from stored subprograms. That way, you can report errors to your application and avoid returning unhandled exceptions.
  1. 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.

  1. 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.
  1. 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.
  1. What is autonomous transaction? Autonomous transaction means a transaction that is embedded in some other transaction, but functions independently.
  1. 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.
  1. What is the difference between ref cursors and normal pl/sql cursors?

if ( to_char(sysdate,’dd’) = 30 ) then
elsif ( to_char(sysdate,’dd’) = 29 ) then

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.

  1. 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.
  1. 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.
  1. What is cascading triggers? When a trigger fires, a SQL statement within its trigger action potentially can fire other triggers, resulting in cascading triggers.
  2. 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.
  3. 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

    Now we can create the materialized view to pull in data (in this example, across the database link):

    CREATE MATERIALIZED VIEW items_summary_mv

    SELECT  a.PRD_ID, a.SITE_ID, a.TYPE_CODE, a.CATEG_ID,

    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,

    GROUP BY  a.PRD_ID, a.SITE_ID, a.TYPE_CODE, a.CATEG_ID;

    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.

    CREATE MATERIALIZED VIEW LOG ON items;

  4. 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
  5. 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.
  6. How to compile Procedures and Packages?
    ALTER <proc/package> <name>COMPILE;

Some questions about basics of plsql/Apps

  • From an Employee table, how will you display the record which has a maximum salary?
  • What is the difference between the Primary and Foreign key?
  • How will you delete a particular row from a Table?
  • How will you select unique values from a list of records?
  • What is meant by Join? What are the different types of Joins available? Explain.
  • overloading of stored procedure is possible in oracle?
  • how to create table with in the procedure or function?
  • what is overloading procedure or overloading function ?
  • what is HASH join?
  • what is SCALAR Queries?
  • what is the use of HASH, LIST partitions?
  • <<labele>> declare a=10 b=20, begin some statements declare a=30 c=40 end; what is the A value in nested block?
  • cursor types? explain with example programs?
  • what is difference b/w pravite procedures and public procedures?
  • How to export the table data (this table having 18 million records) to .csv file. Please tell me is there any faster way to export the data.
  • How to get employee name from employee table which is the fiveth highest salary of the table
  • How would you split a string into seperate values. eg. col1 col2

List 2

What are the various types of Exceptions?

User defined and Predefined Exceptions.

Can we define exceptions twice in same block?

No.

What is the difference between a procedure and a function?

Functions return a single variable by value where as 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 ?

Yes.

Can you have two stored functions with the same name ?

Yes.

Can you call a stored function in the constraint of a table ?

No.

What are the various types of parameter modes in a procedure ?

IN, OUT AND INOUT.

What is Over Loading and what are its restrictions?

Over Loading means an object performing different functions depending upon the no. of parameters or the data type of the parameters passed to it.

Can functions be overloaded?

Yes.

Can 2 functions have same name & input parameters but differ only by return data type

What is the Diff between APPS Schema and other Schemas?

Apps schema contains only Synonyms we can’t create tables in apps schema, where as other schemas contains tables, & all the objects. Here only we will create the tables and giving grants on created tables. Almost all every time we will connect to apps schema only.

What is meant by Custom Top and what is the Purpose?

Custom Top is nothing but Customer Top, which is created for customer only. we can have multiple custom

tops based on client requirement. It is used to store developed & customized components. Whenever oracle corp applying patches it will over ride on all the modules except custom top. That’s why we will use custom top.

What is the Significance of US Folder?

It is nothing but language specification by default it is in american language. We can have multiple languages folders based on installed languages. from backend we can get it from

FND_LANGUAGES — COL –INSTALLED_FLAGNSTALLED,

B–BASE,

D–DISABLE

select language_code,nls_language from fnd_languages where installed_flag like ‘B’

Where did U find the Application short name and basepath names?

select basepath,application_short_name from fnd_application from the backend. From the from end we can get it Navigation Application Developer

Leave a comment