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?

Buran

No, SQL Fiddle will not print out anything because it hasn't been coded that way. If you download Oracle (there's a free version) and use a different client to print it out then you will get an output. – Knepper

Thanks a lot. Is there anyway I can verify my answer in sql fiddle? – Buran

Actually you haven't executed a query, but a PL/SQL block of code :) To answer your question, you just should run your query to get the results: SELECT * FROM Employee WHERE id > 90; check this: sqlfiddle.com/#!4/7417b5/7 – Stop

I am trying to learn coding in PL/SQL :) – Buran

Then, as Ben has suggested, you should download an Oracle database server, which is free of charge, you may download it here (I recommend 11g R2, but you may as well start with the newest version, which is 12c): oracle.com/technetwork/database/enterprise-edition/downloads/… – Stop

It's worth mentioning that SQL Fiddle runs your queries through JDBC calls. I don't believe dbms.output works through JDBC - asktom.oracle.com/pls/asktom/… – Schlock

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

Cirsoid

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.

Tweak