• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

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

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
wasabi3689
Asked:
wasabi3689
  • 5
  • 3
1 Solution
 
Walter RitzelSenior Software EngineerCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
or do you have a PL/SQL TABLE of records?

I didn't see that in the code you posted.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
wasabi3689Author Commented:
xx_product is a table
0
 
slightwv (䄆 Netminder) Commented:
Physical Oracle table or PL/SQL table?  Please post the definition.
0
 
wasabi3689Author Commented:
it's a Physical Oracle table
0
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
wasabi3689Author Commented:
This answer provides useful sample code to me
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now