APP-FND-00874 Routine FDF found no rows in table FND_DESCRIPTIVE_FLEXS error during concurrent program submission.

The cause of this error is unknown.

It can be resolved by following the below steps.:

  1. Remove the affected program from the all the request groups to which it it attached.
  2. Query the concurrent program and uncheck Use In SRS and then save.
  3. Requery the program and check ‘Use in SRS’ and save again.
  4. Attach the program to request group and re-test.

Bug Reference: https://support.oracle.com/epmos/faces/BugDisplay?_afrLoop=185783879448816&id=222553&_afrWindowMode=0&_adf.ctrl-state=1a9g740e9n_4

Snippet:

To do so, you may need to toggle the Standard Submission field in the Define
Concurrent Program form (from Yes to No and back to Yes).  Then you should be
able to re-enter the parameters.

PL/SQL Compiler Limits

Came across this piece in PL/SQL Language Reference:

https://docs.oracle.com/database/121/LNPLS/limits.htm#LNPLS018

Table C-1 PL/SQL Compiler Limits

Item Limit
bind variables passed to a program unit 32768
exception handlers in a program unit 65536
fields in a record 65536
levels of block nesting 255
levels of record nesting 32
levels of subquery nesting 254
levels of label nesting 98
levels of nested collections no predefined limit
magnitude of a PLS_INTEGER orBINARY_INTEGER value -2147483648..2147483647
number of formal parameters in an explicit cursor, function, or procedure 65536
objects referenced by a program unit 65536
precision of a FLOAT value (binary digits) 126
precision of a NUMBER value (decimal digits) 38
precision of a REAL value (binary digits) 63
size of an identifier (characters) 30
size of a string literal (bytes) 32767
size of a CHAR value (bytes) 32767
size of a LONG value (bytes) 32760
size of a LONG RAW value (bytes) 32760
size of a RAW value (bytes) 32767
size of a VARCHAR2 value (bytes) 32767
size of an NCHAR value (bytes) 32767
size of an NVARCHAR2 value (bytes) 32767
size of a BFILE value (bytes) 4G * value of DB_BLOCK_SIZE parameter
size of a BLOB value (bytes) 4G * value of DB_BLOCK_SIZE parameter
size of a CLOB value (bytes) 4G * value of DB_BLOCK_SIZE parameter
size of an NCLOB value (bytes) 4G * value of DB_BLOCK_SIZE parameter
size of a trigger 32 K

SAVE EXCEPTIONS and SQL%BULK_EXCEPTION

CREATE TABLE exception_test (
  id  NUMBER(10) NOT NULL
);

set serveroutput on;

DECLARE
TYPE exc_tab
IS
TABLE OF exception_test%ROWTYPE;
l_exc exc_tab := exc_tab();
l_error_count NUMBER;
E_EXC_ERRORS EXCEPTION;
PRAGMA EXCEPTION_INIT(e_exc_errors, -24381);
BEGIN
— Fill the collection.
FOR I IN 1 .. 100
LOOP
L_EXC.extend;
l_exc(l_exc.last).id := i;
END LOOP;
–Error Statments stated below
L_EXC(50).id := NULL;
L_EXC(51).id := NULL;
–End of Error Statments
EXECUTE IMMEDIATE ‘TRUNCATE TABLE exception_test’;
— Perform a bulk operation.
BEGIN
FORALL i IN l_exc.first .. l_exc.last SAVE EXCEPTIONS
INSERT INTO EXCEPTION_TEST VALUES l_exc
(i
);
EXCEPTION
WHEN e_exc_errors THEN
l_error_count := SQL%BULK_EXCEPTIONS.count;
DBMS_OUTPUT.put_line(‘Number of failures: ‘ || l_error_count);
FOR i IN 1 .. l_error_count
LOOP
DBMS_OUTPUT.put_line
(
‘Error: ‘ || i || ‘ Array Index: ‘ || SQL%BULK_EXCEPTIONS(i).error_index || ‘ Message: ‘ || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)
)
;
END LOOP;
END;
END;
/

Associative Array Example

DECLARE
–– Define a varray of twelve strings.
TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);
–– Define an associative array of strings.
TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR) INDEX BY BINARY_INTEGER;
–– Declare and construct a varray.
month MONTHS_VARRAY :=
months_varray(‘January’,’February’,’March’,’April’,’May’,’June’
,’July’,’August’,’September’,’October’,’November’,’December’);
–– Declare an associative array variable.
calendar CALENDAR_TABLE;
BEGIN
–– Check if calendar has no elements, then add months.
IF calendar.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE(‘Assignment loop:’);
DBMS_OUTPUT.PUT_LINE(‘–——––––––––’);
FOR i IN month.FIRST..month.LAST LOOP
calendar(i) := ”;
DBMS_OUTPUT.PUT_LINE(‘Index [‘||i||’] is [‘||calendar(i)||’]’);
calendar(i) := month(i);

END LOOP;
–– Print assigned element values.
DBMS_OUTPUT.PUT(CHR(10));
DBMS_OUTPUT.PUT_LINE(‘Post-assignment loop:’);
DBMS_OUTPUT.PUT_LINE(‘––––––––––———–‘);
FOR i IN calendar.FIRST..calendar.LAST LOOP
DBMS_OUTPUT.PUT_LINE(‘Index [‘||i||’] is [‘||calendar(i)||’]’);
END LOOP;
END IF;
END;

/

Nested Table Example

TYPE card_table IS TABLE OF VARCHAR2(5 CHAR);
— Declare a nested table with null values.
cards CARD_TABLE := card_table(NULL,NULL,NULL);
BEGIN
— Print initialized null values.
dbms_output.put_line(‘Nested table initialized as null values.’);
dbms_output.put_line(‘——————–––––––––––––––––––––’);
FOR i IN 1..3 LOOP
dbms_output.put (‘Cards Varray [‘||i||’] ‘);
dbms_output.put_line(‘[‘||cards(i)||’]’);
END LOOP;
–– Assign values to subscripted members of the nested table.

cards(1) := ‘Ace’;
cards(2) := ‘Two’;
cards(3) := ‘Three’;
–– Print initialized null values.
dbms_output.put (CHR(10)); — Visual line break.
dbms_output.put_line(‘Nested table initialized as 11, 12 and 13.’);
dbms_output.put_line(‘———————–––––––––––––––––––––’);
FOR i IN 1..3 LOOP
dbms_output.put_line(‘Cards [‘||i||’] ‘||'[‘||cards(i)||’]’);
END LOOP;
END;
/

Varray Example

DECLARE
–– Define a varray with a maximum of 3 rows.
TYPE integer_varray IS VARRAY(3) OF INTEGER;
–– Declare the varray with null values.
varray_integer INTEGER_VARRAY := integer_varray(NULL,NULL,NULL);
BEGIN
— Print initialized null values.
dbms_output.put_line(‘Varray initialized as nulls.’);
dbms_output.put_line(‘––––––––––––––————–‘);
FOR i IN 1..3 LOOP

dbms_output.put (‘Integer Varray [‘||i||’] ‘);
dbms_output.put_line(‘[‘||varray_integer(i)||’]’);
END LOOP;
–– Assign values to subscripted members of the varray.
varray_integer(1) := 11;
varray_integer(2) := 12;
varray_integer(3) := 13;

— Print initialized null values.
dbms_output.put (CHR(10)); –- Visual line break.
dbms_output.put_line(‘Varray initialized as values.’);
dbms_output.put_line(‘––-––––––––––––————–‘);
FOR i IN 1..3 LOOP
dbms_output.put_line(‘Integer Varray [‘||i||’] ‘
|| ‘[‘||varray_integer(i)||’]’);
END LOOP;
END;
/

The syntax of defining varray as an object in database is:

CREATE OR REPLACE TYPE type_name AS {VARRAY | VARYING ARRAY} (size_limit)
OF element_type [ NOT NULL ];

Collections

Collections are programming structures that hold sets of like things. Collections fall into two categories: arrays and lists. Arrays typically have a physical size allocated when you define them, while lists have no physical limit imposed. These lists are often indexed by a series of sequential numbers that start with 0 or 1 and increase one value at a time.  Lists can also be indexed by non-sequential numbers or unique strings. Lists are called associative arrays when they can be indexed by non-sequential numbers or unique strings.The VARRAY and NESTED TABLE collections can be defined as both SQL and PL/SQL datatypes while Associative Array (previously known as PL/SQL Table) is only a PL/SQL datatype. Numeric indexes for associative arrays do not need to be sequential and are non-sequential structures. Non-sequential structures can have gaps in index sequences and are known as sparsely populated structures. Associative arrays are dynamically sized and, like the NESTED TABLE datatype, have no fixed size.

When to use what?

1. Use a varray when the physical size of the collection is static and the collection may be used in tables. Varrays are the closest thing to arrays in other programming languages, such as Java, C, C++, or C#.

2. Use nested tables when the physical size is unknown due to run-time variations and when the type may be used in tables. Nested tables are like lists and bags in other programming languages.

3.Use associative arrays when the physical size is unknown due to run-time variations and when the type will not be used in tables. Associative arrays are ideal for standard programming solutions, such as using maps and sets.

VARRAY :

TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit)
OF element_type [ NOT NULL ];

CREATE OR REPLACE TYPE type_name AS {VARRAY | VARYING ARRAY} (size_limit)
OF element_type [ NOT NULL ];

Varray example: http://wp.me/p1YiQQ-11

NESTED Table:

TYPE type_name IS TABLE OF element_type [ NOT NULL ];

CREATE OR REPLACE TYPE type_name
AS TABLE OF element_type [ NOT NULL ];

Nested table example :http://wp.me/p1YiQQ-15

Associative Arrays:

Associative arrays are single-dimensional structures of an Oracle 11g datatype or a user-defined
record/object type. Associative arrays cannot be used in tables. They may be used only as programming structures. They can be accessed only in PL/SQL.

1.

Do not require initialization and have no constructor syntax. They do not need to
allocate space before assigning values, which eliminates using the Collection API
EXTEND method.

2.

Can be indexed numerically up to and including Oracle 11g. In Oracle 11g, they can
also use unique variable-length strings.

3.

Can use any integer as the index value, which means any negative, positive, or zero
whole number.

4.

Are implicitly converted from equivalent %ROWTYPE, record type, and object type return
values to associative array structures.

5.

Are the key to using the FORALL statement or BULK COLLECT clause, which enables
bulk transfers of records from a database table to a programming unit.

6.

Require special treatment when using a character string as an index value in any
database using globalized settings, such as the NLS_COMP or NLS_SORT initialization
parameters.

Syntax for creating as Associative Array:

CREATE OR REPLACE TYPE type_name AS TABLE OF element_type [ NOT NULL ]
INDEX BY [ PLS_INTEGER | BINARY_INTEGER | VARCHAR2(size) ];

Associative Array Example: http://wp.me/p1YiQQ-17

Query to find Scheduled Concurrent Requests

SELECT DISTINCT frl.responsibility_name,
fu.user_name,
fcr.request_id,
(SELECT meaning
FROM apps.fnd_lookups
WHERE lookup_type=’CP_PHASE_CODE’
AND lookup_code =fcr.phase_code
) Phase,
(SELECT meaning
FROM apps.fnd_lookups
WHERE lookup_type=’CP_STATUS_CODE’
AND lookup_code =fcr.status_code
) Status,
fcs.program,
to_date(fcr.requested_start_date,’DD-MM-RRRR hh24:mi:ss’) Start_Date,
fcr.resubmit_interval
||’ ‘
||fcr.resubmit_interval_unit_code Resubmit_Interval,
NVL2(fcr.resubmit_interval,’PERIODICALLY’,NVL2(fcr.release_class_id, ‘ON SPECIFIC DAYS’,’ONCE’)) schedule_type,
fcs.argument_text
FROM apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs_tl fcp,
apps.fnd_responsibility_tl frl,
apps.fnd_user fu,
apps.fnd_conc_req_summary_v fcs
WHERE fcr.phase_code =’P’
AND fcr.request_id = fcs.request_id
AND frl.language =’US’
AND fcr.requested_by =fu.user_id
AND fcr.responsibility_id =frl.responsibility_id
AND fcr.status_code IN (‘P’,’Q’,’I’)
–AND fcp.language =’US’
AND fcp.source_lang =’US’
AND (NVL(fcr.request_type, ‘X’) != ‘S’)
AND fcr.concurrent_program_id =fcp.concurrent_program_id
AND fcr.requested_start_date >= SYSDATE
AND to_date(fcr.requested_start_date,’DD-MM-RRRR hh24:mi:ss’) BETWEEN NVL(to_date(:p_from_date,’DD-MM-RRRR hh24:mi:ss’),fcr.requested_start_date) AND NVL(to_date(:p_to_date,’DD-MM-RRRR hh24:mi:ss’),fcr.requested_start_date)
ORDER BY program DESC