-1

I am trying following SQL :

SELECT REGEXP_SUBSTR ('00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:,00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:,00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:,00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:,00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:,00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:', '[^,]+', 1, LEVEL) FROM DUAL CONNECT BY REGEXP_SUBSTR ('00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:,00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:,00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:,00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:,00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:,00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:', '[^,]+', 1, LEVEL) IS NOT NULL;

This is giving ORA-01704: string literal too long.

I understand the reason that the string passed is more than 4000 characters, but I want to process these many records in a PL/SQL block. String is nothing but the comma-separated values passed from UI to oracle procedure.

e.g :

00102034334:15:11-JUN-13 ~ ord_no:order_line_no:Date (1 order can have many order lines , parent-child relationship)

When I am above SQL with into clause offcourse, in a PL/SQL table type variable.

It is throwing error for me.

How shall I go ahead? Is there any other way parameters can be passed from UI to Oracle in such case.

jaadooviewer
  • 368
  • 1
  • 10
Abhishek Kapoor
  • 25
  • 1
  • 4
  • How are the values being generated and passed from the UI? What is the UI? Depending on the technology being used, can they be passed as an array instead - then you wouldn't need to treat them as a single value and split them up like this? – Alex Poole Aug 06 '13 at 14:56
  • Thanks for the reply. UI is Dot Net, array is okay but the string we are having or the no. of comma separated values we have differ in every case. It gets generated based on no. of order lines a order is possessing. It will be good if any resolution at backend can be provided. – Abhishek Kapoor Aug 06 '13 at 15:24
  • I was thinking of [something like this](http://stackoverflow.com/questions/10217922/how-to-use-array-table-parameter-to-oracle-odp-net-10g-via-ado-net-c), though this isn't a platform I'm used to. You pass a variable-sized array rather than a fixed number of values or a single massive string. Or [this](http://weblogs.asp.net/ricardoperes/archive/2009/05/14/odp-net-associative-arrays.aspx). – Alex Poole Aug 06 '13 at 15:33

2 Answers2

1

PL/SQL strings can contain up to 32767 characters, if you want to process strings using PL/SQL, use varchar2(32767) in your procedure. If 32767 characters is still too short, just pass your string as a clob, CLOB's length is unlimited.

declare
  long_varchar varchar2( 32767 );
  v_clob clob;
begin
  long_varchar := q'@[00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:,00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:,00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:,00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:,00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:,00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:', '[^,]+', 1, LEVEL) FROM DUAL CONNECT BY REGEXP_SUBSTR ('00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:,00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:,00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:,00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:,00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:,00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:@';
  v_clob := long_varchar;
  long_varchar := REGEXP_SUBSTR ( long_varchar, '[^,]+');
  v_clob := REGEXP_SUBSTR ( v_clob, '[^,]+');
  DBMS_OUTPUT.PUT_LINE( long_varchar );
  DBMS_OUTPUT.PUT_LINE( v_clob );
end;
/

    ---- result ----
[00102034334:1:11-JUN-13:1
[00102034334:1:11-JUN-13:1


krokodilko
  • 35,300
  • 7
  • 55
  • 79
1

For very long string,store the data to be processed in a CLOB type variable and parse the data using REGEXP_SUBSTR by passing the CLOB type variable.

declare
v_clob clob;
begin
v_clob := '00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:,00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:,00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:,00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:,00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:,00102034334:1:11-JUN-13:1,00102034334:2:11-JUN-13:1,00102034334:3:11-JUN-13:1,00102034334:4:11-JUN-13:1,00102034334:5:11-JUN-13:,00102034334:6:11-JUN-13:,00102034334:7:11-JUN-13:,00102034334:8:11-JUN-13:,00102034334:9:11-JUN-13:,00102034334:10:11-JUN-13:,00102034334:11:11-JUN-13:,00102034334:12:11-JUN-13:,00102034334:13:11-JUN-13:,00102034334:14:11-JUN-13:,00102034334:15:11-JUN-13:,00102034334:16:11-JUN-13:,00102034334:17:11-JUN-13:,00102034334:18:11-JUN-13:,00102034334:19:11-JUN-13:,00102034334:20:11-JUN-13:,00102034334:21:11-JUN-13:,00102034334:22:11-JUN-13:,00102034334:23:11-JUN-13:,00102034334:24:11-JUN-13:,00102034334:25:11-JUN-13:,00102034334:26:11-JUN-13:,00102034334:27:11-JUN-13:,00102034334:28:11-JUN-13:,00102034334:29:11-JUN-13:,00102034334:30:11-JUN-13:,00102034334:31:11-JUN-13:,00102034334:32:11-JUN-13:,00102034334:33:11-JUN-13:,00102034334:34:11-JUN-13:,00102034334:35:11-JUN-13:';
select TO_CHAR (regexp_substr(v_clob,'[^,]+', 1, level)) from dual 
               connect by TO_CHAR (regexp_substr(v_clob, '[^,]+', 1, level)) is not null;                       

end;
/
Saumyajit
  • 678
  • 8
  • 10