Home » SQL & PL/SQL » SQL & PL/SQL » Cast to index by table
Cast to index by table [message #671867] Fri, 21 September 2018 01:11 Go to next message
purnima1
Messages: 76
Registered: June 2014
Member
Hi Experts ,
I have string which is storing number for e.g. '1,2,3'

I want to store this data in index by table. One way of doing this iterating trough string and picking each number one by one and putting it in index by table.
Could there be any query which could do this.Can we use cast
Re: Cast to index by table [message #671868 is a reply to message #671867] Fri, 21 September 2018 01:15 Go to previous messageGo to next message
John Watson
Messages: 8318
Registered: January 2010
Location: Global Village
Senior Member
Sorry, man, I don't understand this. What do you mean by "index by table"? If you show an example of the string, the index, the table, what you want to achieve, then perhaps someone can advise.

[Updated on: Fri, 21 September 2018 01:15]

Report message to a moderator

Re: Cast to index by table [message #671869 is a reply to message #671867] Fri, 21 September 2018 01:18 Go to previous messageGo to next message
purnima1
Messages: 76
Registered: June 2014
Member
I am trying below code but getting error

declare 
v varchar2(2000):='1,2,3';
type t is table of number index by binary_integer;
tab t;
begin 

select cast(v as t) from dual ;

if tab.count>0 then 

dbms_output.put_line ('data converted');
end if;


end ;
Re: Cast to index by table [message #671870 is a reply to message #671869] Fri, 21 September 2018 01:26 Go to previous messageGo to next message
_jum
Messages: 571
Registered: February 2008
Senior Member
You need to split your comma separated list. This is a FAQ and you'll find a lot of examples, e.g. here in Row Generator.

[Updated on: Fri, 21 September 2018 01:27]

Report message to a moderator

Re: Cast to index by table [message #672730 is a reply to message #671869] Thu, 25 October 2018 13:04 Go to previous message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
purnima1 wrote on Thu, 20 September 2018 23:18
I am trying below code but getting error

declare 
v varchar2(2000):='1,2,3';
type t is table of number index by binary_integer;
tab t;
begin 

select cast(v as t) from dual ;

if tab.count>0 then 

dbms_output.put_line ('data converted');
end if;


end ;
SCOTT@orcl_12.1.0.2.0> declare
  2    v	 varchar2 (2000) := '1,2,3';
  3    type t is table of number index by binary_integer;
  4    tab	 t;
  5  begin
  6    select regexp_substr (v, '[^,]*', rownum)
  7    bulk collect
  8    into   tab
  9    from   dual
 10    connect by level <= regexp_count (v, ',') + 1;
 11    if tab.count > 0 then
 12  	 dbms_output.put_line (tab.count || ' rows of data converted');
 13    end if;
 14  end;
 15  /
3 rows of data converted

PL/SQL procedure successfully completed.

Previous Topic: query performance
Next Topic: execute immediate (merged)
Goto Forum:
  


Current Time: Fri Aug 07 23:19:50 CDT 2020