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!!

Script to run SQL Query from Host file in Oracle apps

Hi, This post shows you the sample script to run SQL query from host file in Oracle Apps.

In the below example, I used a query to fetch the directory path.

#!/bin/ksh

#----- Standard Parameters: -----#

p_apps_passwd=`echo ${1}`

p_user_id=`echo ${2}`

p_username=`echo ${3}`

p_request_id=`echo ${4}`

target_path=/u01/apps/apps_st/appl/jtm/12.0.0

p_file_directory()                                                         #Directory Path

{

sqlplus -s ${p_apps_passwd} << EOF                        #Starts from here

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

SELECT file_path FROM request_table;                    #sample query

EOF

}

echo "Directory Path: $p_file_directory"                    #Printing directory path

# - Represents comments

Hope this helps for beginners. Great learning ahead!!

Customer tables and their joins in Oracle Apps

Hi! This is post will show you important customer tables in Oracle apps along with their joins.

Important Customer tables:

HZ_PARTIES

HZ_CUST_ACCOUNTS

HZ_PARTY_SITES

HZ_CUST_ACCT_SITES_ALL

HZ_CUST_SITE_USES_ALL

HZ_LOCATIONS

Joins:

HZ_PARTIES, HZ_PARTY_SITES - party_id

HZ_PARTIES, HZ_CUST_ACCOUNTS - party_id

HZ_CUST_ACCOUNTS, HZ_CUST_ACCT_SITES_ALL - cust_account_id

HZ_PARTY_SITES, HZ_CUST_ACCT_SITES_ALL - party_site_id

 HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL - cust_acct_site_id

HZ_PARTY_SITES, HZ_LOCATIONS - location_id.

Queries to get ship to and bill to address details:

Ship to address:

SELECT hp.PARTY_NAME customer,

       hl.ADDRESS1,

       hl.ADDRESS2,

       hl.CITY,

       hl.POSTAL_CODE,

       hl.STATE,

       hl.PROVINCE,

       hl.COUNTRY

FROM hz_parties hp,

  hz_cust_accounts hca,

  hz_cust_acct_sites_all hcasa,

  hz_cust_site_uses_all hcsua,

  hz_party_sites hps,

  hz_locations hl

WHERE hp.party_id        = hca.party_id

AND hca.cust_account_id   = hcasa.cust_account_id

AND hcasa.CUST_ACCT_SITE_ID = hcsua.CUST_ACCT_SITE_ID

AND hp.party_id          = hps.party_id

AND hps.party_site_id = hcasa.party_site_id

AND hl.location_id       = hps.location_id

AND hp.party_name= <party_name>

AND hcsua.site_use_code = 'SHIP_TO'

Bill to address:

SELECT hp.PARTY_NAME customer,

       hl.ADDRESS1,

       hl.ADDRESS2,

       hl.CITY,

       hl.POSTAL_CODE,

       hl.STATE,

       hl.PROVINCE,

       hl.COUNTRY

FROM hz_parties hp,

  hz_cust_accounts hca,

  hz_cust_acct_sites_all hcasa,

  hz_cust_site_uses_all hcsua,

  hz_party_sites hps,

  hz_locations hl

WHERE hp.party_id        = hca.party_id

AND hca.cust_account_id   = hcasa.cust_account_id

AND hcasa.CUST_ACCT_SITE_ID = hcsua.CUST_ACCT_SITE_ID

AND hp.party_id          = hps.party_id

AND hps.party_site_id = hcasa.party_site_id

AND hl.location_id       = hps.location_id

AND hp.party_name = <party_name>

AND hcsua.site_use_code = 'BILL_TO';

Hope this helps you. Great learning ahead!!

Oracle Technical Logical Interview Questions - Part 3

Hi! In this post, I'm going to give you a glimpse of how interviewer would test your logical ability for the requirement given. Having a good logical ability as a technical consultant would not only help you for the interview but also for your career growth. So, lets get started.

1. Consider the Invoices table below:

INVOICE_ID	INVOICE_NUM
101	INV101
102	INV102
103	INV103
103	INV103
102	INV102
101	INV101

Write a query to remove the duplicates from the Invoices table.

Sol: DELETE FROM invoices WHERE rowid NOT IN 

(SELECT MIN(rowid) FROM invoices GROUP BY invoice_id, invoice_num);

OR

DELETE FROM invoices WHERE rowid NOT IN

(SELECT MAX(rowid) FROM invoices GROUP BY invoice_id, invoice_num);

2. Consider below items table:

ITEM_NUM	INV_ORG_ID
Pen	87
Pen	86
Book	87
Laptop	86
Computer	87
Mouse	87
Mouse	86
Keyboard	87
Keyboard	86

Write a query to display items that either exist in inv_org_id 87 or 86 but not both.

Sol: Method 1: SELECT item_num FROM items

                         MINUS

                         (SELECT item_num FROM items WHERE inv_org_id = 87

                         INTERSECT

                         SELECT item_num FROM items WHERE inv_org_id = 86);

Method 2: SELECT item_num FROM items WHERE inv_org_id = 87 AND item_num NOT IN                              (SELECT item_num FROM items WHERE inv_org_id = 86)

                  UNION

                  SELECT item_num FROM items WHERE inv_org_id = 86 AND item_num NOT IN                             (SELECT item_num FROM items WHERE inv_org_id = 87);

Method 3: SELECT item_num FROM items

                  MINUS

                  (SELECT item_num FROM items WHERE inv_org_id = 86 AND item_num IN (SELECT                      item_num FROM items WHERE inv_org_id = 87));

Method 4: SELECT item_num FROM items WHERE item_num NOT IN 

                 (SELECT item_num FROM items WHERE inv_org_id = 87

                 INTERSECT

                 SELECT item_num FROM items WHERE inv_org_id = 86);

Method 5: SELECT item_num FROM items WHERE item_num NOT IN 

                 (SELECT item_num FROM items WHERE inv_org_id = 87 AND item_num IN 

                 (SELECT item_num FROM items WHERE inv_org_id = 86));

3. Write a query to retrieve order numbers that have more than 10 lines.

Sol: SELECT order_number, count(*) 

       FROM oe_order_headers_all ooh, oe_order_lines_all ool

       WHERE ooh.header_id = ool.header_id 

       GROUP BY order_number 

       HAVING count(*) > 10;

4. Write a query to get the hire date of least employee number.

Sol: Method 1: SELECT hire_date FROM emp WHERE empno = (SELECT MIN(empno) FROM                                  emp);

Method 2: SELECT MIN(hire_date) KEEP (dense_rank FIRST ORDER BY empno) FROM emp;

5. Consider below ITEMS table:

ITEM_NUM	INV_ORG_ID
Pen	87
Air Conditioner	86
Book	87
Laptop	86
Computer	87
Ebook	87
Mouse	86
Inkpot	87
Keyboard	86

Write a query to display the items that don't start or end with a vowel.

Sol: SELECT item_num FROM items WHERE SUBSTR(item_num, '1', '1')  NOT IN ('a','e','i','o','u') AND SUBSTR(item_num, '-1', '1') NOT IN (item_num, 'A','E','I','O','U');

6. Consider below table Emp.

Empno	Empname
10	John
11	Jason
12	Joseph
13	Jones
14	Jane
15	Jackmel

Write a query to to display the empname values in the following order: 

Jones, Jason, John, Jackmel, Joseph, Jane.

Sol: SELECT empname FROM emp ORDER BY DECODE(empname, 'Jones',  1, 'Jason', 2, 'John', 3, 'Jackmel', 4, 'Joseph', 5, 'Jane', 6, 7);

7. Display the difference between given two dates in hours: 21-OCT-2019 and 19-AUG-2020

Sol: SELECT (TO_DATE('21-OCT-2019 00:00:00','DD-MON-YYYY')-('19-AUG-2020 00:00:00','DD-MON-YYYY'))*24 FROM dual;

8. Build a hierarchical query on emp table to display the records such that the MGR column equals their EMPNO, start with person(s) who have no manager.

Sol: SELECT * FROM emp

        START WITH mgr IS NULL

        CONNECT BY PRIOR empno = mgr;

9. How to fetch the first record from a table (Take emp as example)?

Sol: SELECT * FROM emp WHERE rownum = 1;

10. How to fetch the last record from a table (Take emp as example)?

Sol: SELECT * FROM emp WHERE rowid = (SELECT MAX(rowid FROM emp);

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!!

Exporting a composite from em console page in SOA 12c

Hi! This post shows you how to export a composite from EM console page and import it in the JDeveloper.

1. Go to em console page and navigate to Deployed Composites.

Click on the Composite that you want to export.

Click on the Composite Drop down menu and click Export.

In the next screen, leave the default options and click on "Export" button.

It'll generate the jar file.

Click on Save File after the jar file is ready.

2. Now, import the file from your JDeveloper. Click on the File>Import.

Select SOA Archive Into SOA Project option and click OK.

Give it a name and click Next.

In the Composite Archive, browse the location where your jar file is located and click Finish. Now you can view the composite in JDeveloper and see the flow.

Happy learning. Cheers!!