Can I somehow get a pseudonym for the built-in type indicating the maximum length?

So that I can declare variables wrong

myvar number(3);

and so

 myvar mynumber;

And also attributes in the object type, like this

 create or replace type myobject as object ( myattribute mynumber );

I know one way - get a table with a column of the desired type.

 create table mytable (mynumber number(3));

Now you can declare variables

 myvar mytable.mynumber%type;

But this is fu, how ugly. Especially, an empty table, dangling in the list of normal.

And also this method is not suitable for declaring the type of attributes in the object type. The compiler will generate an error.

Error: PLS-00329: type at schema level has invalid reference to ...

    3 answers 3

    If I understood correctly from the documentation, then I can create subtypes for standard Oracle data types only at the PL \ SQL level, and use them only in PL \ SQL. And object types are created only at the schema level and, therefore, subtypes cannot be used, only standard data types. So, apparently, only the variant with hack through a table or another object remains, as it was written above.

    Currently, you cannot define object types in a PL / SQL block, subprogram, or package.

    Oracle Built-in Datatypes

    Using PL / SQL With Object Types

    @ 4per this is for option @ 0xdb

     CREATE OR REPLACE TYPE NUMBER3 AS OBJECT (value NUMBER(3)) FINAL; CREATE TABLE TEST_T ( ID NUMBER3 //созданный тип ); INSERT INTO TEST_T (ID) VALUES (NUMBER3(333));

    SQL1.sql: 1 Row inserted [0,001c]

     INSERT INTO TEST_T (ID) VALUES (NUMBER3(3333));

    ORA-01438: value larger than specified precision for this column

    those. this option really works

    And about the "through another table" yes, it really does not work, I apologize.

     CREATE TABLE TEMP_T ( ID NUMBER(3) ); CREATE TABLE TEMP_T2 (id TEMP_T.ID%TYPE);

    ORA-00911: invalid character SQL.sql 7 14

    • @Vladislav Khapin Why hack? Quite normal subtype definition. - 0xdb 4:39
    • Yes, he was wrong. It was just that he himself did not work with objects and subtypes in this form. - Vladislav Khapin

    You can somewhere like this:

     create or replace type number3 as object(value number(3)) final; / Type NUMBER3 compiled create or replace type mytype as object (short_id number3); / Type MYTYPE compiled select mytype(number3(999)).short_id.value as short_id from dual; SHORT_ID ---------- 999 select mytype(number3(9999)).short_id.value as short_id from dual;

    ORA-01438: value larger than specified precision for this column

    Since it is defined at the scheme level, it will work everywhere:

     declare short_id number3 := number3(999); begin dbms_output.put_line(short_id.value); end; / 999

      https://stackoverflow.com/a/7412149/5574962

      You may want to use SUBTYPE. Place a subtype ad in the package and use it.

       CREATE OR REPLACE PACKAGE MYSUBTYPES_PKG IS SUBTYPE MYNUMBER IS NUMBER(3); END;

      Now you can declare the variable

       myvar mysubtypes_pkg.mynumber;

      But the attribute in the object type still does not work.