test_pl.sql

-- create or replace package body test_pkg is
-- create or replace package test_pkg is
-- create or replace function test_func return number is
-- create or replace procedure test_proc is
 declare


   type rec is record (n number, d date, c char);

   type tab is table of number(4) index by varchar2(4);
   type tab is table of number(4) index by varchar2(4) not null; 
   
   type var is varray(10) of number(4);
   type var is varray(10) of number(4) not null;

   procedure p( a in number, b in varchar2);
   function f( a number, b out number, c in out number, d nocopy number) return number;
   function f1 return number deterministic;
   function f2 return number parallel_enable;
   function f3 return number pipelined;
   function f4 return number deterministic pipelined;

   a number;
   a number(5);
   a number(5,2);
   b constant number ;
   c constant number  := 5;
   d number default 5;
   e number not null;
   e constant number not null;
   e number not null := true;
   e constant number not null := 1+5;
   r ref number;

   ex exception;

   pragma restrict_references;
   pragma restrict_references();
   pragma exception_init(ex, -123456);

   a abc%type;
   b abc%rowtype;

   cursor cur;
  
   cursor p( a in number, b in varchar2);
   
   cursor cur is select * from dual;

   cursor cur( a in number, b in varchar2) is select * from dual;

   procedure p( a in number, b in varchar2)
   is
      n number;
   begin
      return;
   exception
   when others then
      a := a + 2**4;
   end p;

   function f( a in number)
   return number
   is
      retval number;
   begin
      return retval;
   end f;
begin
   null;
   a := b(c).aval; --- Weird must have a v following the .

   a := b(c).val;
   b(c).val := 1;

   a := c between 1 and 3;
   select 1 into n
   from dual where 2 between (1 and 3);

--   abc(123).show; -- fails parse
--   a := abc(123).show; -- failse parse, will work if we add a "." operator but there has to be a space after the dot for some reason

   insert into tab (a,b,c) values (1,2,3);

   insert into tab (x,y,z) select 4,5,6 from dual; -- fails parse

   select * from dual;
 
   select a.*, b.* from dual a, dual b;

   update tab, tab2 set x = 3, y=4;

   update tab set x = 3 where current of cur;
   
   update tab set x = 3 where x=2;
   
   delete tab where x=2;

   delete from tab where x=2;


   --   update tab set x = 3, y=4 where rownum = 1;

   execute immediate 'worse ' || than || 'goto';

   for i in 1 .. 7 loop
      a := i * n + 3;
   end loop;

   for i in reverse 1 .. 7 loop
      a := i * n + 3;
   end loop;

   for i in cur loop
      a := 1;
   end loop;
   
   for i in cur(1,2,3) loop
      a := 1;
   end loop;

   for i in (select 1 from dual) loop
      a := 2;
   end loop;

   open cur for 'select 1 from dual';

   open cur for select 1 from dual;

   open cur;
   open cur(1,2);
   open cur(1,a=>2);

   select 1 from dual
   union 
   select 2 from dual
   order by 1;
   
             select 1 a from dual
   minus     select 1 from dual
   union all select 1 from dual
   union     select 1 from dual
   intersect select 1 from dual
   union (select 1 from dual minus select 1 from dual);
   
   select distinct a.c0, a.c1 as z1, a.c2 z1, c3, 3
   from a,b,c, 
        (select 1 a from dual);

   select a.c0, a.c1 as z1, a.c2 z1, c3, 3
   into p.a, p.b, c
   from a,b,c, 
        (select 1 a from dual)
   where 1=5 and a=4
   group by "a", a.b, a.c
   having 1=5 and a=6
   order by a.a, a.b, c
   returning a.a, a, 3;
   

   select 1
   bulk collect into p.a
   from dual
   where d.dummy = (select dual.dummy from dual);
  
   select (select 1 n from dual) as a from dual; 

   with a as (select 1 n from dual),
        b as (select 1 o from dual) -- union select 2 o from dual)
   select n from a;



   commit work;
   commit comment 'done' write nowait;
   commit write batch wait;
   commit force 'distrib';
   commit force 'distrib' 55;


   rollback;
   rollback work to cya;
   rollback force 'distrib';
   savepoint cya;

   a:=null;
   a:=true;

   a := abc%found;
   b := abc%notfound;
   c := abc%isopen;
 
   d := 1 + abc%rowcount;
   e := 2 + abc%bulk_rowcount;
   f := 'a' + abc%charset;
  
   a := 1 > 3;

   a := ('abc') > ('123');
   a := 'abc' > '123';

   a := 'abc' like 'a%';

   a := (1,2,3); 
   a := 1 in (1,2,3);
   a := 1 = any(1,2,3);
   a := 1 = all(1,2,3);

   a := func(1);
   a := func(1,3,4);
   a := func(1,2, a=>1, b=>2);
   a := func(a=>1, b=>2);

   a := f(1) + f2 / 3 / f3() = all ( f(4), f(a=>1), f(1,b=>2));

   proc();
   proc(1,3,4);
   proc(1,2, a => 1, b => 2);
   proc(a=>1, b=>2);
   

   fetch cur into var1, var2, var3;
   fetch cur bulk collect into var1, var2, var3;
   fetch cur bulk collect into var1, var2, var3 limit 10;

   close cur;

   set transaction read only;
   set transaction read write name 'tweedle dee';
   set transaction isolation level serializable;
   set transaction isolation level read committed name 'tweedle dum';
   set transaction use rollback segment "No Go";
   

   lock table a, scott.employee IN 
   ROW excLUSIVE Update
   MODE nowait;

   begin
      a:=1;
   end;

   <<no_end_label>>
   begin
      a:=1;
   end;

   <<match_label>>
   begin
      a:=1;
   end match_label;

   <<mismatch>>
   begin
      a:=1;
   end Zmismatch;

   begin
      a:=1;
   end no_start_label;

   loop 
   exit when true or false or null;
   exit foobar when true or false or null;
      a:=1;
   end loop;

   return;
   return (7+16);
   return (True or false);
   return True or false;
   return 7>16;
   return (7>16);
   i := (true is not null); -- fails parse -- works now
   return true is not null; -- fails parse -- works now
   return i is null;
   return i is not null;

   return (7>16) or (1>5) ; -- fails parse -- works now
   return 'hi';
   return 'hi' || 'there';

 
   while true is null loop a := 1; end loop;

   a := 7 is null;

   while (1 > 4) loop a := 1; end loop; -- fails parse -- works now

   while (n is null) loop a := 1; end loop; -- fails parse -- works now

   goto "goto is bad m'kay";


exception 
when others then
   a := 2;
   null;
when no_data_found or b then
   a := 3;   
   raise no_data_found;
   raise;
when others or a then null;
end;