Thanks for the question, Prasadh.

Asked: April 12, 2019 - 12:51 pm UTC

Last updated: April 26, 2019 - 12:02 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Please help me on one of our prod issue.

We have an object and all the attributes in the object(EMP_OBJ) are columns of a table(Lets say EMP) in diff schema. So when we defined the object we gave the datatype and size of the object attributes(columns) as per the size & datatype of the columns in the table(EMP) of another schema. But recently one developer has changed the size of the column in the table(EMP) which we were not aware of it. when someone tried some operation in front end application we got issue "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" this was because of the size of that column in the Object(EMP_OBJ) which we did not increase after the column size change in the table(EMP). So we are planning to re-define the attributes as column type of the table.

DROP TYPE SCHEMA_A.EMP_OBJ;
create or replace type EMP_OBJ as object
(emp.ename%type,
sal number,
deptno number
);

but we are getting error as below.
Error(2,6): PLS-00329: schema-level type has illegal reference to

so apart from increasing the size of the attribute of the Object to large enough, is there any better way how we can avoid this issue in the future if someone else changes the column size our object should reflect it automatically or should not fail.

creating object is it sql level and column type attribute is pl/sql level which never recognized by sql but what it the better way to handle this scenario. your valuable inputs are very important to me.

Thanks

and Connor said...

Sorry, not much we can do with that here. There is no explicit linkage between a type (which is a SQL object) and anything in PLSQL.

One option you could explore is to expand the type constructor to include a reference to EMP, even if its not used. In that way, you then a dependency relationship between the type and the table, which could be used to identify what objects need addressing if you are going to change EMP, eg

SQL> create or replace type EMP_OBJ as object
  2  (
  3
  4    ename  varchar2(10),
  5    sal    number,
  6    deptno number,
  7
  8    constructor function emp_obj(
  9       self          in out nocopy emp_obj,
 10       ename         varchar2,
 11       sal           number,
 12       deptno         number) return self as result
 13  )
 14  /

Type created.

SQL>
SQL>
SQL> create or replace type body EMP_OBJ as
  2
  3    constructor function emp_obj(
  4       self          in out nocopy emp_obj,
  5       ename         varchar2,
  6       sal           number,
  7       deptno         number) return self as result
  8    is
  9      e scott.emp%rowtype;
 10    begin
 11      self.ename := ename;
 12      self.sal := sal;
 13      self.deptno := deptno;
 14
 15      return;
 16    end;
 17
 18  end;
 19  /

Type body created.

SQL> select * from user_dependencies
  2  where  name = 'EMP_OBJ'
  3  @pr
==============================
NAME                          : EMP_OBJ
TYPE                          : TYPE
REFERENCED_OWNER              : SYS
REFERENCED_NAME               : STANDARD
REFERENCED_TYPE               : PACKAGE
REFERENCED_LINK_NAME          :
SCHEMAID                      : 104
DEPENDENCY_TYPE               : HARD
==============================
NAME                          : EMP_OBJ
TYPE                          : TYPE BODY
REFERENCED_OWNER              : SYS
REFERENCED_NAME               : STANDARD
REFERENCED_TYPE               : PACKAGE
REFERENCED_LINK_NAME          :
SCHEMAID                      : 104
DEPENDENCY_TYPE               : HARD
==============================
NAME                          : EMP_OBJ
TYPE                          : TYPE BODY
REFERENCED_OWNER              : SCOTT
REFERENCED_NAME               : EMP
REFERENCED_TYPE               : TABLE
REFERENCED_LINK_NAME          :
SCHEMAID                      : 104
DEPENDENCY_TYPE               : HARD
==============================
NAME                          : EMP_OBJ
TYPE                          : TYPE BODY
REFERENCED_OWNER              : MCDONAC
REFERENCED_NAME               : EMP_OBJ
REFERENCED_TYPE               : TYPE
REFERENCED_LINK_NAME          :
SCHEMAID                      : 104
DEPENDENCY_TYPE               : HARD

PL/SQL procedure successfully completed.

Rating

(7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

the power of %(ROW)TYPE

Racer I., April 15, 2019 - 6:14 am UTC

Hi,

Ah yes, the perennial disconnect between pl/sql and sql.
You can try to define the type in some (existing appropriate or new) package which allows to use table.column%TYPE or even table%ROWTYPE or define a view or package cursor (spec) and then use view/cursor%ROWTYPE if the type is not 1:1 the same as the table.
Oracle will create a hidden sql-type for this and keep it updated. Over the years there have been some bugs but they are eventually ironed out. I found that more and more use cases can be switched to this. For example a modern JDBC driver can use this like a regular sql-type by referencing the package type (must be a SUBTYPE apparently) :

CREATE TABLE BALogging(ID NUMBER(2), Text VARCHAR2(1000)); 

CREATE OR REPLACE PACKAGE BindArrayTest
IS
  SUBTYPE BARowType IS BALogging%ROWTYPE;
  TYPE BAArrayType IS TABLE OF BARowType INDEX BY BINARY_INTEGER;
  PROCEDURE CallWithBindArray(pRows BAArrayType);
END BindArrayTest;
/
show errors;

CREATE OR REPLACE PACKAGE BODY BindArrayTest IS
  PROCEDURE Log(pText BALogging.Text%TYPE)
  IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO BALogging (ID, Text) VALUES (0, pText); 
    COMMIT;
  END Log;
  ---
  PROCEDURE CallWithBindArray(pRows BAArrayType)
  IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    FORALL i in 0..pRows.COUNT - 1
      INSERT INTO BALogging VALUES pRows(i);
    COMMIT;
  END CallWithBindArray;
END BindArrayTest;
/
show errors;

   public static void callStatement() throws Exception {
      Connection conn;      
      String call_text;
    CallableStatement call;
    Struct[] struct;
    Array    array;
    Object[] p1recobj;
    Object[] p2recobj;
    
      conn  = getConnection(CONNECT_STRING, USER, PASSWORD);
      p1recobj = new Object[] {new Integer(1), "TERM"};
      p2recobj = new Object[] {new Integer(2), "TURM"};
      struct = new Struct[2];
      struct[0] = conn.createStruct("BINDARRAYTEST.BAROWTYPE", p1recobj);
      struct[1] = conn.createStruct("BINDARRAYTEST.BAROWTYPE", p2recobj);
      call_text  = "BEGIN BindArrayTest.CallWithBindArray(?); END;\n";
      call  = conn.prepareCall(call_text);
      array = ((OracleConnection)conn).createOracleArray("BINDARRAYTEST.BAARRAYTYPE", struct);
  call.setArray(1, array);
      call.setQueryTimeout(timeout);
     call.execute();
   }

regards,

April 16, 2019 - 11:42 pm UTC

True. Just make sure no-one inadvertently drops that automatically generated type. I've seen that lots of times and nasty things result :-)

Prasadh avinigadda, April 15, 2019 - 1:06 pm UTC

Hi Tom,

Thanks for your reply.

you were saying "One option you could explore is to expand the type constructor to include a reference to EMP, even if its not used. In that way, you then a dependency relationship between the type and the table, which could be used to identify what objects need addressing if you are going to change EMP"

-- we already having constructor inside which we opened a cursor to get all the columns from EMP table and then assigning to object columns.

When you said Identify what objects need addressing - is this can be querying Invalid_objects ? or is there a diff way to find that? Please suggest.

Thank you

April 16, 2019 - 11:44 pm UTC

DBA_DEPENDENCIES.

Hence if you are *going* to make a change to EMP, you would query this view to see what impacts it would have.

(This is good practice in any situation)

Prasadh avinigadda, April 15, 2019 - 1:17 pm UTC

I mean finding invalid objects as below.

select * from all_objects where status='INVALID';

select * from dba_objects where status='INVALID';

Or is there any better way?

mosey on

Racer I., April 17, 2019 - 11:08 am UTC

Hi,

The type will only become invalid if the code no longer compiles but with Oracles autoboxing (converting types on the fly) that will hardly ever happen as you saw with the longer varchar.
As Connor said you have to manually check everything in dba_dependencies that depends on your table. This would list your type so you are prepared.
After the change the best you could do is check if the type was recompiled recently but I'm not sure that is even always tracked.

regards,

Prasadh avinigadda, April 17, 2019 - 7:12 pm UTC

Thank you for all your suggestions.

but still I'm looking for best way to solve this problem.

My manager asked me to handle this problem in a better and efficient way, but I'm not feeling good to increase the varchar2 type attributes size to large enough. Because few attributes having size of 1 byte or max 100 byte.

any other solutions would be greatly appreciated.

Thank you.

blinkered

Racer I., April 18, 2019 - 7:03 am UTC

Hi,

> to solve this problem.

Which problem are you referring to here? Apparently no longer the "how to detect type-invalidating changes". For that why not try out my package-type method above?

> but I'm not feeling good to increase the varchar2 type attributes size to large enough

If the table field has been enlarged, presumably the type needs to reflect this, no?
If not how about adding a setMethod to the type and using SUBSTR() to the length as defined in the type? If you can live with occasional loss of data as your error indicates someone actually tried to store a longer value in the type.

regards,

Prasadh avinigadda, April 18, 2019 - 12:28 pm UTC

Hi Racer,

Thanks for your reply.

My problem is when the column size in the actual table changes then how to automatically handle it in the Object. as suggested we need to check dba_dependencies and can then alter the object but if we miss to check dba_dependencies (which was happened in our case ) then we would have to face a production failure.

As a solution define the Object attributes size large enough (32767 bytes) irrespective of actual column size but in this case I don't think its a good idea to define 32767 byte size attribute for columns which have 1 byte length.

Coming to your package type code , to be frank I did not understand much. I'm sorry. also worrying about this comment to the solution.

"True. Just make sure no-one inadvertently drops that automatically generated type. I've seen that lots of times and nasty things result :-) "

Thanks

April 26, 2019 - 12:02 am UTC

but if we miss to check dba_dependencies (which was happened in our case ) then we would have to face a production failure.

That strikes me as a problem of process, not of technology.

Because if *that* can happen, then what is to say that something like "We forgot to run step 5 of out 10 step deployment process".

Once you're at the point where things *might* happen in your production system and things *might* get missed....well....I think it is not a case of *if* you have a problem but *when*.

Sorry to be brutal - but software never replaces good processes.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

the power of %(ROW)TYPE

mosey on

blinkered

More to Explore

PL/SQL demos

PL/SQL docs

Create Object with Column type attributes

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

the power of %(ROW)TYPE

mosey on

blinkered

More to Explore

PL/SQL demos

PL/SQL docs