asked on

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)

Walter Ritzel

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.

slightwv (䄆 Netminder)

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

slightwv (䄆 Netminder)

or do you have a PL/SQL TABLE of records?

I didn't see that in the code you posted.

wasabi3689

ASKER

xx_product is a table

slightwv (䄆 Netminder)

Physical Oracle table or PL/SQL table? Please post the definition.

wasabi3689

ASKER

it's a Physical Oracle table

slightwv (䄆 Netminder)

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?

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wasabi3689

ASKER

This answer provides useful sample code to me