ORA-06530 - Error when using a created type in PL/SQL Oracle

Question

first of all, excuse my bad english.

I'm trying to use a created type as a Procedure parameter. This is the Type's spec:

create or replace type swbapps.REEMBOLSO_PORTAL as object
( 
 external_id varchar2(20),
 credito_reembolso varchar2(10),
 credito_manual varchar2(10),
 CONSTRUCTOR FUNCTION REEMBOLSO_PORTAL RETURN SELF AS RESULT,
 MAP MEMBER FUNCTION get_external_id RETURN varchar2

)
-- The constructor function only exists at spec. Do i have to do something at body?

How i used the created type:

at some package spec:

TYPE tReembolso IS TABLE OF REEMBOLSO_PORTAL
   INDEX BY BINARY_INTEGER;

then i used as a procedure parameter:

PROCEDURE PR_SOLICITA_REEMBOLSO (pCpfCnpj in varchar2,
                                 pInfReembolso in tReembolso,
                                 pUser in varchar2,
                                 pObs in varchar2,
                                 pProtocolo in varchar2,
                                 pCodRet out number,
                                 pMsgRet out varchar2
                                 );

and now, i'm trying to test my code:

declare
  -- Non-scalar parameters require additional processing 
  pinfreembolso swbapps.pc_interface_vantive_portal.treembolso;
begin
  pinfreembolso(0).external_id := '000761595406';
  pinfreembolso(0).credito_reembolso := '1234';
  pinfreembolso(0).credito_manual := '4321';

  pinfreembolso(1).external_id := '000261595393';
  pinfreembolso(1).credito_reembolso := '5678';
  pinfreembolso(1).credito_manual := '8765';
  -- Call the procedure
  swbapps.pc_interface_vantive_portal.pr_solicita_reembolso(pcpfcnpj => :pcpfcnpj,
                                                            pinfreembolso => pinfreembolso,
                                                            puser => :puser,
                                                            pobs => :pobs,
                                                            pprotocolo => :pprotocolo,
                                                            pcodret => :pcodret,
                                                            pmsgret => :pmsgret);
end;

but the error ORA-06530 appears to me.

i've tried other ways to declare the variable, but nothing works.

pinfreembolso swbapps.pc_interface_vantive_portal.treembolso := REEMBOLSO_PORTAL();

Didn't work.

Finally, can someone help me?

Answer 1

@Alex Poole Thank you for your help but i didn't worked too. btw, it helped me to in other way.

This is how i solved the problem:

At my created Type, i've created a constructor function:

 CONSTRUCTOR FUNCTION REEMBOLSO_PORTAL(external_id varchar2, credito_reembolso varchar2, credito_manual varchar2)
   RETURN SELF AS RESULT
   AS
   BEGIN 
     SELF.external_id := external_id;
     SELF.credito_reembolso := credito_reembolso;
     SELF.credito_manual := credito_manual;
     RETURN;
   END;

then i used the constructor to initialize and input value in my table:

declare
  pinfreembolso swbapps.pc_interface_vantive_portal.treembolso;

  vCPF varchar2(30) := '';
  vUser varchar2(30) := '';
  vObs varchar2(500) := '';
  vProtocolo varchar2(30) := '';

  nCodRet number;
  vMsgRet varchar2(500);

begin
  pinfreembolso(0) := NEW swbapps.reembolso_portal('000761595406','1234','4321');
  pinfreembolso(1) := NEW swbapps.reembolso_portal('000261595393','5678','8765');

  -- Call the procedure
  swbapps.pc_interface_vantive_portal.pr_solicita_reembolso(vCPF,
                                                            pinfreembolso,
                                                            vUser,
                                                            vObs,
                                                            vProtocolo,
                                                            nCodRet,
                                                            vMsgRet);

  dbms_output.put_line('Código de retorno: '||nCodRet);
  dbms_output.put_line('Mensagem de retorno: '||vMsgRet);
end;

IT WORKED! :) thank you!

Answer 2

You need to imitialise each object before you refer to it:

declare
  -- Non-scalar parameters require additional processing 
  pinfreembolso swbapps.pc_interface_vantive_portal.treembolso;
begin
  pinfreembolso(0) := swbapps.pc_interface_vantive_portal.treembolso();
  pinfreembolso(0).external_id := '000761595406';
  pinfreembolso(0).credito_reembolso := '1234';
  pinfreembolso(0).credito_manual := '4321';

  pinfreembolso(1) := swbapps.pc_interface_vantive_portal.treembolso();
  pinfreembolso(1).external_id := '000261595393';
  pinfreembolso(1).credito_reembolso := '5678';
  pinfreembolso(1).credito_manual := '8765';
  -- Call the procedure
  ...