Re: dbms_output.get_lines problem

Martin Terpstra wrote:
>
> I am not able to compile a PL/SQL source using get_lines().
> I always get the following error message:
>
> PLS-00306: wrong number or types of arguments in call to 'GET_LINES'
>
> ==================
>
> Oracle7 Server Release 7.1.6.2.0 - Production Release
> With the parallel query option
> PL/SQL Release 2.1.6.2.0 - Production
>
> SQL> DECLARE
> 2 nummertje INTEGER;
> 3
> 3 TYPE CHARARRAY IS TABLE OF VARCHAR2(255)
> 4 INDEX BY BINARY_INTEGER;
> 5
> 5 ch_array CHARARRAY;
> 6
> 6 array_teller binary_integer default 1;
> 7
> 7 BEGIN
> 8
> 8 dbms_output.enable(10000);
> 9
> 9 dbms_output.put_line('Dit is een regel met tekst');
> 10 dbms_output.put_line('Dit is regel twee met tekst');
> 11 dbms_output.put_line('Dit is regel drie met tekst');
> 12 dbms_output.put_line('Dit is regel vier met tekst');
> 13 dbms_output.put_line('Dit is regel vijf met tekst');
> 14
> 14 nummertje := 3;
> 15
> 15 dbms_output.get_lines( ch_array, nummertje);
> 16
> 16 END;
> 17 /
> dbms_output.get_lines( ch_array, nummertje);
> *
> ERROR at line 15:
> ORA-06550: line 15, column 9:
> PLS-00306: wrong number or types of arguments in call to 'GET_LINES'
> ORA-06550: line 15, column 9:
> PL/SQL: Statement ignored

There are two solutions. Choose the one most convenient for you:

1. Change line 5 to: ch_array dbms_output.chararr;

or 2) change line 3 to:

     subtype CHARARRAY IS dbms_output.chararr;

Yes, you and I both know that dbms_output.chararr and your CHARARRAY are both TABLE OF VARCHAR2(255), but when the PL/SQL compiler tries to resolve the call to get_lines, it sees only that the LINES argument you are trying to pass is of a different type (by definition) than the one expected.

To clarify (I hope):

Create two identical types:

  type Type1 is integer;
  type Type2 is integer;

Then a procedure specification:

  proc1 ( arg1 in out Type1 );

And this code:

  declare
    myArg Type2;
  begin
    proc1( myArg );
  end;

You will get the same error msg: wrong number or types of arguments in call to 'PROC1'. That is because the variable you are passing as an argument is of type Type2 (don't think of it as Integer) and the only procedure with the name 'proc1' expects an argument of type Type1. If Type2 were a *subtype* of Type1, the call would be resolved. Confusion can be created because if you do a DESCRIBE PROC1, the argument will show up as type Integer.

-- 
Tomm Carr
--
"Can you describe your assailant?"
"No problem, Officer.  That's exactly what I was doing when he hit me!"