Oracle sql function SPLIT?

Thread view:

Alex - 04 May 2006 17:55 GMT

Does it exist?
I need prepared statement like this:
select split(?) from dual
which for parameter like '1 2 3' returns 3 rows
1
2
3
and for parameter 'a b c d' returns 4 rows
a
b
c
d

Any idea how to do this?
Alex.

Reply to this Message

Bjorn Abelli - 04 May 2006 18:20 GMT

"Alex" wrote...

> Does it exist?

Not that I know of, but you can roll your own... ;-)

> I need prepared statement like this:
> select split(?) from dual
[quoted text clipped - 9 lines]
>
> Any idea how to do this?

I had this little snippet laying around.
==========================================

TYPE nr_table IS TABLE OF CHAR(10) NOT NULL;

...

FUNCTION SPLIT ( p_string_in IN VARCHAR2,
delimiter in Varchar2 := ' ') RETURN nr_table IS

V_CHAR nr_table := nr_table();
v_string_in Varchar2(8000);
x_string Varchar2(10);
ix NUMBER;
i1 NUMBER;

BEGIN
v_string_in := delimiter || p_string_in;
i1 := instr(v_string_in, delimiter);
ix := 1;

LOOP
x_string := Substr(v_string_in, i1 + 1, 10);
v_char.EXTEND;
v_char(ix) := x_string;
i1 := instr(v_string_in, delimiter, i1 + 1);
exit when i1 = 0;
ix := ix + 1;
END LOOP;
RETURN v_CHAR;
END;

==========================================
I haven't used it in many years, so it might not work (there might be some
lines of code missing in there), and there might be simpler versions around
in Oracle nowadays.

It should be, as it's possible to write functions in Java for Oracle...

BTW, what has this to do with Java?

// Bjorn A

Reply to this Message

Alex - 04 May 2006 18:29 GMT

>BTW, what has this to do with Java?

It should run in Java in prepared statement.
So, unfortunately, PL/SQL doesn't work...

Reply to this Message

Bjorn Abelli - 04 May 2006 18:58 GMT

> >BTW, what has this to do with Java?
> It should run in Java in prepared statement.
> So, unfortunately, PL/SQL doesn't work...

Then I understand even less of your question... ;-)

You have "1 2 3" and wan't that turned into separate items, by the way of a
PreparedStatement for Oracle? I think it's a tad overkill...

What's wrong with:

String[] items = "1 2 3".split(" ");

?

Maybe it would be better to explain what you intend to use it for.

// Bjorn A

Reply to this Message

Alex - 04 May 2006 19:49 GMT

I simplified task as much as possible.
See, this is quintessence of what I need.
Prepared statement is huge and this is only one piece which I still
don't have.
As well as whole application is huge and complicated. Which I like.
All what I need is this simple piece. The rest I can do by myself.

The reason why I use this groups is that I need it in Java. If I'll use
(let's say database.oracle) it would be definitely PL/SQL answer and
nobody will understand why I can't use it. And I can't since I'm not
DBA and do not have rights to work with database on their level.

Of course I can use not prepared statement but generated one like

"select a,b from table where key in ("+"1,2,3" +")"

but I really like something better like
select a,b from table where key in (select split(?) from dual)

Reply to this Message

Joe Weinstein - 05 May 2006 02:27 GMT

> I simplified task as much as possible.
> See, this is quintessence of what I need.
[quoted text clipped - 14 lines]
> but I really like something better like
> select a,b from table where key in (select split(?) from dual)

Ok, Now that we know really what you want, it's not possible.
A '?' is not a free pass to put in any subset of SQL or any list
of things. It is only for supplying a single data value that the
DBMS can plug into a query plan it generated for the prepared
statement's SQL. The best you can/should do is to parse your
list in java and use/reuse a prepared statement like:

prepareStatement("select a,b from table where key in (?,?,?,?,?,?...)");

with as many '?'s as you want, and if your current list is shorter than
the number of '?'s, fill the remaining ones with impossible or repeat
values. If your list is bigger, collect the returns and rerun the statement
to get the rest...

Joe Weinstein at BEA Systems

Reply to this Message

Alex - 05 May 2006 23:47 GMT

>it's not possible

Joe:
I appreciate you spent your time answering questions.
But don't be so sure about negative answres.
I'm java programmer and definitely not DBA. So, with DBA's help I found
this:

http://asktom.oracle.com/pls/ask/f?p=4950:8:16225135796265676711::NO::F4950_P8_D
ISPLAYID,F4950_P8_CRITERIA:10720306411201

which exactly meet my needs.

I post it only for other forum's readers so they will be not confused
with previuos negative answer.

SQL> create or replace type myArray as table of number
2 /

Type created.

SQL>
SQL> create or replace function str2table( p_string in varchar2 )
return myArray
2 as
3 l_data myArray := myArray();
4 l_string long := p_string;
5 l_n number;
6 begin
7 while (l_string is not null)
8 loop
9 l_n := instr( l_string, ';' );
10 if ( l_n = 0 )
11 then
12 l_n := length(l_string)+1;
13 end if;
14 l_data.extend;
15 l_data(l_data.count) := substr( l_string, 1,
l_n-1 );
16 l_string := substr( l_string, l_n+1 );
17 end loop;
18 return l_data;
19 end;
20 /

Function created.

SQL> select * from TABLE ( str2table('12; 15; 8') );

COLUMN_VALUE
------------
12
15
8

SQL>

Quote:
in fact I want to use this function in a IN clause:
eg:
(...) where id_param in str2table('12, 15, 8');

Alex.

> > I simplified task as much as possible.
> > See, this is quintessence of what I need.
[quoted text clipped - 30 lines]
>
> Joe Weinstein at BEA Systems

Reply to this Message

joeNOSPAM@BEA.com - 07 May 2006 15:19 GMT

I'm glad you found a solution with the fancy PL-SQL I said it would
need.
This didn't have anything to do with JDBC then, and PL-SQL can be
executed with prepared statements, and I thought you said you couldn't
change the DBMS. Making a new type and function is changing the DBMS.
We're still wondering why you need the list of values you already have
in
the client sent to the DBMS and then sent back to you?
Joe

Reply to this Message

Alex - 07 May 2006 23:18 GMT

It was inspired by Business Objects.
Imagine that you have Objects in the database and everything is
property of this Object.
For example Kind (property 1) is Person then Firts Name (property 2)
and Last Name (property 3) and SSN (property 4) and DateOfBirt (propery
5).

To obtain any data you need only one SQL:
select key, property_type, property value where property_type in
str2table(?)

So, to retrieve all persons Names and SSN you need parameter as
2,3,4
for Last Name and DOB
3,5
and so on.

You don't need to write SQL for each request (which is generated
automaticaly or per user request). You don't need to change SQLs or
database structure for new parameters. And so on.

Of course this example is as simple as possible. Other factors are
involved.
But it explains why I need it.

Isn't it wonderful?

Alex.

Reply to this Message

Joe Weinstein - 08 May 2006 16:35 GMT

> It was inspired by Business Objects.
> Imagine that you have Objects in the database and everything is
[quoted text clipped - 22 lines]
>
> Isn't it wonderful?

Got it. Yes. PL-SQL is a good thing.
Joe

> Alex.

Reply to this Message

Joe Weinstein - 04 May 2006 19:16 GMT

> Does it exist?
> I need prepared statement like this:
[quoted text clipped - 11 lines]
> Any idea how to do this?
> Alex.

For an argument that can have any number of entries, it
would take some *very* fancy PL-SQL to do it, if at all possible.
The much better way would be to simply parse the string into
it's bits in java. Why do you need it from the DBMS?
Joe Weinstein at BEA Systems

Reply to this Message

Oracle sql function SPLIT?

Free Magazines