Field as parameter in Generic Update - ORACLE

Asked

Viewed 593 times

0

Currently I have a precedent that reads an XML, and updates the table fields personal based on the values sent by XML, such as CPF, RG, Name, etc.

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

pessoa_fisica_w         pessoa_fisica%rowtype;

Once done, I do the following update.

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

Today it works.

What I need now is to receive the key field to update XML. It is currently fixed cd_person = person and would need and need to perform a dynamic update.

Would that 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 XML.

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

There would be some way to create this generically, or would have to manually make all fields using case/if and always passing fixed the fields of Where update, along with the values?

sql oracle

  • The fields are char ?

  • Field types vary between varchar2 and number only.

  • The fields varchar2 and char need to come between parentheses in the UPDATE command. Concate quotes for this, I think syntax is something like'''|field||''''

  • The problem is that person_fisica_w is a rowtype, not a varchar2, occurs invalid type.

  • Row was an attempt to set all columns of the table ?

  • That’s right, therefore, the columns are in constant changes, so I used to set all the columns. I also used it because it is easier to manipulate the values when transferred from xml cursor to Row.

  • You would have to mount an sql by reading the metadata table dba_tab_columns

  • Okay, a problem would solve it, but the problem of dynamically taking the value of a field from inside a rowtype, would there be like, something like people_fisica_w.field('field')?

  • Yes that would be it. When I’m on a PC I try to mount a response.

  • I am waiting, because I had to put this problem aside and check others, since I could not find a solution.

Show 5 more comments

1 answer

1


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;

  • Okay, I understood this question, but how to visualize information dynamically within the rowtype, wouldn’t there be how, this? For, to select with the dynamic field this resolves, with the "VS_SAI" doing what sent by you, but within the WS_RNOME ||'. '||R.COLUMN_NAME there will be no value inside my rowtype.

  • the idea and mount the update sql that will "catch" the content.

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.