0

I am using oracle 11g. Whenever I encountered strings larger than varchar2 size limit, In sql server I use to split the data into multiple variables as below and then join them while execution. However Oracle seems to be expecting 32K combined size before execution. I am getting "ORA-20000: ORU-10028: line length overflow, limit of 32767 bytes per line" error.

I am using these variables in an oralce script (not stored procs). Last 2 statements are throwing the above error and individually I am able to show the value. Thanks in advance.

DECLARE

sViewQuery varchar2(32000);
sViewSelectQuery varchar2(32000);

BEGIN

    ---Assign values of 32,000 letter string (dynamic query)
    sViewSelectQuery:='32K string...';
    sViewQuery:='32K string..';

    DBMS_OUTPUT.PUT_LINE(sViewQuery||sViewSelectQuery);

    EXECUTE IMMEDIATE sViewQuery||sViewSelectQuery;

END;
Anjan Biswas
  • 7,746
  • 5
  • 47
  • 77
Sanjay Kattimani
  • 899
  • 1
  • 8
  • 7
  • Even with a `clob` you can't directly assign more than 32k, you'd need to build up the `clob` value by appending all the parts into a single variable. You can't concatenate (since that would produce a `varchar2`, and would be too big), or use `dbms_output` on the whole thing. That's what throwing the current error, I think, but the `execute immediate` would if it got that far. – Alex Poole Feb 10 '14 at 23:03
  • use a clob. See [here](http://stackoverflow.com/questions/10803095/not-able-to-run-large-dynamic-select-query-in-stored-procedure/10803440#10803440) for an example – tbone Feb 11 '14 at 02:11
  • 1
    What kind of view or query needs more than 32K to express? – Jeffrey Kemp Feb 11 '14 at 03:36
  • @JeffreyKemp - I have about 450 columns in a table, creating a dynamic view that use a functions like NVL and pics data from 2 tables, did math and it was a little over 32K chars. – Sanjay Kattimani Feb 11 '14 at 14:57
  • @AlexPoole - Both dbms_output and execute immediate are throwing errors, i have tried them individually. Whenever i try to join 2 varchars with ||, Oracle is checking if the resultant string is exceeding 32K chars. I haven't tried CLOB yet, I guess when i convert the data back to varchar2 I might face the same issue. will try it in a few mins. Thanks. – Sanjay Kattimani Feb 11 '14 at 15:55
  • @SanjayKattimani - why would you convert it back to `varchar2` at all once you'd built the `clob` up? [You can pass a `clob` to `execute immediate`](http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/executeimmediate_statement.htm#LNPLS1201). If you wanted to display it then you'd need to split it into chunks though, yes. Still kinda curious why you'd want a view like that, never mind a dynamically-created one. – Alex Poole Feb 11 '14 at 16:04
  • 450 columns screams "data model problem" to me. – Jeffrey Kemp Feb 11 '14 at 21:54
  • @JeffreyKemp I had the same feeling when I saw that table first time. However it is optimized and denormalized for performance reasons. – Sanjay Kattimani Feb 12 '14 at 19:05

2 Answers2

3

You can use DBMS_SQL Package for this:

DECLARE
stmt DBMS_SQL.VARCHAR2A;
c number;
res number;
BEGIN
stmt(1) := 'create view view_a (';
stmt(2) := 'col_a, ';
stmt(3) := 'col_b, ';
stmt(4) := 'col_c) as '
stmt(5) := 'select ';
stmt(6) := 'col_bb, ';
stmt(7) := 'col_cc + col_ee + DECODE(...), ';
stmt(8) := 'col_dd) ';
stmt(9) := 'from table_b ';
stmt(10) := 'where ... '; 
-- each element can have up to 32K characters, number of elements is (almost) unlimited
c := DBMS_SQL.open_cursor;
DBMS_SQL.parse(c, stmt, 1,10, TRUE, DBMS_SQL.NATIVE);
res := DBMS_SQL.execute(c);
DBMS_SQL.close_cursor(c);
  END;
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
0

You should use a CLOB, Character Large OBject. You can handle 32K+ string length, since CLOB can contain up to 4GB of data.

For more info: http://docs.oracle.com/javadb/10.3.3.0/ref/rrefclob.html

Elfentech
  • 747
  • 5
  • 10
  • 1
    The [SQL Language Reference](http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#sthref204) might be a better place to link to than the Derby documentation... – Alex Poole Feb 10 '14 at 23:08
  • These links didn't help much. Exploring more on clob. Thanks. – Sanjay Kattimani Feb 11 '14 at 16:40