0

I'm trying to pass a comma-separated value (which are actually the values of selected list items in a listbox separated by commas) from C#.NET to oracle stored procedure. The Stored procedure parameter is a CLOB. In the WHERE condition of the query, this CLOB value, which is being stored in VARCHAR2(30000) variable, is being used since the CLOB parameter can't be directly used in a WHERE condition. The below query is used for splitting the comma separated values into separate record each. 

SELECT COL_NAME FROM TBL WHERE
CUSTOMER_ID IN (select REGEXP_SUBSTR(V_SELECTED_CUSTOMER,'[^,]+', 1, LEVEL) FROM DUAL
CONNECT BY REGEXP_SUBSTR(V_SELECTED_CUSTOMER, '[^,]+', 1, LEVEL) is NOT NULL);

Now the issue which I'm facing is, when the user selects all the ListBox values, the input parameter is amounting to a length of around 23,000. In this scenario, the Stored Procedure is throwing the below error:

ORA-01460: unimplemented or unreasonable conversion requested

The issue could be due to the REGEXP_SUBSTR function which usually takes STRING upto 4000 characters. The V_SELECTED_CUSTOMER variable is a VARCHAR2(30000). Is there a way to make this work for CLOB values as well. Or is there any other alternative by which this can be achieved?

Megha
  • 1
  • 1
  • build the query in C# using a temp table? – SQLMason May 31 '17 at 09:41
  • Same solution as in the linked question - you just do not need the `BLOB_TO_CLOB()` step. – MT0 May 31 '17 at 09:49
  • Alternatively, [pass the values as a collection](https://stackoverflow.com/a/34699771/1509264) rather than as a delimited string. – MT0 May 31 '17 at 10:06
  • @MT0 : I've gone through the linked question page. Do you mean to suggest that the split_clob function would resolve the issue? Moreover, is there anything wrong in the below statements? `CREATE OR REPLACE TYPE stringlist IS TABLE OF VARCHAR2(4000); CREATE OR REPLACE TYPE cloblist IS TABLE OF CLOB;` When I try executing these I get **ORA-00911: invalid character** error. Same error while trying to create the split_clob function obviously. Could you please help? – Megha May 31 '17 at 11:46
  • @Megha I've updated that answer - it just needs the statements to either be run separately or have a trailing `/` on a new line if you are running them as a block. And, yes, the `split_clob` function that is defined there will generate a collection of the delimited values that you can use in a table collection expression to generate multiple rows. – MT0 May 31 '17 at 12:06
  • @MT0 : Thanks for the response. For some reason I'm still facing the same issue ( **ORA-00911: invalid character **) on running any of those queries or the `split_clob` function. What could possibly be wrong here? – Megha May 31 '17 at 16:08

0 Answers0