Thanks for the question, Peter.

Asked: August 19, 2016 - 4:39 pm UTC

Last updated: August 24, 2016 - 12:41 am UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Viewed 10K+ times! This question is

You Asked

I have written an SP, based on the posts I have read on your site, which is designed to take the text (originally from script files) and update an existing record containing a CLOB field. This works fine with the text from smaller files, but when I try with the text from larger file (181KB) I get the following error: "ORA-01460: unimplemented or unreasonable conversion requested". I am calling the SP via .net and using a OracleType.Clob parameter.

Here is my table structure and stored procedure(both simplified as much as possible):

CREATE TABLE "YY_DATA_TYPE_SCRIPT"
(
"TASK_DATA_UID" NUMBER(9,0) NOT NULL ENABLE,
"SCRIPT_TEXT" CLOB NOT NULL ENABLE
);

create or replace
PROCEDURE yy_data_type_script_Update (p_task_data_uid NUMBER, p_script_text in clob)
AS
l_clob clob;
begin
Select script_text
into l_clob
from yy_data_type_script
WHERE
task_data_uid = p_task_data_uid for update;
dbms_lob.writeappend( l_clob, length(p_script_text), p_script_text);
end;

Thanks for your time.

and Connor said...

dbms_lob.writeappend is for varchar2 and/or raw data. To append a clob to a clob, use "append"

SQL> CREATE TABLE "YY_DATA_TYPE_SCRIPT"
  2  (
  3  "TASK_DATA_UID" NUMBER(9,0) NOT NULL ENABLE,
  4  "SCRIPT_TEXT" CLOB NOT NULL ENABLE
  5  );

Table created.

SQL>
SQL>
SQL>
SQL> create or replace
  2  PROCEDURE yy_data_type_script_Update (p_task_data_uid NUMBER, p_script_text in clob)
  3  AS
  4  l_clob clob;
  5  begin
  6  Select script_text
  7  into l_clob
  8  from yy_data_type_script
  9  WHERE
 10  task_data_uid = p_task_data_uid for update;
 11  dbms_lob.writeappend( l_clob, length(p_script_text), p_script_text);
 12  end;
 13  /

Procedure created.

SQL>
SQL> declare
  2    c clob;
  3  begin
  4    c := rpad('x',32000,'x');
  5    c := c || c || c || c;
  6    insert into YY_DATA_TYPE_SCRIPT
  7    values (1,c);
  8    commit;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL>
SQL> select dbms_lob.getlength(SCRIPT_TEXT) from YY_DATA_TYPE_SCRIPT;

DBMS_LOB.GETLENGTH(SCRIPT_TEXT)
-------------------------------
                         128000

1 row selected.

SQL> declare
  2    c clob;
  3  begin
  4    c := rpad('x',32000,'x');
  5    c := c || c || c || c;
  6    yy_data_type_script_Update(1,c);
  7    commit;
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "MCDONAC.YY_DATA_TYPE_SCRIPT_UPDATE", line 10
ORA-06512: at line 6

SQL> create or replace
  2  PROCEDURE yy_data_type_script_Update (p_task_data_uid NUMBER, p_script_text in clob)
  3  AS
  4  l_clob clob;
  5  begin
  6  Select script_text
  7  into l_clob
  8  from yy_data_type_script
  9  WHERE
 10  task_data_uid = p_task_data_uid for update;
 11  dbms_lob.append( l_clob,  p_script_text);
 12  end;
 13  /

Procedure created.

SQL>
SQL> declare
  2    c clob;
  3  begin
  4    c := rpad('x',32000,'x');
  5    c := c || c || c || c;
  6    yy_data_type_script_Update(1,c);
  7    commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Peter Brown, August 23, 2016 - 12:10 pm UTC

Thank you for taking the time to answer this question.

Sorry, I was not clear. I wanted to replace the existing CLOB value in the field with the new value passed into the stored procedure. Let me know if I should submit this as a separate question.

August 24, 2016 - 12:41 am UTC

In that case, just

update my_table
set clob_col = p_new_col
where ...

should do it.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

CLOB/BLOB/etc

Recieving error ORA-01460: unimplemented or unreasonable conversion requested when running sp to update a clob field.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

CLOB/BLOB/etc