Skip to Main Content
  • Questions
  • ORA-06553: PLS-306: wrong number or types of arguments when selecting object type instance

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Eriks.

Asked: February 10, 2020 - 1:44 pm UTC

Answered by: Chris Saxon - Last updated: February 11, 2020 - 10:41 am UTC

Category: Database Development - Version: 19.5

Viewed 100+ times

You Asked

We're experiencing this when testing upgrade from Ora 12.1 to 19.
What's wrong with the code?

with LiveSQL Test Case:

and we said...

This looks like BUG 27653957 which appeared in 12.2. There's currently no fix for it.

Luckily the workaround is simple: remove the constructor function from the object type!

CREATE OR REPLACE TYPE xx_note_typ force AS OBJECT 
( 
  note_id      NUMBER, 
  note_scope   NUMBER, 
  note_value   VARCHAR2(4000), 
  note_date    VARCHAR2(100)
) ;
/

CREATE OR REPLACE TYPE xx_note_arr_typ FORCE AS TABLE OF xx_note_typ; 
/

create table xx_CUSTOMER_NOTE_ALL 
( 
  id         NUMBER not null, 
  agent_id   NUMBER, 
  debtor_id  VARCHAR2(10), 
  serving_id NUMBER, 
  note_value VARCHAR2(4000), 
  note_scope NUMBER, 
  reg_date   TIMESTAMP(6) WITH TIME ZONE not null, 
  system_id  NUMBER not null, 
  ts         TIMESTAMP(6) WITH TIME ZONE not null 
) ;


DECLARE 
  l_result xx_note_arr_typ; 
BEGIN
  SELECT xx_note_typ(note_id => NULL, note_scope => NULL, note_value => NULL, note_date => NULL) 
    BULK COLLECT 
    INTO l_result 
    FROM xx_CUSTOMER_NOTE_ALL icn 
   WHERE (icn.note_scope = 0 AND icn.serving_id = 42) 
      OR (icn.note_scope = 1 AND icn.debtor_id = '42');    
END; 
/

PL/SQL procedure successfully completed.

and you rated our response

  (1 rating)

Reviews

February 10, 2020 - 4:23 pm UTC

Reviewer: A reader

Thanks for looking into this.

It's a very simple test case. We also have complicated quries, which use pipelined functions and types, and removing constructor from involved types doesn't help there.

This, however, helps - https://livesql.oracle.com/apex/livesql/s/jninu11jnn2m41ghtfncit2xe

My question is - is it reliable workaround? It looks weird, but works.
Chris Saxon

Followup  

February 11, 2020 - 10:41 am UTC

It works for me :)

Not sure if it works in all cases, but it's worth a try.

In any case, you should raise this with support. The more customers that flag an issue, (likely) the quicker a fix will be made.

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.