Oracle Interview Qs?

1. What is Global Variable?

Variable declared in the package specification is nothing but global variable.

2. What is Local Variable?

Variables declare  with in packaged procedures or procedures are local variables.

3. What is the difference between Public & Private Procedure?

Public procedures are directly accessible to user of the package

Private procedure are hidden from the user . The scope of private procedure is within the package.

For example, a package might contain ten procedures. You can define the package so that only three procedures are public and therefore available for execution by a user of the package; the remaining 7 procedures are private and can only be accessed by the procedures within the package.

Examples for the  above

CREATE OR REPLACE package APPS.my_pkg as

      procedure p;

         g_global number:=100;

    end my_pkg;

/

CREATE OR REPLACE package body APPS.my_pkg as

     -- g_global number:=100;

       procedure private(l_num in number) as

       begin

               dbms_output.put_line (' The value of l_num before was '||l_num);

               g_global := 55;

               dbms_output.put_line (' The value of l_num after was '||l_num);

       end;

      procedure p as

      begin

      dbms_output.put_line (' The value in p prcedure was '||g_global);

              --g_global := 55;

              private(g_global);

       dbms_output.put_line (' The value in p prcedure after '||g_global);

      end p;

   end my_pkg;

/

Execution:

Begin

My_pkg.p;

End;

Output:

The value in p prcedure was 100

The value of l_num before was 100

The value of l_num after was 55

The value in p prcedure after 55

4. What is the purpose of Collections? Explain?

A collection is an ordered group of elements, all of the same type. . It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection.

1. Index-by tables(Non -persistant) not stored in database.
2. Nested Tables(persistant)
3. Varrays.( persistant)

http://sheikyerbouti.developpez.com/collections/collections.htm#C1

5. What is Record Type?

Record types are composite datatypes, which means it is a combination of different scalar datatypes like char, varchar, number etc. 

6. What is the purpose or use of Data Definition?
7. What are the mappings(JOINS between tables) for Order to Invoice and Delivery to Invoice?

Oe_order_headers_all.order_number=ra_customer_trx_all.interface_header_attribute1

Wsh_new_deliveries.delivery_id= ra_customer_trx_all.interface_header_attribute3

8. What is UTL File and its purpose?

9. What are the API’s used in Supplier Conversion? What are the tables(11i & R12) used in supplier conversion?

               1.Ap_vendor_pub.create_vendor.

               2.Ap_vendor_pub.create_vendor_site.

               3.Ap_vendor_pub.create_vendor_contact.

Tables:

 Ap_suppliers,

Ap_supplier_sites_all

Hz_org_contacts or AP_SUPPLIER_CONTACTS

10. What are the API’s used in Customer Conversion? What are the tables(11i & R12) used in customer conversion?
11. What is Ref Cursor and its purpose?
12. What is Dynamic Cursor and its purpose?
13. What is Pragma Exception and its purpose?

pragma EXCEPTION_INIT associates an exception name with an Oracle error number.

Eg: FOUND_NOTHING exception; Pragma exception_init(FOUND_NOTHING ,100);

14. What is the purpose of copying a concurrent program?
15. Where do(In which tables) we find Shipment Details?

Wsh_delivery_details

Wsh_delivery_assignments

Wsh_new_deliveries

16. Where do(In which tables) we find Order Information Details?

Oe_order_headers_all

Oe_order_lines_all

17. Where do(In which tables) we find Invoice Information Details?

Ra_customer_trx_all

Ra_customer_trx_lines_all

18. What is the difference between Procedure and Stored Procedure?

Both are same

19. What is the difference between Procedure and Function?

Function should return a value.

Procedure may are may not return a value.

Functions are used for calculations and dmls are not allowed in functions.

Procedure are used to perform dml operations.

Functions are allowed in dml operations.

Procedure are not allowed in dml operations.

20. How do we write DBMS statements in Report?
21. How do we link Template to a concurrent Program?
22. How do we create Multiple Templates with Single Data Definition?
23. How do we link Multiple Templates to single concurrent program?
24. How do we link 2 Valuesets?
25. What are the Value set Types we have?
26. In which tables Payment Information and Invoice Information stored? What is the Link between these two?
27. PO Number is loaded in which table and which column?

Po_headers_all.segment1

28. How do we get UTL file path?

Answer: select * from v$parameter where name like ‘utl_file_dir’.

29. What are UTL file Exceptions?
30. Syntax for Cursor?
31. What is Implicit Cursor?
32. What is Explicit Cursor?
33. How do we define cursor to a variable?
34. What is Discoverer? Explain?
35. How do we get PO details by passing Invoice#?
36. What are the Payments Types used?

Answer: There are 3 Payment Types

1. Quick
2. Manual and 3.Refund
36. What are the Payment Methods used?
37. What is Sub-ledger and what are the sub-ledger tables?
38. What is the difference between 11i and R12?
39. Where we do setup in R12 to get Multi Organizations?
40. Have you used only Placeholder Column to capture the data or any other Attributes? If so what are they?
41. What are Bulk Collectors?
42. What is Autonomous Transaction? Define?

Autonomous transaction is an independent transactions which will execute independently with out affection parent transaction.

44. What is the difference between UNION and UNION ALL?
45. How do we call Multiple Layouts in RTF?
46. How do we design Multiple Layouts in RDF?
47. What is the difference between 6i and 10g?
48. What is the difference between User-defined function and Pre-defined functions?
49. Can we use Pre-defined function in User-defined function? If so How?
50. What are Cursor Attributes?
51. What are Cursor Exceptions?
52. What is the difference between Anonymous Block and Procedure Block?
53. How many Triggers can be applied to a single table?

Answer: 12

54. How many columns(Maximum) can be used in a table?

Answer : 254

55. What is the difference between Sub-Query and Co-related Sub-Query? Explain?
56. What is the difference between Table and View?
57. What is the difference between View and Materialized View?
58. What is Materialized view?
59. What are Profile Options? How many types of Profile Options are there? What is its use? Have you defined any profile options?
60. How to display single record in single page in RDF report?
61. What is Filler and its purpose? Have you used this in SQL*Loader?
62. What is the API used to create Order Number?
63. What is the sequence of O2C cycle?
64. What is the difference between org id and organization id?
65. How to skip particular row from test file while loading into table?
66. How to skip empty(NULL) row from test file while loading into table?
67. How to skip some rows from test file while loading into table?
68. How to load empty(NULL) rows from test file into table?

     

Reports Interview Questions – Frequently Asked

1. What is Frame, Repeating Frame?

2. What are Anchor Objects?

3. What are SYSTEM Parameters?

4. What are USER Parameters?

5. What is Bind Parameter and Lexical Parameter? What is the difference?

6. When we will use Lexical Parameters?

7. Where we will handle the Lexical Parameters?

8. Tell me where you have used Lexical Parameters in your experience?

9. What are Report Triggers?

10. What is the sequence of Triggers firing?

11. What is the difference between After Parameter Form and Before Report Trigger?

12. What is the difference between After Parameter Form and Before Parameter Form?

13. What is Format Trigger and its purpose?

14. What is Validation Trigger and its purpose?

15. What are Action Triggers and its purpose?

16. What is Drill down and Matrix Report?

17. How to call another report from report?

18. What is Confine Mode?

19. What is Flex Mode?

20. How do we define Anchor?

21. What are the SRW Packages we have?

22. What is User Exit and its purpose?

23. What is SRW.MESSAGE and its purpose?

24. What is Formula, Summary and Placeholder column? And differences between them?

25. What is the difference between Data Link and Group by?

26. What is the purpose of Between Pages trigger?

27. If Output is 10 pages how many times Between Pages trigger will be fired? If goes   from last page to first page whether the trigger will be created?