Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, KKA.

Asked: June 03, 2006 - 12:37 pm UTC

Last updated: January 31, 2013 - 9:59 am UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I am a regular visitor to this wonderful site but this is my first question to you. I always find my answers using the search feature, but could not get this one.

My problem is really troubling me a lot. I am creating a type with the definition below:

CREATE OR REPLACE
TYPE typ_record_status_flag_type
AS OBJECT
(
--Member Attributes
flag_value CHAR(1),

--Constructors
CONSTRUCTOR FUNCTION typ_record_status_flag_type(
in_flag_value IN CHAR)
RETURN self AS result,

--Member Functions
MEMBER FUNCTION is_record_active
RETURN INTEGER,

MEMBER FUNCTION get_record_status
RETURN CHAR

);
/

CREATE OR REPLACE
TYPE BODY typ_record_status_flag_type IS

--Constructors
CONSTRUCTOR FUNCTION typ_record_status_flag_type(
in_flag_value IN CHAR)
RETURN self AS result IS
BEGIN
IF in_flag_value IN ('I', 'M', 'D', 'A') THEN
flag_value := in_flag_value;
ELSIF in_flag_value IS NULL THEN
flag_value := 'I';
ELSE
raise_application_error (-20001,'Invalid flag value!');
END IF;

RETURN;
END;

--Member Functions and procedures

MEMBER FUNCTION is_record_active
RETURN INTEGER IS
BEGIN
IF flag_value = 'A' THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;

MEMBER FUNCTION get_record_status
RETURN CHAR IS
BEGIN
RETURN flag_value;
END;

END;
/

Then I create a table using this type:

CREATE TABLE test_country_m_tmp (
country_id INTEGER,
country_code VARCHAR2(10),
country_name VARCHAR2(300) NOT NULL,
country_iso_code VARCHAR2(3),
status TYP_RECORD_STATUS_FLAG_TYPE,
CONSTRAINT test_country_m_tmp_pk
PRIMARY KEY (country_id)
);

After my table gets created successfully, I try inserting data using this query:

INSERT INTO test_country_m_tmp
(country_id, country_code, country_name,
country_iso_code, status)
VALUES
(1, 'USA', 'United States of America', 'US',
typ_record_status_flag_type( 'M'));

But I get an error:

ORA-06553: PLS-307: too many declarations of 'TYP_RECORD_STATUS_FLAG_TYPE' match this call.

Please help!

Thanks and Regards
-KK

and Tom said...

test cases should be really small - free of all non-relevant material. Makes it easier to see what the problem is....

Anyway, you did not "hide the signature of the default constuctor", hence there was a default constructor that takes a single input, there is your new constructor that also takes a single input - it didn't know which one you wanted:

ops$tkyte@ORA10GR2> CREATE OR REPLACE TYPE mytype AS OBJECT
2 ( x CHAR(1),
3 CONSTRUCTOR FUNCTION mytype( in_x IN CHAR) RETURN self AS result
4 )
5 /

Type created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> CREATE OR REPLACE TYPE BODY myType
2 as
3 CONSTRUCTOR FUNCTION myType( in_x IN CHAR) RETURN self AS result
4 as
5 BEGIN
6 x := in_x;
7 return;
8 END;
9
10 END;
11 /

Type body created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> declare
2 l_data myType := myType('M');
3 begin
4 null;
5 end;
6 /
l_data myType := myType('M');
*
ERROR at line 2:
ORA-06550: line 2, column 21:
PLS-00307: too many declarations of 'MYTYPE' match this call
ORA-06550: line 2, column 11:
PL/SQL: Item ignored


You either have to have a unique signature OR hide the signature of the default constructor:


ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> CREATE OR REPLACE TYPE mytype AS OBJECT
2 ( x CHAR(1),
3 CONSTRUCTOR FUNCTION mytype( x IN CHAR) RETURN self AS result
4 )
5 /

Type created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> CREATE OR REPLACE TYPE BODY myType
2 as
3 CONSTRUCTOR FUNCTION myType( x IN CHAR) RETURN self AS result
4 as
5 BEGIN
6 self.x := x;
7 return;
8 END;
9
10 END;
11 /

Type body created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> declare
2 l_data myType := myType('M');
3 begin
4 null;
5 end;
6 /

PL/SQL procedure successfully completed.


Rating

  (12 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Default constuctor

KKA, June 03, 2006 - 9:26 pm UTC

Hi Tom,

That was very helpful! If you please, just two things.

1. When you say that I did not "hide the signature of the default constuctor", does it mean that Oracle creates a default signature other than something like this one:

CONSTRUCTOR FUNCTION typ_record_status_flag_type RETURN self AS result

2. The types compiles and works fine, but when I compile the type in SQL developer (raptor), I get a warning:

PLW-07203: parameter 'SELF' may benefit from use of the NOCOPY compiler hint

Thanks

Tom Kyte
June 04, 2006 - 8:13 am UTC

1) it does not create that one, it creates:

CONSTRUCTOR FUNCTION typ_record_status_flag_type(flag_value IN CHAR)
RETURN self AS result


which has the same inputs as yours did (all of the attributes!) hence the confusion.

2) that is but a warning, no big deal, ignore it.

Thanks Tom, cleared one of my basics.

KKA, June 04, 2006 - 9:25 am UTC


PLS-00307 revisited

Matthias Rogel, July 05, 2006 - 8:37 am UTC 

create or replace package test_pkg is
procedure t(v in varchar2);
procedure t(v in varchar);
procedure t(v in integer);
end;
/


create or replace package body test_pkg is
procedure t(v in varchar2) is
begin
dbms_output.put_line('varchar2');
end t;
procedure t(v in varchar) is
begin
dbms_output.put_line('varchar');
end t;
procedure t(v in integer) is
begin
dbms_output.put_line('integer');
end t;
end;
/

SQL> set serverout on
SQL> exec test_pkg.t(cast(null as integer))
integer

REM great

SQL> exec test_pkg.t(cast(null as varchar2))
BEGIN test_pkg.t(cast(null as varchar2)); END;

      *
FEHLER in Zeile 1:
ORA-06550: line 1, column 7:
PLS-00307: too many declarations of 'T' match this call
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

REM why ?
REM expected 'varchar2'

SQL> exec test_pkg.t(cast(null as varchar2(1)))
BEGIN test_pkg.t(cast(null as varchar2(1))); END;

                                      *
FEHLER in Zeile 1:
ORA-06550: line 1, column 39:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
. ) @ %
The symbol ")" was substituted for "(" to continue.
ORA-06550: line 1, column 43:
PLS-00103: Encountered the symbol ")" when expecting one of the following:
:= . ( % ;
The symbol "(" was substituted for ")" to continue.

REM why again ?
REM expected 'varchar2' again

Tom Kyte
July 08, 2006 - 7:56 am UTC

varchar and the varchar2 are basically the same - doesn't really make sense to overload based on them.

really "ignore" it?

A reader, April 23, 2007 - 3:08 pm UTC

Hi Tom -

Your advice in one of the posts above, concerning the PLW-07203 warning message (PLW-07203: parameter may benefit from use of the NOCOPY compiler hint) was to "ignore it".

Is it worth even thinking about not "ignoring it" and trying to do something to remedy this warning, or is it really just a annoyance to see these messages and the gain isn't worth the effort to look into this?

Thanks for your advice.

Solution: pass SELF as NOCOPY parameter

Ian Bruyninckx, March 31, 2008 - 4:48 am UTC

Passing SELF as IN OUT NOCOPY in the constructor method, seems to be the solution (as documented in the Oracle documentation)

cfr. section "7 Advanced Topics for Oracle Objects" in the document "Oracle® Database Application Developer's Guide - Object-Relational Features".

CREATE TYPE shape AS OBJECT (
name VARCHAR2(30),
area NUMBER,
CONSTRUCTOR FUNCTION shape(SELF IN OUT NOCOPY shape, name VARCHAR2)
RETURN SELF AS RESULT,
CONSTRUCTOR FUNCTION shape(SELF IN OUT NOCOPY shape, name VARCHAR2,
area NUMBER) RETURN SELF AS RESULT
) NOT FINAL;
/

CREATE TYPE BODY shape AS
CONSTRUCTOR FUNCTION shape(SELF IN OUT NOCOPY shape, name VARCHAR2)
RETURN SELF AS RESULT IS
BEGIN
SELF.name := name;
SELF.area := 0;
RETURN;
END;
CONSTRUCTOR FUNCTION shape(SELF IN OUT NOCOPY shape, name VARCHAR2,
area NUMBER) RETURN SELF AS RESULT IS
BEGIN
SELF.name := name;
SELF.area := area;
RETURN;
END;
END;
/
Tom Kyte
March 31, 2008 - 9:40 am UTC

but what is this the solution "to" exactly??!?!?

ORA-06553: PLS-306

Rajeshwaran, Jeyabal, May 13, 2011 - 12:00 am UTC 

rajesh@ORA11GR2> create or replace type dept_type as object
  2  (deptno number,
  3     dname varchar2(30)
  4  );
  5  /

Type created.

Elapsed: 00:00:00.09
rajesh@ORA11GR2>
rajesh@ORA11GR2> create or replace type emp_type as object(
  2   emp_id raw(16),
  3   ename varchar2(30),
  4   dept  ref dept_type,
  5   static function construct_map(p_name varchar2,p_dept ref dept_type)
  6   return emp_type
  7  );
  8  /

Type created.

Elapsed: 00:00:00.20
rajesh@ORA11GR2>
rajesh@ORA11GR2> create or replace type body emp_type as
  2     static function construct_map(p_name varchar2,p_dept ref dept_type)
  3     return emp_type
  4     as
  5     begin
  6             return emp_type(sys_guid(), p_name, p_dept);
  7     end;
  8  end;
  9  /

Type body created.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> create table emp_tab of emp_type;

Table created.

Elapsed: 00:00:00.09
rajesh@ORA11GR2>
rajesh@ORA11GR2> insert into emp_tab values( emp_type.construct_map('A',null) );

1 row created.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> insert into emp_tab values( emp_type.construct_map('B', dept_type(1,'A') ) ) ;
insert into emp_tab values( emp_type.construct_map('B', dept_type(1,'A') ) )
                            *
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'CONSTRUCT_MAP'


Elapsed: 00:00:00.00
rajesh@ORA11GR2>


Tom:

Can you please help me what I am doing here wrong?
Tom Kyte
May 13, 2011 - 12:46 pm UTC

you are not passing a REF, you are passing an instance (a temporary instance) to the constructor.

You said you were going to pass a REF to an existing dept_type, instead you passed a temporary instance of a dept_type.

ORA-06553: PLS-306

Rajeshwaran, Jeyabal, May 13, 2011 - 2:09 pm UTC

Tried, passing REF but ended up with this error.

rajesh@ORA11GR2> insert into emp_tab values( emp_type.construct_map('B', ref(dept_type(1,'A')) ) ) ;
insert into emp_tab values( emp_type.construct_map('B', ref(dept_type(1,'A')) ) )
                                                                     *
ERROR at line 1:
ORA-00907: missing right parenthesis


Elapsed: 00:00:00.01
rajesh@ORA11GR2>

Tom Kyte
May 18, 2011 - 2:36 am UTC

you need a real object that exists, not a temporary.

ORA-06553: PLS-306

Rajeshwaran, Jeyabal, May 14, 2011 - 11:45 am UTC

Tom:

Referred from Product documentation and corrected.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions133.htm#SQLRF00694 

rajesh@ORA11GR2>
rajesh@ORA11GR2> insert into emp_tab values( emp_type.construct_map('B', dept_type(1,'A') ) ) ;
insert into emp_tab values( emp_type.construct_map('B', dept_type(1,'A') ) )
                            *
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'CONSTRUCT_MAP'


Elapsed: 00:00:00.06
rajesh@ORA11GR2>
rajesh@ORA11GR2> create table dept_tab of dept_type;

Table created.

Elapsed: 00:00:00.03
rajesh@ORA11GR2>
rajesh@ORA11GR2> insert into dept_tab values(1,'a');

1 row created.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> insert into emp_tab(emp_id,ename,dept)
  2  select 'a','a',ref(d)
  3  from dept_tab d;

1 row created.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> insert into emp_tab
  2  select emp_type.construct_map('B',ref(d))
  3  from dept_tab d;

1 row created.

Elapsed: 00:00:00.03
rajesh@ORA11GR2>
rajesh@ORA11GR2>

why does this give PLS-00307?

Paul, December 11, 2012 - 5:20 pm UTC 

SQL> CREATE TABLE t1 (c1 NUMBER(2), c2 VARCHAR2(30));

Table created.

SQL> CREATE OR REPLACE PACKAGE test_package IS
  2    PROCEDURE p(x IN TIMESTAMP, y IN t1.c1%TYPE);
  3    PROCEDURE p(x IN TIMESTAMP, y IN t1.c2%TYPE);
  4  END test_package;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY test_package IS
  2    PROCEDURE p(x IN TIMESTAMP, y IN t1.c1%TYPE)
  3    IS
  4      BEGIN
  5        NULL;
  6      END p;
  7
  8    PROCEDURE p(x IN TIMESTAMP, y IN t1.c2%TYPE)
  9    IS
 10      BEGIN
 11        NULL;
 12      END p;
 13
 14  END test_package;
 15  /

Package body created.

SQL> BEGIN
  2    test_package.p(SYSTIMESTAMP,'abc');
  3  END;
  4  /
  test_package.p(SYSTIMESTAMP,'abc');
  *
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00307: too many declarations of 'P' match this call
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

SQL> DECLARE
  2    lv_timestamp TIMESTAMP := SYSTIMESTAMP;
  3  BEGIN
  4    test_package.p(lv_timestamp,'abc');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>


How does the second call succeed when the first one fails?
Tom Kyte
December 17, 2012 - 3:19 pm UTC

systimestamp is a timestamp with time zone, not a timestamp....

http://docs.oracle.com/cd/E14072_01/server.112/e10592/functions190.htm

so you could:
BEGIN
  test_package.p(cast( SYSTIMESTAMP as timestamp) ,'abc');
END;
/



otherwise, a conversion has to happen and it could convert either way

the datatype of second parameter cannot help?

Paul, January 18, 2013 - 12:39 pm UTC

Thanks for the info on SYSTIMESTAMP. I was assuming it returned timestamp instead of timestamp with time zone.

But even if the PL/SQL parser has to convert the first parameter, shouldn't the datatype of the second parameter be enough to determine which overload to execute?
How could it "convert either way"?

Thanks!
Tom Kyte
January 18, 2013 - 1:53 pm UTC

it didn't find an exact match so it looked for all compatible matches - found two and said you were not specific enough.

a simple cast makes it find a specific match.

still wondering

Paul, January 21, 2013 - 6:15 pm UTC

I'm sorry I'm not understanding; but I'm still unsure how there can be two compatible matches.

The second parameter is 'abc', a string. In the package declaration and in the body, the first overload of p requires a NUMBER for the second parameter. The second overload requires a VARCHAR parameter. So in this case, I would think the only possible match is the second overload.


SQL> CREATE TABLE t1 (c1 NUMBER(2), c2 VARCHAR2(30));

Table created.

SQL> CREATE OR REPLACE PACKAGE test_package IS
2 PROCEDURE p(x IN TIMESTAMP, y IN t1.c1%TYPE);
3 PROCEDURE p(x IN TIMESTAMP, y IN t1.c2%TYPE);
4 END test_package;
5 /


Obviously, I am wrong and the PL/SQL engine cannot determine which to run. Is it that if one parameter is converted, other parameters are not looked at? Or perhaps it is based on the order of parameters? If my string or number parameter had been first, PL/SQL could have figured out which to execute even with the implicit conversion?


Tom Kyte
January 22, 2013 - 1:48 am UTC

in the absence of an exact match, all compatible matches are considered.

both are compatible
neither are exact

as soon as you make it exact with a CAST, the ambiguity is removed and you have an exact match.

Sam, January 30, 2013 - 8:48 am UTC

Hi Tom,

I have a package as follows:
create or replace PACKAGE SEND_MAIL_PKG as

procedure html_email(
    p_to            in varchar2,
    p_from          in varchar2,
    p_subject       in varchar2,
    p_text          in clob,
    p_html          in clob,
    p_smtp_hostname in varchar2,
    p_smtp_portnum  in varchar2,
    p_attachment_name varchar2 default null,
    p_critical      in boolean default false);

procedure html_email(
    p_to            in varchar2,
    p_from          in varchar2,
    p_subject       in varchar2,
    p_text          in varchar2,
    p_html          in varchar2,
    p_smtp_hostname in varchar2,
    p_smtp_portnum  in varchar2,
    p_attachment_name varchar2 default null,
    p_critical      in boolean default false);

procedure html_email(
    p_to            in varchar2,
    p_from          in varchar2,
    p_subject       in varchar2,
    p_text          in clob,
    p_html          in clob,
    p_attachment_name varchar2 default null,
    p_critical      in boolean default false);

procedure html_email(
    p_to            in varchar2,
    p_from          in varchar2,
    p_subject       in varchar2,
    p_text          in varchar2,
    p_html          in varchar2,
    p_attachment_name varchar2 default null,
    p_critical      in boolean default false);

end SEND_MAIL_PKG;
/


I am calling this package with the following code where all parameters are non null varchar2 types:
send_mail_pkg.html_email(
                          l_to,
                          l_from,
                          l_subject,
                          l_text,
                          l_html
                          );


I get the error:
Error(46,5): PLS-00307: too many declarations of 'HTML_EMAIL' match this call


The problem is that Oracle is doing an implicit conversion between varchar2 and clob. I tried converting l_text and l_html to clob but faced the same issue. Also tried to cast the type but got error "Expression is on wrong type"

Any Ideas on what is missing here ?
Tom Kyte
January 31, 2013 - 9:59 am UTC

drop the all varchar2 one, it isn't necessary. Your varchar2 routines are likely just trying to call the clob ones anyway (they should if they don't)


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

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

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library