Re: Tablename as Parameter in Stored Procedure

From: Erika Grondzakova <Erika.Grondzakova_at_cern.ch>
Date: Thu, 19 Aug 1999 10:49:12 +0200
Message-ID: <37BBC508.74AA3642@cern.ch>

Hi,

This procedure take a table name and ROWID as a parameter, selects some column from this table for this ROWID, and then UPDATEs it. I hope this is what you are looking for ...

create or replace procedure process_it(p_TableName IN varchar2, p_ROWID IN varchar2)
is

l_cid INTEGER;
l_execute INTEGER;
l_name VARCHAR2(16);
l_statement VARCHAR2(100);

begin

/* select some column from the table p_TableName passed as a parameter to this procedure
/* for a specified ROWID p_ROWID also passed as a parameter   l_cid := DBMS_SQL.OPEN_CURSOR;
  l_statement := 'SELECT name FROM '||p_TableName||' WHERE ROWID =

'''||p_ROWID||'''';
  DBMS_SQL.PARSE(l_cid, l_statement, DBMS_SQL.NATIVE);
  DBMS_SQL.DEFINE_COLUMN (l_cid, 1, l_name,16);

  l_execute := DBMS_SQL.EXECUTE(l_cid);   WHILE DBMS_SQL.FETCH_ROWS(l_cid) <> 0 LOOP     DBMS_SQL.COLUMN_VALUE(l_cid, 1, l_name);     DBMS_OUTPUT.PUT_LINE('Old name: '||l_name);   END LOOP;
  DBMS_SQL.CLOSE_CURSOR(l_cid);

/* the same but with the update
  l_statement := 'UPDATE '||p_TableName||' SET name = ''new_name'' WHERE ROWID = '''||p_ROWID||'''';
  DBMS_SQL.PARSE(l_cid, l_statement, DBMS_SQL.NATIVE);   l_execute := DBMS_SQL.EXECUTE(l_cid);   DBMS_SQL.CLOSE_CURSOR(l_cid);

/* and again select to see that the value was changed after update   l_cid := DBMS_SQL.OPEN_CURSOR;
  l_statement := 'SELECT name FROM '||p_TableName||' WHERE ROWID =

'''||p_ROWID||'''';
  DBMS_SQL.PARSE(l_cid, l_statement, DBMS_SQL.NATIVE);
  DBMS_SQL.DEFINE_COLUMN (l_cid, 1, l_name,16);

  l_execute := DBMS_SQL.EXECUTE(l_cid);   WHILE DBMS_SQL.FETCH_ROWS(l_cid) <> 0 LOOP     DBMS_SQL.COLUMN_VALUE(l_cid, 1, l_name);     DBMS_OUTPUT.PUT_LINE('Name after UPDATE: '||l_name);   END LOOP;
  DBMS_SQL.CLOSE_CURSOR(l_cid);

end;
/

SQL> execute process_it('a','00007DE0.0001.000F'); Old name: old_name
Name after UPDATE: new_name

PL/SQL procedure successfully completed.

Erika

Chittesh Mehta wrote:
>
> Hi,
>
> Is there any way to pass a tableName as parameter and
> get the certain value out of that table.
>
> Basically my stored procedure should take tablename and ROWID
> and then will do some processing on that Row of that table.
>
> (Basically select and update statement...)
>
> Any help is always welcome.
>
> Thanks
>
> - chittesh
Received on Thu Aug 19 1999 - 03:49:12 CDT