getting error

[sona]

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

[balajiyes]

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.

[balajiyes]

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

[sona]

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

[Shestakov]

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.

[jmodic]

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:

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>

[sona]

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

[jmodic]

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.