ROWID as Parameters in PL/SQL

Question

I am trying to create a helper stored proc to save on repeated code.

I wrote the following stored procedure that takes the table name, status_id, and ROWID.

PROCEDURE sp_update_stage_status(p_table_name IN VARCHAR2,
                                 p_status_id IN NUMBER,
                                 p_rowid IN ROWID)
AS
BEGIN       
  execute immediate 'UPDATE ' || p_table_name
                 || ' SET STATUS_ID = ' || p_status_id 
                 || ' WHERE ROWID = ' || p_rowid;       
END;

However whenever I execute it I get the following.

ORA-00904: "AAATQEAAEAAAAHEAAB": invalid identifier
ORA-06512: at "OBR_DEV.PKG_COMMON", line 32
ORA-06512: at "OBR_DEV.PKG_DIRECTORY", line 449

What am I doing wrong here?

To be more generic you should use data type ´UROWID` – Wernfried Domscheit Jul 21 '15 at 16:05 — Wernfried Domscheit, Jul 21 '15 at 16:05

score 7 · Accepted Answer · edited Jul 22 '15 at 07:02

You're dropping the contents of rowid in directly without quoting it.

Your query became WHERE ROWID = AAATQEAAEAAAAHEAAB which is comparing the rowid column to the AAATQEAAEAAAAHEAAB column.
It should be WHERE ROWID = 'AAATQEAAEAAAAHEAAB'. Add some quotes to your dynamic SQL and you should be okay.

Or better yet, use bind variables and don't worry about quoting:

EXECUTE IMMEDIATE 
  'UPDATE ' || p_table_name || ' SET status_id = :status WHERE rowid = :seek_rowid'
  USING p_status_id, p_rowid;

score -2 · Answer 2 · answered Jul 21 '15 at 15:35

You are declaring p_rowid as rowid type, you must declare p_rowid as varchar2(18).

PROCEDURE sp_update_stage_status(
   p_table_name IN VARCHAR2,
   p_status_id IN NUMBER,
   p_rowid IN VARCHAR2(18)
) AS
BEGIN
   execute immediate 'UPDATE ' || p_table_name || ' SET STATUS_ID = ' || p_status_id || ' WHERE ROWID = ' || p_rowid;
END;

ROWID as Parameters in PL/SQL

2 Answers2