Oracle Procedure - array input

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.
Who is Participating?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>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:

Those are ALL single quotes no double quotes.
slightwv (䄆 Netminder) Commented:
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.
eagle_eaAuthor Commented:
example of the input is
Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

slightwv (䄆 Netminder) Commented:
>>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.
eagle_eaAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
I still would not do this but since you insist.

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

for i in (
select mystr from (
	select rtrim(regexp_substr(p_str,'([[:alnum:]]*)(,)?',1,column_value),',') mystr
  	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;

show errors

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

Open in new window

eagle_eaAuthor Commented:
would i be able to store the i.mystr to a variable?
slightwv (䄆 Netminder) Commented:
>>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?
eagle_eaAuthor Commented:
one correction wiht my input,

example of the input is

is this possible?

so, that i can dirrectly use this value for further usage in the procedure
eagle_eaAuthor Commented:
now i'm having trouble again....

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

select * from table1 where cond in (procedure_input)
slightwv (䄆 Netminder) Commented:
>>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

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.