How to kill an active session in Oracle apps

Hi, This post shows the queries to find an active sessions for an object and kill the session.

Note: Use the query to kill a session only if you have authorized to kill the session.

Query to see if the object has an active session:

SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name, session_id

FROM V$Locked_Object A, All_Objects B

WHERE A.Object_ID = B.Object_ID

and b.object_id =:p_object_id;

Query to find the sis and serial# based on session id that is fetched from the above query:

select * from v$session where sid = '2777';

Command to kill the session using sis and serial#:

alter system kill session '2777,23685';

Happy learning. Cheers!!

No organizations currently defined error in Oracle apps

Hi, this post shows how to fix the error "No organizations currently defined " in Oracle apps environment. This error usually occurs when a specific organization that we're trying to access is not defined.

Follow below steps to fix this error.

1. Go to the responsibility: Inventory

2. Navigate to Setup > Organization > Organizations Access.

3. Add the responsibility to the list - Responsibility Application and responsibility.

Getting errors is good in someway because they make us learn. Keep learning!!

Query to fetch users who have a specific responsibility attached to them in Oracle apps

Hi! This post shows you the query to fetch all the users who have a specific responsibility attached to them.

SELECT fu.user_name,

  frt.responsibility_name,

  TO_CHAR(furg.start_date,'DD-MON-YYYY') start_date,

  furg.end_date,

  fu.DESCRIPTION

FROM fnd_user fu ,

  fnd_user_resp_groups_direct furg ,

  fnd_responsibility_vl frt

WHERE fu.user_id                 = furg.user_id

AND frt.responsibility_id        = furg.responsibility_id

AND frt.application_id           = furg.responsibility_application_id

AND NVL(furg.end_date,sysdate+1) > sysdate

AND NVL(frt.end_date,sysdate +1) > sysdate

AND NVL(fu.end_date,sysdate  +1) > sysdate

AND frt.responsibility_name      ='Order Management Superuser';

Here, 'Order Management Superuser' is the responsibility and the query would fetch the users who have this responsibility attached to them.

Query to find the location of Application top in Oracle

Hi! This post shows the query to find the location of the application top in Oracle.

SELECT distinct fec.variable_name, fec.value

   FROM fnd_env_context fec

 WHERE fec.variable_name like '%ONT_TOP%' 

ORDER BY fec.variable_name;

Here, ONT_TOP is the application top. Similarly, other paths can be found by providing the application top name including the custom top.

How to fix invalid character in XML Error in Oracle apps R12

Hi, this post shows how to fix the invalid character in XML data error that may occur sometimes in RDF reports in Oracle apps. To fix the error, follow the below enlisted steps.

1) Run following query to determine the characterset:

select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET'

2) Set profile option FND: NATIVE CLIENT ENCODING to same value as the output of query in step 1.

--FND: NATIVE CLIENT ENCODING for XML data generation

If browser is not responding when loading XML data, recheck RDF then try fetching less data from query. If that works, recheck with all the data.

Cheers!!

How to compile package spec and body dynamically in shell script in Oracle

Hi, This post is to show how to compile package spec and package body in bash script.

Compile package spec:

pkg_spec=`ls *.pks -1|sed -e ''s/\.pks$//''`;

pkg_body=`ls *.pkb -1|sed -e ''s/\.pkb$//''`;

compile_spec()

{

sqlplus -s <user>/<password>@<host>:<port>/<SID> << EOF

WHENEVER SQLERROR EXIT SQL.SQLCODE;

SET LINES 255

SET PAGES 0

SET FEEDBACK OFF

SET ECHO OFF

SET PAGESIZE 0

SET TIMING OFF

SET SERVEROUTPUT ON

SET TERM ON

ALTER PACKAGE $pkg_spec COMPILE PACKAGE;

EOF

}

compile=`compile_spec`

echo $compile

if [ $? -ne 0 ]; then

  echo "error in compiling package spec"

  exit 1

fi

Here, <user> should be replaced with your username.

Eg: user12

Likewise, <password>, <host>, <port> and <SID> should also be replaced with their respective values.

Compile package body:

compile_body()

{

sqlplus -s <user>/<password>@<host>:<port>/<SID> << EOF

WHENEVER SQLERROR EXIT SQL.SQLCODE;

SET LINES 255

SET PAGES 0

SET FEEDBACK OFF

SET ECHO OFF

SET PAGESIZE 0

SET TIMING OFF

SET SERVEROUTPUT ON

SET TERM ON

ALTER PACKAGE $pkg_body COMPILE BODY;

EOF

}

PKB=`compile_body`

echo $PKB

if [ $? -ne 0 ]; then

  echo "error in compiling body"

  exit 1

fi  

Cheers!!

How to get the count of a query with Group by Clause in Oracle

Hi, This post shows you how to get the count of the query where group by clause is used in Oracle.

The below query is used to fetch sales order related data. The inner query contains the GROUP BY Clause and the outer query is used on top of it to get the count of the main query.

SELECT COUNT(*)  FROM 

(SELECT HCA.ACCOUNT_NUMBER "ACCOUNT_NUMBER"

      ,HP.PARTY_NAME "PARTY_NAME" 

      ,OOS.NAME "ORDER_SOURCE_NAME"

      ,COUNT(OOH.ORDER_NUMBER) "ORDER_COUNT"

 FROM OE_ORDER_HEADERS_ALL OOH,

      HZ_CUST_ACCOUNTS_ALL HCA,

      HZ_PARTIES HP,

      OE_ORDER_SOURCES OOS

WHERE OOH.ORDER_SOURCE_ID IN(1001, 13)             --custom order source IDs

  AND OOH.SOLD_TO_ORG_ID = HCA.CUST_ACCOUNT_ID

  AND HCA.PARTY_ID = HP.PARTY_ID

  AND TRUNC(OOH.CREATION_DATE) BETWEEN '01-JAN-2018' AND '31-JAN-2018'

  AND OOS.ORDER_SOURCE_ID = OOH.ORDER_SOURCE_ID

GROUP BY OOS.NAME,

HCA.ACCOUNT_NUMBER

        ,HP.PARTY_NAME

ORDER BY 3 DESC);

Any comments or queries? Hit them in the comment section below. Happy learning!!

Oracle Apps Technical Interview Questions - Part 2

Hi! This post is a continuation of interview questions on Oracle apps technical. Hope this helps whoever is reading - Click here for Part 1.

1. What is an alert in Oracle and what are the types of alerts?

An Alert in Oracle Apps works like a notification that notifies every time when a certain condition specified is met. There are 2 types of alerts in Oracle.

(i) Event Alerts - Every time when an event occurs, an event alert sends a notification.

For example, these alerts can be used whenever a row is inserted or gets updated in a particular table.

(ii) Periodic Alerts - These alerts can be triggered weekly monthly, yearly based on a condition.

2. What is a materialised view?

A materialized view Works like a snapshot, balances the data between a table and a materialised view.

3. What is the difference between a view and a materialized view in Oracle?

A view rerun the query each time it is executed where as a materialised view goes to the storage location and run from there without executing the query again, therefore results in faster retrieval of data.

4. How can we give the layout manually in Oracle apps?

fnd_request.add_layout (

template_appl_name  => 'Template Application',

template_code       => 'Template Code',

template_language   => 'en', --Use language from template definition

template_territory  => 'US', --Use territory from template definition

output_format       => 'PDF' --Use output format from template definition

);

5. How to submit a concurrent request from backend?

FND_REQUEST.SUBMIT_REQUEST (

                            application  =>  'ONT'-->Application short name

                           ,program      =>  'OEOIMP');-->Concurrent program short name

6. What is the difference between CHAR and VARCHAR in Oracle?

Both are used to store strings but CHAR datatype stores strings of fixed lengths and reserves space for null value where as VARCHAR can store strings of variable lengths and doesn't reserve space for null values.

7. What is the use of WITH clause in Oracle?

WITH Clause allows us to give a sub query block a name which is also known as sub query refactoring which can be referenced in several places within main query.

8. What is a collection and what are the types of collections in Oracle?

A collection is an ordered group of elements, all of the same type that helps in faster retrieval of data in Oracle.

Types of Collections:

Associative Array or Index by Table: Indexing can be done with strings, map.

Nested Tables: List, Variable size, Index starts with 1.

Varrays: Arrays, Pre-defined size, Index starts with 1, Cannot delete elements.

9. Name some analytical functions.

LEAD, LAG, RANK, DENSE_RANK, FIRST, LAST, LISTAGG, MATCH_RECOGNIZE.

10. Write a query which shows a running total.

select empno, sal, sum(sal) over(order by sal) as running_total from emp;

11. What do you mean by lexical parameter?

Lexical references are placeholders for text that is embedded in the SELECT statement.  Lexical references can be used to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.

12. What are transaction control statements in Oracle?

Transaction control statements are used to manage transactions in Oracle.

Examples are COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION.

13. What are the types of Sub queries:

Correlated Sub Query: Correlated sub queries depend on data provided by the outer query. This type of sub query also includes sub queries that use the EXISTS operator.

Inline sub query: A query that starts in the FROM clause of a another query is called an Inline sub query.

Row-based:

Single Row Sub queries:  A single row Sub query which returns single row output. They mark the usage of single row comparison operators, when used in WHERE conditions.

Multi-row Sub queries: A Sub query that returns multiple rows. They make use of multiple row comparison operators like IN, ANY, ALL. There can be sub queries returning multiple columns also.

14. Name some PO interface tables.

Interface Tables:

PO_HEADERS_INTERFACE

PO_LINES_INTERFACE

PO_DISTRIBUTIONS_INTERFACE

Error Table:

PO_INTERFACE_ERRORS

Base Tables:

PO_HEADERS_ALL

PO_LINES_ALL

PO_DISTRIBUTIONS_ALL

15. What is the difference between Org, Organization in Oracle apps?

ORG_ID works at operating unit level

ORGANIZATION_ID works at INVENTORY module level.

16. What is the command to delete a bursting file in server?

DELETE

FROM XDO_LOBS

WHERE LOB_CODE=<DATA_DEFINITION_CODE>

AND LOB_TYPE='BURSTING_FILE';

17. What is the difference between UNION and UNION ALL?

UNION merges the results from given queries/ tables where as UNION ALL retrieves the merged result including duplicates.

Performance wise, UNION ALL is preferred to UNION.

18. What is the command for page break in RTF template?

<?split-by-page-break:?>

19. What is an order hold and give an example?

A Hold is to stop an order, order line, or return line from continuing to progress through its life cycle. It can also be applied manually by the users but a user can not apply a hold if the order has been pick released.

Examples: Credit Checking Hold, Configurator Validation Hold, GSA, Violation Hold.

20. Name some UTL file commands.

UTL_FILE.FOPEN, 

UTL_FILE.GET, 

UTL_FILE.PUT, 

UTL_FILE.FCLOSE

21. What is the of NOCOPY hint in Oracle?

NOCOPY hint tells a procedure to use pass by reference instead of default pass by value.

Pass By Value : By default, it creates a temporary buffer ,copy the data from the parameter variable to that buffer and work on the temporary buffer during the lifetime of the procedure. When the procedure successfully gets completed, the contents of the temporary buffer are copied back into the parameter variable. In the event of an exception occurring, the copy back operation does not happen.

Pass By Reference : With the NOCOPY hint, it tells the compiler to use pass by reference, so no temporary buffer is needed and no copy forward and copy back operations happen. Instead, any modification to the parameter values are written directly to the parameter variable.

22. What is a hash cluster?

Hash cluster is a technique to store data in hash table and improve the performance of data retrieval. Hash function is applied on table row's cluster key value and store in hash cluster.

23. What is a table space?

Table space is a logical storage unit in Oracle. 

24. What is the difference between pre-select and pre query triggers in oracle forms?

A pre-query trigger fires before the query executes and fires once while you try to query the data. With the help of this trigger, you can modify the where clause part dynamically.

Pre-select query fires during the execute query and count query processing after Oracle forms construct the select statement to be issued, but before the statement is actually issued.

Note: Pre-query trigger fires before pre-select trigger. 

25. What is the use of control file in Oracle?

In Oracle, Control file is used for database recovery. the control file is also used to identify the database and redo log files that must be opened for database operation to go ahead, whenever an instance of an Oracle database begins.

26. What are the limitations of CHECK constraint?

The main limitation of CHECK constraint is that the condition should be a boolean expression evaluated using the values in the row being inserted or updated and it cannot contain sub queries.

27. How will you identify Oracle Database Software release?

Oracle follows a number of formats for every release. For example, Release 10.1.1.1.1 can be referred to as:

10: Major DB Release number

1: DB Maintenance Release number

1: Application Server Release number

1: Component Specific Release number

1: Platform Specific Release number

28. What is the use of COALESCE function in Oracle?

Coalesce function is used to return the first not null value in the given argument list. If all the values in the list are NULL, then this function returns null value.

29. What is Procedure overloading?

Procedure overloading is the concept of creating multiple procedures or functions of the same name in a package but having different number of arguments and/ or where the arguments have different datatypes.

30. What is the difference between WHERE and HAVING clause in SQL?

The main difference between WHERE and HAVING clause is noticed when using GROUP BY clause. WHERE condition filters out the rows before GROUP BY clause and HAVING can filter out the rows after GROUP BY.

If you any questions or comments, hit them in the comment section below. Have a good day!!