CODEWITHSUNDEEP
databaseoracle-databaserowid
Eduardo Grajeda
Eduardo Grajeda

Reputation: 328

Problem when select'ing by ROWID inside procedure

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.

Upvotes: 0

Views: 900

Answers (3)

Juergen Hartelt
Juergen Hartelt

Reputation: 674

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.

Upvotes: 1

APC
APC

Reputation: 146249

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;
/

Upvotes: 0

WW.
WW.

Reputation: 24301

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.

Upvotes: 0

Related Questions