How to push a split results into array PL SQL and return array data with a ref cursor

Question

I am trying to push a split result into an array, but I do not know how to do it. Here is my stored procedure:

   CODE_KZ_i NVARCHAR2 (4000):='';
   FTKZ NVARCHAR2 (4000):='';

   TYPE namesarray IS TABLE OF NVARCHAR2(4000);
   FTarray namesarray:=namesarray();
   i NUMBER;

begin

    FOR FTKZ IN ( SELECT REGEXP_SUBSTR (KZ,'[^;]+', 1,LEVEL)  TXT  FROM DUAL 
                  CONNECT BY REGEXP_SUBSTR (KZ,'[^;]+', 1, LEVEL) IS NOT NULL)

    LOOP

    FTarray.extend;

    IF CONTA = 0 THEN CODE_KZ:=FTKZ.TXT;
    ELSE 
    CODE_KZ:= FTKZ.TXT;

    END IF;

    FTarray(i):= CODE_KZ;
    DBMS_OUTPUT.put_line('FT:'|| FTarray(i));
    i:=i+1;
    CONTA:=CONTA+1;

My goal is to push the result of FTKZ.TXT into FTarray. Does anyone can help me please?

Next step is to return array's data in a sys_refcursor. I get some code in input named KZ and I have to return another code, linked to kz which is in this table: SG_AN_FT

    open p_cur_result for
    SELECT DISTINCT FT.CODE as FT INTO P_RESULT FROM SG_AN_FT FT 
          INNER JOIN SG_BOM_PIVOT_PN_KZ BOM
          ON FT.ID = BOM.ID_FT
          INNER JOIN SG_AN_KZ K
          ON K.ID = BOM.ID_KZ
          WHERE K.CODE in (SELECT * FROM TABLE(cast(FTarray as FT));

    Errore(317,60): PL/SQL: ORA-00902: tipo di dati non valido

Thank you in advance

Answer 1

You loop code seems pretty close; adding a definition and dummy value for KZ and cleaning up other variables you can do:

declare
  -- made up as not shown in the question
  KZ NVARCHAR2 (4000) := 'A;B;C;D';

  CODE_KZ NVARCHAR2 (4000);

  TYPE namesarray IS TABLE OF NVARCHAR2(4000);
  FTarray namesarray:=namesarray();
  CONTA NUMBER := 1;
begin
  FOR FTKZ IN ( SELECT REGEXP_SUBSTR (KZ,'[^;]+', 1,LEVEL)  TXT  FROM DUAL 
                CONNECT BY REGEXP_SUBSTR (KZ,'[^;]+', 1, LEVEL) IS NOT NULL)
  LOOP
    FTarray.extend;

    -- redundant as both branches do the same thing; also can't be zero
    IF CONTA = 0 THEN
      CODE_KZ:=FTKZ.TXT;
    ELSE 
      CODE_KZ:= FTKZ.TXT;
    END IF;

    FTarray(CONTA):= CODE_KZ;
    DBMS_OUTPUT.put_line('FT:'|| FTarray(CONTA));
    CONTA:=CONTA+1;
  END LOOP;
end;
/

PL/SQL procedure successfully completed.

FT:A
FT:B
FT:C
FT:D

Your IF CONTA = 0 checks suggests you were starting your loop with index zero, which would get ORA-06532: Subscript outside of limit as collections are indexed from one not zero. That check seems redundant anyway as you assign the same value to the variable in both branches, so you can just assign FTKZ.TXT directly to the newly-extended array element. You don't really need CONTA or CODE_KZ at all:

declare
  -- made up as not shown in the question
  KZ NVARCHAR2 (4000) := 'A;B;C;D';

  TYPE namesarray IS TABLE OF NVARCHAR2(4000);
  FTarray namesarray:=namesarray();
begin
  FOR FTKZ IN ( SELECT REGEXP_SUBSTR (KZ,'[^;]+', 1,LEVEL)  TXT  FROM DUAL 
                CONNECT BY REGEXP_SUBSTR (KZ,'[^;]+', 1, LEVEL) IS NOT NULL)
  LOOP
    FTarray.extend;
    FTarray(FTArray.count) := FTKZ.TXT;
    DBMS_OUTPUT.put_line('FT:'|| FTarray(FTArray.count));
  END LOOP;
end;
/

PL/SQL procedure successfully completed.

FT:A
FT:B
FT:C
FT:D

But you also don't need to use a loop, you can use bulk collect to populate your collection in one go:

declare
  -- made up as not shown in the question
  KZ NVARCHAR2 (4000) := 'A;B;C;D';

  TYPE namesarray IS TABLE OF NVARCHAR2(4000);
  FTarray namesarray:=namesarray();
begin
  SELECT REGEXP_SUBSTR (KZ,'[^;]+', 1,LEVEL) TXT
  BULK COLLECT INTO FTarray
  FROM DUAL 
  CONNECT BY REGEXP_SUBSTR (KZ,'[^;]+', 1, LEVEL) IS NOT NULL;

  FOR i IN 1..FTarray.count LOOP
    DBMS_OUTPUT.put_line('FT:' || FTarray(i));
  END LOOP;
end;
/

PL/SQL procedure successfully completed.

FT:A
FT:B
FT:C
FT:D