CONNECT sys/p@10gR2_Linux AS SYSDBA
drop user Usr cascade
/
grant Create Session, Resource to Usr identified by p
/
CONNECT Usr/p@10gR2_Linux
----------------------------------------------------------------------
create or replace procedure p1 is


  $if $$Big_String_Is_Clob $then
    subtype Big_String is clob;
    Caption constant varchar2(20) := 'clob version';
  $else
    subtype Big_String is varchar2(32767);
    Caption constant varchar2(20) := 'varchar2 version';
  $end


  x                   Big_String;
  s                   Big_String;
  Expected_s constant Big_String := '+            257';
  t0 integer; t1 integer;
begin
  t0 := DBMS_Utility.Get_Cpu_Time();


  for j in 1..2047 loop
    x := x||'+'||Lpad(j,15);
  end loop;
  if Length(x) <> 32752 then raise Program_Error; end if;
  s := Substr(x,4097,16);
  if s <> Expected_s then raise Program_Error; end if;


  t1 := DBMS_Utility.Get_Cpu_Time();
  DBMS_Output.Put_Line (Caption||': '||To_Char(t1-t0));
end;
/


create or replace procedure p2 is


  $if $$Big_String_Is_Clob $then
    subtype Big_String is clob;
    Caption constant varchar2(20) := 'clob version';
  $else
    subtype Big_String is varchar2(32767);
    Caption constant varchar2(20) := 'varchar2 version';
  $end


  Len                 constant pls_integer := 16;
  Lim                 constant pls_integer := 2047;
  x                   Big_String;
  s                   Big_String;
  type Big_Strings    is table of Big_String index by pls_integer;
  function Populate_Expected return Big_Strings;


  Expected_Substrings constant Big_Strings := Populate_Expected();


  t0 integer; t1 integer;


  function Populate_Expected return Big_Strings is
    a Big_Strings;
  begin
    for j in 1..Lim loop
      a(j) := '+'||Lpad(j,Len-1);
    end loop;
    return a;
  end Populate_Expected;


begin
  for j in 1..Lim loop
    x := x||'+'||Lpad(j,Len-1);
  end loop;
  t0 := DBMS_Utility.Get_Cpu_Time();


  if Length(x) <> 32752 then raise Program_Error; end if;
  for j in 0..Lim-1 loop
    s := Substr(x,(j*Len)+1,Len);
    if s <> Expected_Substrings(j+1) then raise Program_Error; end if;
  end loop;


  t1 := DBMS_Utility.Get_Cpu_Time();
  DBMS_Output.Put_Line (Caption||': '||To_Char(t1-t0));
end p2;
/
----------------------------------------------------------------------
-- clob version: 62
-- varchar2 version:  0


alter procedure p1 compile plsql_ccflags = 'Big_String_Is_Clob:true' reuse settings
/
begin p1(); end;
/
alter procedure p1 compile plsql_ccflags = 'Big_String_Is_Clob:false' reuse settings
/
begin p1(); end;
/


----------------------------------------------------------------------
-- clob version: 60
-- varchar2 version: 0


alter procedure p2 compile plsql_ccflags = 'Big_String_Is_Clob:true' reuse settings
/
begin p2(); end;
/
alter procedure p2 compile plsql_ccflags = 'Big_String_Is_Clob:false' reuse settings
/
begin p2(); end;
/