2

I am using oracle verion 10. There is stored procedure in PL/SQL using varchar2 variable. The code is constantly appending the varchar2 variable. When the varchar2 variable size exceeds 32767, it cannot append any more value. Now I want to change the data type to long or clob(in order to accomodate more characters), it does not work. How to modify the code here to have the same appending functionality with clob or long?

sample appending x:= x || 'mydata';

user215005
  • 175
  • 1
  • 3
  • 10
  • 1
    `long` has been deprecated for a long (no pun intended) time, and has restrictions. Stick to `clob`. – Alex Poole Jun 27 '13 at 22:54
  • @AlexPoole, I tested by changing the data type to clob, it did not work. – user215005 Jun 27 '13 at 22:56
  • I just meant you should be looking for `clob` solutions and shouldn't be considering `long` at all. I didn't mean you could concatenate like you can with `varchar2`. You might want [`dbms_lob.writeappend()`](http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_lob.htm#i999705), but I'm not currently able to post more than a link-only answer... – Alex Poole Jun 27 '13 at 23:01

1 Answers1

2

The long datatype is deprecated; if you can you should seriously consider migrating your long column to a clob.

If you were working with a clob you could append past the 32k varchar2 limit like this:

declare
  l_clob clob;
begin
  dbms_lob.createtemporary(l_clob, true);
  dbms_lob.open(l_clob, dbms_lob.lob_readwrite);
  dbms_lob.writeappend(l_clob, 4, '1234');
  for i in 1..10000 loop
    dbms_lob.writeappend(l_clob, 5, '.5678');
  end loop;
  dbms_output.put_line('CLOB length: ' || length(l_clob));
  dbms_lob.close(l_clob);
  dbms_lob.freetemporary(l_clob);    end;
/

CLOB length: 50004

You can append to a long with the concatenate operator ||, but as you've seen already, only up to 32k. There is no easy way to handle long values above that within PL/SQL. You might be able to do soemthing with dbms_sql but it's really not going to be worth the effort if there is any possiblility of switching the table column to a clob.

If you want to pass the clob back to the caller, and it's a temporary clob, it will have to be defined by the caller and be passed it after it's created:

create or replace procedure proc1 as
  l_clob clob;
begin
  dbms_lob.createtemporary(l_clob, true);

  proc2(l_clob);
  dbms_output.put_line('proc1 CLOB length: ' || length(l_clob));

  dbms_lob.freetemporary(l_clob);
end;
/

create or replace procedure proc2(p_clob in out clob) as
begin
  dbms_lob.open(p_clob, dbms_lob.lob_readwrite);
  dbms_lob.writeappend(p_clob, 5, '12345');
  for i in 1..9999 loop
    dbms_lob.writeappend(p_clob, 5, '.56789');
  end loop;
  dbms_output.put_line('proc2 CLOB length: ' || length(p_clob));
  dbms_lob.close(p_clob);
end;
/

exec procs;

proc2 CLOB length: 50000
proc1 CLOB length: 50000

Otherwise the object won't exist as far as the caller is concerned.

If the clob exists - selected from a table, say, so you don't need the createtemporary call - then you can just assign it to an out parameter, but I don't think that's the case for what you've described.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Thanks @Alex, much helpful, I just want to know how to return the value from this clob. – user215005 Jun 28 '13 at 13:43
  • @user215005 - return to where, and how? Are you trying to insert/update a `long` column from the proc, or passing the value back to a client which will do the insert/update? – Alex Poole Jun 28 '13 at 13:46
  • This a SP called from another SP with an out param. So the value of the clob var need to be return to the called SP – user215005 Jun 28 '13 at 13:53
  • @user215005 - is the out parm a `clob` or a `long`, or even a `varchar2`? It would need to be a `clob` to work, with the `createtemporary` etc. in the caller. But does it still have to become a `long` at some point, which will be somewhere between difficult and impossible in PL/SQL, or will it now be stored as a `clob` too? It would be useful to show the whole process in your question, with the data type(s), and what the client/language is at each step. CHanging the column to a `clob` is the only viable strategy really. – Alex Poole Jun 28 '13 at 14:15
  • I agree with you @Alex, the out param is currently long, I am changing that too clob. Just want to know how to assign the temp clob value to this out param. – user215005 Jun 28 '13 at 14:23
  • @user215005 - OK, that's good. I've updated the answer with an example of a proc calling another one to get have the `clob` populated. If you want both procs to append to the `clob` then you'll need to open and close it in the outermost proc too, as well as creating/freeing it. – Alex Poole Jun 28 '13 at 14:46
  • I don't believe there is in PL/SQL, no. You can do it from an external program (Perl, Java, OCI) by extracting the `clob` and inserting as a `long`, but the PL/SQL engine doesn't seem to be able to (at least if the value is > 32k, which is where we came in...) – Alex Poole Jun 28 '13 at 16:54