CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
Jones
Jones

Reputation: 19

How to correctly use an object type in an Oracle stored procedure?

I have this type definition:

Create Type "T1" as Table of Varchar2(10);/

This got executed.

How to use the T1 type variable in PL/SQL?. I have the following procedure:

Create or replace PROCEDURE P1
AS
  P_K   T1;
BEGIN
  SELECT P_K_J INTO P_K FROM SOME_TABLE WHERE NAME='JONES' ;
  FOR I IN P_K.FIRST..P_K.LAST LOOP
    DBMS_OUTPUT.PUT_LINE('THE VALUES OF P_K ARE' || P_K(I));
  END LOOP;
END P1;

This gives the following error:

ERROR:
3/5      PL/SQL: Item ignored
3/5      PLS-00311: the declaration of "T1" is incomplete or malformed

My question is: how to instantiate a variable of type T1

Upvotes: 0

Views: 1853

Answers (1)

Paciv
Paciv

Reputation: 1487

Create Type "T1" as Table of Varchar2(10);
/
Create or replace PROCEDURE P1
AS
  P_K   T1;
BEGIN

  SELECT P_K_J BULK COLLECT INTO P_K FROM SOME_TABLE WHERE NAME='JONES';

  FOR I IN P_K.FIRST..P_K.LAST LOOP
    DBMS_OUTPUT.PUT_LINE('THE VALUES OF P_K ARE' || P_K(I));
  END LOOP;

END P1;
/

Upvotes: 1

Related Questions