Calling PL/SQL procedures via Dynamic SQL

From: Simon Morley <morleys_at_entcf2.agw.bt.co.uk>
Date: 1997/07/30
Message-ID: <33DEF94E.1DDE@entcf2.agw.bt.co.uk>#1/1

I have a problem which requires me to call PL/SQL subprograms from a file parsing routine using dynamic SQL. The name of the subprogram to call is read in from a file by the parser and the parameters are appended to the string before calling. Whilst I have managed to do this using DBMS_SQL for procedures which have no out parameters I cannot seem to get it to work for procedures which return values (or functions for that matter). If I use dbms_sql.bind_variable and call dbms_sql.execute ut falls over with

ORA-06502: PL/SQL: numeric or value error

Am I missing something obvious? A simple test program to illustrate what I am trying to acheive is below.

function do_sql return varchar2 is

	the_result varchar2 (2000) := '';
	c_cursor integer := 0;
	status integer;
		
begin
	c_cursor := dbms_sql.open_cursor;
	dbms_sql.parse(c_cursor, 
			'begin
		sol2b.display_product_name(''FREE_120'',:the_result);
 			end;', 
			dbms_sql.v7);
	dbms_sql.bind_variable(c_cursor, 'the_result', the_result);
	status := dbms_sql.execute(c_cursor);
	dbms_sql.close_cursor(c_cursor);
	return the_result;

end;

Thanks in advance for any help.

Simon Morley.

-- 
_____________________________________________________________

Opinions expressed are my own and do not reflect my employers

Received on Wed Jul 30 1997 - 00:00:00 CDT