CODEWITHSUNDEEP
oracle-databaseplsql
refresh
refresh

Reputation: 1329

PL/SQL : ALL_TAB_COLUMNS.table_name%TYPE value

I have the following PL/SQL code:

    PROCEDURE alterTab(
        nbChar IN NUMBER)
IS

    CURSOR SQL_TABLE     IS
        SELECT TABLE_OWNER,TABLE_NAME,COLUMN_NAME,TYPE_METHODE,SEPARATEUR,PATTERN,ID FROM TAB_PARAM ;

  sTable                                  sys.ALL_TAB_COLUMNS.table_name%TYPE;
  sOwner                                  sys.ALL_TAB_COLUMNS.owner%TYPE;
  sColumn                                 sys.ALL_TAB_COLUMNS.COLUMN_NAME%TYPE;
  sType                                 sys.ALL_TAB_COLUMNS.COLUMN_NAME%TYPE;
  sPattern                                 TAB_PARAM.PATTERN%TYPE;
  sSeparateur                                 TAB_PARAM.SEPARATEUR%TYPE;
  sId                                 sys.ALL_TAB_COLUMNS.COLUMN_NAME%TYPE;

  nNbTable number;

I can't understand what sys.ALL_TAB_COLUMNS.table_name%TYPE is. For example, if the 1st row of TAB_PARAM is like this:

('USER','EMPLOYEE','NAME','rien','REP','','ID_USER');

Does sTable take the EMPLOYEE value?

Upvotes: 0

Views: 1172

Answers (2)

Alex Poole
Alex Poole

Reputation: 191265

It is saying that your sTable variable has the same data type as the table_name column in all_tab_columns. Up to 11gR2 that would be the equivalent of doing:

sTable varchar2(30);

From 12cR1 the object name limit has increased, so in that version it would be the equivalent of:

sTable varchar2(128);

By using the %TYPE syntax for the declaration, the variable will get the appropriate size for the environment the procedure is in. If you compile it in 11gR2 or earlier it will be 30 bytes, in 12cR1 or higher it will be 128 bytes - either way it will be able to hold values from that system view, or in this case presumably from your cursor, but only when you fetch into it.

You can use this to reference your own tables too of course, as you are with tab_param. The advantage is that if a column type changes you don't have to manually go through all of your PL/SQL code and change any variables to the new type/size. It's automatic - changing the table definition will invalidate the procedure, it will be recompiled on next reference, and will pick up the new type/size automatically.

Read more about the %TYPE attribute in the documentation.

You haven't shown the rest of your procedure, but it may be that you're fetching the rows from your cursor into those variables. There's an assumption there that the table_owner, table_name and column_name columns in your tab_param table are the same data type and size as those columns in all_tab_columns - which may not be true. Not sure why you wouldn't use the equivalent tab_param.column%TYPE for those. The declaration of sType looks odd too; maybe that is supposed to be tab_param.type_methode%TYPE? (If it is you could also declare a record type as sql_table%rowtype instead of having one scalar variable per column, but that's going rather off-topic...)

Upvotes: 2

MT0
MT0

Reputation: 167962

ALL_TAB_COLUMNS.table_name%TYPE is the same data type as the data type of the TABLE_NAME column in the ALL_TAB_COLUMNS table.

So:

sTable sys.ALL_TAB_COLUMNS.table_name%TYPE;

Will declare a variable sTable which has the same data type as the TABLE_NAME column in the ALL_TAB_COLUMNS table. The variable has not been given a value so it will, initially, have a value of NULL.

If you later put a cursor row into the corresponding variables then given your example, yes, it would probably have the value of EMPLOYEE - however, that code is not shown in your example.

Upvotes: 2

Related Questions