Home » SQL & PL/SQL » SQL & PL/SQL » dbms_utility.comma_to_table (merged)
dbms_utility.comma_to_table (merged) [message #402638] Mon, 11 May 2009 13:24 Go to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Good Afternoon.

Would you please to help me with this? I have a script:

DECLARE
      v_table   dbms_utility.uncl_array;
      v_len   number;
BEGIN
      dbms_utility.comma_to_table('"1","2","3"', v_len, v_table);     
      for i in 1..v_len
      loop
       dbms_output.put_line(v_table(i));
      end loop;
END;


my output is:
"1"
"2"
"3"

Would you please to show me how to get rid of the double quote " " in my output? I would appreciate that.

Thanks much
LN
Re: dbms_utility.comma_to_table (merged) [message #402644 is a reply to message #402638] Mon, 11 May 2009 14:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Either:
- don't put it in the input string
- remove it from the input table using REPLACE
- remove it from the output using TRIM

Regards
Michel
Re: dbms_utility.comma_to_table (merged) [message #402655 is a reply to message #402644] Mon, 11 May 2009 21:16 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Michel,

Thank you very much. I will try tomorrow.

LN
Re: dbms_utility.comma_to_table (merged) [message #402783 is a reply to message #402644] Tue, 12 May 2009 08:05 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yiou have to wrap your values in double quotes if you want to use comma_to_table - otherwise it tries to validate each item as a valid oracle object name.

In addition to Michel's suggestions of REPLACE and TRIM, you could use SUBSTR, or REGEXP_REPLACE, or, if you really want to go over the top, DBMS_UTILITY.CANONICALIZE
Previous Topic: Custom Sort
Next Topic: Update with Sequence using Order By
Goto Forum:
  


Current Time: Sat Nov 09 18:22:24 CST 2024