Solved

Oracle Procedure - array input

Posted on 2014-02-24
11
334 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
  • 6
  • 5
11 Comments
 
LVL 76

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 76

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
 

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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

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

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 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now