Re: PL/SQL: variable column & table name in select

> Hi, All!
> Help me please, I've broken my head on this problem :). The situation is as
> follows: I need making a procedure on PL/SQL to get the max number of the
> first column in any table (of course if this column is numeric). How can I
> say in SELECT "first column in any table"? The procedure looks something
> alike:
> procedure max_number (table_name varchar2,
> maxnum number) is
> begin
> select max([What here?]) into maxnum from [What here?];
> dbms_output.enable;
> dbms_output.put_line('maxnum='||to_char(maxnum));
> end max_number;
>
> WBR, Viatcheslav.

Here is an anonymous pl/sql-block which does, waht you want

declare

	cid				number;
	stmt			varchar2(1000);
	rows_processed	number;
	result			number;
	tab_name		user_tab_columns.table_name%type := 'komned$netzknoten';
	dat				user_tab_columns.data_type%type;
	col_name		user_tab_columns.column_name%type;
begin
	select data_type, column_name
	into dat, col_name
	from user_tab_columns
	where 
		table_name = upper(tab_name) and
		column_id = 1;
--		
	if dat = 'NUMBER' then
		cid := dbms_sql.open_cursor;
--		
		stmt := 'select max(' || col_name || ') maxval from '|| tab_name;
		dbms_sql.parse (cid, stmt, dbms_sql.native);
--		
		dbms_sql.define_column (cid, 1, result); 
		rows_processed := dbms_sql.execute_and_fetch (cid, exact => true);
		dbms_sql.column_value (cid, 1, result);
--		
		dbms_sql.close_cursor(cid);
--
		/* to see this, type 'set serveroutput on' in sqlplus */
		dbms_output.enable(20000);
		dbms_output.put_line (result);
	end if;
exception
	when others then
		if dbms_sql.is_open (cid) then
			dbms_sql.close_cursor(cid);
		end if;
		raise;

Im sure you can this easily rewrite it as a procedure. The package is used for 'dynamic SQL'...in Oracle8 (8i) it can be done more elegantly by

execute immediat stmt

Look into your manual for more

Regards, Stephan Received on Thu Jan 11 2001 - 11:34:10 CST