SQL & PL/SQL

I have a procedure that takes in a string value.
This can be over 4000 characters long and as you are probably aware - it's not liking it.
ORA-01704: string literal too long
01704. 00000 - "string literal too long"
*Cause: The string literal is longer than 4000 characters.

Is there a data type I can use that will handle this?

or how else should I handle it?

Thanks in advance.

Ant

Edited by: user10071099 on Jun 25, 2010 3:55 PM

Here's the function in question
CREATE OR REPLACE FUNCTION GET_COLUNS_AS_LIST( P_SQL CLOB, Add_Equals_Sign Number := 0)
RETURN CLOB
IS
fResult CLOB;
HNDL NUMBER;
d NUMBER;
colCount INTEGER;
i INTEGER;

rec_tab DBMS_SQL.DESC_TAB;
cCRLF VARCHAR(2) := CHR(13) || CHR(10);

BEGIN
--INITIIALISE RESULT
fResult := '';

HNDL := DBMS_SQL.OPEN_CURSOR;

if HNDL <> 0 THEN

DBMS_SQL.PARSE( HNDL, P_SQL, DBMS_SQL.NATIVE);
d := DBMS_SQL.EXECUTE( HNDL );
DBMS_SQL.DESCRIBE_COLUMNS( HNDL, colCount, rec_tab);

FOR i in 1..colCount
LOOP
IF Add_Equals_Sign > 0 AND i > 1 THEN
fResult := ltrim( fResult || '=' || cCRLF || UPPER( rec_tab( i ).col_name ), cCRLF );
ELSE
fResult := ltrim( fResult || cCRLF || UPPER( rec_tab( i ).col_name ), cCRLF );
END IF;

END LOOP;

IF Add_Equals_Sign > 0 THEN
fResult := fResult ||'=';
END IF;

ELSE
fResult := '!!COULD NOT OPEN CURSOR!!';

END IF;

RETURN fResult;


--Tidy Up
DBMS_SQL.CLOSE_CURSOR(HNDL);

END;
/

Edited by: user10071099 on Jun 25, 2010 4:53 PM