Re: giving tablenames to functions or procedures

From: Matthias Gresz <GreMa_at_t-online.de>
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.de

Received on Wed Apr 08 1998 - 00:00:00 CEST