Re: Newbie ? - Howto pass table name to lower PL/SQL proc

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 21 Sep 1998 17:36:42 GMT
Message-ID: <360b8dd8.16123914@192.86.155.100>

A copy of this was sent to Brian Richardson <RichaBK_at_kscgws00.ksc.nasa.gov> (if that email address didn't require changing) On Mon, 21 Sep 1998 13:22:09 -0400, you wrote:

>Hi all,
>
>I am using Netscape/PC/Win95 client, PL/SQL, Oracle v7.3.x.
>
>I am writing procedures to dynamically create web pages and
>allow the user to extract/update data in an Oracle database.
>
>I am trying to figure out how to pass a table name, (selected by
>the user in a top-level procedure) to a lower level procedure
>which will perform the UPDATE of the database.
>
>I am successfully passing the table name through a parameter list
>to the Procedure as follows:
>
>PROCEDURE upd_data(
> arg1 IN varchar2,
> arg2 IN varchar2,
> tabl_nam IN varchar2,
> arg4 IN varchar2,
> arg5 IN varchar2,
> agr6 IN varchar2) IS
>
>

you need to use dynamic sql to do this. It might look like:

PROCEDURE upd_data(

                        arg1 IN varchar2,
                        arg2 IN varchar2,
                        tabl_nam IN varchar2,
                        arg4 IN varchar2,
                        arg5 IN varchar2,
                        agr6 IN varchar2) IS

begin
  execute_immediate( 'update ' || tabl_nam ||

                     ' set var1 = ''' || arg1 || ''', ' ||
                         ' var2 = ''' || arg2 || ''' where ' ||
                         ' var4 = ''' || arg4 || ''' );

   ....
end;

where execute immediate was another procedure you wrote that looks like:

create or replace procedure execute_immediate( sql_stmt in varchar2 ) as

    exec_cursor integer default dbms_sql.open_cursor;     rows_processed number default 0;
begin

    dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
    rows_processed := dbms_sql.execute(exec_cursor);
    dbms_sql.close_cursor( exec_cursor );
    dbms_output.put_line( dbms_sql.last_row_id );

exception

    when others then

      if dbms_sql.is_open(exec_cursor) then
        dbms_sql.close_cursor(exec_cursor);
      end if;
      raise;

end;
/

Note that you might want to read up on dbms_sql and bind variables -- that would be much more efficient then hard coding the string literals into the update statement as my example above does (and it'll make the code easier to read as you won't have to quote bind variables as you do literals -- all of the '' in the string gets ugly)

>I would like to use the contents of tabl_name as the name of the
>table to be updated.
>
>I want to use an UPDATE statement, which I believe would be similar to
>the following:
>
>UPDATE tabl_nam SET var1=arg1, var2=arg2 WHERE var4=arg4;
>
>I am getting a compilation msg indicating that tabl_nam must name a
>table
>to which the user has access
>
>Do I need any TYPE declarations local to this Procedure in order for
>tabl_nam to
>be recognized as an accessible table?
>
>Thanks in advance for any suggestions.
>
>Brian R.
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Sep 21 1998 - 12:36:42 CDT