Home » SQL & PL/SQL » SQL & PL/SQL » problem insert clob with more than 4000 bytes
problem insert clob with more than 4000 bytes [message #291529] Fri, 04 January 2008 13:59 Go to next message
fabiank
Messages: 10
Registered: January 2008
Junior Member
Hi Folks !

I'm having problems with strings that exceed 4000 bytes. I have found several workarounds that work fine for procedures, however, I need to pass a long string to a function which in turn will call a procedure.

The call will look something like this:

select * from table(my_function('my-very-long-string....'));


This function will in turn call a procedure like

DECLARE
o_output1 NUMBER;
o_output2 VARCHAR2(40);
BEGIN
my_procedure(i_my_very_long_string, o_output1, o_output2);
END;


I keep getting the error "ORA-01704: string literal too long"

I have followed the ideas provided in the equally named thread http://www.orafaq.com/forum/t/48485/0/
but although both my function and the procedure take the very-long-string into VARCHAR variables, it won't work.

Why do I need this function/procedure thing? To cut a long story short: we need the return values (o_output1, o_output2) to be returned in form of a table and not in form of variables in order to read the output through a JDBC connection into an XML driven request broker. This request broker is, by the way, the place where the actual PL/SQL call to the function gets created.

Updating to a newer version of Oracle isn't an option, unfortunately.

Any ideas?

Thanks & Regards,

Fabian.

[Updated on: Fri, 04 January 2008 14:01]

Report message to a moderator

Re: problem insert clob with more than 4000 bytes [message #291532 is a reply to message #291529] Fri, 04 January 2008 14:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't really understand your issue but anyway a VARCHAR2 cannot exceeds 4000 bytes.
If you want more you have to use CLOB.

Regards
Michel
Re: problem insert clob with more than 4000 bytes [message #291534 is a reply to message #291532] Fri, 04 January 2008 14:12 Go to previous messageGo to next message
fabiank
Messages: 10
Registered: January 2008
Junior Member
well, true; it wouldn't be possible to store more than 4000 characters in a VARCHAR2 table field.

However, when you create a procedure and have a VARCHAR2 variable as input parameter you can very well pass more than 4000 characters to it (see the thread I have linked in my original post). I have verified this on my system.

The question now is, how can I get the same trick to work when having the whole thing in a function, respectively when casting the output of the function to a table in an in-line statement.

Thanks for any hints !

Regards, Fabian.
Re: problem insert clob with more than 4000 bytes [message #291542 is a reply to message #291534] Fri, 04 January 2008 14:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

when you create a procedure and have a VARCHAR2 variable as input parameter you can very well pass more than 4000 characters to it

True, up to 32K but you can't select on it which limits the VARCHAR2 to 4000 BYTES (not characters).

To get larger, you have to return CLOB.

Regards
Michel

Re: problem insert clob with more than 4000 bytes [message #291543 is a reply to message #291534] Fri, 04 January 2008 15:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For instance:
create or replace function f return clob
is
  s clob := empty_clob();
begin
  DBMS_LOB.CREATETEMPORARY (s, TRUE);
  for i in 1..10 loop
    s := s || chr(10) || rpad(chr(ascii('A')+i-1),1000,chr(ascii('A')+i-1));
  end loop;
  return s;
end;
/
select f from dual;

Regards
Michel
Re: problem insert clob with more than 4000 bytes [message #291624 is a reply to message #291543] Sat, 05 January 2008 09:20 Go to previous messageGo to next message
fabiank
Messages: 10
Registered: January 2008
Junior Member
I'm not trying to return a string that's longer than 4000 bytes but the string >4000 bytes needs to be passed to the function as a parameter (the function calls a procedure that stores the long string in a CLOB table field)

here is the function and procedure definition, maybe this makes it clearer:

FUNCTION my_func (
  i_long_string   VARCHAR2
) RETURN my_tableset PIPELINED
IS
  p_error_code    NUMBER;
  p_error_desc    VARCHAR2(1000);
  line1           my_return_line := my_return_line(NULL,NULL);

BEGIN
  my_proc(i_long_string, p_error_code, p_error_desc);
  
  line1.column1 := p_error_code;
  line1.column2 := p_error_desc;
 
  PIPE ROW(line1);
  RETURN;
END my_func;


PROCEDURE my_proc (
  i_long_string  VARCHAR2,
  o_error_code   NUMBER,
  o_error_desc   VARCHAR2
)
IS
 /* code to store i_long_string in a CLOB table field */
END my_proc;


The return of my_func is then a table with a single data row like
 -----------------------------------
| Column1  | Column2                |
|----------|------------------------|
| 0        | Success                |
 -----------------------------------
Re: problem insert clob with more than 4000 bytes [message #291629 is a reply to message #291624] Sat, 05 January 2008 09:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is what I said, don't use VARCHAR2, use CLOB.
My function was just an example, do in input, in ouput it is the same, use CLOB.

Regards
Michel
Re: problem insert clob with more than 4000 bytes [message #291638 is a reply to message #291629] Sat, 05 January 2008 10:50 Go to previous messageGo to next message
fabiank
Messages: 10
Registered: January 2008
Junior Member
CLOB doesn't work either - that had been my first attempt before I followed the example from the post I linked in my initial post.. same error message.
Re: problem insert clob with more than 4000 bytes [message #291642 is a reply to message #291638] Sat, 05 January 2008 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you say so... as you don't post the code all what I can say is... allright this can't be done you are on your own.

Regards
Michel
Re: problem insert clob with more than 4000 bytes [message #291646 is a reply to message #291642] Sat, 05 January 2008 11:06 Go to previous messageGo to next message
fabiank
Messages: 10
Registered: January 2008
Junior Member
The code is equal to my post from 9:20, except that the i_long_string is a CLOB and not a VARCHAR2 in both the function and the procedure.

Thanks & Regards,

Fabian.

[Updated on: Sat, 05 January 2008 11:08]

Report message to a moderator

Re: problem insert clob with more than 4000 bytes [message #291648 is a reply to message #291646] Sat, 05 January 2008 11:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you don't show you use it, I still don't see why this does not work.
I repeat it should work (if you do it correctly).

Regards
Michel
Re: problem insert clob with more than 4000 bytes [message #291662 is a reply to message #291646] Sat, 05 January 2008 14:53 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> CREATE OR REPLACE TYPE my_return_line AS OBJECT
  2    (column1 	   NUMBER,
  3  	column2 	   VARCHAR2(1000));
  4  /

Type created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE TYPE my_tableset AS TABLE OF my_return_line;
  2  /

Type created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE my_proc
  2    (i_long_string  IN  CLOB,
  3  	o_error_code   OUT NUMBER,
  4  	o_error_desc   OUT VARCHAR2)
  5  IS
  6    v_dummy		   VARCHAR2(1);
  7  BEGIN
  8    -- intentionally cause no_data_found exception for testing:
  9    SELECT DUMMY INTO v_dummy FROM DUAL WHERE DUMMY <> 'X';
 10    o_error_code := 0;
 11    o_error_desc := 'SUCCESS';
 12  EXCEPTION
 13    WHEN OTHERS THEN
 14  	 o_error_code := SQLCODE();
 15  	 o_error_desc := SQLERRM(SQLCODE);
 16  END my_proc;
 17  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION my_func
  2    (i_long_string  IN CLOB)
  3    RETURN my_tableset PIPELINED
  4  IS
  5    p_error_code	   NUMBER;
  6    p_error_desc	   VARCHAR2(1000);
  7    line1		   my_return_line := my_return_line(NULL,NULL);
  8  BEGIN
  9    my_proc (i_long_string, p_error_code, p_error_desc);
 10    line1.column1 := p_error_code;
 11    line1.column2 := p_error_desc;
 12    PIPE ROW(line1);
 13    RETURN;
 14  END my_func;
 15  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> VARIABLE my_very_long_string CLOB
SCOTT@orcl_11g> EXEC :my_very_long_string := RPAD ('*', 32000, '*')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT LENGTH (:my_very_long_string) FROM DUAL
  2  /

LENGTH(:MY_VERY_LONG_STRING)
----------------------------
                       32000

SCOTT@orcl_11g> select * from table (my_func (:my_very_long_string))
  2  /

   COLUMN1 COLUMN2
---------- ------------------------------------------------------------
       100 ORA-01403: no data found

SCOTT@orcl_11g>  

[Updated on: Sat, 05 January 2008 14:56]

Report message to a moderator

Re: problem insert clob with more than 4000 bytes [message #291990 is a reply to message #291662] Mon, 07 January 2008 06:38 Go to previous messageGo to next message
fabiank
Messages: 10
Registered: January 2008
Junior Member
Hi Barbara,

thanks a lot for your reply !

I can successfully reproduce your example when usting the RPAD function to build the string. However, when trying to pass such a long string directly I still get errors. Now, however:

ORA-01756: quoted string not properly terminated

VARIABLE my_very_long_string CLOB;
EXEC :my_very_long_string := '424D0E08000000 [...] FFFFFFFFF' ;
ERROR:
ORA-01756: quoted string not properly terminated


Any idea?

Thanks & Regards,

Fabian.

[Edit: remove very long string]

[Updated on: Mon, 07 January 2008 12:47] by Moderator

Report message to a moderator

Re: problem insert clob with more than 4000 bytes [message #291994 is a reply to message #291990] Mon, 07 January 2008 06:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL*PLus input limit.
Cut your input data in several strings and concatenate them.

Regards
Michel
Re: problem insert clob with more than 4000 bytes [message #292051 is a reply to message #291994] Mon, 07 January 2008 10:28 Go to previous messageGo to next message
fabiank
Messages: 10
Registered: January 2008
Junior Member
Unfortunately that doesn't help either... or am I misunderstanding your proposal?

VARIABLE my_very_long_string CLOB;
EXEC :my_very_long_string := '424D0E080000000000[...]3F030FFFFFFFF' || [...]FFFFFFFF' ;
ERROR:
ORA-01756: quoted string not properly terminated


Thanks & Regards,

Fabian.

[Edit: remove very long string]

[Updated on: Mon, 07 January 2008 12:46] by Moderator

Report message to a moderator

Re: problem insert clob with more than 4000 bytes [message #292062 is a reply to message #292051] Mon, 07 January 2008 10:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, I meant in several lines:
begin
  :my_very_long_string := 'first part';
  :my_very_long_string := :my_very_long_string || 'second part';
  ...
end;
/

Each line must be less than 2500 characters.

Regards
Michel
Re: problem insert clob with more than 4000 bytes [message #292072 is a reply to message #292051] Mon, 07 January 2008 12:38 Go to previous messageGo to next message
fabiank
Messages: 10
Registered: January 2008
Junior Member
Sorry guys ... Barbara's code worked once but now I'm having problems with it again.

SQL> CREATE OR REPLACE TYPE my_return_line AS OBJECT
  2        (column1     NUMBER,
  3       column2     VARCHAR2(1000));
  4  /

Type created.

SQL>  
SQL>  
SQL> SHOW ERRORS
No errors.
SQL> 
SQL> 
SQL> CREATE OR REPLACE TYPE my_tableset AS TABLE OF my_return_line;
  2  /

Type created.

SQL>    
SQL> SHOW ERRORS    
No errors.
SQL> 
SQL> CREATE OR REPLACE PROCEDURE my_proc
  2        (i_long_string  IN  CLOB,
  3       o_error_code   OUT NUMBER,
  4       o_error_desc   OUT VARCHAR2)
  5      IS
  6        v_dummy     VARCHAR2(1);
  7      BEGIN
  8        -- intentionally cause no_data_found exception for testing:
  9        SELECT DUMMY INTO v_dummy FROM DUAL WHERE DUMMY <> 'X';
 10       o_error_code := 0;
 11       o_error_desc := 'SUCCESS';
 12     EXCEPTION
 13       WHEN OTHERS THEN
 14       o_error_code := SQLCODE();
 15       o_error_desc := SQLERRM(SQLCODE);
 16     END xxusf_fk1_my_proc;
 17  /

Procedure created.

SQL>    SHOW ERRORS
No errors.
SQL> 
SQL>    CREATE OR REPLACE FUNCTION my_func
  2        (i_long_string  IN CLOB)
  3        RETURN my_tableset PIPELINED
  4      IS
  5        p_error_code    NUMBER;
  6        p_error_desc    VARCHAR2(1000);
  7        line1     my_return_line := xxusf_fk1_my_return_line(NULL,NULL);
  8      BEGIN
  9        my_proc (i_long_string, p_error_code, p_error_desc);
 10       line1.column1 := p_error_code;
 11       line1.column2 := p_error_desc;
 12       PIPE ROW(line1);
 13       RETURN;
 14     END my_func;
 15  /

Function created.

SQL> 
SQL>    
SQL>    SHOW ERRORS
No errors.
SQL>    
SQL>    
SQL>    VARIABLE my_very_long_string CLOB
SQL>    EXEC :my_very_long_string := RPAD ('*', 32000, '*')

PL/SQL procedure successfully completed.

SQL>    SELECT LENGTH (:my_very_long_string) FROM DUAL
  2  / 
   SELECT LENGTH (:my_very_long_string) FROM DUAL
                                             *
ERROR at line 1:
ORA-22275: invalid LOB locator specified


I'm getting a huge know in my brain. Any ideas?

Thanks & Regards,
Fabian.
Re: problem insert clob with more than 4000 bytes [message #292073 is a reply to message #292051] Mon, 07 January 2008 12:38 Go to previous messageGo to next message
fabiank
Messages: 10
Registered: January 2008
Junior Member
Sorry guys ... Barbara's code worked once but now I'm having problems with it again.

SQL> CREATE OR REPLACE TYPE my_return_line AS OBJECT
  2        (column1     NUMBER,
  3       column2     VARCHAR2(1000));
  4  /

Type created.

SQL>  
SQL>  
SQL> SHOW ERRORS
No errors.
SQL> 
SQL> 
SQL> CREATE OR REPLACE TYPE my_tableset AS TABLE OF my_return_line;
  2  /

Type created.

SQL>    
SQL> SHOW ERRORS    
No errors.
SQL> 
SQL> CREATE OR REPLACE PROCEDURE my_proc
  2        (i_long_string  IN  CLOB,
  3       o_error_code   OUT NUMBER,
  4       o_error_desc   OUT VARCHAR2)
  5      IS
  6        v_dummy     VARCHAR2(1);
  7      BEGIN
  8        -- intentionally cause no_data_found exception for testing:
  9        SELECT DUMMY INTO v_dummy FROM DUAL WHERE DUMMY <> 'X';
 10       o_error_code := 0;
 11       o_error_desc := 'SUCCESS';
 12     EXCEPTION
 13       WHEN OTHERS THEN
 14       o_error_code := SQLCODE();
 15       o_error_desc := SQLERRM(SQLCODE);
 16     END my_proc;
 17  /

Procedure created.

SQL>    SHOW ERRORS
No errors.
SQL> 
SQL>    CREATE OR REPLACE FUNCTION my_func
  2        (i_long_string  IN CLOB)
  3        RETURN my_tableset PIPELINED
  4      IS
  5        p_error_code    NUMBER;
  6        p_error_desc    VARCHAR2(1000);
  7        line1     my_return_line := my_return_line(NULL,NULL);
  8      BEGIN
  9        my_proc (i_long_string, p_error_code, p_error_desc);
 10       line1.column1 := p_error_code;
 11       line1.column2 := p_error_desc;
 12       PIPE ROW(line1);
 13       RETURN;
 14     END my_func;
 15  /

Function created.

SQL> 
SQL>    
SQL>    SHOW ERRORS
No errors.
SQL>    
SQL>    
SQL>    VARIABLE my_very_long_string CLOB
SQL>    EXEC :my_very_long_string := RPAD ('*', 32000, '*')

PL/SQL procedure successfully completed.

SQL>    SELECT LENGTH (:my_very_long_string) FROM DUAL
  2  / 
   SELECT LENGTH (:my_very_long_string) FROM DUAL
                                             *
ERROR at line 1:
ORA-22275: invalid LOB locator specified


I'm getting a huge knot in my brain. Any ideas?

Thanks & Regards,
Fabian.

[Updated on: Mon, 07 January 2008 14:31]

Report message to a moderator

Re: problem insert clob with more than 4000 bytes [message #292074 is a reply to message #292073] Mon, 07 January 2008 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your Oracle version (at least 3 decimals)?

Regards
Michel
Re: problem insert clob with more than 4000 bytes [message #292075 is a reply to message #292073] Mon, 07 January 2008 12:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course, what you posted is not what you executed in spite of you're trying to make us think.
This is obvious as what you posted don't compile.

Regards
Michel

[Updated on: Mon, 07 January 2008 12:59]

Report message to a moderator

Re: problem insert clob with more than 4000 bytes [message #292091 is a reply to message #292075] Mon, 07 January 2008 14:24 Go to previous messageGo to next message
fabiank
Messages: 10
Registered: January 2008
Junior Member
My version is 9.2.0.7.0.

I aplogize - I had to add a prefix to the type, function and procedure names and forgot to take it out in 2 places in the code I posted. My bad ...

Thanks & Regards

[Updated on: Mon, 07 January 2008 14:31]

Report message to a moderator

Re: problem insert clob with more than 4000 bytes [message #292092 is a reply to message #292091] Mon, 07 January 2008 14:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It works in 10.2.0.3
SQL> VARIABLE my_very_long_string CLOB
SQL> EXEC :my_very_long_string := RPAD ('*', 32000, '*')

PL/SQL procedure successfully completed.

SQL> SELECT LENGTH (:my_very_long_string) FROM DUAL
  2  /
LENGTH(:MY_VERY_LONG_STRING)
----------------------------
                       32000

1 row selected.

SQL> @v

Version Oracle : 10.2.0.3.0

But not in 9.2.0.8:
SQL> VARIABLE my_very_long_string CLOB
SQL> EXEC :my_very_long_string := RPAD ('*', 32000, '*')

PL/SQL procedure successfully completed.

SQL> SELECT LENGTH (:my_very_long_string) FROM DUAL
  2  /
SELECT LENGTH (:my_very_long_string) FROM DUAL
                                          *
ERROR at line 1:
ORA-22275: invalid LOB locator specified


SQL> @v

Version Oracle : 9.2.0.8.0

It seems to be a 9.2 SQL*Plus error as if I use a 10.2 SQL*Plus against a 9.2 database it works:
SQL> VARIABLE my_very_long_string CLOB
SQL> EXEC :my_very_long_string := RPAD ('*', 32000, '*')

PL/SQL procedure successfully completed.

SQL> SELECT LENGTH (:my_very_long_string) FROM DUAL
  2  /
LENGTH(:MY_VERY_LONG_STRING)
----------------------------
                       32000

1 row selected.

SQL> @v

Version Oracle : 9.2.0.8.0

SQL> def _SQLPLUS_RELEASE
DEFINE _SQLPLUS_RELEASE = "1002000300" (CHAR)

So you have to make your test in another way, only in PL/SQL for instance.

Regards
Michel

[Updated on: Mon, 07 January 2008 14:57]

Report message to a moderator

Re: problem insert clob with more than 4000 bytes [message #292143 is a reply to message #292092] Tue, 08 January 2008 00:47 Go to previous message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
cakask's question was splitted in "More than 4000 characters in PL/QL variable" in Newbies forum.

Regards
Michel
Previous Topic: And condition within the same table
Next Topic: Row to columns based on delimiter!
Goto Forum:
  


Current Time: Tue Dec 06 02:27:43 CST 2016

Total time taken to generate the page: 0.16102 seconds