Home » SQL & PL/SQL » SQL & PL/SQL » String to a table (10 g)
String to a table [message #350673] Thu, 25 September 2008 20:04 Go to next message
sultanmasood
Messages: 11
Registered: March 2006
Junior Member
Hi,

I have a string for 3 inputs for my procedure wrapper.

A =>('1,2,3);
B =>('11,34,22');
C =>('2,3,4);

Need to convert these strings to a record so that it can be passed in the API as numbers.

test_rec.a=>1
test_rec.b=>11
test_rec.c=>2
Re: String to a table [message #350688 is a reply to message #350673] Thu, 25 September 2008 22:35 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Not much clear about the requirement .

You can enter the string as it is in to the table. And can apply substr, instr and to_number to parse as and when needed.

Thumbs Up
Rajuvan.
Re: String to a table [message #350692 is a reply to message #350673] Thu, 25 September 2008 22:53 Go to previous messageGo to next message
faq_orcl
Messages: 1
Registered: September 2008
Location: India
Junior Member
The Procedure has 3 inputs and all of them have been passed as a string.
Procedure test('1,2,3','11,34,22','2,3,4');

The 3 inputs are actually numbers passed as varchar.
Need to store them as record and then pass to the API one by one as numbers.
First time it would be 1,11,2 second time it would be 2,34,3 and then 3,22,4 something like

rec_test.a=>1
rec_test.b=>11
rec_test.c=>2
Re: String to a table [message #350719 is a reply to message #350692] Fri, 26 September 2008 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.orafaq.com/forum/mv/msg/125536/350713/102589/#msg_350713

Regards
Michel
Re: String to a table [message #350754 is a reply to message #350673] Fri, 26 September 2008 02:53 Go to previous message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
I found a solution using collection objects.

The functions and techniques I used are covered on the following links:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions024.htm#i1271564
http://articles.techrepublic.com.com/5100-10878_11-5259821.html
http://www.orafaq.com/forum/m/347339/129340/#msg_347339

And this is a sample of what you could do

CREATE OR REPLACE 
TYPE string_table                                                                           
	as table of varchar2(4000)
/

CREATE OR REPLACE 
FUNCTION split_string (
	string IN varchar2,
    delimiter IN varchar2
) RETURN  string_table IS
	tab string_table;
	pre integer;
	post integer;
	step integer;
	i integer;
BEGIN
	pre := 1;
	step := length(delimiter);
	i := 0;
	tab := string_table();
	if string is null then 
		return tab;
	end if;
	loop
		i := i + 1;
		post := instr(string,delimiter,pre);
		tab.extend;
		if ( post = 0 ) then
			tab(i) := substr(string,pre);
			return tab;
		end if;
		tab(i) := substr(string,pre,post-pre);
		pre := post + step;
	end loop;
END;
/


And then
SQL> select idx,collect(column_value order by rid) as your_array
  2  from (
  3             select rid,column_value,
  4                     row_number() over (partition by rid order by null) as idx
  5             from (
  6                             select a.rid,b.column_value
  7                             from (
  8                                             select rownum rid,column_value
  9                                             from table(string_table('1,2,3','11,34,22','2,3,4'))
 10                                     ) a cross join table (split_string(a.column_value,',')) b
 11                     )
 12     )
 13  group by idx
 14  /

       IDX  YOUR_ARRAY
----------  -----------------------------------------------
         1  SYSTPV8eCiX7AHKXgRAAXpFGBgA==('1', '11', '2')
         2  SYSTPV8eCiX7AHKXgRAAXpFGBgA==('2', '34', '3')
         3  SYSTPV8eCiX7AHKXgRAAXpFGBgA==('3', '22', '4')



Bye Alessandro

[Updated on: Fri, 26 September 2008 03:18]

Report message to a moderator

Previous Topic: how can i connect a paradox database in a plsql block?
Next Topic: db2 link from oracle
Goto Forum:
  


Current Time: Sun Dec 04 16:34:35 CST 2016

Total time taken to generate the page: 0.06037 seconds