?
Solved

how to assign an oracle table each values to another table values in an easy way

Posted on 2011-04-21
9
Medium Priority
?
444 Views
Last Modified: 2013-12-07
I declare data type like below in a package A

TYPE product_type is record (
product_id     xx_product.product_id%TYPE
description     xx_product.description%TYPE
....
);


in another package B also declare the same thing in it

TYPE product_type is record (
product_id     xx_product.product_id%TYPE
description     xx_product.description%TYPE
....
);

In the B pkg, I have the same as below

TYPE product_type is record (
product_id     xx_product.product_id%TYPE
description     xx_product.description%TYPE
....
);


and  I create a procedure to


 PROCEDURE create_product (
      p_pd_rec               IN       B.product_type,
      p_commit                 IN       VARCHAR2,
      p_return_status          OUT      NUMBER,
      p_error_msg              OUT      VARCHAR2
)
IS
....
v_pd_rec          A.product_type;
...

        v_pd_rec.product_id := p_pd_rec.product_ID;  
        .....

product_type is a long table with a lot of fields. I have to assign each one from p-pd_rec to v_pd_rec
My questions is if there is a easy way to assign all like

   v_pd_rec := p_pd_rec ( but it doesn't work)





0
Comment
Question by:wasabi3689
  • 5
  • 3
9 Comments
 
LVL 16

Expert Comment

by:Walter Ritzel
ID: 35441897
as per your example, it is not quite clear what you need.
But if you have 2 tables that are identical, to transfer information from A to B, you should do something like this:

insert into B select * from A;

Again, if they are identical.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35441989
If I understand what you want, it works for me.  I can assign a record to a record.

See below (tested using 10.2.0.3)
declare

	type product_type is record(
		product_id char(1),
		description char(1)
		);

	recA product_type ;
	recB product_type ;

begin
	recA.product_id := 'a';
	recA.description := 'b';


	recB := recA;

	dbms_output.put_line('ID: ' || recB.product_id);
	dbms_output.put_line('Desc: ' || recB.description);
end;
/

Open in new window

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35442003
or do you have a PL/SQL TABLE of records?

I didn't see that in the code you posted.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:wasabi3689
ID: 35442061
xx_product is a table
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35442073
Physical Oracle table or PL/SQL table?  Please post the definition.
0
 

Author Comment

by:wasabi3689
ID: 35442132
it's a Physical Oracle table
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35442207
So, you have a PL/SQL RECORD that mirrors the Oracle table and you want to take the values of that record and insert a new row into the table without coding each column value?
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 375 total points
ID: 35442230
Try this
drop table tab1 purge;
create table tab1 (product_id char(1), description char(1));

declare

	type product_type is record(
		product_id tab1.product_id%TYPE,
		description  tab1.description%TYPE
		);

	recA product_type ;

begin
	recA.product_id := 'a';
	recA.description := 'b';


	insert into tab1 values recA;
end;
/

select * from tab1;

Open in new window

0
 

Author Closing Comment

by:wasabi3689
ID: 35695473
This answer provides useful sample code to me
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question