1

There is a package with type.

CREATE OR REPLACE PACKAGE MY_TYPES IS
PRAGMA SERIALLY_REUSABLE;
  SUBTYPE my_number IS NUMBER(3,0);
END MY_TYPES;
/

There is a procedure:

DECLARE
  PROCEDURE print(my_number_i IN my_types.my_number) IS
  BEGIN
    dbms_output.put_line(my_number_i);
  END;
BEGIN
  print(my_number_i => 35);
END;
/

The problem is that such a call gives an error in PL/SQL Developer.

ORA-06502: PL/SQL: numeric or value error: number precision too large.

But if you substitute the value my_number_i => !=35, the procedure is completed.

However, if you run it from SQL Developer, everything works. If you run it via SQL*Plus, it also works. It only does not work from PL/SQL Developer.

Please tell me what the problem might be?

KayaNatsumi
  • 414
  • 5
  • 12
Dki0m7
  • 11
  • 2
  • "CREATE OR REPLACE TYPE MY_TYPES ", not "package" – OldProgrammer Jun 29 '20 at 16:00
  • your "create or replace" does not work. Please post the correct code. – gsalem Jun 29 '20 at 16:05
  • I can't change an existing package – Dki0m7 Jun 29 '20 at 16:06
  • 3
    The point is that `subtype my_number number(3,0);` is invalid; presumably this isn't your real code and you've partially changed the names, but not correctly. You probably didn't mean to have the `subtype` in there? Or you meant `subtype my_number is number(3,0);` ? Please show minimal *working* code - at least working to the point you get the error you refer to. With `is` and a missing semicolon added your code doesn't error at all... [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=0688cbf43c58aa1715f96bc5bc0106b0) – Alex Poole Jun 29 '20 at 16:07
  • The question is detailed – Dki0m7 Jun 29 '20 at 16:19
  • OK, thanks; that is strange. It might be worth including the full version of PL/SQL Developer you're seeing this in. If it's very old then maybe it's a version clash with 12c. (I don't have any version installed so I can't help...) – Alex Poole Jun 29 '20 at 16:30
  • I don't see anything wrong with your `CREATE OR REPLACE PACKAGE MY_TYPES..` ..But are you sure about your `declare procedure print(my_number_i in my_types.my_number);`? That's it's not `CREATE OR REPLACE procedure print(my_number_i in my_types.my_number) IS`? – Scratte Jun 29 '20 at 19:50
  • @Scratte - it's a procedure that's local to the anonymous block. That's valid, and works. (Except, it seems, in PL/SQL Developer...) – Alex Poole Jun 29 '20 at 20:03
  • It works fine for me in PL/SQL Developer 13 and 14 (Oracle 19.3) after correcting the semicolon in `procedure print`, which should be `is`. – William Robertson Jul 07 '20 at 22:14
  • Can confirm PL/SQL Developer 14, it worked in SQL Window, Test Window and also Command Window and Oracle 11.2.0.4, the `;` instead of `IS` was added by @AlexPoole most likely by mistake, I proposed an edit that is waiting for approval. – KayaNatsumi Jul 16 '20 at 16:37
  • @KayaNatsumi - hmm, not sure how/why I added a semicolon instead of `IS` - I did it right in my fiddle; or why my comment mentions a missing semicolon as well as `IS`. Must have been an off day... I asked the OP to include their PL/SQL Developer version but that doesn't seem to have happened. If it it's really doing this then I imagine it's a bug in an older version. – Alex Poole Jul 16 '20 at 16:46

0 Answers0