Home » SQL & PL/SQL » SQL & PL/SQL » Record Types (10g )
Record Types [message #579827] Fri, 15 March 2013 15:37 Go to next message
jay_cooldude
Messages: 50
Registered: March 2013
Location: Hyd
Member
declare 
type v1 is record(u emp%rowtype,x dept%rowtype);
v v1;
begin
select * into v.u from emp where  sal=800 ;
select * into v.x from dept where deptno =v.u.deptno;
dbms_output.put_line(v.u.empno||' , '||v.u.ename||' , '||v.u.job||' , '||v.u.sal||' , '||v.u.deptno||' , '||v.x.dname||' , '||v.x.loc); 
end;



Can i merge the two select statments into single statement
Re: Record Types [message #579829 is a reply to message #579827] Fri, 15 March 2013 16:12 Go to previous messageGo to next message
Michel Cadot
Messages: 57606
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes but you have to list all columns and not use "*" which is, anyway, a very bad idea.

Regards
Michel
Re: Record Types [message #579831 is a reply to message #579829] Fri, 15 March 2013 16:40 Go to previous messageGo to next message
jay_cooldude
Messages: 50
Registered: March 2013
Location: Hyd
Member
Hi Michel,
Can tell me how can you assign values of group of columns for two row type columns .

Thanks & Regards,

JAY.

[EDITED by LF: removed awkward & unnecessary quote of the whole previous message]

[Updated on: Sat, 16 March 2013 01:55] by Moderator

Report message to a moderator

Re: Record Types [message #579850 is a reply to message #579831] Sat, 16 March 2013 01:34 Go to previous messageGo to next message
Michel Cadot
Messages: 57606
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot.
You have to assign each column to each field of your record.

Regards
Michel
Re: Record Types [message #579854 is a reply to message #579831] Sat, 16 March 2013 01:47 Go to previous messageGo to next message
sss111ind
Messages: 427
Registered: April 2012
Location: India
Senior Member

Though you can't assign rowtype columns directly becuase it is directly not supported yet.You have to individually.As follows
suppose if you are doing like this you will be getting following error.

select e.*,d.* into v.u ,v.x from emp e,dept d where d.deptno=e.deptno and  sal=800 ;
PLS-00494: coercion into multiple record targets not supported 
Cause: The INTO list of a SELECT or FETCH specified more than a single record- type target and the column types required coercion into a record to match the INTO list. This is type checked as correct, but not yet supported.
Action: Create a new record type to hold all of the column types or code the SELECT with an INTO target for every source column. 
 

declare 
--type v1 is record(u emp%rowtype,x dept%rowtype);
type v1 is record(empno emp.empno%type,ename emp.ename%type,job emp.job%type,sal emp.sal%type,deptno emp.deptno%type,dname dept.dname%type,loc dept.loc%type);
v v1;
begin
select empno,ename,job,sal,e.deptno,dname,loc into v.empno,v.ename,v.job,v.sal,v.deptno,v.dname,v.loc from emp e ,dept d where e.deptno=d.deptno and  sal=800 ;
--select * into v.x from dept where deptno =v.u.deptno;
dbms_output.put_line(v.empno||' , '||v.ename||' , '||v.job||' , '||v.sal||' , '||v.deptno||' , '||v.dname||' , '||v.loc); 
end;

[Updated on: Sat, 16 March 2013 01:48]

Report message to a moderator

Re: Record Types [message #579861 is a reply to message #579854] Sat, 16 March 2013 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 57606
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks to confirm what I said although this is not exactly what I meant which was (keeping the same type as OP):
SQL> declare 
  2   type v1 is record(u emp%rowtype,x dept%rowtype);
  3  v v1;
  4  begin
  5   select empno,ename,job,sal,e.deptno,dname,loc 
  6   into v.u.empno,v.u.ename,v.u.job,v.u.sal,v.x.deptno,v.x.dname,v.x.loc 
  7   from emp e ,dept d where e.deptno=d.deptno and  sal=800 ;
  8   dbms_output.put_line(v.u.empno||' , '||v.u.ename||' , '||v.u.job||' , '||v.u.sal||' , '
  9                        ||v.u.deptno||' , '||v.x.dname||' , '||v.x.loc);
 10  end;
 11  /
7369 , SMITH , CLERK , 800 ,  , RESEARCH , DALLAS

PL/SQL procedure successfully completed.

Regards
Michel

Re: Record Types [message #579989 is a reply to message #579827] Mon, 18 March 2013 11:37 Go to previous messageGo to next message
jay_cooldude
Messages: 50
Registered: March 2013
Location: Hyd
Member
declare
type v1 is record(u emp%rowtype,x dept%rowtype);
v v1;
begin
        select empno,ename,job,mgr,hiredate,sal,comm,e.deptno,dname,loc into v.u.empno,v.u.ename,v.u.job,v.u.mgr,v.u.hiredate,v.u.sal,v.u.comm,v.u.deptno,v.x.dname,v.x.loc from emp e dept d where e.deptno=d.deptno and e.empno=7566;
       dbms_output.put_line( v.u.empno||' , '||v.u.ename||' , '||v.u.job||' , '||v.u.mgr||' , '||v.u.hiredate||' , '||v.u.sal||' , '||v.u.comm||' , '||v.u.deptno||' , '||v.x.dname||' , '||v.x.loc); 
end;
 



Error at line 1
ORA-06550: line 5, column 184:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 5, column 9:
PL/SQL: SQL Statement ignored
Re: Record Types [message #579990 is a reply to message #579861] Mon, 18 March 2013 11:37 Go to previous message
jay_cooldude
Messages: 50
Registered: March 2013
Location: Hyd
Member
Thanks Mr Michel,
Previous Topic: populating the collection of date type with date values of all saturday 's of one month -- Help
Next Topic: pivot query
Goto Forum:
  


Current Time: Thu Apr 17 08:57:46 CDT 2014

Total time taken to generate the page: 0.10000 seconds