Home » SQL & PL/SQL » SQL & PL/SQL » Passing CHAR literal containing more than 4000 char in function parameter (Oracle 10g, R2, Windows XP)
Passing CHAR literal containing more than 4000 char in function parameter [message #365253] Thu, 11 December 2008 04:16 Go to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hello friends,

I am having problem in passing CHAR type literal into function as an argument.
This happens because literal contains more than 4000 char.
I think, I am required to pass string as CLOB type.
But dont know to pass it as CLOB.
I tried CAST() function. but failed.

Please help in this matter.

Regards,
Delna
Re: Passing CHAR literal containing more than 4000 char in function parameter [message #365265 is a reply to message #365253] Thu, 11 December 2008 04:42 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
my knowledge says arguments differs from data, I am ready to correct myself. Let me have an idea about your argument function which exceeds 4000 characters. or do you want to push the data into some storage type through some function?
let me get clarified?
yours
dr.s.raghunathan
Re: Passing CHAR literal containing more than 4000 char in function parameter [message #365268 is a reply to message #365265] Thu, 11 December 2008 04:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you're passing a string in Pl/Sql then it can be up to 32767 chrs long.
In SQL, it may be a maximum of 4000 chrs.

If you to build up a CLOB to longer than these limits, then you have to append clobs to each other.

This works because no string of more than 4000 chrs exists at any point:
SQL> select length(to_clob(rpad('A',4000,'B'))||to_clob(rpad('C',4000,'D'))) from dual;

LENGTH(TO_CLOB(RPAD('A',4000,'B'))||TO_CLOB(RPAD('C',4000,'D')))
----------------------------------------------------------------
                                                            8000


This fails because two strings are appended together to make a string of > 4000 chrs:
SQL> select length(to_clob(rpad('A',4000,'B')||rpad('C',4000,'D'))) from dual;
select length(to_clob(rpad('A',4000,'B')||rpad('C',4000,'D'))) from dual
                                                                    *
ERROR at line 1:
ORA-01489: result of string concatenation is too long
Re: Passing CHAR literal containing more than 4000 char in function parameter [message #365269 is a reply to message #365253] Thu, 11 December 2008 04:58 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thank you sir for your reply.

following are the details

Nested Table
create or replace
type typ_nest_varchar2 is table of varchar2(300)


Function prototype
create or replace
function string_to_table(p_string VARCHAR2)
return typ_nest_varchar2


Calling function using SELECT
select * from table(cast(string_to_table('a:s:fghfghd:fhjga...') as typ_nest_varchar2));


Regards,
Delna
Re: Passing CHAR literal containing more than 4000 char in function parameter [message #365270 is a reply to message #365268] Thu, 11 December 2008 05:02 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
But sir, I am required to pass a single string containing more than 4000 char. from select query
Re: Passing CHAR literal containing more than 4000 char in function parameter [message #365271 is a reply to message #365269] Thu, 11 December 2008 05:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If your function takes a VARCHAR as a parameter then you can't pass more than 4000 chrs to it.

Rewrite the function to take a CLOB parameter instead, and build and pass a CLOB to it.
Re: Passing CHAR literal containing more than 4000 char in function parameter [message #365272 is a reply to message #365270] Thu, 11 December 2008 05:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure you have to do it in SQL?
Where does the string comes from?
I bet this is not someone writing 40000 character string in a SQL statement, so you have it somewhere.

Regards
Michel
Re: Passing CHAR literal containing more than 4000 char in function parameter [message #365276 is a reply to message #365272] Thu, 11 December 2008 05:12 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Yes Michel Sir, I am required to call that function from SQL.

Actually instead of that string I am using one Java function which returns long string literal.

and one more thing,
how to convert that CHAR type value to CLOB, as said by JRowbottom
Re: Passing CHAR literal containing more than 4000 char in function parameter [message #365282 is a reply to message #365276] Thu, 11 December 2008 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So if you have the string in Java program variable, you can use the Oracle CLOB type for this.
And why do you want to split the string within Oracle and not in Java?
I would be better if you posted the real issue, maybe you are trying to solve a problem that should not exist.

Regards
Michel
Re: Passing CHAR literal containing more than 4000 char in function parameter [message #365299 is a reply to message #365282] Thu, 11 December 2008 05:54 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Sorry for inconvience,

Actual situation is like story as follows:

Following is a list() function written in Java.
It returns list of file hierarchy seperated by colon(: )
FUNCTION list (p_path  IN  VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA 
NAME 'FileHandler.list (java.lang.String) return java.lang.String';


PL/SQL table
create or replace
type typ_nest_varchar2 is table of varchar2(300)


string_to_table() function
create or replace
function string_to_table(p_string varchar2)
return typ_nest_varchar2
as


Calling string_to_table() from SQL.
select * from table(cast(string_to_table(list('c:\1')) as typ_nest_varchar2));


Now please tell me, how to solve this issue.

Regards,
Delna
Re: Passing CHAR literal containing more than 4000 char in function parameter [message #365307 is a reply to message #365299] Thu, 11 December 2008 06:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Change your String_to_table function to accept a CLOB.
Change your Java wrapper to return a CLOB

Just replace the bit where it says 'VARCHAR2' in the parameter definitions with 'CLOB'.

You may have to do a bit of tinkering with string_to_table if you want it to handle strings of > 32K, but less than that should work transparantly.
Re: Passing CHAR literal containing more than 4000 char in function parameter [message #365654 is a reply to message #365307] Thu, 11 December 2008 19:21 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
The following should work up to 32k. I don't know what your FileHandler.list returns but I am guessing it is a list of files separated by colons or some such thing, so I have used something like that for demonstration purposes.

SCOTT@orcl_11g> create or replace and compile java source named "FileHandler" as
  2  	import java.io.*;
  3  	import java.sql.*;
  4  	import java.util.*;
  5  	public class FileHandler
  6  	{
  7  public static String list (String path) {
  8  	 String list = "";
  9  	 String file = "";
 10  	 File myFile = new File (path);
 11  	 String[] arrayList = myFile.list();
 12  Arrays.sort(arrayList, String.CASE_INSENSITIVE_ORDER);
 13  	 for (int i=0; i < arrayList.length; i++) {
 14  	     list += arrayList[i] + ":";
 15  	 }
 16  	 return list;
 17    }
 18  }
 19  /

Java created.

SCOTT@orcl_11g> create or replace function list
  2    (p_path	in varchar2)
  3    return varchar2
  4  as LANGUAGE JAVA
  5  NAME 'FileHandler.list (java.lang.String) return java.lang.String';
  6  /

Function created.

SCOTT@orcl_11g> create or replace function CLOBlist
  2    (p_path in varchar2)
  3    return CLOB
  4  as
  5  begin
  6    return to_clob (list (p_path));
  7  end CLOBlist;
  8  /

Function created.

SCOTT@orcl_11g> create or replace type typ_nest_varchar2 as table of varchar2 (300);
  2  /

Type created.

SCOTT@orcl_11g> create or replace function string_to_table
  2    (p_string in CLOB)
  3    return typ_nest_varchar2
  4  as
  5    l_string        long := p_string || ':';
  6    l_data	       typ_nest_varchar2 := typ_nest_varchar2();
  7  begin
  8    loop
  9  	   exit when l_string is null;
 10  	   l_data.extend;
 11  	   l_data (l_data.count) := ltrim (rtrim (substr (l_string, 1, instr (l_string, ':') - 1)));
 12  	   l_string := substr (l_string, instr (l_string, ':') + 1);
 13    end loop;
 14    return l_data;
 15  end string_to_table;
 16  /

Function created.

SCOTT@orcl_11g> select length (CLOBlist ('c:\oracle11g')) as length_of_string
  2  from   dual
  3  /

LENGTH_OF_STRING
----------------
           16141

SCOTT@orcl_11g> select column_value as files
  2  from   table (cast (string_to_table (CLOBlist ('c:\oracle11g')) as typ_nest_varchar2))
  3  /

FILES
--------------------------------------------------------------------------------
00000001_20071225102058_RQ_SERVICES2.txt
00000001_20071225102406_RQ_SERVICES2.txt
00000001_20071225104643_RQ_SERVICES2.txt
1.txt
20080318.LST
24686orclBases de Cotizaci•n 27-04.doc
afiedt.buf
any.txt
arraydemo.sql
bad_emp.bad
badcode.pl
Banana.pdf
batchfeed.bad
BATCHFEED.csv
budget.tXt
budget3.sql
c_dir.txt
c_dir_test.ctl
CAR2.XML
cars.xml
CARS2.XML
cat.bmp
... input truncated to save space
upcase.pl
utldtree.sql
valid.key
who_called_me.sql


1276 rows selected.

SCOTT@orcl_11g>

Re: Passing CHAR literal containing more than 4000 char in function parameter [message #365669 is a reply to message #365654] Thu, 11 December 2008 23:19 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thanks to all for valuable help (Specially Barbara mam (in bracket Laughing ))
Previous Topic: External Tables for a fixed width file
Next Topic: Split value and sort by lastname
Goto Forum:
  


Current Time: Thu Dec 08 00:15:00 CST 2016

Total time taken to generate the page: 0.15175 seconds