Home » SQL & PL/SQL » SQL & PL/SQL » Help in using record type and object type (Oracle 11g)
Help in using record type and object type [message #593494] Sat, 17 August 2013 11:34 Go to next message
ramya_162
Messages: 96
Registered: August 2013
Location: Banglore
Member
Hi Experts,

I am new to object types and record types.

I want to return the output of this query using one OUT parameter
from the procedure using RECORD type or OBJECT type.

SELECT empno,ename,sal FROM emp WHERE deptno=30;


Let us assume the query is returning 50 records.

I want to send those 50 records to OUT parameter using record type or object type.

Please provide the for the requirement code using RECORD TYPE and OBJECT TYPE separately.

Your earliest response is appreciated.

Thanks in advance.
Re: Help in using record type and object type [message #593495 is a reply to message #593494] Sat, 17 August 2013 11:47 Go to previous messageGo to next message
BlackSwan
Messages: 22498
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

record type is a scalar; which means it can hole only a single row.
So by definition record type can not return 50 rows (not records) ever.

The way to return multiple rows is via a REFCURSOR.
Re: Help in using record type and object type [message #593496 is a reply to message #593494] Sat, 17 August 2013 11:48 Go to previous messageGo to next message
Michel Cadot
Messages: 58558
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is the wrong way, you must return a ref cursor.

Regards
Michel
Re: Help in using record type and object type [message #593497 is a reply to message #593496] Sat, 17 August 2013 12:00 Go to previous messageGo to next message
ramya_162
Messages: 96
Registered: August 2013
Location: Banglore
Member
Thanks for your response.

Like this can we record type?

TYPE t_record IS RECORD
empno emp.empno%TYPE,
ename emp.ename%TYPE,
sal emp.sal%TYPE);

TYPE tt_type IS TABLE OF t_record%TYPE;

result tt_type;

and using this as OUT parameter.

Can we use object type?

Apart from refcursor any other process.

please help me.

Thanks.
Re: Help in using record type and object type [message #593498 is a reply to message #593497] Sat, 17 August 2013 12:04 Go to previous messageGo to next message
BlackSwan
Messages: 22498
Registered: January 2009
Senior Member
>Can we use object type?
yes, you can use object type.
You can also poke yourself in the eye with a sharp pencil; but neither action is recommended.

Usually OBJECT TYPE only adds complexity & complications without any measurable advantage.

Re: Help in using record type and object type [message #593499 is a reply to message #593497] Sat, 17 August 2013 12:06 Go to previous messageGo to next message
Michel Cadot
Messages: 58558
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you want to use a screwdriver to drive a nail?
You don't want?
So why do you want other things when you have THE thing: a REF CURSOR?

Regards
Michel
Re: Help in using record type and object type [message #593500 is a reply to message #593499] Sat, 17 August 2013 12:26 Go to previous messageGo to next message
ramya_162
Messages: 96
Registered: August 2013
Location: Banglore
Member
Once I get the records from the query I want to do some calculations and
send to OUT parameter.

Can you please provide the code with using object type.

Thanks.
Re: Help in using record type and object type [message #593501 is a reply to message #593500] Sat, 17 August 2013 12:29 Go to previous messageGo to next message
BlackSwan
Messages: 22498
Registered: January 2009
Senior Member
>Can you please provide the code with using object type.
NO!
OBJECT TYPE is wrong tool for this task!
Re: Help in using record type and object type [message #593502 is a reply to message #593500] Sat, 17 August 2013 12:30 Go to previous messageGo to next message
ramya_162
Messages: 96
Registered: August 2013
Location: Banglore
Member
Can we use any collection type?

Thanks.
Re: Help in using record type and object type [message #593503 is a reply to message #593502] Sat, 17 August 2013 12:31 Go to previous messageGo to next message
BlackSwan
Messages: 22498
Registered: January 2009
Senior Member
NEVER use PL/SQL when plain SQL can accomplish the same requirements!
Re: Help in using record type and object type [message #593505 is a reply to message #593502] Sat, 17 August 2013 13:19 Go to previous messageGo to next message
ramya_162
Messages: 96
Registered: August 2013
Location: Banglore
Member
Actually I want to know.

How can we return multiple values through OUT parameter
in a procedure with out using ref cursor.

Can you please provide the code which one is good except ref cursor.

Thanks.
Re: Help in using record type and object type [message #593506 is a reply to message #593505] Sat, 17 August 2013 13:33 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1956
Registered: January 2010
Senior Member
ramya_162 wrote on Sat, 17 August 2013 14:19
How can we return multiple values through OUT parameter
in a procedure with out using ref cursor.


First you need to meditate over the difference between value, record, object and cursor. Seems you confuse all four. If you want to return multiple values or multiple records or multiple objects you could use collections.

SY.
Re: Help in using record type and object type [message #593507 is a reply to message #593505] Sat, 17 August 2013 13:45 Go to previous messageGo to next message
Michel Cadot
Messages: 58558
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
2 good books for you with examples:
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Re: Help in using record type and object type [message #593508 is a reply to message #593505] Sat, 17 August 2013 14:01 Go to previous messageGo to next message
BlackSwan
Messages: 22498
Registered: January 2009
Senior Member
ramya_162 wrote on Sat, 17 August 2013 11:19
Actually I want to know.

How can we return multiple values through OUT parameter
in a procedure with out using ref cursor.

Can you please provide the code which one is good except ref cursor.

Thanks.



you need to do your own homework assignment!
Re: Help in using record type and object type [message #593524 is a reply to message #593497] Sun, 18 August 2013 03:37 Go to previous messageGo to next message
ramya_162
Messages: 96
Registered: August 2013
Location: Banglore
Member
Hi All,

I have tried this.But it ising not work
CREATE OR REPLACE PACKAGE maultiplevalues_pkg
IS
TYPE t_record IS RECORD
(empno emp.empno%TYPE,
ename emp.ename%TYPE,
sal emp.sal%TYPE);
V_RECORD t_record;
TYPE t_type IS TABLE OF V_RECORD%TYPE;
PROCEDURE maultiplevalues_pROC(p_deptno IN emp.deptno%TYPE,
dept_result OUT t_type);
END;

CREATE OR REPLACE PACKAGE body maultiplevalues_pkg
IS
PROCEDURE maultiplevalues_pROC(p_deptno IN emp.deptno%TYPE,
dept_result OUT t_type)
is
begin
dept_result :=t_type();
for I in(
select EMPNO,ENAME,SAL from EMP WHERE deptno=p_deptno
)
LOOP
dept_result.extend;
dept_result(i).empno :=i.empno;
dept_result(i).ename :=i.ename;
dept_result(i).sal :=i.sal;
END LOOP;
END;
END;


Please help me OUT return multiple values through single OUT variable in a procedure.

Thanks.

Re: Help in using record type and object type [message #593525 is a reply to message #593524] Sun, 18 August 2013 03:54 Go to previous messageGo to next message
Michel Cadot
Messages: 58558
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why should we help you to do something the wrong way?
Read the books I pointed you to and do it yourself.

Regards
Michel
Re: Help in using record type and object type [message #593527 is a reply to message #593525] Sun, 18 August 2013 04:05 Go to previous messageGo to next message
ramya_162
Messages: 96
Registered: August 2013
Location: Banglore
Member
Hi Michel,

Sorry in doing wrong way.

I have gone through the documents still I am not able to find how to
multiple values through single OUT variable in a procedure.

Can you please kindly provide one example.

That is very helpful to me please.

Thanks.
Re: Help in using record type and object type [message #593531 is a reply to message #593527] Sun, 18 August 2013 04:47 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1817
Registered: May 2013
Location: World Wide on the Web
Senior Member
ramya_162 wrote on Sun, 18 August 2013 14:35

I have gone through the documents still I am not able to find how to
multiple values through single OUT variable in a procedure.


You seem to not properly follow what Michel and others have suggested you.

Anyway, go through using-ref-cursors-to-return-recordsets
Re: Help in using record type and object type [message #593538 is a reply to message #593527] Sun, 18 August 2013 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 58558
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can you please kindly provide one example.


Please kindly read the books I told you there are MANY examples.

Regards
Michel
Re: Help in using record type and object type [message #593541 is a reply to message #593538] Sun, 18 August 2013 06:47 Go to previous messageGo to next message
ramya_162
Messages: 96
Registered: August 2013
Location: Banglore
Member
Hi,

Thanks for your reply.
However I know ref cursor.
Apart from ref cursor I need another method.
Please help me.

Thanks.
Re: Help in using record type and object type [message #593543 is a reply to message #593541] Sun, 18 August 2013 06:54 Go to previous message
Lalit Kumar B
Messages: 1817
Registered: May 2013
Location: World Wide on the Web
Senior Member
The question is WHY? What problem are you facing with ref cursor? Have a look at this OTN thread where a poster had a similar question, look at the replies https://forums.oracle.com/thread/1775900
Previous Topic: doubt in writing query
Next Topic: Query to find number of NULLs for each record.
Goto Forum:
  


Current Time: Fri Jul 25 08:46:03 CDT 2014

Total time taken to generate the page: 0.11364 seconds