Return multiple result set from Procedure

hi Team ,
I have a scenario where a Oracle stored procedure have around 20 select Queries as a sing varchar input argument . I need to execute all these 20 select statement and return 20 results sets .

create or replace PROCEDURE POC_PROJECT
(
  SELECT_QUERIES IN VARCHAR2 -- All select Queries will come in this Ex:- SELECT BORROWER FROM TABLE WHERE LOANID = 123456;
 
) AS
sql_stmt  VARCHAR2(4000);
BEGIN
  dbms_output.put_line(SELECT_QUERIES);-- Here i am seeing the query
  sql_stmt:=SELECT_QUERIES;
  EXECUTE IMMEDIATE sql_stmt; -- How will i return the result set of this query ? If there are 10 select queries i need 10 result set
END POC_PROJECT;


Please help me Thanks.
Vinoy K PAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Execute immediate doesn't return a result set.  It also cannot execute a SELECT unless you provide it something to hold the results.

Can you provide more detail about the inputs you will be provided and what you plan on doing with the results once you get them?

Will all the queries ALWAYS return the same number of columns and data types?

Off the top of my head, I would take each query and generate XML and return on large XML doc containing all the information.  This way the number of columns and data types do not really matter.
0
Vinoy K PAuthor Commented:
hi ,

In put queries will be always like
SELECT BORROWER FROM TABLE WHERE LOANID = 123456;
SELECT CITY FROM TABLEABC  WHERE BORROWER = VKP;
SELECT SAL FROM XYZ WHERE LOANTYPE= COV;

Result of query always return only one single column value but return types may vary .
0
slightwv (䄆 Netminder) Commented:
OK, what about what you want to do with the results and how you will be using them?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Vinoy K PAuthor Commented:
i will just iterate all the resultsets and crate a Hashmap in Java .
The Input queries are associated to each fields in UI and we will map these fields with the results from the procedure
0
awking00Information Technology SpecialistCommented:
If you're trying to create a Java HashMap with queries as the keys and the results as the values, it might be quicker to actually get the result sets in Java and store them in an array (creating a Hashmap<String, String[]>). Can you provide some more details on how the queries are entered in the stored procedure (e.g. a comma delimited string or select values from a queries table, etc) and how the HashMap is to be used?
0
slightwv (䄆 Netminder) Commented:
Since you want it, here is a working model:
create or replace function myfunc ( queries in varchar2 ) return clob is
	sql_value	varchar2(100);
	stmt_count	number := 1;
	sql_stmt	varchar2(4000);
	retVal		clob;
begin

	sql_stmt := regexp_substr(queries,'[^;]+',1,stmt_count);

	while sql_stmt is not null loop
		--dbms_output.put_line(sql_stmt);

		execute immediate sql_stmt into sql_value;
		retVal := retVal || ':' || sql_value;
		stmt_count := stmt_count + 1;
		sql_stmt := regexp_substr(queries,'[^;]+',1,stmt_count);
	end loop;

	return retVal;
end;
/
show errors

select myfunc('select sysdate from dual; select ''Hello World'' from dual; select 123 from dual;') from dual;
	

Open in new window


I wouldn't return a delimited list.  This is just the basic concept.  Change the return value to something better suited to your Hashmap.
0
slightwv (䄆 Netminder) Commented:
I should point out that if you SQL contains a ';' in a single statement, my code will break.

for example:
select ';' from dual;
0
Vinoy K PAuthor Commented:
Thanks it works , Just one more question can we this function keep running even if one select query failed ( Syntax failure ).
I don't want to stop the execution if any one select query is syntactically wrong . Can we make a static string say ERROR and continue with the other queries ?
0
Vinoy K PAuthor Commented:
I should point out that if you SQL contains a ';' in a single statement, my code will break :- Thanks but this we are validating and catching UI itself. So query content will never have ;
0
slightwv (䄆 Netminder) Commented:
>>Can we make a static string say ERROR and continue with the other queries ?

Simple error trapping:
create or replace function myfunc ( queries in varchar2 ) return clob is
	sql_value	varchar2(100);
	stmt_count	number := 1;
	sql_stmt	varchar2(4000);
	retVal		clob;
begin

	sql_stmt := regexp_substr(queries,'[^;]+',1,stmt_count);

	while sql_stmt is not null loop
		dbms_output.put_line(sql_stmt);

		begin
			execute immediate sql_stmt into sql_value;
			exception when others then
			sql_value := 'ERROR on stmt: ' || stmt_count;
		end;
		retVal := retVal || ':' || sql_value;
		stmt_count := stmt_count + 1;
		sql_stmt := regexp_substr(queries,'[^;]+',1,stmt_count);
	end loop;

	return retVal;
end;
/
show errors


select myfunc('select sysdate from dual; select ''Hello World'' from dual; select error from dual; select 123 from dual;') from dual;
	

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
Have you considered turning the SQL into a series of UNION ALL statements and querying the database directly?

Then you don't need to worry about the function and parsing the results.

BUT, it will either ALL run or ALL fail on an error.

Using your example:
SELECT BORROWER FROM TABLE WHERE LOANID = 123456
union all
SELECT CITY FROM TABLEABC  WHERE BORROWER = VKP
union all
SELECT SAL FROM XYZ WHERE LOANTYPE= COV;
0
Vinoy K PAuthor Commented:
Thanks for the suggestion , but in the UNION ALL  i will loose the flexibility of handling Syntax failure of any single select statement right ?
0
johnsoneSenior Oracle DBACommented:
And what prevents someone from sending in statements like DELETE, UPDATE, DROP, etc?  Maybe your UI does but anybody with a database login an privilege to run the procedure can pass those statements through.
0
slightwv (䄆 Netminder) Commented:
>>i will loose the flexibility of handling Syntax failure of any single select statement right ?

correct.
0
Vinoy K PAuthor Commented:
Thanks , It will be great if you can help me with below two item as well .

1)The below select query is returning ERROR in our function , but running fine when stand alone .Any specific reason ?
SELECT LISTAGG(AGE, '  ') WITHIN GROUP (ORDER BY AGE) AS DEPENDENTS_AGE
FROM DEPENDENTS WHERE LOAN_ID = 123456;

2) If select result is null or empty i need to return EMPTY just like we are returning ERROR
0
slightwv (䄆 Netminder) Commented:
1) Might be a single quote issue.  Use two to get 1 (not a double quote, two single quotes):
SELECT LISTAGG(AGE, ''  '') WITHIN GROUP (ORDER BY AGE) AS DEPENDENTS_AGE
FROM DEPENDENTS WHERE LOAN_ID = 123456;

What is the error (remove the exception handler and you'll see the stack trace)?

2) NVL:
            retVal := retVal || ':' || nvl(sql_value,'EMPTY');
0
Vinoy K PAuthor Commented:
Awesome  . All looks Perfect. Great, Thanks .
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.