FAQ’s in SQL & PL/SQL


FAQ’s in SQL & PL/SQL

 

What is PL/SQL?

PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.

 

What are the components of a PL/SQL Block?

Declarative part

Executable part

Exception part

 

What are the datatypes a available in PL/SQL?

Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN. Some composite data types such as RECORD & TABLE.

 

What are % TYPE and % ROWTYPE? What are the advantages of using these over datatypes?

% TYPE provides the data type of a variable or a database column to that variable.

% ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.

The advantages are:

I. Need not know about variable’s data type

ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.

 

What is difference between % ROWTYPE and TYPE RECORD?

% ROWTYPE is to be used whenever query returns an entire row of a table or view. TYPE RECORD is to be used whenever query returns columns of different table or views and variables.

 

Explain the two types of Cursors?

There are two types of cursors, Implicit Cursor and Explicit Cursor.

PL/SQL uses Implicit Cursors for queries.

User defined cursors are called Explicit Cursors. They can be declared and used.

 

What are the cursor attributes used in PL/SQL?

% ISOPEN – Used to check whether a cursor is open or not.

% ROWCOUNT – Used to check the number of rows fetched/updated/deleted.

% FOUND – Used to check whether cursor has fetched any row. True if rows are fetched.

% NOT FOUND – Used to check whether cursor has fetched any row. True if no rows are fetched.

 

What is a cursor for loop?

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.

 

What is the difference between implicit and explicit cursors?

An explicit cursor is declared opened and fetched from in the program block where as an implicit cursor is automatically generated for SQL statements that process a single row only.

 

What are the different types of joins available in Oracle?

Equi Join: When primary and foreign key relationship exists between the tables that are going to be joined.

Self Join: If comparison comes in a single table

Cartesian Join: When tables are joined without giving any join condition.

Inner Join: The resultant set includes all the rows that satisfy the join condition.

Outer Join: The resultant set includes the rows which doesn’t satisfy the join condition.  The outer join operator Plus sign (+) will be included in the join condition.

 

What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. 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.

 

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.

 

What is the difference between View and Materialized view?

Materialized view will not be refreshed every time you query the view. So to have good performance when data is not changed so rapidly, we use Materialized views rather than normal views which always fetches data from tables every time you run a query on it.

 

What is dynamic SQL?

Dynamic SQL allows you to construct a query, a DELETE statement, a CREATE TABLE statement, or even a PL/SQL block as a string and then execute it at runtime.

 

Can you use COMMIT in a trigger?

Yes but by defining an autonomous transaction.

 

What is the difference between anonymous blocks and stored procedures?

Anonymous block is compiled only when called. Stored procedure is compiled and stored in database with the dependency information as well. Former is PL/SQL code directly called from an application. Latter is stored in database. Former has declare statement. Latter doesn’t.

 

What is a package spec and package body? Why the separation?

Spec declares public constructs. Body defines public constructs, additionally declares and defines Private constructs.

Separation helps make development easier. Dependency is simplified. You can modify body without invalidating dependent objects.

 

What is Correlated Subquery?

Correlated Subquery is a subquery that is evaluated once for each row processed by the parent statement. Parent statement can be Select, Update or Delete.

 

What is Sequence?

Sequences are used for generating sequence numbers without any overhead of locking. Drawback is that after generating a sequence number if the transaction is rolled back, then that sequence number is lost.

 

What is SQL Deadlock?

Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource. First user needs a resource locked by the second user and the second user needs a resource locked by the first user. To avoid dead locks, avoid using exclusive table lock and if using, use it in the same sequence and use Commit frequently to release locks.

 

What is SQL*Loader?

SQL*Loader is a product for moving data in external files into tables in an Oracle database. To load data from external files into an Oracle database, two types of input must be provided to SQL*Loader: the data itself and the control file.

 

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.


Explain forward declaration used in functions?

A forward declaration means that modules (procedures and functions) are declared in advance of their actual body definition. This declaration makes that module available to be called by other modules even before the program’s body is defined. A forward declaration consists simply of the module header, which is just the name of the module followed by the parameter list (and a RETURN clause in case the module is a function), no more no less.

Forward declarations are required in one specific situation: mutual recursion.

 

What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. 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.

 

What is the difference between Truncate and Delete Commands?

 

TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back. WHERE clause can be used with DELETE and not with TRUNCATE.

 

What is the Purpose of HAVING Clause?

The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns.

 

What is INLINE View in SQL?

The inline view is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just as if the query was a table name.

 

While creating a sequence, what does cache and nocache options mean?

With respect to a sequence, the cache option specifies how many sequence values will be stored in memory for faster access.

 

Does the view exist if the table is dropped from the database?

Yes, in Oracle, the view continues to exist even after one of the tables (that the view is based on) is dropped from the database. However, if you try to query the view after the table has been dropped, you will receive a message indicating that the view has errors.

 

What is an Index?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

 

What types of index data structures can you have?

An index helps to faster search values in tables. The three most commonly used index-types are:

  • B-Tree: builds a tree of possible values with a list of row IDs that have the leaf value. Needs a lot of space and is the default index type for most databases.
  • Bitmap: string of bits for each possible value of the column. Each bit string has one bit for each row. Needs only little space and is very fast. (However, domain of value cannot be large, e.g. SEX(m,f); degree(BS,MS,PHD)
  • Hash: A hashing algorithm is used to assign a set of characters to represent a text string such as a composite of keys or partial keys, and compresses the underlying data. Takes longer to build and is supported by relatively few databases.

 

What is the difference between a “where” clause and a “having” clause?

“Where” is a kind of restriction statement. You use where clause to restrict all the data from DB. Where clause is used before result retrieving. But Having clause is using after retrieving the data. Having clause is a kind of filtering command.

 

Can a view be updated/inserted/deleted? If Yes – under what conditions?

A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.

 

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.

 

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.

 

What are the Lock types?

Share Lock: It allows the other users for only reading not to insert or update or delete.

Exclusive Lock: Only one user can have the privileges of insert or update and delete of particular object, others can only read.

Update Lock: Multiple user can read, update delete .

 

What is Pragma EXECPTION_INIT? Explain the usage?


The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error.
E.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

 

What is Raise_application_error?

Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue a user_defined error messages from stored sub-program or database trigger.

 

What are the modes for passing parameters to Oracle?

There are three modes for passing parameters to subprograms
IN – An In-parameter lets you pass values to the subprogram being called. In the subprogram it acts like a constant and cannot be assigned a value.
OUT – An out-parameter lets you return values to the caller of the subprogram. It acts like an initialized variable its value cannot be assigned to another variable or to itself.
INOUT – An in-out parameter lets you pass initial values to the subprogram being called and returns updated values to the caller.

 

What is the difference between Package, Procedure and Functions?

A package is a database objects that logically groups related PL/SQL types, objects, and Subprograms.

Procedure is a sub program written to perform a set of actions and can return multiple values.

Function is a subprogram written to perform certain computations and return a single value.
Unlike subprograms packages cannot be called, passed parameters or nested.

 

How do you make a Function and Procedure as a Private?

Functions and Procedures can be made private to a package by not mentioning their declaration in the package specification and by just mentioning them in the package body.

 

What is Commit, Rollback and Save point?

Commit – Makes changes to the current transaction permanent. It erases the savepoints and releases the transaction locks.

Savepoint –Savepoints allow to arbitrarily hold work at any point of time with option of later committing. They are used to divide transactions into smaller portions.

Rollback – This statement is used to undo work.

 

What is the difference between DDL, DML and DCL structures?

DDL statements are used for defining data. Ex: Create, Alter, Drop, Truncate, Rename.
DML statements are used for manipulating data. Ex: Insert, update, truncate.
DCL statements are used for to control the access of data. Ex; Grant, Revoke.
TCL statements are used for data saving. Ex; Commit, Rollback, Savepoint.

 

What is the difference between the snapshot and synonym?

A snapshot refers to read-only copies of a master table or tables located on a remote node. A snapshot can be queried, but not updated; only the master table can be updated. A snapshot is periodically refreshed to reflect changes made to the master table. In this sense, a snapshot is really a view with periodicity.

A synonym is an alias for table, view, sequence or program unit. They are of two types private and public.

 

What is the difference between data types char and varchar?

Char reserves the number of memory locations mentioned in the variable declarations, even though not used (it can store a maximum of 255 bytes). Where as Varchar does not reserve any memory locations when the variable is declared, it stores the values only after they are assigned (it can store a maximum of 32767 bytes).

 

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.

 

Tell some new features in PL/SQL in 10g?

-Regular expression functions REGEXP_LIKE, REGEXP_INSTR, REGEXP_REPLACE, and   REGEXP_SUBSTR

-Compile time warnings

– Conditional compilation

– Improvement to native compilation

– BINARY_INTEGER made similar to PLS_INTEGER

– Implicit conversion between CLOB and NCLOB

– Improved Overloading

– New datatypes BINARY_FLOAT, BINARY_DOUBLE

– Global optimization enabled

– PLS_INTEGER range increased to 32bit

– DYNAMIC WRAP using DBMS_DDL

 

What is Overloading in PL/SQL?

Overloading is an oops concept (Object Oriented Programming). By using the same name we can write any number of Procedures or functions in a package but either number of parameters in the procedure/function must vary or parameter datatype must vary.

 

What is a mutating and constraining table?

“Mutating” means “changing”. A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered “mutating” and raises an error since Oracle should not return data that has not yet reached its final state.

Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.

 

What is Nested Table?

A nested table is a table within a table. A nested table is a collection of rows, represented as a column within the main table. For each record within main table, the nested table may contain multiple rows.  In a sense, it’s a way of storing a one-to many relationship within one table.

 

What is Varying Array?

A varying array is a set of objects, each with the same data types. The size of the array is limited when it is created. (When the table is created with a varying array, the array is a nested table with a limited set of rows). Varying arrays also known as VARRAYS, allows storing repeated attributes in tables.

 

Give some most often used predefined exceptions?

a) NO_DATA_FOUND (Select Statement returns no rows)
b) TOO_MANY_ROWS (Single row Select statement returns more than 1 row)
c) INVALID_CURSOR (Illegal cursor operations occurred)
d) CURSOR_ALREADY_OPEN (If cursor is opened & we are trying to reopen it)
e) INVALID_NUMBER (Conversion of Character to number fails)
f) ZERO_DIVIDE
g) DUP_VAL_ON_INDEX (Attempted to insert a duplicate value)

 

Give some important Oracle supplied packages?

DBMS_SQL: It is used to write Procedures & Anonymous blocks that use Dynamic SQL.

DBMS_JOB: Using it, we can submit PL/SQL programs for execution, execute PL/SQL programs on a schedule, identify when programs should run, remove programs from the schedule & suspend programs from running.

DBMS_OUTPUT: This package outputs values & messages from any PL/SQL block.

UTL_FILE: With this package, you can read from & write to Operating system files

UTL_HTTP: This package allows to make HTTP Requests directly from the database.

 

What is Instead Of Trigger?

This trigger is used to perform DML operation directly on the underlying tables, because a view cannot be modified by normal DML Statements if it contains joins or Group Functions. These triggers are Only Row Level Triggers. The CHECK option for views is not enforced when DML to the view are performed by Instead of Trigger.

 

What is the Sequence of Firing Database Triggers?

a) Before Row Level Trigger
b) Before Statement Level Trigger
c) After Row Level Trigger
d) Statement Operation
e) After Statement Level Trigger

 

What is the Difference between PL/SQL Table & Nested Table?

PL/SQL Table: Index by Tables are not Stored in Database.
Nested Table: Nested Tables are Stored in Database as Database Columns.

 

What is the Difference between Nested Table & Varray?

Nested Table
a) This are Sparse

b) We can Delete its Individual Elements
c) It do not have an Upper Boundary
d) This are Stored in System Generated Table

Varray
a) This are Dense

b) We cannot Delete its Elements
c) This are Fixed Size & always need to specify the size
d) These are Stored in Tablespaces

 

What are the various SQL Statements?

a) Data Retrieval: Select
b) DML: Insert, Update, Delete
c) DDL: Create, Alter, Drop, Rename, Truncate
d) Transaction Control: Commit, Rollback, Savepoint
e) DCL: Grant, Revoke
f) Session Control: Alter Session, Set Role
g) System Control: Alter System
h) Embedded SQL Statements: Open, Close, Fetch & Execute.

What is Rowid?

It is a Hexadecimal Representation of a Row in a Table. Rowid can only be Changed if we ‘Enable Row Movement’ on a Partitioned Table. Rowid’s of Deleted Rows can be Reused if Transaction is Committed.

 

What is Partitioning?

It Enables Tables & Indexes or Index-Organized tables to be subdivided into smaller manageable Pieces & these each small Piece is called Partition.
They are of following Types:
a) Range Partitioning
b) Hash Partitioning
c) List Partitioning
d) Composite Range-Hash Partitioning

What is a Cluster?

A cluster provides an optional method of storing table data. A cluster is comprised of a group of tables that share the same data blocks, which are grouped together because they share common columns and are often used together. For example, the EMP and DEPT table share the DEPTNO column. When you cluster the EMP and DEPT, Oracle physically stores all rows for each department from both the EMP and DEPT tables in the same data blocks. You should not use Clusters for tables that are frequently accessed individually.

 

What is the Difference between Nested Subquery & Correlated Subquery?

Nested Subquery
a) Inner Query runs first and executes once, returning values which are to be used by the Main query or outer query

b) Outer query is driver by Inner Query

Correlated Subquery
a) A Correlated Subquery or Inner Query execute once for each candidate row considered by outer query

b) Inner Query is Driven by Outer Query

 

What is the Difference between Translate & Replace?

Translate function converts each character in String with specified one whereas Replace function replaces part of the string in continuity by another sub-string.

 

Multi-Org or multiple organization access (MOAC) in R12


Multi-Org or multiple organization access (MOAC) in R12

What is MOAC?

Multi-Org or multiple organization access (MOAC) is basically an ability to access multiple operating units from a single application responsibility.

Why it has been created?

Prior to R12, end users use to toggle / switch / change responsibilities in order to do transactions (like invoice / payment processing in AP) in different operating units. This is a very time consuming and inefficient way of recording transactions when you have 100s of operating units specially Internet based organizations who have worldwide operations in almost all the countries.

To address this, a new feature in R12 has been introduced in which user can switch between operating units within a responsibility something similar to “Change Organization” feature in inventory. Prior to R12, user would have to switch responsibilities in order to enter transactions in respective operating units (tagged to the responsibility).

What are its advantages?

  • Multi-Org Access Control (MOAC) enables companies that have implemented a Shared Services operating model to efficiently process business transactions by allowing them to access, process and report on data for an unlimited number of operating units within a single applications responsibility.
  • This increases the productivity of Shared Service Centers, as users no longer have to switch application responsibilities when processing transactions for multiple operating units at a time.
  • Ability to view data from multiple operating units from a single responsibility, gives users more information. This enables them to make better decisions.

The following SQL will dump out the Security Profiles and Operating Unit Names assigned to them.

SELECT   psp.SECURITY_PROFILE_NAME,
         psp.SECURITY_PROFILE_ID,
         hou.NAME,
         hou.ORGANIZATION_ID
FROM     PER_SECURITY_PROFILES psp,
         PER_SECURITY_ORGANIZATIONS pso,
         HR_OPERATING_UNITS hou
WHERE    pso.SECURITY_PROFILE_ID = psp.SECURITY_PROFILE_ID
         AND pso.ORGANIZATION_ID = hou.ORGANIZATION_ID;

There are three Profile Options you need to be aware of related to Multi-Org that should be set at the Responsibility Level.

  • MO: Security Profile– Always evaluated first.
  • MO: Operating Unit– Secondary priority being evaluated after ‘MO: Security Profile’
  • MO: Default Operating Unit– Sets the default Operating Unit for transactions when running under a Security Profile.

How it is done in R12?

In Release 12, one creates a Security Profile and assigns as many operating units as you required. One can tie that security profile to a single responsibility using a profile option called MO: Security Profile. For example, you could assign the security profile to the EMEA Payables responsibility to allow that responsibility to process invoices across all operating units.

In Release 12, define a security profile in HR using the Security profile form or the Global Security profile form, and assign all of the operating units that one would want a responsibility to access. The one needs to run a concurrent request called “Run Security List Maintenance” from HR which will make those security profile available and allow one to assign them to a responsibility via a profile option called MO: Security Profile.

One can define another profile option called MO: Default Operating Unit which is optional and allows one to specify a default operating unit that will be the default when you open different subledger application forms.

My First Hello World Page in OA Framework!


My First Hello World Page in OA Framework!

If you are new to OAF Development and want to create a new Hello World Page, then this tutorial will help you. For that first you need to do a Setup to use JDeveloper to develop and run your OA Pages.

You can refer here for that.

After the Setup is completed, you are all set to run your first OA Page. Oracle itself has created the Hello World page for you! Just go through the below steps to run the page.

1] Open Oracle JDeveloper and go to File > Open. Open the “toolbox.jws” file from the location \jdevhome\jdev\myprojects.

2] Right click on Tutorial and then click on Project properties.

3] Go to Oracle Applications > Database Connections tab

4] Click on New to create a new database connection. Choose the Connection Type as Oracle (JDBC).

5] Give your Oracle Apps database username and password.

6] Choose the thin driver and give Host Name, Port and SID details of your Oracle Application Database Server. You can get the details in your tnsnames.ora file.

7] After that click on Test Connection button to check if the JDeveloper can connect to the specified database or not. If the status is success then go ahead or check the above settings if you get different status.

8] Go to Run Options and select OADiagnostic and OADeveloperMode.

9] In the Runtime Connection tab, add the path of your DBC file name and give your Oracle Apps username and password. Click Ok. Also add Application Short Name as AK and Responsibility Key as FWK_TBX_TUTORIAL.

10] Go to Oracle Application Front end and add the responsibilities “OA Framework ToolBox Tutorial” & “OA Framework ToolBox Tutorial Labs” to the user that you have added on the Runtime Connection tab above.

11] Now right click on Tutorial and Rebuild the project. You should get zero errors. If you get few warnings it is ok.

12] Now go to toolbox > Tutorial > Web Content and Run the test_fwktutorial.jsp page.

13] Click on Hello, World!


14] Here is your Hello World page.

You might get errors while running the project. Here I have listed such few errors and their solutions.

Error: oracle.apps.fnd.framework.OAException: Application: ICX, Message Name: Could not find the specified responsibility.

Solution :

1 . Check that the user name and password are correct in the project settings.(Make sure to give Application username and password.)

2. Check the Responsibility key and Application short name.

3. Check whether the user name given in point #1 is attached to the responsibility mentioned in the point #2.

4. Check your dbc file for correct settings.

Error: oracle.apps.fnd.framework.OAException: Application: FND, Message Name: FND_GENERIC_MESSAGE. Tokens: MESSAGE = java.lang.NullPointerException;

Solution :

Set profile “Sign-On:Notification” to “No” at site level.

Discoverer 10g Installation steps


Discoverer 10g Installation steps

Oracle Business Intelligence 10g Release 2, a key component of Oracle Application Server 10g Release 2, is an integrated business intelligence solution supporting intuitive ad hoc query, reporting, analysis, and web publishing. Discoverer 10.1.2 is tightly integrated with Oracle E-Business Suite Release 12.  Release 12 users can use Discoverer to analyze data from selected business areas in Financials, Operations, and Human Resources etc.

The following are the Discoverer 10g Installation steps for Oracle R12 environment.

1] Install Oracle Business Intelligence Tools 10g (10.1.2.0.2) using below link

http://www.oracle.com/technology/software/products/ids/htdocs/101202winsoft.html


First right click on the zip file (as_windows_x86_bi_tools_101202). Extract all to a directory on your PC. Click on the Extracted folder which has the same name (as_windows_x86_bi_tools_101202). Click on the 10g set up icon and follow only the default settings. This installation creates a home “BIToolsHome_1” (If Default location is selected during install).

2] Apply the latest certified Discoverer Plus and Viewer Patches

To upgrade to Oracle Discoverer 10.1.2.2 – Apply the patch: 4960210

To upgrade to Oracle Discoverer 10.1.2.3 – Apply the patch: 5983622

Follow the installation instructions provided in the patch README to install the patch on your Discoverer 10g Server and to check supported operating systems.

3] Copy Database Connection file

Discoverer needs access to the Database Connection (dbc) file for the database you wish to connect to. A dbc file is a text file which stores all the information required to connect to a particular database.

First identify the Oracle Home for Discoverer. For example: If you could find dis51usr.exe under the directory D:\oracle\BIToolsHome_1\bin. Then the Oracle Home is D:\oracle\BIToolsHome_1

Create a folder named “secure” in the ORACLE_HOME directory (i.e. D:\oracle\BIToolsHome_1\Secure).

Then copy the dbc file from the $FND_SECURE directory of the E-Business Suite Release 12 instance you are setting up Discoverer with to the Discoverer 10.1.2 “ORACLE_HOME\secure” directory you just created.

Save the dbc file in lowercase.

4] Update tnsnames.ora file

On your standalone Oracle Business Intelligence Server 10g Release 2 node, update the file ORACLE_HOME/network/admin/tnsnames.ora and include the tnsnames entry to connect to your Oracle E-Business Suite Release 12 database. Use the same entry as exists in the tnsnames.ora file on your Oracle E-Business Suite Release 12 application tier server node.

5] Set the Environment

Discoverer requires Windows Environment variables to dynamically retrieve the location of the .dbc file on the PC.

Open Windows Control Panel and double click on the System icon. Select the Environment tab and create two new System Variables.

FND_SECURE = ORACLE_HOME\Secure
FND_TOP= ORACLE_HOME

Note: The Oracle Home is the one identified in step 3.

6] Restart the PC and you should be able to connect to Discoverer in an Oracle Applications mode.

Note: When you are connecting to Discoverer for the first time, then Goto Tools > Options. Check the proper EUL in the Connection Tab.

For More Detailed Information Please refer the below metalink note:

Using Discoverer 10.1.2 with Oracle E-Business Suite Release 12 [ID 373634.1]

How to run Discoverer Reports in Multi-Org Environment?


How to run Discoverer Reports in Multi-Org Environment?

When you are working in R12 MOAC environment, sometimes discoverer reports which are based on some context based view will not give data. Then do the following setup to make the discoverer reports work in MOAC environment.

1)      Create a Security Profile in HR

1)      Run concurrent program Security List Maintenance

1)      Tag this profile to MO: Security Profile option at responsibility level.

1)      Update profile option “Initialization SQL Statement – Custom” with following pl/sql block at responsibility level.

begin if (fnd_profile.value(‘XLA_MO_SECURITY_PROFILE_LEVEL’)= 0) then mo_global.init(‘S’); else GL_SECURITY_PKG.init(); mo_global.init(‘M’); end if; end;

When multiple organizations are included in Security Profile: TEST SECURITY PROFILE, This profile is attached to resp Discoverer EUL Management.

 

Initial Setup in JDeveloper for OAF Development


Initial Setup in JDeveloper for OAF Development

 

If you want to do some OAF Development in JDeveloper for the first time, you will need to do the following things.

1] Download JDeveloper Patch

Based on your instance release level, check out the version of JDeveloper to use using below link.

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=416708.1

You can identify the OA Framework version in your instance by activating diagnostics and click the “About This Page” from any OAF page. Click the “Technology Components” tab. The OA Framework version in the top row of the table can then be matched to the JDeveloper Patch.

Download the JDeveloper Patch.

2] Extract the JDeveloper patch in a directory say D:\DevSuiteHome_1\jdev

The patch actually contains below three directories

  • jdevbin – Includes an extended version of the Oracle JDeveloper 10g executable and OA Framework class libraries.
  • jdevhome – Includes the OA Framework Toolbox Tutorial source and developer working area.
  • jdevdoc – Contains documentation.

3] Define an environment variable

Define an environment variable JDEV_USER_HOME for your local machine. This has to be set to jdevhome\jdev directory. In above example it has to be D:\DevSuiteHome_1\jdev\jdevhome\jdev.

My Computer Properties Advanced tab Environment Variables New

Variable: JDEV_USER_HOME

Value: D:\DevSuiteHome_1\jdev\jdevhome\jdev

4] Extract Tutorial.zip

Extract D:\DevSuiteHome_1\jdev \jdevbin\Tutorial.zip into D:\DevSuiteHome_1\jdev \jdevhome.

It will create following directories

D:\DevSuiteHome_1\jdev \jdevhome\jdev\myhtml

D:\DevSuiteHome_1\jdev \jdevhome\jdev\myprojects

5] Get the DBC file

Obtain the FND database connection (.dbc) file from the system administrator who installed the OA Framework database where you want to do your development.

For the instance to use, you can get the .dbc file from $FND_SECURE and put it in <JDEV_USER_HOME>\dbc_files\secure i.e. D:\DevSuiteHome_1\jdev\jdevhome\jdev \dbc_files\secure

6] Creating a Desktop Shortcut to JDeveloper

To facilitate launching JDeveloper, create a desktop shortcut to jdevbin\jdev\bin\jdevw.exe.

7] Configuring the Environment Encoding of JDeveloper

Confirm the environment encoding of your JDeveloper if it is appropriately set.

Go to Tools – Preferences – Environment – Encoding

If Encoding is not set to “UTF-8”, set it to “UTF-8”.

The initial setup is now complete!….Now you are ready to develop your first OAF page. For more information you can refer OAF Developer’s Guide.