Status of Procedure

If a procedure is referring a table and if the structure of the table is changed, lets say data type of the any column is changed, then is it possible that the status of the procedure remains valid? Explain

Questions by anulc

Editorial / Best Answer

tar.goyal  

  • Member Since Aug-2010 | Aug 4th, 2010

It is possible that a procedure remain valid even when the data type of columns of a table on which it is operating change.


This can be achieved by using Anchor declaration i.e. %TYPE and %ROWTYPE while defining reference to such table columns and rows respectively in the procedure.

Showing Answers 1 - 18 of 18 Answers

avinaspandey

  • Jun 3rd, 2010
 

Yes the package will become invalid and it will be automatically recompiled when it is called (in case it is local object), if it is not local then we have manually recompile it.

  Was this answer useful?  Yes

muraligrandhi

  • Jun 7th, 2010
 

Whenever any changes are occur in referenced objects then automatically subprograms are come into invalid. So you need to compile the subprogram for valid status.

Example: By using the below query you can find the status of the subprogram.

select status from user_objects where object_name=&SUBPROGRAM_NAME;

  Was this answer useful?  Yes

neerajalatha

  • Jul 5th, 2010
 

Whenever the column of the data type of a table which is referred by particular procedure is changed then in that case to retain the valid status of procedure we have to recompile the procedure.

  Was this answer useful?  Yes

anironic

  • Jul 21st, 2010
 

If PL/SQL stored SP %type and does not explicity declare a datatype, then it is quite possible that Stored procedure remains valid, even after changing the datatype in the table.

Ex, you have a declaration num TAB.Num%type

If this was NUMBER(11)  earlier, and now it is changed to NUBER(13,2), it does not make the SP invalid.

But yes, during runtime you might face some isues

  Was this answer useful?  Yes

tar.goyal

  • Aug 4th, 2010
 

It is possible that a procedure remain valid even when the data type of columns of a table on which it is operating change.


This can be achieved by using Anchor declaration i.e. %TYPE and %ROWTYPE while defining reference to such table columns and rows respectively in the procedure.

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions