PL/SQL Coding Standards and %type 2

[BJCooperIT]

I have inherited a set of PL/SQL coding standards written in 2007 for an Oracle 8i database. My task is to make recomendations to update these standards for 10g. While I am sure I might have more later, right now I have two questions.

1. The original standards state that public procedures inside a package should not contain %TYPE variables. Outside of compiling issues should the referenced table be modified, which is unlikely here, can someone give insight on why this "standard" might have been established?

2. Are there any 10g features that invalidate the common standards for 8i?

Thanks for any help you can offer.

[sup]Beware of false knowledge; it is more dangerous than ignorance. ~ George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)

 

[Dagon]

1) I've yet to come across any significant drawback to %TYPE in any situation. However, I've regularly come across the reverse where procedures have to be changed or have stopped working because a column has been lengthened.

2) Can't think of anything that would invalidate existing standards (if those standards were any good). There are some new things that could be introduced e.g. when using a FORALL statement, I now always use the INDICES OF notation instead of explicitly enumerating the array. You would perhaps need standards for newer pieces of functionality like the MERGE command (e.g. when to use it, how to format it etc).

 

[ChrisHunt]

I can't think of any good reason not to use %TYPE. There aren't really any "compiling issues should the referenced table be modified", look:

SQL> create table ch_temp2 (col1 varchar2(5));

Table created.

SQL> CREATE OR REPLACE package xxx as
  2     function echo (p1 in ch_temp2.col1%TYPE) return ch_temp2.col1%TYPE;
  3  end;
  4  /

Package created.

SQL> CREATE OR REPLACE package body xxx as
  2     function echo (p1 in ch_temp2.col1%TYPE) return ch_temp2.col1%TYPE is
  3        x1 ch_temp2.col1%TYPE;
  4     begin
  5        x1 := p1;
  6        return x1;
  7     end;
  8  end;
  9  /

Package body created.

SQL> SELECT xxx.echo('1') FROM dual;

XXX.ECHO('1')
-------------------------------------
1

SQL> -- Let's make it fail!
SQL> SELECT xxx.echo('123456') FROM dual;
SELECT xxx.echo('123456') FROM dual
       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "me.XXX", line 5

SQL> alter table ch_temp2 modify col1 varchar2(10);

Table altered.

SQL> -- At this point, both the package spec and body 
SQL> -- are marked as invalid...
SQL>
SQL> SELECT xxx.echo('123456') FROM dual;

XXX.ECHO('123456')
-----------------------------------------
123456

SQL> -- ...so Oracle automatically re-compiles them both
SQL> -- when the function is called. They're both valid now!
SQL>
SQL> -- The only way you can get a problem is by dropping
SQL> -- the table:
SQL>
SQL> drop table ch_temp2;

Table dropped.

SQL>  SELECT xxx.echo('123456') FROM dual;
 SELECT xxx.echo('123456') FROM dual
        *
ERROR at line 1:
ORA-06575: Package or function XXX is in an invalid state

SQL> -- But you're not going to do that, are you?

I assume the reason for your standard is because they're based on what someone assumed would happen, rather than what actually does happen. As Dagon points out, you're far more likely to be bitten by not using %TYPE.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[KenCunningham]

A well-deserved star for such a walk-though, Chris!

Some days are diamonds, some days are rocks - make sure most are the former.

 

[BJCooperIT]

A well-deserved star from me, too!

Thanks for everyone's help. Talking to the client, apparently the aversion to %TYPE is simply a personal preference of the head DBA.

[sup]Beware of false knowledge; it is more dangerous than ignorance. ~ George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)