Re: giving tablenames to functions or procedures
Date: 1998/04/08
Message-ID: <6gg23i$p$1_at_news01.btx.dtag.de>#1/1
Hi Michael,
first: stop using char, use varchar2 instead. second: unlike SQL-Server or Access you can't code SELECT-statements in oracle-SP's. You've got to use cursors instead. Since your input parameter is the table name you've got to use DBMS_SQL. Run 'Dbmssql.sql'-scipt put of your rdbms<version>-folder to create the DBMS_SQL-package.
Example for using DBMS_SQL:
Procedure DOPPELTEEINHEIT(p_TABELLE VARCHAR2, p_EINH NUMBER) is
l_SQL VarChar2(2000); l_ANZAHL NUMBER; C1 INTEGER;
BEGIN
l_SQL := 'SELECT NVL(COUNT(*),0) INTO l_ANZAHL FROM ' || p_Tabelle; l_SQL := l_SQL || ' WHERE '; l_SQL := l_SQL || ' EINHEIT = ' || p_EINH ; l_SQL := l_SQL || ' '; RAISE_APPLICATION_ERROR(-20003,l_SQL); c1 := dbms_sql.open_cursor; dbms_sql.parse(c1,l_SQL,1); dbms_sql.define_column(c1, 1, l_ANZAHL ); l_ANZAHL:= dbms_sql.execute(c1); if dbms_sql.fetch_rows( c1 )>0 then dbms_sql.column_value( c1 , 1, l_ANZAHL ); else exit; end if; dbms_sql.close_cursor(c1); IF l_ANZAHL<>0 then RAISE_APPLICATION_ERROR(-20033,'Einheitennummer wird in schon benutzt!'); END IF; exception when others then if dbms_sql.is_open(c1) then dbms_sql.close_cursor(c1); end if; RAISE;
END DOPPELTEEINHEIT; On 8 Apr 1998 13:19:57 GMT, michael.brohl_at_t-online.de (Michael Brohl) wrote:
>Is there any way to give tablenames to functions or
>procedures and how can I refer to them?
>
>e.g.
>
> create function Test
> (cTabName CHAR)
> RETURN CHAR IS
> .
> .
> BEGIN
> .
> .
> SELECT Testfield FROM cTabName;
> .
> .
> END;
> /
>
>This example doesn't work, because the compiler didn't know
>if the specified table is accessable at runtime.
>
>Any hints?
>
>Thanks,
>
> Michael
>
>---
> Fido: 2:2449/480.3
> Email: michael.brohl_at_usa.net
> Homepage: http://home.t-online.de/home/michael.brohl
>
-- Regards Matthias Gresz :-) GreMa_at_T-online.deReceived on Wed Apr 08 1998 - 00:00:00 CEST