Concurrent Programs Warning-Error: ERRBUF and RETCODE

The ERRBUF can be returned with any message.

The RETCODE can be returned with one of three values:

0  — Success
1  — Warning
2  — Error

Below is an example of a package where I can pass an employee number and it can return its full name. If no data found for the employee number passed, the concurrent program will turn YELLOW with the message ‘No Employee Found’. If there is any other error occurs, it will turn RED with SQLERRM message.

NOCOPY Hint to Improve Performance of OUT and IN OUT Parameters in PL/SQL

 

Oracle has two methods of passing passing OUT and IN OUT parameters in PL/SQL code:

  • Pass By Value : The default action is to create a temporary buffer (formal parameter), copy the data from the parameter variable (actual parameter) to that buffer and work on the temporary buffer during the lifetime of the procedure. On successful completion of the procedure, 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 : Using the NOCOPY hint 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 (actual parameter).

———————————————————-

— Package Specification

———————————————————-

CREATE OR REPLACE PACKAGE apps.emp_test_pkg

IS

FUNCTION emp_name (

errbuff       OUT  NOCOPY  VARCHAR2,

retcode       OUT  NOCOPY  VARCHAR2,

p_emp_number  IN           NUMBER)

RETURN  VARCHAR2;

 

END emp_test_pkg;

/

———————————————————-

— Package Body

———————————————————-

CREATE OR REPLACE PACKAGE BODY apps.emp_test_pkg

IS

 

FUNCTION emp_name (

errbuff       OUT  NOCOPY  VARCHAR2,

retcode       OUT  NOCOPY  VARCHAR2,

p_emp_number  IN           NUMBER)

RETURN  VARCHAR2

IS

lv_emp_name   VARCHAR2(300)  DEFAULT  NULL;

 

BEGIN

 

SELECT (papf.first_name || ‘ ‘ || papf.last_name)

INTO lv_emp_name

FROM per_all_people_f  papf

WHERE 1=1

AND papf.employee_number = p_emp_number;

 

RETURN (lv_emp_name);

 

EXCEPTION

WHEN NO_DATA_FOUND THEN

errbuff := ‘No employee found for ‘ || p_emp_number;

retcode := ‘1’;        — warning

 

fnd_file.put_line(fnd_file.log, errbuff);

RETURN (lv_emp_name);

 

WHEN OTHERS THEN

 

errbuff := SQLERRM;

retcode := ‘2’;        — error

 

fnd_file.put_line(fnd_file.log, errbuff);

RETURN (lv_emp_name);

 

END emp_name;

 

END emp_test_pkg;

/

Advertisements

Oracle HRMS Interview Questions

1. What is interface and conversion and what interface you developed explain complete process along with error handling part.
2. API name and table for element entry
3. How to do conversion process.
4. Inbound interface name and outbounf interface name that you worked on.
5. AOL basics like tables for value set and types of value sets and table for indepdent VS.
6. Pragmatic autonomous transaction.
7. SQL function for string
8. Cascading Decode statement.
9. Bulk collect with limit syntax and limitation .
10. The largest component you have ever worked on explain that.
11. Unix basics command for file access.
12. XML publisher reports registration steps.
13. Trigger
14. All base tables name for Oracle HRMS
15. Workflow tables.
16. Hint in SQL
17. Public and private API in Oracle
18. Ref cursor and usage of that.
19. Collection type variables.
20. Count(*) will generate no data found exception or not if conditions don’t match in where clause.
21. How to generate XML file from PLSQL package.
22. How to migrate template from one instance to other.
23. How to skip records from ctl file.
24. What will be status after awaiting for shipping in o2c.
25. How to submit request set from back end.
26. Profile objective creation and order of precedence at different level.
27. Ddl dml and command for SQL.
28. Max number of the columns in oracle tables?
29. Total attributes in table atnd what is Global attribute.
30. Total segments in table kff enabled table.
31. Total size of any object in oracle database .
32. What is AuthID in create or replace. Package.
33. Difference between procedure and Function.
34. INOUT parameters what is the purpose of nocopy with that?
35. What is lookups and what all are the tables for the same.
36. If emp is terminated then what will be the current employer flag in per_all_peope_f table.
37. When to use NOCOPY parameters in parameters.
38. What is basic difference between conversion process and open Interfaces.

AME PART 5 – UNDERSTANDING HR_API_TRANSACTIONS

When a Self Service HRMS user creates any data from the screens, such data goes and resides in some temporary tables. This data is retained within the temporary tables until the transaction is Completed. A transaction in this example gets completed when Holiday Rejection complaint is either Rejected or Approved. These temporary table names begin with name HR_API%. In reality, the data entered by the user is either stored within just one table i.e. HR_API_TRANSACTIONS or within HR_API_TRANSACTION_VALUES. When the data is stored in just the HR_API_TRANSACTIONS then the entire data entered by the user is captured in a CLOB column. This feature was introduced couple of years ago, whereby for performance reasons the temporary data is captured in a clob column named HR_API_TRANSACTIONS within HR_API_TRANSACTIONS. This CLOB column contains XML contents. By now, you might have the following questions 

Continue reading →

Using AME to Approve an Activity in Self Service HRMS

Oracle Self Service Human Resources (SSHR) is all driven by a single workflow. The name of this workflow is HR and its short code is HRSSA. The workflow is kicked off as soon as any business activity is initiated in Self Service HRMS. This workflow can be customised to initiate AME for the generation of approval lists.

Let’s take approvals of Competencies for example. As standard when employees enter their Competencies, they don’t need approval. As a Professional Services consultancy, we need changes to Competencies approved so Line Managers are aware of their employee’s skill sets. As soon as you open the Competency Profile screen the HRSSA workflow is fired up.

Continue reading →

AME SSHR – PART 2 – HOW AME RELATES TO SELF SERVICE HR

 

How does Oracle know which workflow process to initiate when a user enters “Person Information” screen in SSHR

When user clicks on SSHR Menu Item, the name of the workflow process is passed as a parameter to the form function. Therefore, the sequence of events are
  • User clicks on a menu in SSHR responsibility
  • Form Function attached to Menu is invoked.
  • This Form Function invokes AK Region HR_CREATE_PROCESS_TOP_SS [See Web HTML of Form Function HR_PERINFO_SS]

Continue reading →