Thursday, July 24, 2014

ORA-22160: element at index [9] does not exist

ORA-22160 Error For FORALL

We have been just amazed at how much better our programs perform when we use FORALL to do our inserts and updates. We are now building a new application on Oracle Database 10gRelease 2, and we have run into a problem. In all previous usages of FORALL, we would generally take a collection that was populated with a BULK COLLECT and push it into one or more tables.
Now we have a more complicated scenario, in which we must go through our collection of "candidate" data for inserts and remove some (perhaps all) of the rows before doing the insert. When we try to use FORALL, we get this error message: 
ORA-22160: element at index [2750] does
not exist

How can we avoid this error and get all our data inserted?
I agree that FORALL is wonderful—one of the most important enhancements to PL/SQL since Oracle8i was released. And back in Oracle8i and Oracle9i Database, it is true that the only format with which you could use FORALL was this:  
FORALL index_variable
IN low_value .. high_value
   <DML_Statement>;

And as in a "regular" numeric FOR loop, FORALL will iterate through each integer between low_value and high_value , using that integer to identify an element in all collections that are bound into the DML statement with the index_variable . If no element exists at a particular index value, Oracle Database raises an exception, as you can see in the example in Listing 3.

Code Listing 3: Raising ORA-22160 

DECLARE
   TYPE list_of_names_t IS TABLE OF VARCHAR2(32767)
      INDEX BY PLS_INTEGER;

   happyfamily                                  list_of_names_t;
BEGIN
   happyfamily( 1)                                          := 'Eli';
   happyfamily( 2)                                          := 'Chris';
   happyfamily( 3)                                          := 'Veva';
   happyfamily( 5)                                          := 'Steven';

   FORALL indx IN happyfamily.FIRST .. happyfamily.LAST
      INSERT INTO first_names
           VALUES (happyfamily( indx));
END;
/
DECLARE
*
ERROR at line 1:
ORA-22160: element at index [4] does not exist

FORALL, in other words, requires a sequentially or densely filled collection. Now if you were still running Oracle8i or Oracle9i Database and wanted to fix this problem, you would have to copy the data from your sparsely filled collection over to one without any gaps. From a performance standpoint, this is nothing to worry about; manipulating collections is very fast. But it does involve writing and maintaining even more code.
In Oracle Database 10g, Oracle added two new clauses to the FORALL statement: INDICES OF and VALUES OF. They allow you to avoid the restriction on using densely filled collections. Instead of using an IN range of values, you can point to a collection (usually, but not necessarily, the same collection that is referenced inside the FORALL's DML statement) and say, in effect, "Use only those index values defined in that other collection" (INDICES OF) or "Use only index values that are found in the elements of that other collection" (VALUES OF).
Here is a rewrite of the code in Listing 3 that avoids the ORA-22160 error (notice the boldfacedlines): 

DECLARE
  TYPE list_of_names_t
  IS TABLE OF VARCHAR2 (32767)
     INDEX BY PLS_INTEGER;

  happyfamily   list_of_names_t;
BEGIN
  happyfamily (1) := 'Eli';
  happyfamily (2) := 'Chris';
  happyfamily (3) := 'Veva';
  happyfamily (5) := 'Steven';

  FORALL indx
  IN INDICES OF happyfamily
    INSERT INTO first_names
         VALUES (happyfamily (indx));
END;
/

That is an example of the simplest way to apply INDICES OF: "self- reference" the same collection used within the DML statement, to easily avoid errors due to sparseness in that collection.
Now let's take a look at VALUES OF. This clause comes in very handy when you want to use only a subset of the collection to be used within the DML statement.
Suppose, for example, that I have a procedure that accepts a collection of employee records and should insert only records for employees with a salary of $10,000 or more. Listing 4 contains the package specification and the body for this employees_dml program.
Code Listing 4: Package and body for employees_dml 

CREATE OR REPLACE PACKAGE employees_dml
IS
   TYPE employees_aat IS TABLE OF employees%ROWTYPE
      INDEX BY PLS_INTEGER;

   PROCEDURE insert_some (employees_in IN employees_aat);
END employees_dml;
/

CREATE OR REPLACE PACKAGE BODY employees_dml
IS
   PROCEDURE insert_some( employees_in IN employees_aat)
   IS
      TYPE index_values_aat IS TABLE OF PLS_INTEGER
         INDEX BY PLS_INTEGER;

      l_values_of                                  index_values_aat;
      l_index                                      PLS_INTEGER;
   BEGIN
      l_index                                                  := employees_in.FIRST;

      WHILE (l_index IS NOT NULL)
      LOOP
         IF employees_in( l_index).salary >= 10000
         THEN
            l_values_of( l_values_of.COUNT + 1)                      := l_index;
         END IF;

         l_index                                                  := employees_in.NEXT( l_index);
      END LOOP;

      FORALL indx IN VALUES OF l_values_of
         INSERT INTO employees
              VALUES employees_in( indx);
   END insert_some;
END employees_dml;

Lines 5 through 9 in Listing 4 declare the VALUES OF collection, a collection of PLS_INTEGER values. Then in my WHILE loop (lines 14 through 22), I populate a row in l_values_of with the index value from employees_in, only if the salary in that record is at least $10,000.
Thus, when I get to the FORALL statement (lines 24 through 26), the VALUES OF clause ensures that all other employee records are ignored.
If you have the standard Oracle employees table installed with default data, you can run the script in Listing 5 to verify the behavior of the employees_dml package.

Code Listing 5: Verifying behavior of employees_dml package 

SELECT COUNT(*)
  FROM employees
WHERE salary < 10000
/

DECLARE
   l_employees                   employees_dml.employees_aat;
BEGIN
   SELECT *
   BULK COLLECT INTO l_employees
     FROM employees;

   DELETE FROM employees;

   employees_dml.insert_some (l_employees);
END;
/

SELECT COUNT(*)
  FROM employees
WHERE salary < 10000
/

ROLLBACK
/

  COUNT(*)
-------------------------
            88

1 row selected.
PL/SQL procedure successfully completed.


  COUNT(*)
-------------------------
             0

1 row selected.
Rollback complete.

Finally, you can also use INDICES OF with an entirely different collection that serves as a kind of filter for the collections used in the DML statement.
Listing 6 shows an example of this approach.
Code Listing 6: Using INDICES OF as a filter 

DECLARE
   TYPE employee_aat IS TABLE OF employees.employee_id%TYPE
      INDEX BY PLS_INTEGER;

   l_employees                                  employee_aat;

   TYPE boolean_aat IS TABLE OF BOOLEAN
      INDEX BY PLS_INTEGER;

   l_employee_indices                           boolean_aat;
BEGIN
   l_employees( 1)                                          := 137;
   l_employees( 100)                                        := 126;
   l_employees( 500)                                        := 147;
   --
   l_employee_indices( 1)                                   := FALSE;
   l_employee_indices( 500)                                 := TRUE;
   l_employee_indices( 799)                                 := NULL;

   --
   FORALL l_index IN INDICES OF l_employee_indices BETWEEN 1 AND 500
      UPDATE employees
         SET salary                                                    = 10000
       WHERE employee_id = l_employees( l_index);
END;

In this code, I use the index values of defined elements in the l_employee_indices collection to specify which elements of the l_employees collection to use in my update statement. Note that on line 21 of Listing 6, I include a BETWEEN clause to constrain which of the index values of l_employee_indices will be used. So INDICES OF should fix your problem in this case.
Using Encapsulation Without Privileges
I have taken to heart your advice about writing SQL statements (don't write SQL in application-level code; hide it behind a packaged API, with as much of it generated as possible). I also decided (and I am a team leader, so my decision carries some weight) to go the full route, and I revoked privileges on the tables, so my developers have no choice but to use the encapsulation packages.
Here's my problem: another of your best practices is to avoid hard-coded declarations and to anchor variables back to database tables and columns with %TYPE and %ROWTYPE. But my people can't do that, because I revoked privileges and they need the SELECT privilege on a table to do that.
What's a best-practice-oriented guy supposed to do?
So nice to hear that you are going the encapsulation route! I have used it myself in recent years in every single one of my development projects, and I really can't imagine going back to the "old way" of writing all the SQL statements every time I need them.
And I am very impressed to hear about your decision to revoke table privileges. That's a hard thing to do, but once the revocation is in place, it can greatly improve the robustness of your application.
Yet, as you point out, it leads to an interesting conflict of best practices.
Here's how I resolve this problem: I generate three table API packages for each of my tables: 
·         A change package that implements all the basic INSERT, UPDATE, and DELETE operations
·         A query package that gives me a wide range of functions for querying data from the table
·         A types package that creates a set of subtypes , which in essence hide the %TYPE and %ROWTYPE declarations and ensure that I don't even need SELECT authority on tables to build high-quality code
How does it work? Simple.
Suppose I am building an application to maintain a category of things my company sells. One of my tables is cat_tools, which contains information about tools. The table has a name, a description, and a universal_id (primary key, defined as a GUID, or globally unique identifier). Listing 7, includes a portion of the types package for this table.
Code Listing 7: Creating a type package as an API 

CREATE OR REPLACE PACKAGE cat_tools_tp
IS
   SUBTYPE cat_tools_rt IS cat_tools%ROWTYPE;

   SUBTYPE universal_id_t IS cat_tools.universal_id%TYPE;

   SUBTYPE name_t IS cat_tools.NAME%TYPE;

   SUBTYPE description_t IS cat_tools.description%TYPE;

   TYPE table_refcur IS REF CURSOR
      RETURN cat_tools%ROWTYPE;

   TYPE cat_tools_tc IS TABLE OF cat_tools%ROWTYPE
      INDEX BY PLS_INTEGER;

   TYPE universal_id_cc IS TABLE OF cat_tools.universal_id%TYPE
      INDEX BY BINARY_INTEGER;
END cat_tools_tp;
/

So now assume that the cat_tools table and both the cat_tools_tp (types) and cat_tools_qp (query) packages are defined in the CATALOG schema. I grant EXECUTE on cat_tools_tp and cat_tools_qp to HR. Then from HR, I can write the block of code in Listing 8.
Code Listing 8: Access using the type and query packages 

DECLARE
   /* A string to hold the assertion name */
   l_name CATALOG.cat_tools_tp.name_t;
  
   /* A collection to hold the set of tools rows. */
   l_tools CATALOG.cat_tools_tp.cat_tools_tc;
BEGIN
   /* The allrows function retrieves all rows from cat_tools. */
   l_tools := CATALOG.cat_tools_qp.allrows;

   /* Assign each name from collection to local variable. */
   FOR indx IN 1 .. l_tools.COUNT
   LOOP
      l_name := l_tools (indx).NAME;
   END LOOP;
END;
/

As you can see, even though the HR schema has no access to the cat_tools table, I am able to declare variables by using datatypes that are anchored (indirectly) to the table and its columns. I can also query the contents of the table.

And if the table ever changes, the types package will also change (that is, you will regenerate it). All programs that reference this package will then have to be recompiled; thus, you have the same dependencies you would have with %ROWTYPE and %TYPE declarations.

Friday, July 11, 2014

TCA API: Sample Code to Create AR Customer & Site from AP Supplier & Supplier Site using same Party


Doc ID 1447883.1

We will use following sql to find out Party details from Supplier tables

SELECT DISTINCT a.party_number, ps.party_site_number, a.party_id,
'CUST-' ||a.party_id||'-'||ass.org_id AS customer_ref,
'ADD-'||ps.party_site_id||'-'||ass.org_id AS address_ref,
ps.party_site_id,
ass.address_line1,
ass.city, ass.state, ass.zip, ass.country,
a.orig_system_reference AS party_ref,
a.party_name AS customer_name, 
ass.org_id
FROM ap_suppliers ap,ap_supplier_sites_all ass,
hz_parties a,hz_party_sites ps
WHERE ap.vendor_id = ass.vendor_id
AND a.party_id = ap.party_id
AND ps.party_id = ap.party_id
AND ass.party_site_id = ps.party_site_id
AND a.party_name = ap.vendor_name
and a.party_id = 12465 -- Please pass your Party_id or Comment this line
and NOT Exists (select 1 from hz_cust_accounts a
where a.party_id = a.party_id);


DECLARE
 l_num_user_id       NUMBER;
 l_num_appl_id       NUMBER;
 l_num_resp_id       NUMBER;
 cust_account_rec_type   hz_cust_account_v2pub.cust_account_rec_type;
 l_num_obj_ver_num     NUMBER;
 l_chr_return_status    VARCHAR2 (2000);
 l_num_msg_count      NUMBER;
 l_chr_msg_data       VARCHAR2 (500);
 l_num_profile_id      NUMBER;
 l_organization_rec     hz_party_v2pub.organization_rec_type;
 l_customer_profile_rec   hz_customer_profile_v2pub.customer_profile_rec_type;
 l_num_cust_id       NUMBER;
 l_chr_acct_num       VARCHAR2 (500);
 l_num_party_id       NUMBER;
 l_chr_party_number     VARCHAR2 (500);
 l_cust_acct_site_use_rec  hz_cust_account_site_v2pub.cust_site_use_rec_type;
 l_cust_acct_site_rec    hz_cust_account_site_v2pub.cust_acct_site_rec_type;
 l_chr_sit_return_status  VARCHAR2 (500);
 l_num_sit_msg_count    NUMBER;
 l_chr_sit_msg_data     VARCHAR2 (500);
 l_chr_situ_return_status  VARCHAR2 (500);
 l_num_situ_msg_count    NUMBER;
 l_chr_situ_msg_data    VARCHAR2 (500);
 l_num_site_use_id     NUMBER;
 CURSOR update_base_tables_cur
 IS
   SELECT DISTINCT a.party_number, ps.party_site_number, a.party_id,
           'CUST-' ||a.party_id||'-'||ass.org_id AS customer_ref,
           'ADD-'||ps.party_site_id||'-'||ass.org_id AS address_ref,
           ps.party_site_id,
           ass.address_line1,
           ass.city, ass.state, ass.zip, ass.country,
           'N' primary_site_use_flag,
           a.orig_system_reference AS party_ref,
           a.party_name AS customer_name, ass.org_id
        FROM apps.ap_suppliers ap,
           apps.ap_supplier_sites_all ass,
           apps.hz_parties a,
           apps.hz_party_sites ps
        WHERE ap.vendor_id = ass.vendor_id
         AND a.party_id = ap.party_id
         AND ps.party_id = ap.party_id
         AND ass.party_site_id = ps.party_site_id
         AND a.party_name = ap.vendor_name
         and a.party_id = 12465 -- modify this part/query
         --and ass.org_id = 204
         and NOT Exists (select 1 from hz_cust_accounts a
         where a.party_id = a.party_id);
BEGIN
FND_GLOBAL.APPS_INITIALIZE( &user_id, &responsibility_id, 222); -- input from 1st sql
dbms_output.put_line('***************************');
 FOR update_base_tables_rec IN update_base_tables_cur
 LOOP
   NULL;
   cust_account_rec_type.cust_account_id := fnd_api.g_miss_num;
   cust_account_rec_type.account_name := update_base_tables_rec.customer_name;
   l_organization_rec.party_rec.party_id := update_base_tables_rec.party_id;
   l_organization_rec.party_rec.party_number := update_base_tables_rec.party_number;
   l_organization_rec.organization_name := update_base_tables_rec.customer_name;
   cust_account_rec_type.orig_system_reference := update_base_tables_rec.customer_ref;
   l_customer_profile_rec.party_id := update_base_tables_rec.party_id;
   l_customer_profile_rec.profile_class_id := 0 ; -- use DEFAULT profile with id=0
   l_customer_profile_rec.created_by_module := 'HZ_CPUI';
   cust_account_rec_type.created_by_module := 'HZ_CPUI';
   hz_cust_account_v2pub.create_cust_account
             (p_init_msg_list       => fnd_api.g_false,
              p_cust_account_rec     => cust_account_rec_type,
              p_organization_rec     => l_organization_rec,
              p_customer_profile_rec   => l_customer_profile_rec,
              p_create_profile_amt    => fnd_api.g_true,
              x_cust_account_id      => l_num_cust_id,
              x_account_number      => l_chr_acct_num,
              x_party_id         => l_num_party_id,
              x_party_number       => l_chr_party_number,
              x_profile_id        => l_num_profile_id,
              x_return_status       => l_chr_return_status,
              x_msg_count         => l_num_msg_count,
              x_msg_data         => l_chr_msg_data
             );
dbms_output.put_line('x_return_status: '||l_chr_return_status);
dbms_output.put_line('x_cust_account_id: '||l_num_cust_id);
dbms_output.put_line('x_account_number: '||l_chr_acct_num);
dbms_output.put_line('x_party_id: '||l_num_party_id);
   IF l_chr_return_status != 'S'
   THEN
    --Display all the error messages
    FOR j IN 1 .. fnd_msg_pub.count_msg
    LOOP
      DBMS_OUTPUT.put_line (j);
      l_chr_msg_data :=
            fnd_msg_pub.get (p_msg_index   => j,
                     p_encoded    => 'F');
      DBMS_OUTPUT.put_line ('Message(' || j || '):= ' || l_chr_msg_data);
    END LOOP;
   END IF;
   BEGIN
    SELECT cust_account_id
     INTO l_cust_acct_site_rec.cust_account_id
     FROM hz_cust_accounts
     WHERE orig_system_reference = update_base_tables_rec.customer_ref;
   EXCEPTION
    WHEN OTHERS
    THEN
      l_cust_acct_site_rec.cust_account_id := fnd_api.g_miss_num;
   END;
   l_cust_acct_site_rec.party_site_id := update_base_tables_rec.party_site_id;
   l_cust_acct_site_rec.created_by_module := 'HZ_CPUI';
   l_cust_acct_site_rec.orig_system_reference := update_base_tables_rec.address_ref;
   l_cust_acct_site_rec.status := 'A';
   l_cust_acct_site_rec.org_id := update_base_tables_rec.org_id;
   mo_global.init ('ONT');
   mo_global.set_policy_context (p_access_mode   => 'S',
                  p_org_id      => update_base_tables_rec.org_id
                 );
   hz_cust_account_site_v2pub.create_cust_acct_site
                (p_init_msg_list      => 'T',
                p_cust_acct_site_rec   => l_cust_acct_site_rec,
                x_cust_acct_site_id    => l_num_obj_ver_num,
                x_return_status      => l_chr_sit_return_status,
                x_msg_count        => l_num_sit_msg_count,
                x_msg_data        => l_chr_sit_msg_data
                );
dbms_output.put_line('x_cust_acct_site_id: '||l_num_obj_ver_num);
dbms_output.put_line('x_return_status: '||l_chr_sit_return_status);
   IF l_chr_sit_return_status != 'S'
   THEN
    --Display all the error messages
    FOR j IN 1 .. fnd_msg_pub.count_msg
    LOOP
      DBMS_OUTPUT.put_line (j);
      l_chr_sit_msg_data :=
            fnd_msg_pub.get (p_msg_index   => j,
                     p_encoded    => 'F');
      DBMS_OUTPUT.put_line (  'Site Message('
                 || j
                 || '):= '
                 || l_chr_sit_msg_data
                );
    END LOOP;
   END IF;
   BEGIN
    SELECT cust_acct_site_id
     INTO l_cust_acct_site_use_rec.cust_acct_site_id
     FROM hz_cust_acct_sites_all
     WHERE orig_system_reference = update_base_tables_rec.address_ref;
   EXCEPTION
    WHEN OTHERS
    THEN
      l_cust_acct_site_use_rec.cust_acct_site_id := fnd_api.g_miss_num;
   END;
   l_cust_acct_site_use_rec.org_id := update_base_tables_rec.org_id;
   l_cust_acct_site_use_rec.site_use_code := 'BILL_TO';
   l_cust_acct_site_use_rec.status := 'A';
   l_cust_acct_site_use_rec.primary_flag := 'Y';
   l_cust_acct_site_use_rec.orig_system_reference :=
                      update_base_tables_rec.address_ref;
   l_cust_acct_site_use_rec.created_by_module := 'HZ_CPUI';
   mo_global.set_policy_context (p_access_mode   => 'S',
                  p_org_id      => update_base_tables_rec.org_id
                 );
   hz_cust_account_site_v2pub.create_cust_site_use
              (p_init_msg_list       => 'T',
              p_cust_site_use_rec     => l_cust_acct_site_use_rec,
              p_customer_profile_rec   => l_customer_profile_rec,
              p_create_profile      => fnd_api.g_true,
              p_create_profile_amt    => fnd_api.g_true,
              x_site_use_id        => l_num_site_use_id,
              x_return_status       => l_chr_situ_return_status,
              x_msg_count         => l_num_situ_msg_count,
              x_msg_data         => l_chr_situ_msg_data
              );
dbms_output.put_line('x_site_use_id: '||l_num_site_use_id);
dbms_output.put_line('x_return_status: '||l_chr_situ_return_status);
   IF l_chr_situ_return_status != 'S'
   THEN
    --Display all the error messages
    FOR j IN 1 .. fnd_msg_pub.count_msg
    LOOP
      DBMS_OUTPUT.put_line (j);
      l_chr_situ_msg_data :=
            fnd_msg_pub.get (p_msg_index   => j,
                     p_encoded    => 'F');
      DBMS_OUTPUT.put_line (  'Site Use Message('
                 || j
                 || '):= '
                 || l_chr_situ_msg_data
                );
    END LOOP;
   END IF;
 END LOOP;
END;
/

Oracle Trading Community: TCA API Overview, Setup, Sample Scripts and Troubleshooting (Doc ID 1637029.2)

Metalink Id for TCA API's

Oracle Trading Community: TCA API Overview, Setup, Sample Scripts and Troubleshooting (Doc ID 1637029.2)

Monday, July 7, 2014

Function to convert number to Word


FUNCTION to_word_i( amount IN NUMBER)
   RETURN VARCHAR2
AS
   v_length                                     INTEGER := 0;
   v_num2                                       VARCHAR2(50) := NULL;
   v_amount                                     VARCHAR2(50) := TO_CHAR( TRUNC( amount));
   v_word                                       VARCHAR2(4000) := NULL;
   v_word1                                      VARCHAR2(4000) := NULL;

   TYPE myarray IS TABLE OF VARCHAR2(255);

   v_str                                        myarray
                                                   := myarray(
                                                         ' thousand '
                                                       , ' lakh '
                                                       , ' crore '
                                                       , ' arab '
                                                       , ' kharab '
                                                       , ' shankh ');
BEGIN
   IF TO_CHAR( amount) LIKE '%.%'
   THEN
      IF SUBSTR( amount, INSTR( amount, '.') + 1) > 0
      THEN
         v_num2                                    := SUBSTR( amount, INSTR( amount, '.') + 1);

         IF LENGTH( v_num2) < 2
         THEN
            v_num2                                    := v_num2 * 10;
         END IF;

         v_length                                  := LENGTH( v_num2);
         v_word1                                   :=
               ' AND '
            || (TO_CHAR( TO_DATE( SUBSTR( v_num2, LENGTH( v_num2) - 1, 2), 'j'), 'Jsp'))
            || ' paise ';
      END IF;
   END IF;

   IF v_amount = '0'
   THEN
      v_word                                    := 'zero';
   ELSE
      IF (SUBSTR( v_amount, LENGTH( v_amount) - 2, 3) <> 0)
      THEN
         v_word                                    :=
               TO_CHAR( TO_DATE( SUBSTR( v_amount, LENGTH( v_amount) - 2, 3), 'J'), 'Jsp')
            || v_word;
      END IF;

      v_amount                                  := SUBSTR( v_amount, 1, LENGTH( v_amount) - 3);

      FOR i IN 1 .. v_str.COUNT
      LOOP
         EXIT WHEN v_amount IS NULL;

         IF (SUBSTR( v_amount, LENGTH( v_amount) - 1, 2) <> 0)
         THEN
            v_word                                    :=
                  TO_CHAR( TO_DATE( SUBSTR( v_amount, LENGTH( v_amount) - 1, 2), 'J'), 'Jsp')
               || v_str( i)
               || v_word;
         END IF;

         v_amount                                  := SUBSTR( v_amount, 1, LENGTH( v_amount) - 2);
      END LOOP;
   END IF;

   v_word                                    :=
         v_word
      || ' '
      || v_word1
      || ' only ';
   v_word                                    := REPLACE( RTRIM( v_word), '  ', ' ');
   v_word                                    := REPLACE( RTRIM( v_word), '-', ' ');
   RETURN INITCAP( v_word);
END to_word_i;