Re: PL/SQL

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Wed, 18 Nov 1998 12:58:26 GMT
Message-ID: <3652c029.1302382@inet16.us.oracle.com>

On Wed, 18 Nov 1998 10:56:12 GMT, h.haag_at_fnt.de (Horst Haag) wrote:

>Hi,
>
>I am trying to access a table within a oracle function.
>The function looks similar to:
>
>The function get an id as argument it then select in one main table (elements)
>the tablename where to look for an specific attribute in the detail table
>(v_class).
>While compiling the function oracle tells me that v_class is no
>table.
>Hints ver welcome !!
>Thanks.
>
>create or replace function get_ident
>(v_comp_elid in varchar2)
>RETURN number
>is
> v_ident number;
> v_class varchar2(20);
>begin
>--
>-- Select tablename from elements
>--
> select
> e.tablename into v_class
> from
> elements e
> where
> e.elid=v_comp_elid;
>--
>-- Now select ident from table
>--
> select
> ident into v_ident
> from
> v_class
> where elid=v_comp_elid;
>--

v_class is not a table name. It is a variable.

You can't use a variable as the table name. Oracle tries to verify all queries during compilation of procedures and functions. You will need to use dynamic SQL to accomplish this.

eg.

create or replace function get_ident
(v_comp_elid in varchar2)
RETURN number
is

   v_ident  number;
   v_class  varchar2(20);
   l_cursor number;
   l_status number;

begin

--
-- Select tablename from elements
--
   select
      e.tablename into v_class
   from
      elements e
   where
      e.elid=v_comp_elid;

/*

--
-- Now select ident from table
--
   select
      ident into v_ident
   from
      v_class

   where elid=v_comp_elid;
*/

--
-- Using Dynamic SQL to select ident from table
--

  l_cursor := dbms_sql.open_cursor;
  dbms_sql.parse( l_cursor, 'select ident from ' || v_class ||

                  ' where elid = ''' || v_comp_elid || '''',
                  dbms_sql.native );

  dbms_sql.define_column( l_cursor, 1, v_ident );   l_status := dbms_sql.execute( l_cursor );   if dbms_sql.fetch_rows( l_cursor ) > 0 then     dbms_sql.column_value( l_cursor, 1, v_ident );   end if;
  dbms_sql.close_cursor( l_cursor );
--
--
RETURN (v_ident);
end get_ident;
/

Assuming that the input v_comp_elid will always only produce exactly one row, this will work.

Hope this helps.

chris.

--
Chirstopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Nov 18 1998 - 06:58:26 CST