Home » SQL & PL/SQL » SQL & PL/SQL » %rowtype (oracle 9i)
%rowtype [message #357848] Thu, 06 November 2008 22:48 Go to next message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
Hi All,
can u help me to solve my prob?
when i write code as:

declare emp_rec employees%rowtype;
begin
select * into emp_rec
from employees
where empno=7369;
end;

when i compile this code it says pl/sql procedure completed succesfully.

but how can i execute it?

Thanks
Re: %rowtype [message #357849 is a reply to message #357848] Thu, 06 November 2008 22:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
An anonymous block is executed when it is compiled, so if you got "pl/sql procedure completed succesfully", you executed it.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: %rowtype [message #357850 is a reply to message #357848] Thu, 06 November 2008 22:58 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


SQL> declare
  2  emp_rec emp%rowtype;
  3  begin
  4  select * into emp_rec
  5  from emp
  6  where empno=7369;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>


ie. it is sucesfully completed or executed.
now what do you want to do with this code?

if you want you can try with DBMS_OUTPUT to display the values.

Smile
Rajuvan.
Re: %rowtype [message #357851 is a reply to message #357849] Thu, 06 November 2008 22:58 Go to previous messageGo to next message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
Thanks for your Quick response Michel,
but i m nt getting my o/p. so in this case how can i get o/p?
Thanks
Re: %rowtype [message #357852 is a reply to message #357851] Thu, 06 November 2008 23:00 Go to previous messageGo to next message
Srinandini
Messages: 13
Registered: November 2008
Junior Member
Check if you have enabled server output for your session

If you are using SQL*plus

set serveroutput on;

Sri
Re: %rowtype [message #357856 is a reply to message #357850] Thu, 06 November 2008 23:10 Go to previous messageGo to next message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
Hi Rajuvan
i want to display my o/p, it should be a whole row whose employee id is 7369. my server output is already ON. i m using isql *plus. which stored package of DBMS_OUTPUT should i use?

thanks.
Re: %rowtype [message #357859 is a reply to message #357850] Thu, 06 November 2008 23:19 Go to previous messageGo to next message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
i have tried DBMS_OUTOUT.PUT_LINE(employees.ename), this way i m able to print only employee name, whose id is 7369. but my need is to display whole row. bcz i want to use %rowtype properly.

Thanks
Re: %rowtype [message #357860 is a reply to message #357848] Thu, 06 November 2008 23:24 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

DBMS_OUTPUT.PUT_LINE

Smile
Rajuvan.
Re: %rowtype [message #357864 is a reply to message #357859] Thu, 06 November 2008 23:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you concatenate the result in dbms_output.put_line.
You have to do it yourself, PL/SQL does not display anything by itself.
Or you can use a ref cursor and print it in SQL*Plus, but this is an entire different thing.

Regards
Michel
Re: %rowtype [message #357866 is a reply to message #357848] Thu, 06 November 2008 23:40 Go to previous messageGo to next message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
yes Michel
i think i should use cursor with rowtype. i think this will work.

will try Hope it will work:)
Thanks
Re: %rowtype [message #357869 is a reply to message #357866] Thu, 06 November 2008 23:51 Go to previous messageGo to next message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
SOLUTION:

DECLARE
CURSOR csr_1 IS
SELECT ename, empno
FROM emp;
org_rec csr_1%ROWTYPE;
BEGIN
OPEN csr_1;
LOOP
FETCH csr_1 INTO org_rec;
EXIT WHEN csr_1%NOTFOUND;
dbms_output.put_line(org_rec.ename);
dbms_output.put_line(org_rec.empno);


END LOOP;
CLOSE csr_1;
END;

Re: %rowtype [message #357871 is a reply to message #357848] Fri, 07 November 2008 00:00 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Refcursor is different from cursor with rowtype and your solution doesn't display the whole row.

SQL> variable r refcursor
SQL> set autoprint on
SQL> begin
  2  OPEN :r for select * from emp
  3                      where empno=7369;
  4  end;
  5  /

PL/SQL procedure successfully completed.


 EMPNO ENAME      JOB          MGR HIREDATE         SAL       COMM     DEPTNO
------ ---------- --------- ------ --------- ---------- ---------- ----------
  7369 SMITH      CLERK       7902 17-DEC-80        800                    20

SQL>


Smile
Rajuvan.

[Updated on: Fri, 07 November 2008 00:01]

Report message to a moderator

Previous Topic: cursor
Next Topic: Validating data
Goto Forum:
  


Current Time: Sat Dec 03 20:42:11 CST 2016

Total time taken to generate the page: 0.33473 seconds