Oracle apps Technical Interview Questions - Part 1

Oracle apps Technical Interview Questions - Part 2

Oracle apps Technical Interview Questions - Part 3

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

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

Hi! This post shows you the query to fetch the users who have a particular responsibility 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      = :p_resp_name;

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

Calling a procedure from a function in Oracle apps R12

Hi, this post will give you an idea on how to call a PL/SQL procedure from a PL/SQL function in Oracle apps R12.

In this example, the function named demo_function calls the procedure demo_procedure and returns procedure's output.

Procedure to return a value:

CREATE OR REPLACE PROCEDURE demo_procedure (p_output OUT VARCHAR2)

AS

l_dummy VARCHAR2(20);

BEGIN

SELECT dummy INTO l_dummy FROM dual;

IF l_dummy IS NOT NULL THEN

p_output := 'YES';

ELSE

p_output := 'NO';

END IF;

EXCEPTION WHEN OTHERS THEN

dbms_output.put_line('An unknown exception occurred '||sqlerrm);

END;

/

Checking the output of the demo_procedure:

DECLARE

l_op VARCHAR2(20);

BEGIN

demo_procedure(l_op);

dbms_output.put_line(l_op);

END;

/

Function to call the demo_procedure:

CREATE OR REPLACE FUNCTION demo_function

RETURN VARCHAR2 AS

l_op VARCHAR2(10);

BEGIN

demo_procedure(l_op);

RETURN l_op;

EXCEPTION WHEN OTHERS THEN

dbms_output.put_line('Error in executing procedure demo_procedure '||sqlerrm);

END;

/

Output:

SELECT demo_function FROM dual;

So, this is how a procedure can be called from a function.

Cheers!!

How to compile a form in oracle apps

Hello! This post shows you the syntax to compile a form i.e., .fmb to .fmx file.

Syntax to compile a form:

frmcmp_batch module=$AU_TOP/forms/US/XX_DEMO_FORM.fmb Userid=<username>/<pwd> output_file=$FND_TOP/forms/US/XX_DEMO_FORM.fmx compile_all=special batch=yes

Here, XX_DEMO_FORM is the form name in .fmb format. <username> and<pwd> should be replaced with your username and password.

Cheers!!

Query to retrieve complete Sales Order details in Oracle apps R12

Hi In this post, I will show you how to retrieve complete sales order information along with Invoice details from backend in Oracle apps R12.

SELECT OOH.ORDER_NUMBER ORDER_NUMBER,

       OTT.NAME ORDER_TYPE,

       OOH.ORDERED_DATE DATE_ORDERED,

       QLH.NAME PRICE_LIST,

       OOH.CUST_PO_NUMBER CUSTOMER_PO,

       OOH.FLOW_STATUS_CODE STATUS,

       OOH.TRANSACTIONAL_CURR_CODE CURRENCY,

       OOL.LINE_NUMBER LINE,

       OOL.ORDERED_ITEM ORDERED_ITEM,

       MSIB.DESCRIPTION ITEM_DESC,

       OOL.ORDERED_QUANTITY QTY,

       OOL.UNIT_SELLING_PRICE UNIT_SELLING_PRICE,

       (OOL.ORDERED_QUANTITY*OOL.UNIT_SELLING_PRICE) SUBTOTAL,

       OOL.TAX_VALUE TAX,

       OOL.ORDER_QUANTITY_UOM UOM,

       OOL.REQUEST_DATE REQUEST_DATE,

       OOL.SCHEDULE_SHIP_DATE SCHEDULE_SHIP_DATE,

       OOL.SCHEDULE_ARRIVAL_DATE SCHEDULE_ARRIVAL_DATE,

       OOL.PROMISE_DATE PROMISE_DATE,

       OOL.FLOW_STATUS_CODE LINE_STATUS,

       OTT1.NAME LINE_TYPE,

       OOL.CANCELLED_QUANTITY QUANTITY_CANCELLED,

       OOL.SHIPPED_QUANTITY QUANTITY_SHIPPED,

       OOL.RETURN_REASON_CODE REASON,

       OOL.SERVICE_TXN_COMMENTS COMMENTS,

       OOL.SERVICE_REFERENCE_TYPE_CODE SERVICE_REFERENCE_TYPE,

       NULL PROJECT_NUMBER,

       NULL TASK_NUMBER,

       OOL.END_ITEM_UNIT_NUMBER UNIT_NUMBER,

       OOL.CUSTOMER_JOB CUSTOMER_JOB,

       OOL.CUSTOMER_PRODUCTION_LINE PRODUCTION_LINE,

       OOL.CUST_MODEL_SERIAL_NUMBER MODEL_SERIAL_NUMBER,

       OOL.CUSTOMER_DOCK_CODE CUSTOMER_DOCK,

       OOL.CUST_PRODUCTION_SEQ_NUM CUST_PRODUCTION_SEQUENCE,

       OOL.RLA_SCHEDULE_TYPE_CODE RLM_SCHEDULE_TYPE,

       OOS.NAME ORDER_SOURCE,

       OOH.ORIG_SYS_DOCUMENT_REF ORDER_SOURCE_REFERENCE,

       OOL.ORIG_SYS_LINE_REF ORDER_SOURCE_LINE_REFERENCE,

       OOL.TAX_CODE TAX_CLASSIFICATION_CODE,

       OOL.PRICING_QUANTITY_UOM PRICING_UOM,

       OOL.PRICING_DATE PRICING_DATE,

       MP.ORGANIZATION_CODE WAREHOUSE,

       OOL.SOURCE_TYPE_CODE SOURCE_TYPE,

       OOL.SHIPPING_METHOD_CODE SHIPPING_METHOD,

       RCT.TRX_NUMBER INVOICE_NUMBER,

       ACR.RECEIPT_NUMBER RECEIPT_NUMBER,

       OOD.ORGANIZATION_NAME LEGAL_ENTITY,

 HL.ADDRESS1||HL.ADDRESS2||HL.ADDRESS3||HL.ADDRESS4||','||HL.CITY||','||HL.STATE||','||HL.COUNTY SHIP_TO_LOC,

 HL1.ADDRESS1||HL1.ADDRESS2||HL1.ADDRESS3||HL1.ADDRESS4||','||HL1.CITY||','||HL1.STATE||','||HL1.COUNTY BILL_TO_LOC,

       JRS.NAME SALES_PERSON

FROM OE_ORDER_HEADERS_ALL OOH,

     OE_ORDER_LINES_ALL OOL,

     OE_TRANSACTION_TYPES_TL OTT,

     OE_TRANSACTION_TYPES_TL OTT1,

     QP_LIST_HEADERS_TL QLH,

     OE_ORDER_SOURCES OOS,

     MTL_SYSTEM_ITEMS_B MSIB,

     MTL_PARAMETERS MP,

     ORG_ORGANIZATION_DEFINITIONS OOD,

     AR_RECEIVABLE_APPLICATIONS_ALL ARA, 

     AR_CASH_RECEIPTS_ALL ACR, 

     RA_CUSTOMER_TRX_ALL RCT,

     RA_CUSTOMER_TRX_LINES_ALL RCTL,

     HZ_CUST_ACCOUNTS HCA,

     JTF_RS_SALESREPS JRS,

--   JTF_RS_DEFRESOURCES_V JRD,    

     HZ_PARTIES HP,

     HZ_PARTY_SITES HPS,

     HZ_CUST_ACCOUNTS_ALL HCAA,

     HZ_CUST_SITE_USES_ALL HCSUA,

     HZ_CUST_ACCT_SITES_ALL HCASA,

     HZ_LOCATIONS HL,   

     HZ_PARTY_SITES HPS1,

     HZ_CUST_ACCOUNTS_ALL HCAA1,

     HZ_CUST_SITE_USES_ALL HCSUA1,

     HZ_CUST_ACCT_SITES_ALL HCASA1,

     HZ_LOCATIONS HL1  

WHERE OOH.HEADER_ID = OOL.HEADER_ID

  AND OOH.ORG_ID = OOL.ORG_ID

  AND OOH.ORDER_TYPE_ID = OTT.TRANSACTION_TYPE_ID

  AND OOL.LINE_TYPE_ID = OTT1.TRANSACTION_TYPE_ID

  AND QLH.LIST_HEADER_ID = OOH.PRICE_LIST_ID

  AND OOH.ORDER_SOURCE_ID = OOS.ORDER_SOURCE_ID

  AND OOL.ORG_ID = OOD.OPERATING_UNIT

  AND OOD.ORGANIZATION_ID = MSIB.ORGANIZATION_ID

  AND OOL.ORDERED_ITEM = MSIB.SEGMENT1

  AND OOL.ORG_ID = MP.ORGANIZATION_ID

  AND OOD.ORGANIZATION_ID = RCT.LEGAL_ENTITY_ID

  AND RCT.CUSTOMER_TRX_ID = RCTL.CUSTOMER_TRX_ID

  AND OOL.LINE_ID = RCTL.INTERFACE_LINE_ATTRIBUTE6

  AND ARA.STATUS='APP' 

  AND ARA.CASH_RECEIPT_ID=ACR.CASH_RECEIPT_ID 

  AND ARA.APPLIED_CUSTOMER_TRX_ID=RCT.CUSTOMER_TRX_ID

  AND OOH.ORDER_NUMBER = RCT.INTERFACE_HEADER_ATTRIBUTE1

  AND OOH.SOLD_TO_ORG_ID = HCAA.CUST_ACCOUNT_ID

  AND JRS.SALESREP_ID = RCT.PRIMARY_SALESREP_ID

  AND HPS.PARTY_ID = HP.PARTY_ID

  AND HCASA.CUST_ACCOUNT_ID = HCAA.CUST_ACCOUNT_ID

  AND RCT.SHIP_TO_SITE_USE_ID = HCSUA.SITE_USE_ID

  AND HCSUA.CUST_ACCT_SITE_ID = HCASA.CUST_ACCT_SITE_ID

  AND HCASA.PARTY_SITE_ID = HPS.PARTY_SITE_ID

  AND HPS.LOCATION_ID = HL.LOCATION_ID

  AND RCT.BILL_TO_SITE_USE_ID = HCSUA1.SITE_USE_ID

  AND HCASA1.CUST_ACCOUNT_ID = HCAA1.CUST_ACCOUNT_ID

  AND HCSUA1.CUST_ACCT_SITE_ID = HCASA1.CUST_ACCT_SITE_ID

  AND HCASA1.PARTY_SITE_ID = HPS1.PARTY_SITE_ID

  AND HPS1.LOCATION_ID = HL1.LOCATION_ID

  AND OOH.ORDER_NUMBER = '90291' --<Your_order_number>

  AND ACR.RECEIPT_NUMBER = 'RECEIPT_90291'--<Your_receipt_ number>

GROUP BY OOH.ORDER_NUMBER,

         OTT.NAME,

         OOH.ORDERED_DATE,

         QLH.NAME,

         OOH.CUST_PO_NUMBER,

         OOH.FLOW_STATUS_CODE,

         OOH.TRANSACTIONAL_CURR_CODE,

         OOL.LINE_NUMBER,

         OOL.ORDERED_ITEM,

         MSIB.DESCRIPTION,

         OOL.ORDERED_QUANTITY,

         OOL.UNIT_SELLING_PRICE,

         OOL.TAX_VALUE,

         OOL.ORDER_QUANTITY_UOM,

         OOL.REQUEST_DATE,

         OOL.SCHEDULE_SHIP_DATE,

         OOL.SCHEDULE_ARRIVAL_DATE,

         OOL.PROMISE_DATE,

         OOL.FLOW_STATUS_CODE,

         OTT1.NAME,

         OOL.CANCELLED_QUANTITY,

         OOL.SHIPPED_QUANTITY,

         OOL.RETURN_REASON_CODE,

         OOL.SERVICE_TXN_COMMENTS,

         OOL.SERVICE_REFERENCE_TYPE_CODE,

         OOL.END_ITEM_UNIT_NUMBER,

         OOL.CUSTOMER_JOB,

         OOL.CUSTOMER_PRODUCTION_LINE,

         OOL.CUST_MODEL_SERIAL_NUMBER,

         OOL.CUSTOMER_DOCK_CODE,

         CUST_PRODUCTION_SEQ_NUM,

         OOL.RLA_SCHEDULE_TYPE_CODE,

         OOS.NAME,

         OOH.ORIG_SYS_DOCUMENT_REF,

         OOL.ORIG_SYS_LINE_REF,

         OOL.TAX_CODE,

         OOL.PRICING_QUANTITY_UOM,

         OOL.PRICING_DATE,

         MP.ORGANIZATION_CODE,

         OOL.SOURCE_TYPE_CODE,

         OOL.SHIPPING_METHOD_CODE,

         RCT.TRX_NUMBER,

         ACR.RECEIPT_NUMBER,

         OOD.ORGANIZATION_NAME,                 HL.ADDRESS1||HL.ADDRESS2||HL.ADDRESS3||HL.ADDRESS4||','||HL.CITY||','||HL.STATE||','||HL.COUNTY,

HL1.ADDRESS1||HL1.ADDRESS2||HL1.ADDRESS3||HL1.ADDRESS4||','||HL1.CITY||','||HL1.STATE||','||HL1.COUNTY,

JRS.NAME;

Happy Learning!!

Sample example to use Ref cursor in Oracle Apps for beginners

Hi, This post is to show you a sample example on how to use ref cursor in Oracle apps R12 which will be helpful for beginners. Below code shows an example of using Ref cursor.

CREATE OR REPLACE PROCEDURE RETURNING_REFCURSOR(

                   P_EMPNO EMP05.EMPNO%TYPE,

                  L_REFCURSOR OUT SYS_REFCURSOR) IS

L_ENAME EMP05.ENAME%TYPE;

L_DNAME DEPT05.DNAME%TYPE;

BEGIN

OPEN L_REFCURSOR FOR SELECT E.ENAME,D.DNAME FROM EMP05 E,DEPT05 D WHERE E.DEPTNO = D.DEPTNO AND E.EMPNO = P_EMPNO;

LOOP

FETCH L_REFCURSOR INTO L_ENAME,L_DNAME;

EXIT WHEN L_REFCURSOR%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(L_ENAME||' '||L_DNAME);

END LOOP;

CLOSE L_REFCURSOR;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END RETURNING_REFCURSOR;

/

SET SERVEROUTPUT ON

VARIABLE G_REF REFCURSOR

VARIABLE G_EMPNO NUMBER

EXECUTE :G_EMPNO := 7839

EXECUTE RETURNING_REFCURSOR(:G_EMPNO,:G_REF)

Happy learning!!

How to pass multiple values to a parameter in concurrent program in Oracle apps

Hi! In this post, I will show you how to pass multiple values separated by comma in concurrent program parameter in Oracle apps.

To achieve the same requirement in the database, we can use lexical parameter like below. It retrieves the department names based on the department numbers that we pass in the p_deptno parameter.

SELECT dname "Department" FROM dept WHERE deptno IN (&p_deptno);

However, the above format won't work in concurrent program parameter but it can be achieved using REGEXP. The below code shows a sample procedure that uses a cursor with SELECT query to achieve the same requirement that can be used in concurrent programs.

To pass multiple values separated by commas in Concurrent program parameter:

CREATE OR REPLACE PROCEDURE getDept (p_deptno IN VARCHAR2)

AS

CURSOR get_dept_cur IS SELECT dname FROM dept WHERE deptno IN 

(SELECT REGEXP_SUBSTR(UPPER(p_deptno), '[^,]+', 1, level) FROM dual

CONNECT BY REGEXP_SUBSTR(p_deptno, '[^,]+', 1, level) IS NOT NULL);

BEGIN

FOR i IN get_dept_cur LOOP

dbms_output.put_line(i.dname);

END LOOP;

END;

/

EXEC getDept('10,20,30,40');

Hope this helps. Happy learning! 

Query to find RDF report path in Oracle apps

Hi, This post shows you how to find the RDF report path in server in Oracle apps R12. The query below retrieves the report path along with application name which takes execution file name is the parameter.

SELECT fa.application_name,

               '$'||basepath||'/'||'reports/US' Reports_Path,

   FROM apps.fnd_executables_vl fe, 

                apps.fnd_application_vl fa 

WHERE fe.execution_method_code =' P' 

      AND fe.application_id = fa.application_id

      AND fe.execution_file_name = 'XXSAMPLEFILE';                  --Your file name without extension

Happy learning!!

Split one column into multiple columns in Oracle apps

Hi! In this post, I will show you how to split up one column into multiple columns based on delimiters.

Let's say there is a column called Cardholder_name and it has values in the format last_name, first_name. In order to split them into two separate columns i.e., last_name and first_name separately, below syntax can be used.

Method 1:

SELECT REGEXP_SUBSTR(cardholder_name, '[^,]+', 1, 1) AS last_name, 

                REGEXP_SUBSTR(cardholder_name, '[^,]+', 1, 2) AS first_name,

   FROM xx_cardholder_tbl;

Method 2:

SELECT SUBSTR( cardholder_name, 1, INSTR(cardholder_name, ',')-1 ) last_name,

                SUBSTR( cardholder_name, INSTR(cardholder_name, ',')+1, INSTR(cardholder_name, ',',1,2)-INSTR(cardholder_name, ',')-1 ) first_name

   FROM xx_cardholder_tbl;                         --Here ',' is the delimiter.

Happy learning. Cheers!

Script to delete data definition and template in XML Publisher Reports in Oracle apps

Hi! This post will show you the script to delete the data definition and template in XML Publisher reports in Oracle apps.

Script to delete template and data definition in XML Publisher Reports:

BEGIN

DELETE from xdo_templates_b WHERE template_code = 'XXEMP';

DELETE from xdo_templates_tl WHERE template_code = 'XXEMP';

DELETE from xdo_lobs WHERE lob_code = 'XXEMP';

DELETE from xdo_ds_definitions_tl WHERE data_source_code = 'XXEMP';

DELETE from xdo_ds_definitions_b WHERE data_source_code = 'XXEMP';

COMMIT;

EXCEPTION WHEN OTHERS THEN

dbms_output.put_line('An unknown error occurred '||SQLERRM);

END;

In the above script, XXEMP is the sample template or data definition code which is nothing but the concurrent program short name.

Happy Learning!!

Query to find order holds in Oracle apps R12

Hi! This post is to show the query to find holds on sales orders in Oracle apps R12.

Below is the query that you can use to find holds on a specific order:

SELECT ooh.order_number,

       ohd.hold_id,

       ohd.name hold_name

FROM oe_order_headers_all ooh,

     oe_order_holds_all ooha,

     oe_hold_sources_all ohs,

     oe_hold_definitions ohd

WHERE ooh.header_id = ooha.header_id

  AND ohs.hold_id = ohd.hold_id

  AND ohs.hold_source_id = ooha.hold_source_id

  AND ooh.order_number = <order_number>;

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