Problem when select'ing by ROWID inside procedure

Question

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.

Answer 1

Can you get an explain plan of this:

select TABLA       from BITACORA where rowid = 'AAAEC5AAFAAAADHAAC';

and this:

select TABLA       from BITACORA where rowid = :1;

It depends on which version of Oracle you are on, but try this:

explain plan for
select TABLA       from BITACORA where rowid = 'AAAEC5AAFAAAADHAAC';

select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

delete from plan table;

And then this:

explain plan for
select TABLA       from BITACORA where rowid = :1;

select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

delete from plan table;

Update your question with the output and that might give some further clues.

Answer 2

Why are you selecting from DUAL? Unless you've omitted something vital in your simplification this ought to work for you:

declare
  row_id   ROWID;
  consulta VARCHAR2(1000);
begin  
  row_id := 'AAAEC5AAFAAAADHAAC';
  select 'insert into ' ||TABLA || ' values(' ||VALOR_VIEJO|| ')' 
  into   Consulta
  where rowid = row_id; 
  DBMS_OUTPUT.PUT_LINE(Consulta);
  execute immediate Consulta;        
end;
/

Answer 3

You might have to use the CHARTOROWID() conversion function:

row_id := CHARTOROWID('AAAEC5AAFAAAADHAAC');

According to the oracle doc, this would not be necessary for the data type urowid.