How to turn a varchar in a variable

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, 24 May 2006 11:19 am

I'm doing a Trigger using a dynamic sql (the DBMS_SQL package). To finalize what I intend to do, I need to figure out how to turn a varchar in a variable. In my sql, as I don't know what's the name of the column beforehand, have to do with the Oralce interpret the varchar ': new. ' || v_campo as a variable. Any idea how to do this?
Location: São Luís - MA

Poston Wed, 24 May 2006 11:25 am

What do you really intend to do with the dynamic query and this variable of type varchar? :?:
Location: Videira - SC

Poston Wed, 24 May 2006 11:40 am

I'm doing a trigger to perform a cascading update. Use two cursors, one for find that uses the PK and another to find out the name of FK.

Code: Select all
/*Cursor que recupera todas as tabelas que utilização a chave primária dessa
tabela. Quando a chave primária é atualizadas, todos as tabelas listadas nessa
consulta deverão receber a atualização.*/
CURSOR c_tabelas_atualizadas is
SELECT A.TABLE_NAME, A.constraint_name FROM SYS.user_constraints A
WHERE A.r_constraint_name = 'PK_A';

/*Cursor que recupera o nome das colunas de cada chave estrangeira que utiliza
a chave primária dessa tabela*/
CURSOR c_colunas_atualizadas is
select COLUMN_NAME from user_Cons_Columns a
where a.constraint_name = v_restricao
ORDER BY A.position;
the table and fields that should I upgrade do the update using the DBMS_SQL package.

Code: Select all
            DBMS_SQL.PARSE (cur_update,
              'UPDATE ' || v_tabela || ' SET ' || v_chave_FK(0) || ' = ' || :new.ID
              || ' WHERE ' || v_chave_FK(0) || ' = ' || :old.ID, DBMS_SQL.NATIVE);
Note that I had to put in the UPDATE command: new.ID. Is it that I do not want canister so, because if I change the value of my key I have to change this trigger. I want to make it dynamic.
To do this, use the following SQL to find the columns that are part of the key:
Code: Select all
CURSOR c_colunas_chave_primaria is
select COLUMN_NAME from user_Cons_Columns a
where a.constraint_name = 'PK_A'
ORDER BY A.position;
Then wanted to do the concatenation of ': new ' with the result of this cursor. But then Oracle does not interpret the: new. column as a varchar and not as a command to get the column of the new row in the table.

Gave it to understand the problem?
Location: São Luís - MA

Return to PL/SQL

Who is online

Users browsing this forum: No registered users and 5 guests