What parameter changes the default length of dbms_lob.substr?

Here are my codes:

CREATE OR REPLACE PROCEDURE exec_now (
  s      CLOB,                     -- SQL statements
  p_lvl  INTEGER  DEFAULT 0        -- message level      
) IS
  v_prg  VARCHAR2(100) := 'exec_now';
  n      NUMBER;
  m      NUMBER;
  msg    VARCHAR2(200);

  PROCEDURE echo (
    msg clob,
    lvl NUMBER DEFAULT 999
  ) IS
  BEGIN
    IF lvl <= p_lvl THEN
        dbms_output.put_line(msg);
    END IF;
  END;

BEGIN
  n := dbms_lob.getlength(s);
  msg := 'INFO('||v_prg||'): '||TO_CHAR(n)||' characters.';
  echo(msg, 1);
  dbms_output.enable(1000000);
  IF n < 32767 THEN
      echo('INFO('||v_prg||'): executing codes (1)...', 1);
      m := LENGTH(DBMS_LOB.SUBSTR(s));
      -- echo(DBMS_LOB.SUBSTR(s), 1);
      msg := 'INFO('||v_prg||'): '||TO_CHAR(m)||'/'||TO_CHAR(n)||' characters.';
      echo(msg, 1);
      EXECUTE IMMEDIATE DBMS_LOB.SUBSTR(s);
      msg := 'INFO('||v_prg||'): OK!';
  ELSIF n < 64000 THEN
      echo('INFO('||v_prg||'): executing codes (2)...', 1);
      m := n - 32000;
      EXECUTE IMMEDIATE DBMS_LOB.SUBSTR(s, 32000, 1)||DBMS_LOB.SUBSTR(s, m, 32001);
      msg := 'INFO('||v_prg||'): OK!';
  ELSE
      msg := 'ERR('||v_prg||'): ';
      msg := msg||'skipped - too long('||TO_CHAR(n)||').';
  END IF;
  echo(msg,1);

  EXCEPTION  
  WHEN OTHERS THEN  
    echo('ERR('||v_prg||'): '||SQLERRM,0);
    raise;
END;
/  

show err

DECLARE
  s CLOB;
  s1   LONG;
  s2   LONG;
  n    NUMBER;
BEGIN
  dbms_lob.createtemporary(s, TRUE);
  dbms_lob.open(s, dbms_lob.lob_readwrite);
  s1 := 'BEGIN null; ' || LPAD (' ', 32000, ' ');
  n := length(s1||' END;'); dbms_lob.writeappend(s, n, s1||' END;');  
  exec_now(s,5);
  dbms_lob.close(s);

  dbms_lob.createtemporary(s, TRUE);
  dbms_lob.open(s, dbms_lob.lob_readwrite);
  -- n := dbms_lob.getlength(s); dbms_lob.erase(s, n, 1);
  n := length(s1);            dbms_lob.writeappend(s, n, s1);  
  s2 := RPAD (' ', 12000, ' ') || ' END;';
  n := length(s2); dbms_lob.writeappend(s, n, s2);    
  exec_now(s,5);
  dbms_lob.close(s);

END;
/

I used the above test code and got two different outputs:

in instance on Window 2003 server:
----------------------------------
INFO(exec_now): 32017 characters.
INFO(exec_now): executing codes (1)...
INFO(exec_now): 32017/32017 characters.
INFO(exec_now): Codes in was executed.
INFO(exec_now): 44017 characters.
INFO(exec_now): executing codes (2)...
INFO(exec_now): Codes was executed.

PL/SQL procedure successfully completed.

In instance on Solaris server
-------------------------------
INFO(exec_now): 32017 characters.
INFO(exec_now): executing codes (1)...
INFO(exec_now): 8191/32017 characters.
ERR(exec_now): ORA-06550: line 1, column 8191:
PLS-00103: Encountered the symbol
 "end-of-file" when expecting one of the following:

Q: What parameter sets the limit on what dbms_lob.substr returns? It is supposed to be 32k but it only returns 8k in one of the Oracle instances. Both instances are Oracle 10.2.0.3.





Start Free Trial