DB Version : 11.2.0.4.0
**Issue : ** While running the below function inside the package , I am getting the below error , while running the same function independently , it runs with success.
STEP :
- Initial error was
- Created the collection at the schema level to overcome the problem
CREATE TYPE l_part_rec_type IS OBJECT
(
partition_name VARCHAR2 (128),
high_value VARCHAR2 (4000),
partition_date DATE
);
/
CREATE OR REPLACE TYPE l_part_tab_type is TABLE of l_part_rec_type;
/
3. After this ,start getting below error
inside PACKAGE --failure
ERROR : - PLS-00386: type mismatch found between FETCH cursor and INTO
INDEPENDENT BLOCK--SUCCESS
DECLARE
l_table_name VARCHAR2(100):='WMS_ECOM_ORD_PUB_XML';
CURSOR c_partition (p_table_name VARCHAR2)
IS
WITH
c_master_data
AS
( SELECT a.partition_name,
EXTRACTVALUE (
DBMS_XMLGEN.getxmltype (
'SELECT high_value
FROM all_tab_partitions
WHERE table_name='''
|| a.table_name
|| ''' AND
table_owner = '''
|| a.table_owner
|| ''' AND
partition_name = '''
|| a.partition_name
|| ''''),
'//text()') high_value,
NULL partition_date
FROM all_tab_partitions a
INNER JOIN all_part_tables b
ON ( a.table_owner = b.owner
AND a.table_name = b.table_name)
INNER JOIN ( SELECT owner,
name,
column_name,
object_type
FROM all_part_key_columns
GROUP BY owner,
name,
column_name,
object_type
HAVING COUNT (1) = 1) c
ON ( a.table_owner = c.owner
AND a.table_name = c.name
AND c.object_type = 'TABLE')
INNER JOIN all_tab_columns d
ON ( a.table_owner = d.owner
AND a.table_name = d.table_name
AND c.column_name = d.column_name)
WHERE a.table_owner = 'WMSPRD'
AND a.table_name = p_table_name
AND b.partitioning_type = 'RANGE'
AND b.subpartitioning_type = 'NONE'
AND b.status = 'VALID'
AND ( d.data_type = 'DATE'
OR d.data_type LIKE 'TIMESTAMP%')
ORDER BY a.table_name, a.partition_position)
SELECT partition_name,
CASE
WHEN INSTR (high_value, 'TIMESTAMP') > 0
THEN
'TO_DATE('
|| REPLACE (high_value, 'TIMESTAMP')
|| ', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')'
ELSE
high_value
END high_value,
NULL partition_date
FROM c_master_data;
TYPE l_part_rec_type IS RECORD
(
partition_name VARCHAR2 (128),
high_value VARCHAR2 (4000),
partition_date DATE
);
TYPE l_part_tab_type IS TABLE OF l_part_rec_type;
l\_tab l\_part\_tab\_type;
BEGIN
OPEN c_partition (l_table_name);
FETCH c\_partition BULK COLLECT INTO l\_tab LIMIT 200; --getting error here
CLOSE c\_partition;
DBMS\_OUTPUT.PUT\_LINE(l\_tab.count);
END;