Hello, I've spent hours trying to find a fix to this problem, but since I'm unable to find I've decided to ask here.
I'm doing a procedure to retrieve information from a table based on the row's ROWID
and with the results I'm doing some custom query using execute immediate
. I've reduced the problem to the following lines, which I'm executing directly in SQL Developer:
declare
row_id ROWID;
consulta VARCHAR2(1000);
begin
row_id := 'AAAEC5AAFAAAADHAAC';
select 'insert into ' ||
(select TABLA from BITACORA where rowid = row_id) || ' values(' ||
(select VALOR_VIEJO from BITACORA where rowid = row_id) || ')'
into Consulta from dual;
DBMS_OUTPUT.PUT_LINE(Consulta);
--execute immediate Consulta;
end;
The previous lines doesn't work as it is. When I print the contents of Consulta
it shows:
insert into values()
but if I eliminate the variable row_id
and put it directly in the queries like this, it works:
declare
consulta VARCHAR2(1000);
begin
select 'insert into ' ||
(select TABLA from BITACORA where rowid = 'AAAEC5AAFAAAADHAAC') || ' values(' ||
(select VALOR_VIEJO from BITACORA where rowid = 'AAAEC5AAFAAAADHAAC') || ')'
into Consulta from dual;
DBMS_OUTPUT.PUT_LINE(Consulta);
--execute immediate Consulta;
end;
Displaying what I would expect, something like:
insert into MI_TABLA values(1,'Hola','Adios',3,1)
This is not work-related, so there is no concerns about performance/security. Thanks for any feedback.