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?
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With