CODEWITHSUNDEEP
sqloracle-databaseconcatenation
Bruno Mello
Bruno Mello

Reputation: 25

SELECT CONCAT in Oracle DB

I'm working with an oracle database but I don't have much experience with it, in case I need to make a 'backup' of a table column I was wanting to use CONCAT on the sql server but it works on Oracle, no, it gives the error ORA-00909. the query I'm using is:

SELECT Concat ( 'UPDATE HCAlerta SET descricao="', descricao, '" WHERE neoId=', neoid )
FROM   hcalerta --that would give me AS an answer
UPDATE hcalerta
SET    descricao = "Tarefa 000015 - Solicitar Vaga -  - Requisição de Pessoal."
WHERE  neoid = 1071178

Upvotes: 0

Views: 435

Answers (2)

Alex Poole
Alex Poole

Reputation: 191245

Oracle's concat() function only takes two arguments. You could nest calls but that's messy:

CONCAT(CONCAT(CONCAT('UPDATE HCAlerta SET descricao="', descricao,), '" WHERE neoId='), neoId)

It's simpler to user the concatenation operator:

'UPDATE HCAlerta SET descricao="' || descricao || '" WHERE neoId=' || neoId

But your quotes are wrong; double quotes surround an identifier, like a table or column name, not a value; so you need single quotes - but need to escape them:

'UPDATE HCAlerta SET descricao=''' || descricao || ''' WHERE neoId=' || neoId

In your query that would then be:

select'UPDATE HCAlerta SET descricao=''' || descricao || ''' WHERE neoId=' || neoId
from HCAlerta

db<>fiddle demo (which isn't displaying the multibyte characters properly; but that's an issue with that environment, not the query...).

You could also use the alternative quoting mechanism but that is maybe more confusing in this particular case.

unfortunately returned to me Error: ORA-00923: FROM keyword not found where expected

If you have a LONG column then you could use a PL/SQL block to handle it:

begin
  for r in (
    select neoId, descricao from HCAlerta
  )
  loop
    dbms_output.put_line('UPDATE HCAlerta SET descricao=''' || r.descricao || ''' WHERE neoId=' || r.neoId);
  end loop;
end;
/

... remembering to enable output in your client (e.g. set serveroutput on in SQL*Plus etc.).

db<>fiddle

It might be easier to take a complete copy of the table and restore it if necessary; or export/import, etc.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Use the || operator:

SELECT 'UPDATE HCAlerta SET descricao="' || descricao || '" WHERE neoId=' || neoId
FROM HCAlerta;

In Oracle, CONCAT() only takes two arguments.

If you want a version that works in both databases, you can use REPLACE():

SELECT REPLACE(REPLACE('UPDATE HCAlerta SET descricao="[descricao]" WHERE neoId=[neoId]',
                       '[neoId]', neoid
                      ), '[descricao]', descricao
              )
FROM HCAlerta;

Upvotes: 1

Related Questions