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?

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. — Ben, Oct 02 '13 at 15:46
Thanks a lot. Is there anyway I can verify my answer in sql fiddle? — user2133404, Oct 02 '13 at 15:48
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: http://sqlfiddle.com/#!4/7417b5/7 — Przemyslaw Kruglej, Oct 02 '13 at 15:54
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): http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html — Przemyslaw Kruglej, Oct 02 '13 at 15:58
It's worth mentioning that SQL Fiddle runs your queries through JDBC calls. I don't believe dbms.output works through JDBC - http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:45027262935845 — Jake Feasel, Oct 02 '13 at 18:15

score 12 · Accepted Answer · answered Oct 02 '13 at 17:25

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

score 5 · Answer 2 · answered Oct 02 '13 at 17:25

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.

SQL Fiddle Output Error

2 Answers2

Linked