analytics/informix/extend/excompat.1.0/test.sql

-- Generated by BladeSmith 4.20.TC1  DO NOT MODIFY. --

execute procedure ifx_allow_newline('t');

begin work;

create distinct type utl_file_file_type as integer;

grant usage on type utl_file_file_type to public;

create distinct type dbms_output_chararr as lvarchar;

grant usage on type dbms_output_chararr to public;

create function utl_file_fopen (lvarchar,lvarchar,lvarchar,integer)
returns utl_file_file_type
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(utl_file_fopen)" language c;

alter function utl_file_fopen (lvarchar,lvarchar,lvarchar,integer)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(utl_file_fopen)");

grant execute on function utl_file_fopen (lvarchar,lvarchar,lvarchar,integer) to public;

create function utl_file_is_open (utl_file_file_type)
returns boolean
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(utl_file_is_open)" language c;

alter function utl_file_is_open (utl_file_file_type)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(utl_file_is_open)");

grant execute on function utl_file_is_open (utl_file_file_type) to public;

create procedure utl_file_fclose (utl_file_file_type)
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(utl_file_fclose)" language c;

alter procedure utl_file_fclose (utl_file_file_type)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(utl_file_fclose)");

grant execute on procedure utl_file_fclose (utl_file_file_type) to public;

create procedure utl_file_get_line (utl_file_file_type,out lvarchar)
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(utl_file_get_line)" language c;

alter procedure utl_file_get_line (utl_file_file_type,lvarchar)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(utl_file_get_line)");

grant execute on procedure utl_file_get_line (utl_file_file_type,lvarchar) to public;

create procedure utl_file_put (utl_file_file_type)
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(utl_file_put)" language c;

alter procedure utl_file_put (utl_file_file_type)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(utl_file_put)");

grant execute on procedure utl_file_put (utl_file_file_type) to public;

create procedure utl_file_new_line (utl_file_file_type,integer default 1)
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(utl_file_new_line)" language c;

alter procedure utl_file_new_line (utl_file_file_type,integer)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(utl_file_new_line)");

grant execute on procedure utl_file_new_line (utl_file_file_type,integer) to public;

create procedure utl_file_put_line (utl_file_file_type)
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(utl_file_put_line)" language c;

alter procedure utl_file_put_line (utl_file_file_type)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(utl_file_put_line)");

grant execute on procedure utl_file_put_line (utl_file_file_type) to public;

create procedure utl_file_putf (utl_file_file_type,lvarchar,lvarchar default NULL,lvarchar default NULL,lvarchar default NULL,lvarchar default NULL,lvarchar default NULL)
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(utl_file_putf)" language c;

alter procedure utl_file_putf (utl_file_file_type,lvarchar,lvarchar,lvarchar,lvarchar,lvarchar,lvarchar)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(utl_file_putf)");

alter procedure utl_file_putf (utl_file_file_type,lvarchar,lvarchar,lvarchar,lvarchar,lvarchar,lvarchar)
	with (add handlesnulls);

grant execute on procedure utl_file_putf (utl_file_file_type,lvarchar,lvarchar,lvarchar,lvarchar,lvarchar,lvarchar) to public;

create procedure utl_file_fflush (utl_file_file_type)
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(utl_file_fflush)" language c;

alter procedure utl_file_fflush (utl_file_file_type)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(utl_file_fflush)");

grant execute on procedure utl_file_fflush (utl_file_file_type) to public;

create procedure utl_file_fclose_all ()
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(utl_file_fclose_all)" language c;

alter procedure utl_file_fclose_all ()
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(utl_file_fclose_all)");

grant execute on procedure utl_file_fclose_all () to public;

create procedure dbms_output_enable (integer default 20000)
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_output_enable)" language c;

alter procedure dbms_output_enable (integer)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_output_enable)");

grant execute on procedure dbms_output_enable (integer) to public;

create procedure dbms_output_disable ()
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_output_disable)" language c;

alter procedure dbms_output_disable ()
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_output_disable)");

grant execute on procedure dbms_output_disable () to public;

create procedure dbms_output_put (lvarchar)
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_output_put)" language c;

alter procedure dbms_output_put (lvarchar)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_output_put)");

grant execute on procedure dbms_output_put (lvarchar) to public;

create procedure dbms_output_put_line (lvarchar)
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_output_put_line)" language c;

alter procedure dbms_output_put_line (lvarchar)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_output_put_line)");

grant execute on procedure dbms_output_put_line (lvarchar) to public;

create procedure dbms_output_new_line ()
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_output_new_line)" language c;

alter procedure dbms_output_new_line ()
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_output_new_line)");

grant execute on procedure dbms_output_new_line () to public;

create procedure dbms_output_get_line (lvarchar,out integer)
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_output_get_line)" language c;

alter procedure dbms_output_get_line (lvarchar,integer)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_output_get_line)");

grant execute on procedure dbms_output_get_line (lvarchar,integer) to public;

create procedure dbms_output_get_lines (dbms_output_chararr,out integer)
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_output_get_lines)" language c;

alter procedure dbms_output_get_lines (dbms_output_chararr,integer)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_output_get_lines)");

grant execute on procedure dbms_output_get_lines (dbms_output_chararr,integer) to public;

create procedure dbms_lob_append (blob,blob)
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_append)" language c;

alter procedure dbms_lob_append (blob,blob)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_append)");

alter procedure dbms_lob_append (blob,blob)
	with (add handlesnulls);

grant execute on procedure dbms_lob_append (blob,blob) to public;

create procedure dbms_lob_append (clob,clob)
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_append)" language c;

alter procedure dbms_lob_append (clob,clob)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_append)");

grant execute on procedure dbms_lob_append (clob,clob) to public;

create function dbms_lob_getlength (blob)
returns integer
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_getlength)" language c;

alter function dbms_lob_getlength (blob)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_getlength)");

grant execute on function dbms_lob_getlength (blob) to public;

create function dbms_lob_getlength (clob)
returns integer
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_getlength)" language c;

alter function dbms_lob_getlength (clob)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_getlength)");

grant execute on function dbms_lob_getlength (clob) to public;

create function dbms_lob_compare (blob,blob,integer default 2147483647,integer default 1,integer default 1)
returns integer
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_compare)" language c;

alter function dbms_lob_compare (blob,blob,integer,integer,integer)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_compare)");

grant execute on function dbms_lob_compare (blob,blob,integer,integer,integer) to public;

create procedure dbms_lob_copy (blob,blob,integer,integer default 1,integer default 1)
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_copy)" language c;

alter procedure dbms_lob_copy (blob,blob,integer,integer,integer)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_copy)");

alter procedure dbms_lob_copy (blob,blob,integer,integer,integer)
	with (add handlesnulls);

grant execute on procedure dbms_lob_copy (blob,blob,integer,integer,integer) to public;

create procedure dbms_lob_copy (clob,clob,integer,integer default 1,integer default 1)
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_copy)" language c;

alter procedure dbms_lob_copy (clob,clob,integer,integer,integer)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_copy)");

alter procedure dbms_lob_copy (clob,clob,integer,integer,integer)
	with (add handlesnulls);

grant execute on procedure dbms_lob_copy (clob,clob,integer,integer,integer) to public;

create procedure dbms_lob_erase (blob,integer,integer default 1)
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_blob_erase)" language c;

alter procedure dbms_lob_erase (blob,integer,integer)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_blob_erase)");

grant execute on procedure dbms_lob_erase (blob,integer,integer) to public;

create procedure dbms_lob_erase (clob,integer,integer default 1)
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_clob_erase)" language c;

alter procedure dbms_lob_erase (clob,integer,integer)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_clob_erase)");

grant execute on procedure dbms_lob_erase (clob,integer,integer) to public;

create function dbms_lob_substr (clob,integer default 32767,integer default 1)
returns lvarchar
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_substr)" language c;

alter function dbms_lob_substr (clob,integer,integer)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_substr)");

grant execute on function dbms_lob_substr (clob,integer,integer) to public;

create function dbms_lob_instr (clob,lvarchar,integer default 1,integer default 1)
returns integer
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_instr)" language c;

alter function dbms_lob_instr (clob,lvarchar,integer,integer)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_instr)");

grant execute on function dbms_lob_instr (clob,lvarchar,integer,integer) to public;

create procedure dbms_lob_read (clob,integer,integer,out lvarchar)
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_read)" language c;

alter procedure dbms_lob_read (clob,integer,integer,lvarchar)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_read)");

grant execute on procedure dbms_lob_read (clob,integer,integer,lvarchar) to public;

create procedure dbms_lob_trim (blob,integer)
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_trim)" language c;

alter procedure dbms_lob_trim (blob,integer)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_trim)");

grant execute on procedure dbms_lob_trim (blob,integer) to public;

create procedure dbms_lob_trim (clob,integer)
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_trim)" language c;

alter procedure dbms_lob_trim (clob,integer)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_trim)");

grant execute on procedure dbms_lob_trim (clob,integer) to public;

create procedure dbms_lob_write (clob,integer,integer,lvarchar)
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_write)" language c;

alter procedure dbms_lob_write (clob,integer,integer,lvarchar)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_write)");

grant execute on procedure dbms_lob_write (clob,integer,integer,lvarchar) to public;

create function dbms_lob_new_clob (lvarchar)
returns clob
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_new_clob)" language c;

alter function dbms_lob_new_clob (lvarchar)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_new_clob)");

alter function dbms_lob_new_clob (lvarchar)
	with (add not variant);

grant execute on function dbms_lob_new_clob (lvarchar) to public;

create procedure dbms_lob_append (clob,lvarchar)
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_append_str)" language c;

alter procedure dbms_lob_append (clob,lvarchar)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_append_str)");

alter procedure dbms_lob_append (clob,lvarchar)
	with (add not variant);

grant execute on procedure dbms_lob_append (clob,lvarchar) to public;

create function dbms_lob_compare (clob,clob,integer default 2147483647,integer default 1,integer default 1)
returns integer
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_compare)" language c;

alter function dbms_lob_compare (clob,clob,integer,integer,integer)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(dbms_lob_compare)");

grant execute on function dbms_lob_compare (clob,clob,integer,integer,integer) to public;

create procedure dbms_alert_register (name lvarchar)
  define evid integer;

  let evid = DBMS_ALERT_GET_EVENT_ID(name);
  insert into DBMS_ALERT_REGISTERED values (DBINFO('sessionid'), evid);

end procedure;

grant execute on procedure dbms_alert_register (lvarchar) to public;

create function dbms_alert_get_event_id (name lvarchar)
returns integer
  define evid integer;

  select event_id into evid from DBMS_ALERT_EVENTS
    where name = event_name;
  if evid IS NULL then
    insert into DBMS_ALERT_EVENTS(event_name) values (name);
    let evid = DBINFO('sqlca.sqlerrd1');
  end if;

  return evid;

end function;

grant execute on function dbms_alert_get_event_id (lvarchar) to public;

create procedure dbms_alert_remove (name lvarchar)
  define evid integer;
  define sesid integer;

  let evid = DBMS_ALERT_GET_EVENT_ID(name);
  let sesid = DBINFO('sessionid');
  delete from DBMS_ALERT_REGISTERED where
    (session_id = sesid AND event_id = evid);
  delete from DBMS_ALERT_SIGNALED where
    (session_id = sesid AND event_id = evid);

end procedure;

grant execute on procedure dbms_alert_remove (lvarchar) to public;

create procedure dbms_alert_removeall ()
  define sesid integer;

  let sesid = DBINFO('sessionid');
  delete from DBMS_ALERT_REGISTERED where
    (session_id = sesid);
  delete from DBMS_ALERT_SIGNALED where
    (session_id = sesid);

end procedure;

grant execute on procedure dbms_alert_removeall () to public;

create procedure dbms_alert_set_defaults (sensitivity integer)
  define global poll_interval INTEGER DEFAULT 1;
  let poll_interval = sensitivity;

end procedure;

grant execute on procedure dbms_alert_set_defaults (integer) to public;

create function dbms_alert_get_defaults ()
returns integer
  define global poll_interval INTEGER DEFAULT 1;
  return poll_interval;

end function;

grant execute on function dbms_alert_get_defaults () to public;

create procedure dbms_alert_signal (name lvarchar,message lvarchar)
  define evid integer;
  define cnt integer;
  define signal_time DATETIME year to second;

  let evid = DBMS_ALERT_GET_EVENT_ID(name);
  if evid IS NOT NULL THEN
    let signal_time = CURRENT;
    insert into DBMS_ALERT_SIGNALED
      select evid, session_id, signal_time, message
      from DBMS_ALERT_REGISTERED where (evid = event_id);
  end if;

end procedure;

grant execute on procedure dbms_alert_signal (lvarchar,lvarchar) to public;

create procedure dbms_alert_waitone (name lvarchar,message lvarchar,status integer,timeout integer default 86400)
  define global poll_interval INTEGER DEFAULT 1;
  define evid integer;
  define sesid integer;
  define cnt integer;
  define sig_time DATETIME year to second;
  define yet integer;

  let sesid = DBINFO('sessionid');
  let evid = DBMS_ALERT_GET_EVENT_ID(name);
  let status = 2;
  if evid IS NOT NULL THEN
    let status = 1;
    let sesid = DBINFO('sessionid');
    let yet = timeout;
    LOOP
      select count(alert_msg),MAX(signal_time) into cnt,sig_time from DBMS_ALERT_SIGNALED
        where (evid = event_id AND sesid = session_id);
      if cnt > 0 then
        foreach sig_cursor for
        select alert_msg into message from DBMS_ALERT_SIGNALED
            where (evid = event_id AND sesid = session_id AND signal_time = sig_time)
        delete from DBMS_ALERT_SIGNALED
          where current of sig_cursor;
        end foreach;
        let status = 0;
        exit loop;
      elif yet > 0 then
        call DBMS_ALERT_WAIT(poll_interval);
        let yet = yet - poll_interval;
      else
        exit loop;
      end if;
    end loop;
  end if;

end procedure;

grant execute on procedure dbms_alert_waitone (lvarchar,lvarchar,integer,integer) to public;

create procedure dbms_alert_waitany (name lvarchar,message lvarchar,status integer,timeout integer default 86400)
  define global poll_interval INTEGER DEFAULT 1;
  define evid integer;
  define sesid integer;
  define cnt integer;
  define sig_time DATETIME year to second;
  define yet integer;

  let sesid = DBINFO('sessionid');
  let name = NULL;

  let status = 1;
  let sesid = DBINFO('sessionid');
  let yet = timeout;
  LOOP
    select count(alert_msg),MAX(signal_time) into cnt,sig_time from DBMS_ALERT_SIGNALED
      where (sesid = session_id);
    if cnt > 0 then
      begin work;
      foreach sig_cursor for
      select alert_msg, event_id into message, evid
        from DBMS_ALERT_SIGNALED
          where (sesid = session_id AND signal_time = sig_time)
      delete from DBMS_ALERT_SIGNALED
        where current of sig_cursor;
      select event_name into name
        from DBMS_ALERT_EVENTS
        where evid = event_id;
      end foreach;
      commit work;
      let status = 0;
      exit loop;
    elif yet > 0 then
      call DBMS_ALERT_WAIT(poll_interval);
      let yet = yet - poll_interval;
    else
      exit loop;
    end if;
  end loop;

end procedure;

grant execute on procedure dbms_alert_waitany (lvarchar,lvarchar,integer,integer) to public;

create procedure dbms_alert_wait (integer default 1)
external name "(mt_yield)" language c;

alter procedure dbms_alert_wait (integer)
	with (Modify External Name = "(mt_yield)");

grant execute on procedure dbms_alert_wait (integer) to public;

create procedure dbms_random_initialize (seed integer)
  define global XI bigint default 0;
  let XI = seed * 4294967296;

end procedure;

grant execute on procedure dbms_random_initialize (integer) to public;

create procedure dbms_random_seed (seed integer)
  define global XI bigint default 0;
  let XI = seed * 4294967296;

end procedure;

grant execute on procedure dbms_random_seed (integer) to public;

create function dbms_random_random ()
returns integer
  define global XI bigint default 0;
  if XI = 0 then
    let XI = 8589934592;
  end if;
  return dbms_random_jrand48(XI);

end function;

grant execute on function dbms_random_random () to public;

create procedure dbms_random_terminate ()
  define global XI bigint default 0;
  let XI = 0;


end procedure;

grant execute on procedure dbms_random_terminate () to public;

create function dbms_random_jrand48 (out integer)
returns integer
external name "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(jrand48)" language c;

alter function dbms_random_jrand48 (integer)
	with (Modify External Name = "$INFORMIXDIR/extend/excompat.1.0/excompat.bld(jrand48)");

alter function dbms_random_jrand48 (integer)
	with (add handlesnulls);

grant execute on function dbms_random_jrand48 (integer) to public;

create table if not exists DBMS_ALERT_EVENTS
( event_id SERIAL unique,
  event_name VARCHAR(30) primary key
);

create table if not exists DBMS_ALERT_REGISTERED
( session_id INTEGER,
  event_id INTEGER references DBMS_ALERT_EVENTS(event_id)
);

create unique index if not exists DBMS_ALERT_SESIDX
  on DBMS_ALERT_REGISTERED (session_id, event_id);

create table if not exists DBMS_ALERT_SIGNALED
( event_id INTEGER references DBMS_ALERT_EVENTS(event_id),
  session_id INTEGER,
  signal_time DATETIME year to second,
  alert_msg LVARCHAR
);

insert into informix.syserrors
	(level, seqno, sqlstate, locale, message)
values
(
	0, 1, "EXC21", "en_us.1252", "DBMS_LOB error"
);

commit work;