Solved

Oracle Procedure - array input

Posted on 2014-02-24
11
343 Views
Last Modified: 2014-02-26
need to write a procedure to take a list of values as input.
i believe it is a array.
also, need to parse the input value from the array.
0
Comment
Question by:eagle_ea
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39882372
There are several ways you can do this.  Which way you choose should be based on what and how you are calling the procedure.  Also what will be in the array.

Will it just be an array of numbers or strings or will it be more complex?
What apps/languages will be calling the procedure?

I would look at passing in XML.  This is the most common data exchange format and probably the easiest to work with.

If you can provide more information we can probably provide a working model.
0
 

Author Comment

by:eagle_ea
ID: 39882377
example of the input is
abc1,xyz3,yuh5
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39882384
>>example of the input is

That is not an ARRAY.  It is a delimited string.  Different things.

So something will pass you in a single string separated by commas?

This can be bad once the string exceeds 4000 characters and you have to mess with CLOBs.

I strongly encourage you to work with Management and the developers to NOT choose this method and go with XML.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:eagle_ea
ID: 39882386
Ok, then it should be a delimited string.
can you please provide me a code example on how this will be handled in the procedure?
we dont believe it will exceed 4000 characters
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39882409
I still would not do this but since you insist.

Here is a working model:
create or replace procedure myproc(p_str in varchar2)
is
begin


for i in (
select mystr from (
	select rtrim(regexp_substr(p_str,'([[:alnum:]]*)(,)?',1,column_value),',') mystr
	from 
 	table(
  	cast(
  	multiset(select level from dual connect by level <= ((length(p_str)-length(replace(p_str,','))))+1)
  	as sys.odcivarchar2list
  	)
  	)
)
) loop

	dbms_output.put_line('Got: ' || i.mystr);
end loop;
end;
/

show errors

exec myproc('a,b,c,d,e');

Open in new window

0
 

Author Comment

by:eagle_ea
ID: 39882458
would i be able to store the i.mystr to a variable?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39882527
>>would i be able to store the i.mystr to a variable?

You can do whatever you want with it.

The 'variable' will need to be an array as well.

I still think you are heading down the wrong path...

What will you be doing with your 'variable' once it assigned?
0
 

Author Comment

by:eagle_ea
ID: 39882597
one correction wiht my input,

example of the input is
'abc1','xyz3','yuh5'


is this possible?

so, that i can dirrectly use this value for further usage in the procedure
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39882628
>>example of the input is  'abc1','xyz3','yuh5'

You cannot pass that into an Oracle procedure.

To make it a string you need to add single quotes to it:
'''abc1'',''xyz3'',''yuh5'''

Those are ALL single quotes no double quotes.
0
 

Author Comment

by:eagle_ea
ID: 39882890
now i'm having trouble again....

i need to pass this inpu value into a select statement....

like
select * from table1 where cond in (procedure_input)
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39882910
>>now i'm having trouble again....

The more detailed your question from the start, the quicker and more correct the answer you receive...

>>select * from table1 where cond in (procedure_input)

You cannot do this.

You can either use dynamic SQL (I do NOT recommend this way):

v_sql := 'select * from table1 where cond in ( || ' procedure_input || ')';

Then execute v_sql as a cursor


or
Take my select statement that converts it into a TABLE and select from it:

select * from table1 where cond in (
select ... -- my select statement above that turns it into a table.
)


Before you ask, yes, I can help convert my sample procedure above into this.

Before I do this, I would like your COMPLETE requirements so I don't have to keep changing things as you change the requirements.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question