1. Cursors
PL/SQL allows the programmer to control the context area through the cursor.
A cursor holds the rows returned by the SQL statement. The set of rows the cursor holds is referred as active set.
Cursor curser_name is (parameter1 datatype)
select e.name , c.salary
from employee e,
perks c,
where e.eid = c.pid
and c.salary >= 1000 ;
===============================
1.1 implicit cursors
Curser used by system to complete DML (Delete, insert, update) transaction is using this type of cursor.
===============================
1.2 explicit cursors
this is mainly used to get required data set to execute further actions, using Select clause. This needs definition, call for opening and closing.
=================================
1.3 cursor attributes
Cursor Attribute Description
%FOUND -It returns the Boolean result 'TRUE' if the most recent fetch operation fetched a record successfully, else it will return FALSE.
%NOTFOUND -This works oppositely to %FOUND it will return 'TRUE' if the most recent fetch operation could not able to fetch any record.
%ISOPEN - It returns Boolean result 'TRUE' if the given cursor is already opened, else it returns 'FALSE'
%ROWCOUNT -It returns the numerical value. It gives the actual count of records that got affected by the DML activity.
=================================
1.4 for loop cursor application
"FOR LOOP" statement can be used for working with cursors. We can give the cursor name instead of range limit in the FOR loop statement so that the loop will work from the first record of the cursor to the last record of the cursor. The cursor variable, opening of cursor, fetching and closing of the cursor will be done implicitly by the FOR loop.
===================================
1.5 Difference between static and dynamic cursors
1.6 Ref cursors
PL/SQL Cursor Variables with REF CURSOR (oracletutorial.com)
1.7 dynamic sql
Oracle PL/SQL Dynamic SQL Tutorial: Execute Immediate & DBMS_SQL (guru99.com)
1. execute immediate
EXECUTE IMMEDIATE(<SQL>) [INTO<variable>] [USING <bind_variable_value>]
2. DBMS _sql
PL/SQL provide the DBMS_SQL package that allows you to work with dynamic SQL. The process of creating and executing the dynamic SQL contains the following process.
- OPEN CURSOR: The dynamic SQL will execute in the same way as a cursor. So in order to execute the SQL statement, we must open the cursor.
- PARSE SQL: The next step is to parse the dynamic SQL. This process will just check the syntax and keep the query ready to execute.
- BIND VARIABLE Values: The next step is to assign the values for bind variables if any.
- DEFINE COLUMN: The next step is to define the column using their relative positions in the select statement.
- EXECUTE: The next step is to execute the parsed query.
- FETCH VALUES: The next step is to fetch the executed values.
- CLOSE CURSOR: Once the results are fetched, the cursor should be closed.
==================================================================
2. Triggers
==================================================================
3. Exceptions
Oracle PL/SQL Exception Handling: Examples to Raise User-defined Exception (guru99.com)
==================================================================
4. what is NO COPY
5. Nested tables
6. Pragma autonomous transactions and its application
7. Global temporary tables and transactions
8. Views
9. Collections, Bulk collect
Oracle PL/SQL BULK COLLECT: FORALL Example (guru99.com)
10. NVL, NVL2
11. what is RICEW , explain with each example
- Reports, Interfaces, Conversions, Extensions, Workflow
12. Reports, triggers in reports
13. Forms, why do you need Custom.pll , how and where do you use it, give example
14. Form personalization steps, what is profile option for this
15. wht is profile options
16. what is flex fields, dff, kff its differences
17. how to use OAF, can we change value run time, development steps of OAF. what is OAF MVC architecture.
18. what is a Workflow, how to customize workflow, development steps
19. what is XML report, define steps to build the same
20. XML publisher, xml parser, ZPL with example, discoverer reports
21. Mandatory parameters in API
22. Seeded APIs - create customers, create orders, create PO etc
23. what is tkprof
24. performance tunning with examples, how to tune given query
25. what is business events in oracle, how to write code for the same
26. what is hook, what are types of hooks. Difference between hooks and business events. where do you use hooks
27. what is processformdata, what is controller
28. how to add picture in report
29. how to change language for report or program specific to country. explain with example.
30. explain Order to Cash flow with table details.
Drop ship orders-
Items which are mot produced but procured from supplier and fulfilled.
the main setup here is the Item setup --> OM tab having source type as EXTERNAL
OM : Create sales order --> Book the order --> status - booked
click on Progress order --> Purchase Release eligible --> click OK --> now status - Awaiting shipping
Go to purchasing resp --> run program 'Purchase Release' sales order number as parameter --> run
once complete then
run program 'Requisition Import ' where parameter import source - Order Import
important link - oracle drop ship order flow (erpschools.com)
Order management --> Shipped --> awaiting billing --> AR interface --> AR invoice -->amount recieved --> create reciept --> complete
here system automatically places order on supplier
( approved supplier list, sourcing rules, assigning to sourcing rules, sourcing rule assignment sets, blanket purchase agreement)
31. How do you define a new order type
32. explain orders - CTO, PTO, ATO, Drop ship with examples
33. explain P2P cycle with table details
Oracle Apps: P2P(Procure-to-Pay) Cycle Tables with Joins (kommaoracleapplications.blogspot.com)
34. what are types of purchase orders, requisition types
35. what is match recipt, which tables do you check for this
36. can we define item on warehouse without defining in master org? tables involved in item definition.
37. how to check on hand quantity, tables for the same
38. what is bursting ? its application
39. WMS what is it ? its application
40. difference between WMS and inventory systems
41. what are month end closure activities (list all)
42. what are 3 Cs , define all different types of calendars.
43. when do you need to run inventory interface, what does it do
44. how do you define request set, explain steps. Can we hide mandatory parameters
45. what is XML gateway, how to set it up, explain tables.
48. what is FTP, how do you use it, what file can you read.
49. tables for AP invoices, Recipts
50. Finance cycle and its tables, cogs accounting what is it.
51. how to find out a program is run though which responsibility, explain by writing query.
Ans -
52. How to set Organization Context in R12
mo_global.init ('AR');
mo_globa.set_policy_context ('S', '&org_id') ;
