Re: Oracle sql function SPLIT?

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_DISPLAYID,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.

Joe Weinstein wrote:
Alex wrote:

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)


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

.

Relevant Pages

  • Re: Oracle sql function SPLIT?
    ... this is quintessence of what I need. ... Prepared statement is huge and this is only one piece which I still ... The reason why I use this groups is that I need it in Java. ... statement's SQL. ...
    (comp.lang.java.databases)
  • Re: use of DBI; I am getting multiple error messages mixed in with the correct output.
    ... Perl was to relate it to uninitialized values in Java or C++. ... The fact that nulls in SQL have special ... Both Java and C++ have containers that support set theoretic ...
    (comp.lang.perl.misc)
  • Re: SQL
    ... Persistence access is the abstraction, ... However, UML is just one of many OOA/D notations proposed, so it is an implementation of an OOA/D notation. ... Note that I was careful to say that SQL is a solution to persistence /access/ when the data is represented in RDB form. ... Sounds a little bit like Java and the JVM.... ...
    (comp.object)
  • Re: object databases
    ... Calling SQL from say java code is one of the easiest tasks in mordern ... The major problem is that SQL statements are written as strings in ... the strings are not compiled until runtime. ...
    (comp.object)
  • Re: Is perl better? :(((
    ... draw from variable "table" and execute a slightly modified SQL just once. ... > public void openCon(String url, ... > String user, String password) { ... > Total Java expenses 4.5 minutes. ...
    (comp.lang.java.databases)