Field as parameter in Generic Update - ORACLE

Question

Field as parameter in Generic Update - ORACLE

Navigation

#1 by (1 votes)

0

I currently have a procedure that reads an XML, and does an update on the fields of the physical_person table based on the values sent by the XML, such as CPF, RG, Name, etc.

Today I read the XML values and insert into the table variable.

pessoa_fisica_w         pessoa_fisica%rowtype;

Once this is done, I'll do the following update.

update  pessoa_fisica
set row = pessoa_fisica_w
where   cd_pessoa_fisica = pessoa_fisica_w.cd_pessoa_fisica;

Today this works.

What I need now is to receive the key field to update the XML. Currently it is fixed cd_people_fisica = person_fisica_w.cd_pessoa_fisica and would need and need to perform a dynamic update.

It would be this query:

Exec_sql_Dinamico('grava_log_01',
                  'update   pessoa_fisica ' ||
                  'set  row =' ||  pessoa_fisica_w ||
                  'where    ' || c01_w.ds_campo_acao || '= pessoa_fisica_w.'||c01_w.ds_campo_acao||';');

c01_w is the cursor with the values obtained from the XML.

My problem is, as a dynamic query would read the personal_women_w , Today I run into problem PLS-00306: incorrect number of argument types in the call to '||' because of concatenation, and there would also be a problem accessing the personal_wiz_w. '|| c01_w .ds_campo_acao .

Would there be any way to create this generically, or would I have to manually do all fields using case / if and always passing fixed fields of where update, along with values?

sql oracle

asked by anonymous 26.02.2018 / 16:08

1 answer

Execute function managed by Redux How to store return on Ajax request in a variable

score 1 · Accepted Answer

The basic idea in an anonymous block follows below

DECLARE
  VS_SAI    VARCHAR2(4000);
  VS_TABELA VARCHAR2(30) := 'PESSOA_FISICA';--nome da tabela do banco
  WS_RNOME  VARCHAR2(30) := 'PESSOA_FISICA_W';--nome do array
BEGIN
  VS_SAI := 'UPDATE ' || VS_TABELA;
  VS_SAI := VS_SAI || ' SET ';
  --lê a matadados para montar as colunas 
  FOR R IN (SELECT * FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = VS_TABELA)
  LOOP
    VS_SAI := VS_SAI || R.COLUMN_NAME || '=' || WS_RNOME ||'.'||R.COLUMN_NAME ||', ';
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(VS_SAI);--APENAS PARA EXIBICAO 
END;