Forum Stats

  • 3,768,176 Users
  • 2,252,755 Discussions
  • 7,874,483 Comments

Discussions

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

user1699124
user1699124 Member Posts: 7 Red Ribbon

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


Tagged:

Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,581 Red Diamond
    edited Jul 1, 2021 7:00AM

    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
    BEDE Oracle Developer Member Posts: 2,302 Gold Trophy

    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
    BEDE Oracle Developer Member Posts: 2,302 Gold Trophy

    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
    BEDE Oracle Developer Member Posts: 2,302 Gold Trophy

    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.