Getting PLS-00306: wrong number or types of arguments in call in my Pkg

user1699124

Hi All,

I am getting "PLS-00306: wrong number or types of arguments in call to 'CP_TABLE'" error while i create a plsql pkg, Can you help to advice where am i missing. Attached the pkg, below is the full error message with position.

SQL> @org_copy_main.sql

Package created.

Warning: Package Body created with compilation errors.

SQL> show error

Errors for PACKAGE BODY PRODDBA.PRESTOCOPYORGMAIN:

LINE/COL ERROR

-------- -----------------------------------------------------------------

207/25  PL/SQL: Statement ignored

207/25  PLS-00306: wrong number or types of arguments in call to

     'CP_TABLE'

Regards

Kannan

org_copy_main.txt

Billy Verreynne

Please don't post code like this before my first cup of morning coffee... <sigh>

Use bind variables. Always!! Why? SQL injection. Hard parse CPU overheads. Performance.

Use record structures for structured parameter passing and processing. Why? Modularisation. Simplification. Abstraction. Increase readability and maintainability.

Use proper programming standards. Why? PL/SQL is an Ada language implementation. Ada programming style and standards should be primary consideration when writing PL/SQL code. See https://en.wikibooks.org/wiki/Ada_Style_Guide

As for your error calling Cp_Table() - possibly due to using the ROWID data type incorrectly. When parameters are many, use named parameter call syntax. And consider using structured record types as parameters instead.

BEDE

When you have procedures with many parameters, to make sure you do pass the right parameters, use the following syntax for procedure call:

Cp_table

 (

   powner      =>...,

   ptable_name    =>...,

   towner       =>...,

   ttable_name    =>...,

   pdblink      =>...,

   pstart_rowid   =>...,

   pend_rowid     =>...,

   scn       =>...,

   key_val     =>...,

   org_id      =>...,

   org_list      =>...,

   src_tns      =>...,

   part_num      =>...

 )

;

BEDE

Your exception handler shows that you did not understand how exception handling works.

 EXCEPTION

   WHEN OTHERS THEN

    dbms_output.Put_line('Error message was: ' || SQLERRM);

    RAISE;

 END Cp_table;

Well, dbms_output.put_line shows something only after procedure completion if there is no exception and if that client calling the procedure is enabled for dbms_output. Thus, dbms_output_put.line is useless in your code.

BEDE

The wrong parameter type must be CpyStRowid which is supposed to be rowid, while the vairable CpyStRowid is declared as varchar2. Use rowidtochar and chartorowid for conversions.