We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

Medium Priority
453 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)





Comment
Watch Question

Walter RitzelSenior Software Engineer
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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

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

Author

Commented:
xx_product is a table
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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

Author

Commented:
it's a Physical Oracle table
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
This answer provides useful sample code to me
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.