getting error

SET SERVEROUTPUT ON
DECLARE
v_file Utl_File.File_Type;
v_xml CLOB;
v_more BOOLEAN := TRUE;
BEGIN
-- Create XML document from query.
v_xml := DBMS_XMLQuery.GetXML('SELECT table_name, tablespace_name FROM user_tables WHERE rownum < 6');

-- Output XML document to file.
v_file := Utl_File.FOpen('C:\Development\XML', 'test1.xml', 'w');
WHILE v_more LOOP
Utl_File.Put(v_file, Substr(v_xml, 1, 32767));
IF Length(v_xml) > 32767 THEN
v_xml := Substr(v_xml, 32768);
ELSE
v_more := FALSE;
END IF;
END LOOP;
Utl_File.FClose(v_file);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(Substr(SQLERRM,1,255));
Utl_File.FClose(v_file);
END;
/


/* Utl_File.Put(v_file, Substr(v_xml, 1, 32767)); **/

/** IF Length(v_xml) > 32767 THEN **/

in these 2 lines i am gettting error

invalid number of arguments to substr function

invalid number of arguments to length function

i checked the syntax its right.can anybody try this out

hi sona
chk this line:
"v_xml := Substr(v_xml, 32768); "
max length is "32767" but u start with "32768"
this might be the reason
CORRECT ME IF IAM WRONG.

chk http://www.oraclebase.com
Cheers!

no i am getting error in that particular lines only.i commented out those and it worked properly.i cannot figure it out why

Quote:

---

Originally posted by sona
SET SERVEROUTPUT ON
...
v_xml CLOB;
...
BEGIN
...
Utl_File.Put(v_file, Substr(v_xml, 1, 32767));
IF Length(v_xml) > 32767 THEN v_xml := Substr(v_xml, 32768);
...

---

U can't implement functions for varcha2(char) datatypes to LOB datatype,
v_xml CLOB --> this is not CONTENT of clob variable. This is LOCATOR to content of clob variable.
U MUST use DBMS_LOB package for manipulate of clob objects.

Quote:

---

Originally posted by Shestakov
U can't implement functions for varcha2(char) datatypes to LOB datatype,
v_xml CLOB --> this is not CONTENT of clob variable. This is LOCATOR to content of clob variable.
U MUST use DBMS_LOB package for manipulate of clob objects.

---

That's wrong. SUBSTR() and LENGTH() (among other similar functions) are not limited to VARCHAR2 or CHAR datatypes. They can be used with CLOBs too! I can see absolutely no reason why there should be any errors reporetd because those two functions ere used on CLOB datatype. Check the following example:

Code:

---

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> set serveroutput on
SQL> DECLARE
  2    v_xml  CLOB := 'TEST';
  3  BEGIN
  4    IF SUBSTR(v_xml, 1, 32767) = 'TEST' THEN
  5      NULL;
  6    END IF;
  7    IF Length(v_xml) > 32767 THEN
  8      v_xml := SUBSTR(v_xml, 32768);
  9    END IF;
 10    DBMS_OUTPUT.PUT_LINE(v_xml);
 11  END;
 12  /
TEST

PL/SQL procedure successfully completed.

SQL>

---

hai i tried yours.
but it is giving the same error:
wrong number of arguments to substr

i am using 8.1.7, does it have something to do with the error

Ah, because you are obviously handling XML I assumed you are using 9i.

Yes, you are getting this error because you are using pre-9i database. And yes, in this case Shestakov is absolutely correct: you can't use SUBSTR() or LENGTH() on CLOB datatype if you are not using 9i. You'll have to look at DBMS_LOB package to accomplish this.