[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Oracle Procedure - array input

Posted on 2014-02-24
11
Medium Priority
?
345 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 2000 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

649 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