26 September 2011

What's the value of using %TYPE for parameters?

I received this question from a player last week:

This is not a question about the 13 September quiz in particular, but a related issue that it raises in my mind. I know that I should never do this:
l_myval VARCHAR2(100);
when I can do this:
l_myval mytab.col1%TYPE;
But what about parameters? Until Tuesday's quiz, I assumed that the constraints on subtypes and anchored parameter would be checked. Now I see that they're not. So is there any advantage in writing
PROCEDURE myproc(p_in IN mytable.col1%TYPE) 
instead of:
? The only advantage that I can see is that the first version might make it more obvious how the procedure is intended to be used.

Rather than answer with my views immediately, I thought I'd offer it up to our players to see what you think.


  1. Hello All,

    I only wonder "in which measure" was this behavior deliberately implemented by the PL/SQL
    product developer team as it currently is, and what are the reasons behind this.

    I remember another quiz, where a subprogram parameter was defined as SIMPLE_INTEGER and
    it DID NOT accept a NULL value to be passed as actual parameter.

    I don't see any reason why the defined NOT NULL constraint of a subtype would be inherited,
    while the size of a VARCHAR2 subtype would not.

    By the way, if we base a variable on a TABLE.COLUMN%TYPE, then it behaves almost the opposite, that is, it does inherit the size but not the NOT NULL constraint.

    Looks like using a %TYPE attribute just defines a new entity with "some degree of compatibility"
    with the referenced entity, but not strictly identical, in the sense that there might exist values that can be assigned to one of them, but not to the other.

    It would be interesting to hear what do they say at Oracle about this issue.

    Thanks & Best Regards,

  2. I think there's still benefit in using %type for parameters over the actual underlying type (number, date, ...). While rare in my experience, the "base type" of column can change over time (e.g. number -> varchar2). By keeping the reference to %type, procedures accepting this don't need to be recoded to accept the new type.

    Also, parameters using %type can easily be searched. So if a columns base type does change, the references can be found and tested!

  3. What about the impact of using procedures with %TYPE arguments in a distributed database environment (remote procedure calls)?

    Should you use %TYPE when referencing a remote table?

    Anyone doing this? Run into any issues?

  4. Hello Steven, All,

    Back several good years ago, we had an issue related to online replicating some tables from an Oracle8i source database to an Oracle7 target database using database triggers for pushing the data.

    Trying to push data from Oracle8i to Oracle7i through triggers worked ok when DML was performed on the source table using for example SQL*PLUS, but encountered
    "ORA-03106: fatal two-task communication protocol error"
    when the same DML was performed in an Oracle Developer (web-deployed Forms6i) application.

    The workaround was to use an RPC, instead of directly performing distributed DML-s in the trigger code, like this:

    On the target Oracle7 database we defined a package containing code like the following (in the spec):

    CREATE OR REPLACE PACKAGE copy_target_pkg
    SUBTYPE t_mytable IS mytable%ROWTYPE;

    PROCEDURE p_copy (p_operation IN VARCHAR2, p_rec IN t_mytable);
    END copy_target_pkg;

    On the source Oracle8i database we defined a synonym:

    CREATE SYNONYM copy_target_pkg FOR myuser.copy_target_pkg@db_link_to_target

    and a package that was using the remote subtype and called the remote procedure:

    PROCEDURE p_copy (p_operation IN VARCHAR2, p_source_rec IN t_my_local_table%ROWTYPE)
    -- define a record having the remote record subtype
    l_rec copy_target_pkg.t_mytable;
    -- populate the target record l_rec from p_source_rec
    -- call the remote procedure
    copy_target_pkg.p_copy (p_operation, l_rec);
    END p_copy;
    END copy_source_pkg;

    This was one of the problematic issues I can remember related to using type anchoring
    with a remote database.

    Otherwise, today I am widely using remote %TYPE definitions like mysyn.mycolumn%TYPE,
    where "MYSYN" is a synonym pointing to a remote table or view.

    This works ok already for many years, without any problems, starting with Oracle8i, then 10gR2 and now 11gR1,
    with the remote database being first 9i and then 10gR2.

    Thanks & Best Regards,