SQL Fiddle Output Error

Question

Actually I am quite new to PL/SQL

I created the following table using oracle pl/sql in SQL Fiddle

create table Employee(name varchar2(100),id integer, salary integer,PRIMARY KEY(id));
insert into Employee(name,id,salary) values('sa',94,100);
insert into Employee(name,id,salary) values('pr',88,150);
insert into Employee(name,id,salary) values('ji',33,900);
insert into Employee(name,id,salary) values('na',24,880);
insert into Employee(name,id,salary) values('po',65,770);
insert into Employee(name,id,salary) values('ri',69,910);
insert into Employee(name,id,salary) values('uj',12,650);
insert into Employee(name,id,salary) values('ad',43,440);
insert into Employee(name,id,salary) values('sam',40,550);

I executed the following query

 DECLARE
 employee_record Employee%ROWTYPE;
 BEGIN
 select * into employee_record from Employee where id>90;
 dbms_output.put_line(employee_record.name||' '||employee_record.id||' '||employee_record.salary);
 END;
 /

I am getting the following output

Record Count: 0; Execution Time: 2ms 

It should print the values present in the employee record, right? Is there something wrong in my sql query or some problem with sql fiddle not able to display dbms_output?

Answer 1

You need to emulate dbms_output.put_line :)

Schema:

create table Employee(
  name varchar2(100),
  id integer, 
  salary integer,
  PRIMARY KEY(id)
);

insert into Employee(name,id,salary) values('sa',94,100);
insert into Employee(name,id,salary) values('pr',88,150);
insert into Employee(name,id,salary) values('ji',33,900);
insert into Employee(name,id,salary) values('na',24,880);
insert into Employee(name,id,salary) values('po',65,770);
insert into Employee(name,id,salary) values('ri',69,910);
insert into Employee(name,id,salary) values('uj',12,650);
insert into Employee(name,id,salary) values('ad',43,440);
insert into Employee(name,id,salary) values('sam',40,550);

create table dbmsoutput (
  pos int,
  mes varchar2(4000)
);

---

SQL:

DECLARE
  employee_record Employee%ROWTYPE;
  procedure put_line(p_mes in varchar2) is
     v_pos int; 
  begin  
     select count(0) into v_pos from dbmsoutput;  
     insert into dbmsoutput (pos, mes) values (v_pos, p_mes);
  end;
BEGIN
 put_line('Hello!  This code is powered by dbms_output emulator :)');
 -- Your code here:
 select * into employee_record from Employee where id>90;
 put_line(employee_record.name||' '||employee_record.id||' '||employee_record.salary);
 --
 put_line('Bye!');
END;
/


SELECT mes FROM dbmsoutput order by pos

fiddle

Answer 2

Just as a curiosity really, you can get limited dbms_output results from SQL Fiddle, but you need a function to extract the buffered lines and return them in a form you can select. This uses a pipelined table:

create type t_lines as table of varchar2(4000)
/

create or replace function get_lines
return t_lines pipelined is
  lines dbms_output.chararr;
  numlines integer;
begin
  numlines := 999;
  dbms_output.get_lines(lines, numlines);
  if numlines > 0 then
    for i in 1..numlines loop
      pipe row (lines(i));
    end loop;
  else
    pipe row ('No data');
  end if;
end;
/

And then, after whatever you have issuing dbms_output.put_line calls:

select * from table(get_lines);

Demo. And see the dbms_output documentation to see what its get_lines procedure does and how it relates to your put_lines calls.

But just because you can do something, doesn't mean you necessarily should. This is awkward and doesn't scale, but neither does trying to learn PL/SQL through SQL Fiddle really.

I'd second Ben's recommendation to get your own database to play with, but I'd suggest you look at a pre-built VM image you can run in VirtualBox, which saves you a lot of time in the setup - you don't have to worry about how to install the Oracle software or create and configure a database, it's just ready to use, and you can throw it away or easily start again if things go wrong.