Variable whose contents is the name of a column

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc

Poston Wed, 25 Sep 2013 3:20 pm

Hello, is it possible to use the contents of a variable that stores the name of a column of a table and use it as a reference to get the value of this column?

Example; say I want to compare the old and new values of the columns of a table. Not to have multiple ifs for each column, I would have to use a variable that will receive the name of the column you want to compare the new antidos values of an update. Like this: var iavel varchar2 column;

column: = ' NM_CLIENTE ' if (: old. <>: new <coluna>. <coluna>).
Robson Costa
Location: Campo Grande - MS

Poston Wed, 25 Sep 2013 3:31 pm

What do you intend to actually do?

Create a trigger that compares the fields (old and new) from a variable??

As you would report what the field that you want to compare?

If you can tell which field you want to compare, not give to make your process before the trigger??
Location: Fortaleza - CE


Daniel N.N.

Poston Wed, 25 Sep 2013 3:42 pm

The column names I caught user_tab_columns table.

Do I have to check all columns from multiple tables and I didn't want to do several ifs.
Robson Costa
Location: Campo Grande - MS

Poston Thu, 26 Sep 2013 10:41 pm

Robson, the suggestion may be a bit complex, but maybe you can develop a querie with the help of USER_TAB_COLUMNS and DDL command syntax of creating triggers.

Search a little more on the subject in these links, which is the ASKTOM's site from a technical director of Oracle named Tom Kyte: ... 9412348055 ... 4825535375 Maybe the solution Tom presents these links (the first him already shows the complete solution) is as close as possible to what you want.

Another suggestion, much more laborious " ", would you use the ERWIN and the same macro language to create these triggers. Except that in this case you would need a little time to study how the macro language works in ERWIN. It seems a little complicated at first but once you get the hang of " ", it is possible to develop the DDL codes with speed and reliability, from a small quantity of rows of macro commands. And the best part is that these macros you warrant that you will not have future problems by changing the table structure. The macro reads this structure and assemble the trigger code dynamically in real time. You don't spend any time trying to do the maintenance/updating of the code of the triggers. And once you'vê managed to develop a macro trigger to a table, you can reuse this macro to generate the DDL trigger to the other system tables (and here no matter how many columns the table).

I understand that it is a paid product, but you can download the COMMUNITY EDITION Web site ERWIN CA (a trial version which allows you to work with up to 20 tables) and do the reverse engineering of one of the tables from your bank. Study then macros and then try to develop a trigger. Getting a macro, you can import your table table Erwin and reuse the macro to generate the DDL trigger from the table.

Hugs and good luck, Sergio Coutinho
Location: Sao Paulo - SP

Poston Fri, 27 Sep 2013 7:11 pm

It Worked. Thank you
Robson Costa
Location: Campo Grande - MS

  • See also
    Last Post

      Return to PL/SQL

      Who is online

      Users browsing this forum: No registered users and 6 guests