Re: PL/SQL table as procedure argument

A copy of this was sent to Bob Jones <bob_at_commercegroup.com> (if that email address didn't require changing) On Tue, 27 Apr 1999 17:25:35 -0400, you wrote:

>I am looking for a way to pass a pl/sql table to a procedure when you
>don't know ahead of time what the type of the parameter is...
>
>The pl/sql table will always be a table of strings....
>
>I am trying to call procedures dynamically which is why I won't know the
>types... There is obviously some way to do it because the various Oracle
>Web Server PL/SQL cartridges do it when a form is submitted to them that
>contains more than one form object with the same name... Of course
>those cartridges are written in C which is not an option here....
>
>Any help would be great....
>
>thanks,
> Bob

This is not 100% but it works in most 'normal' cases (it fails pretty bad on procedures with tables of records or vice versa, it fails on FUNCTIONS that return a table as well -- but you have the code, you can fix it :)... A test case showing how to use it follows (tested in 7.3 and 8.0):

create or replace function NameToTableType( p_procedure in varchar2,

											p_version   in number,
						                    p_name      in varchar2 ) return

	l_comma			number;
	l_piece		    varchar2(255);
	l_txt			varchar2(4000);
	l_status		number;
	l_schema		varchar2(32);
	l_part1			varchar2(32);
	l_part2			varchar2(32);
	l_dblink		varchar2(32);
	l_part1_type 	number;
	l_part2_type	number;
	l_object_number number;
	l_return_val	varchar2(255);
begin
	dbms_utility.name_resolve( p_procedure, 1, l_schema, l_part1, l_part2,
							   l_dblink, l_part1_type, l_object_number );
	
	if ( l_part1 is null ) then
		l_part1 := l_part2;
		l_part2 := NULL;
	end if;

	sys.diutil.subptxt( l_part1, l_part2, l_schema, 
					    null, null, l_txt, l_status );

	for i in 1 .. nvl(p_version,0)-1 loop
		l_txt := substr( l_txt, 1+instr( l_txt, ';' ) );
	end loop;
	l_txt := substr( l_txt, 1, instr( l_txt, ')' )-1 ) || ',';
	

	if ( instr( l_txt, '(' ) > 0 ) then
		l_txt := substr( l_txt, instr( l_txt, '(' )+1 );
	
		loop
			exit when ( l_txt is null or l_return_val is NOT null );
			l_comma := instr( l_txt, ',' );	
			l_piece := substr( l_txt, 1, l_comma-1 );
			l_txt   := ltrim(rtrim(substr( l_txt, l_comma+1 )));
			if ( instr( l_piece, '/* DEFAULTED */' ) > 0 ) 
			then	
				l_piece := substr( l_piece, 1, instr(l_piece,'/* D')-1 );
			end if;
			l_piece := ltrim(rtrim(l_piece));
			if (upper(p_name) = upper(substr(l_piece,1,instr(l_piece,' ')-1)))
			then
				l_return_val := substr(l_piece, instr(l_piece,' ',-1)+1);
			end if;
		end loop;
	end if;
	
	if ( nvl(instr( l_return_val, '.' ),0) = 0 and l_part1 is not null and
l_part2 is not null ) 
	then
		l_return_val := l_part1 || '.' || l_return_val;
	end if;

	if ( l_return_val is not null AND instr(l_return_val, '.', 1, 2) = 0 )
	then
		l_return_val := l_schema || '.' || l_return_val;
	end if;

	if ( l_return_val like '%.' ) 
	then
		l_return_val := null;
	end if;
	return l_return_val;

end NameToTableType;
/

and here is how to use it:

create or replace procedure test_get_table_types is

	l_procedure varchar2(255);
	l_typename	varchar2(255);

begin
	for x in ( select user owner, package_name, object_name, overload,
argument_name
				 from user_arguments
				where data_type = 'PL/SQL TABLE' )
	loop
		if ( x.package_name is not null )
		then
			l_procedure := x.owner || '.' || x.package_name || '.' ||
x.object_name;
		else
			l_procedure := x.owner || '.' || x.object_name;
		end if;

		l_typename := NameToTableType( l_procedure, x.overload, x.argument_name

		dbms_output.put_line( to_char( to_number(x.overload), '99' ) || ' ' ||
							  rpad( l_procedure, 30 ) || ' ' || 
							  rpad( x.argument_name, 30 ) || ' ' || 
							  l_typename );
		dbms_output.put_line( '================' );
	end loop;

end test_get_table_types;
/
exec test_get_table_types;

In C we don't need to know the table type, we just bind strings or numbers or dates......  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation