By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TYPE SOME_OBJECT AS OBJECT (
ATTR1 VARCHAR2(20)
, ATTR2 VARCHAR2(30)
);
/
CREATE TYPE C_SOME_OBJECT AS TABLE OF SOME_OBJECT;
/
CREATE PROCEDURE SOME_PROCEDURE(
I_OBJECT IN C_SOME_OBJECT
)
IS
i PLS_INTEGER;
BEGIN
-- Check that the collection is not NULL
IF I_OBJECT IS NULL THEN
RETURN;
END IF;
i := I_OBJECT.FIRST;
WHILE i IS NOT NULL LOOP
IF I_OBJECT(i) IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE( i || ' = (' || I_OBJECT(i).attr1 || ', ' || I_OBJECT(i).attr2 || ')' );
ELSE
DBMS_OUTPUT.PUT_LINE( i || ' IS NULL' );
END IF;
i := I_OBJECT.NEXT(i);
END LOOP;
END;
/
DECLARE
o C_SOME_OBJECT := C_SOME_OBJECT(
SOME_OBJECT( '1.1', '1.2' ),
SOME_OBJECT( '2.1', '2.2' ),
NULL,
SOME_OBJECT( '4.1', '4.2' )
);
BEGIN
o.DELETE(2);
DBMS_OUTPUT.PUT_LINE('Starting');
SOME_PROCEDURE( o );
END;
/
1 rows affected
dbms_output:
Starting
1 = (1.1, 1.2)
3 IS NULL
4 = (4.1, 4.2)
DECLARE
o C_SOME_OBJECT := C_SOME_OBJECT(
SOME_OBJECT( '1.1', '1.2' ),
SOME_OBJECT( '2.1', '2.2' ),
NULL,
SOME_OBJECT( '4.1', '4.2' )
);
BEGIN
o.DELETE(2);
FOR i IN 1 .. o.COUNT LOOP
IF o(i) IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE( i || ' = (' || o(i).attr1 || ', ' || o(i).attr2 || ')' );
ELSE
DBMS_OUTPUT.PUT_LINE( i || ' IS NULL' );
END IF;
END LOOP;
END;
/
dbms_output:
1 = (1.1, 1.2)
ORA-01403: no data found
ORA-06512: at line 12
DECLARE
o C_SOME_OBJECT := C_SOME_OBJECT(
SOME_OBJECT( '1.1', '1.2' ),
SOME_OBJECT( '2.1', '2.2' ),
NULL,
SOME_OBJECT( '4.1', '4.2' )
);
BEGIN
o.DELETE(2);
FOR i IN 1 .. o.COUNT LOOP
BEGIN
IF o(i) IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE( i || ' = (' || o(i).attr1 || ', ' || o(i).attr2 || ')' );
ELSE
DBMS_OUTPUT.PUT_LINE( i || ' IS NULL' );
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE( i || ' NOT FOUND' );
END;
END LOOP;
END;
/
1 rows affected
dbms_output:
1 = (1.1, 1.2)
2 NOT FOUND
3 IS NULL
DECLARE
o C_SOME_OBJECT := C_SOME_OBJECT(
SOME_OBJECT( '1.1', '1.2' ),
SOME_OBJECT( '2.1', '2.2' ),
NULL,
SOME_OBJECT( '4.1', '4.2' )
);
BEGIN
o.DELETE(2);
FOR i IN o.FIRST .. o.LAST LOOP
BEGIN
IF o(i) IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE( i || ' = (' || o(i).attr1 || ', ' || o(i).attr2 || ')' );
ELSE
DBMS_OUTPUT.PUT_LINE( i || ' IS NULL' );
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE( i || ' NOT FOUND' );
END;
END LOOP;
END;
/
1 rows affected
dbms_output:
1 = (1.1, 1.2)
2 NOT FOUND
3 IS NULL
4 = (4.1, 4.2)