Hi,
There are three small questions in this, I wanted to ask in december but you guys were on vacation that's why I could not ask.

Q1. What is the exact size restrictions of trigger I have read 32k here k is what is it KB or (32000 bytes or 32768 bytes) and if it KB then I have created a trigger more than this byte but oracle did not object. Please tell be exact figure in bytes.

Q2. Reference cursor related question

PROMPT CREATING THE TABLE... DELME BACKUP ON FRIDAY, 08-DEC-2017 02:43:24 AM

CREATE TABLE DELME
(
ID                              NUMBER,
NAME                            VARCHAR2(30)
)
/
PROMPT INSERTING INTO THE TABLE... DELME

Insert Into Delme
Values('101','Ankur')
/
Insert Into Delme
Values('102','Kumar')
/
Insert Into Delme
Values('103','Thakran')
/
Insert Into Delme
Values('104','Danny')
/
Insert Into Delme
Values('105','Danger')
/
Commit
/

--Block-1--
declare
type rct is ref cursor;
rc rct;
rec delme%rowtype;
begin
open rc for
'
select *
from delme
where id = nvl(:Id, id)
'
using Null--When I am passing NULL like this then this block is raising error.
;
loop
 fetch rc
 into rec;
 exit when rc%notfound or rc%notfound is null;
 print(rec.Id||' ' ||rec.name);
end loop;
close rc;
end;
/

--Block-2--
declare
type rct is ref cursor;
rc rct;
rec delme%rowtype;
begin
open rc for
'
select *
from delme
where id = nvl(:Id, id)
'
using ''--But when I call this block using '' then it is executing as expected while null and '' both are same please explain why the above program is not working.
;
loop
 fetch rc
 into rec;
 exit when rc%notfound or rc%notfound is null;
 print(rec.Id||' ' ||rec.name);
end loop;
close rc;
end;
/

--I have tested this code on Oracle 10.2.0.3.0--

In block 1, When I am passing NULL through using clause then this block is raising error. But
In block 2, When I am passing '' through using clause then this block executes as I expected.

My question is that both null and '' represents NULL then why this differemce in the execution of both the blocks. Is it a kind of a bug?

Q3. When I run

select *
from delme
union all
select empno, ename
from emp
order by name
/

It raises error (ORA-00904: "NAME": invalid identifier)

but when I run

select id, name
from delme
union all
select empno, ename
from emp
order by name
/

it is executed without a problem, in first query I used * while in the second query I wrote column names, can't oracle resolve its column name own its own?

create table t (
  x int
);

declare
  stmt clob;
begin
  dbms_lob.createtemporary(stmt,true);
  
  stmt := 'create or replace trigger trg
  before insert on t
  for each row
begin
';

  for i in 1 .. 4 loop
    dbms_lob.writeappend(stmt, 20026, 'dbms_output.put_line(''' || lpad(i, 20000, 'x') || ''');
');
  end loop;
  
  dbms_lob.writeappend(stmt, 4, 'end;');
  
  execute immediate stmt;
  
  dbms_lob.freetemporary(stmt);
end;
/
sho err

select sum(length(text))
from   user_source
where  name = 'TRG';

SUM(LENGTH(TEXT))   
              80162 

select status from user_objects
where  object_name = 'TRG';

STATUS   
VALID

declare
  type rct is ref cursor;
  rc    rct;
  rec   delme%rowtype;
  nul   delme.id%type;
begin
  open rc for '
select *
from delme
where id = nvl(:Id, id)' using nul;

  loop
    fetch rc into rec;
    exit when rc%notfound or rc%notfound is null;
    dbms_output.put_line( rec.id || ' ' || rec.name );
  end loop;

  close rc;
end;
/

101 Ankur
102 Kumar
103 Thakran
104 Danny
105 Danger