| problem insert clob with more than 4000 bytes [message #291529] |
Fri, 04 January 2008 13:59  |
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 #291534 is a reply to message #291532] |
Fri, 04 January 2008 14:12  |
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 #291624 is a reply to message #291543] |
Sat, 05 January 2008 09:20 |
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 #291662 is a reply to message #291646] |
Sat, 05 January 2008 14:53 |
 |
Barbara Boehmer
Messages: 9106
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 |
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 #292051 is a reply to message #291994] |
Mon, 07 January 2008 10:28 |
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 #292072 is a reply to message #292051] |
Mon, 07 January 2008 12:38 |
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 |
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 #292092 is a reply to message #292091] |
Mon, 07 January 2008 14:52 |
 |
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
