Pass ROWID into a procedure...

[Monkeygumbo]

Hi,

In PL/SQL can I pass ROWID into a procedure? If I can, what variable type should i use in the declaration of the procedure?

PROCEDURE insert_info (p_rowid IN ??????)

How can I fill in that blank?

Thanks,

MG

 

[SantaMufasa]

Monkey,

Just use "rowid" as the datatype. Here is an example using the Oracle "S_EMP" table:

create or replace procedure ShowByRowid (rowin in rowid) is
namehold varchar2(50);
begin
select last_name into nameHold from s_emp where rowid = rowin;
dbms_output.put_line(nameHold);
end;
/
Procedure created.

SQL> exec ShowByRowid('AAABx4AAEAAAAMiAAA')
Velasquez

PL/SQL procedure successfully completed.

SQL> exec ShowByRowid('AAABx4AAEAAAAMiAAC')
Nagayama

PL/SQL procedure successfully completed.

Does this answer the question satisfactorily?

Dave

 

[sem]

Unfortunately Oracle doesn't guarantee the same ROWID structure in different versions, so persisting rowids for future use is not a good style. Though within procedure it may (and should!) be used, because this access path is faster than any index.

Regards, Dima

 

[SantaMufasa]

But Dima, WITHIN a given Oracle version, rowid structure certainly is consistent, and the code structure, above, will work on any version; only the LITERAL rowid structure might change. Additionally, I don't see any evidence that Monkey expects persistent rowids or rowid values.

So, I believe we have given Monkey the answer he is asking for: "...procedure ShowByRowid (rowin in ROWID)..."

Dave

 

[sem]

It wasn't written as a disclaimer, just a precaution. I tried to note that

SQL> declare
2 ri rowid;
3 begin
4 select rowid into ri from emp where rownum=1;
5 dbms_output.put_line(ri);
6 end;
7 /
AAABx4AAEAAAAMiAAA

before invoking ShowByRowid might be usefull.

Regards, Dima

 

[Monkeygumbo]

Thanks guys!