Home » SQL & PL/SQL » SQL & PL/SQL » %Rowtype
icon5.gif  %Rowtype [message #248160] Thu, 28 June 2007 03:36 Go to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
Hi all,

Please help me out to this questions..

I have a similar requirement. But in my case, the select query has multiple tables, so I cannot use %ROWTYPE.

Also, I have not used 'sys_refcursor' , can you tell me whats the use of this?
Re: %Rowtype [message #248173 is a reply to message #248160] Thu, 28 June 2007 04:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have a similar requirement.

Similar to what?

Incomprehensible post.

Regards
Michel
Re: %Rowtype [message #248177 is a reply to message #248173] Thu, 28 June 2007 04:06 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Post your code and clearly explain what you are in need of.

Re: %Rowtype [message #248178 is a reply to message #248160] Thu, 28 June 2007 04:07 Go to previous messageGo to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
Sorry Michel

You looking the Orafaq issues very nice...

The select query has multiple tables, so I cannot use %ROWTYPE.

Also, I have not used 'sys_refcursor' , can you tell me whats the use of this?

Now ok Michel Razz
Re: %Rowtype [message #248180 is a reply to message #248178] Thu, 28 June 2007 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Answer caliguardo's post.

Regards
Michel
Re: %Rowtype [message #248182 is a reply to message #248180] Thu, 28 June 2007 04:14 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Cursors are pointers used to fetch rows from a result set.
One can think of a cursor as a data structure that describes 
the results returned from a SQL SELECT statement. 
One of the variables in this structure is a pointer to the 
next record to be fetched from the query results. 



--Yash

[Updated on: Thu, 28 June 2007 04:25] by Moderator

Report message to a moderator

Re: %Rowtype [message #248184 is a reply to message #248182] Thu, 28 June 2007 04:15 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
sorry michel i forgot to break the line
Re: %Rowtype [message #248190 is a reply to message #248160] Thu, 28 June 2007 04:25 Go to previous messageGo to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
Hi,

Generally I am asking this questions,pls dont mistake me

why it is not possible to define the %rowtype when i used multiple tables in my select query?If so, pls tell
Re: %Rowtype [message #248191 is a reply to message #248182] Thu, 28 June 2007 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this explain what you want to do?

Regards
Michel
Re: %Rowtype [message #248194 is a reply to message #248191] Thu, 28 June 2007 04:29 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

It's like a Child's play. No offence intended. Razz
Re: %Rowtype [message #248195 is a reply to message #248190] Thu, 28 June 2007 04:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
why it is not possible to define the %rowtype when i used multiple tables in my select query?

Maybe you can if you posted what you are trying to do.

Regards
Michel
Re: %Rowtype [message #248201 is a reply to message #248195] Thu, 28 June 2007 04:38 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Yes you can use %ROWTYPE when you are selecting from multiple tables. Take this script:
SET SERVEROUT ON

DECLARE
  -- A cursor selecting from 2 tables.
  CURSOR my_cursor 
  IS
  SELECT UPPER(e.last_name)||', '||e.first_name AS employee_name
       , d.department_name
  FROM   departments d
     ,   employees   e
  WHERE  d.department_id = e.department_id;
  
  -- A record variable of %ROWTYPE (based on the above cursor)
  my_record my_cursor%ROWTYPE;
  
BEGIN
  OPEN my_cursor;
  FETCH my_cursor INTO my_record;  
  CLOSE my_cursor;
  
  dbms_output.put_line(  'Employee: '     ||my_record.employee_name
                       ||' of department '||my_record.department_name
                       ||' is fetched.'
                      );
END;
/


When executed it produces this (on the standard HR demo schema):
SQL> @orafaq
Employee: KING, Steven of department Executive is fetched.

PL/SQL procedure successfully completed.

MHE
Re: %Rowtype [message #248206 is a reply to message #248160] Thu, 28 June 2007 04:53 Go to previous messageGo to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
Than you Maher,

Very Nice reply...Hmmm I think michel is in angry with me..thts why he send the answer for me
Re: %Rowtype [message #248210 is a reply to message #248206] Thu, 28 June 2007 05:13 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I don't think he's angry but there are a only handful of people actually answering questions. Michel is one of them. You could have tested this for yourself if you bothered to look in those Oracle manuals or tested yourself. That's what I do often enough: look it up in the manuals and test it. It can be frustrating if you see that 90% of the questions asked here at OraFAQ could have been resolved by the original poster himself.

MHE
Re: %Rowtype [message #248233 is a reply to message #248206] Thu, 28 June 2007 06:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maarten is right I'm not angry.
I'm just trying to make you clearly explain your problem.
This helps you to find the solution as often when one can clearly explain the problem then the solution is obvious.

Btw, it you just put %ROWTYPE in the Oracle search field, you'd get the answer.

Regards
Michel
Re: %Rowtype [message #260962 is a reply to message #248233] Tue, 21 August 2007 06:36 Go to previous messageGo to next message
QuestionMaster
Messages: 3
Registered: August 2007
Location: Earth
Junior Member
If one tries to this in a function and wants the %rowtype as the return value, how can it be done?

Because the cursur will only be declared after the return value's type is declared.
Re: %Rowtype [message #260964 is a reply to message #260962] Tue, 21 August 2007 06:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If one tries to this in a function and wants the %rowtype as the return value, how can it be done?

See Maarten's post above.
Quote:
Because the cursur will only be declared after the return value's type is declared.

What does this mean?
You declare the cursor then the variable.

Regards
Michel
Re: %Rowtype [message #260973 is a reply to message #260962] Tue, 21 August 2007 07:01 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you want to have a function return a user defined type, then this type needs to either be created as an SQL type or to be visible in a package specification.
Eg:
CREATE OR REPLACE PACKAGE user_types AS

  type cur_return is record (date_1  date
                            ,date_2  date);

END user_types;
/

CREATE OR REPLACE FUNCTION sample_func RETURN user_types.cur_return IS

  cursor c_1 return user_types.cur_return is
    SELECT sysdate,add_months(sysdate,1)
    FROM   dual;
    
  r_return   user_types.cur_return;
BEGIN
  open c_1;
  fetch c_1 into r_return;
  close c_1;
  
  return r_return;
END sample_func;
/

declare
  r_Rec  user_types.cur_return;
begin
  r_rec := sample_func;
  
  dbms_output.put_line('Date_1 '||to_char(r_rec.date_1,'dd-mm-yyyy hh24:mi:ss'));
  dbms_output.put_line('Date_2 '||to_char(r_rec.date_2,'dd-mm-yyyy hh24:mi:ss'));  
end;
/

Date_1 21-08-2007 13:00:15
Date_2 21-09-2007 13:00:15
Previous Topic: Comparing comma seperated variable in IN clause
Next Topic: updating date fields is there a better way using regular expressions?
Goto Forum:
  


Current Time: Mon Dec 05 21:04:43 CST 2016

Total time taken to generate the page: 0.18718 seconds