Append Tablename dynamically for Insert Statements

User_FRTCM

Hi,

I need a hand on this issue as it is critical for the phase we are in of our project.

Scenario :-

We have about 200 products which are having 10 tables each. Each Product is having the same name of tables but the columns differ for each product.

For example,

My Product 'Alfa' has a table XYZ which is having columns as 1,2,3,4,5,6,7,8,9,10 while the other product 'Beta' has table XYZ with columns as 1,2,3,4,5,11,12,13,14,15.

There are other tables also as XYZ like PQR, UVW etc ( 10 for each product ) which are common for all 200 products but the columns inside these tables are different.

I hope am clear with the scenario.

Current Architecture :-

Source (Oracle) - VB Codes - Destination (Access MDBs - Product Wise)

Each Access MDB is having 10 tables for each product coz of which same tablename have been maintained across all the products.

Note: The Tables are same for all products, its only the columns that are changing for each product.

Architecture After Migration :-

Source (Oracle) - PL/SQL Package + Procedures - Destination (Oracle - Tables in the form of <Product>_<tablename>)

I need help on the below :-

Insert into <Product>_<tablename> values () where Product has to be appended dynamically for each product.

I want to have Product ID as a changing dynamic value like

Insert into Alfa_XYZ values();

Insert into Beta_XYZ values();

Kindly suggest me a solution, I am trying from my end too. It would be appreciable if you could share a sample query/procedure/function to do this.

Thanks

Pablolee

RIGHT EVERYONE. DROP WHAT YOUR DOING! THIS POSTER HAS AN URGENT REQUIREMENT. C'MON, NONE OF YOU HAVE JOBS WHICH HAVE HIGHER PRIORITY THAN THIS POSTER'S REQUIREMENTS. HOP TO IT!

Karthick2003

I need help on the below :-
Insert into <Product>_<tablename> values ();

I want to have Product ID as a changing dynamic value like
Insert into Alfa_XYZ values();
Insert into Beta_XYZ values();

I don't understand the question here. What do you want to do? Any way each table will have different set of columns. So how you are going to supply the VALUES part?

Please read and explain your requirement in a better understandable way.

Nimish Garg

If you are migrating the tables manually, move them in oracle per product wise, and rename them by adding prefix using

rename tab_old to tab_new;

you may write a pl/sql block for renaming tables.

Stew Ashton

Right now, is the source data in Access or Oracle? If it is in Access, you should ask the question in an Access forum.

Will the target tables have the same structure? In other words, will Alpha_XYZ have the same structure as Beta_XYZ? If so, I recommend having one XYZ table with a product_id column, then maybe partition by product_id. That way you divide by 200 the code you have to write and the parsing Oracle will have to do.

You shouldn't have to create INSERT with VALUES statements at all. I suggest you look at the Migration tools in Oracle SQL Developer:

Oracle® SQL Developer Supplementary Information for Microsoft Access Migrations Release 3.0

Paul Horth

It might be worthwhile making each product a schema in Oracle and having the corresponding tables in each schema.

You don't need dynamic SQL - as Stew said, use the Migration tools.

Finally, if you have a critical urgent requirement for your project - get paid support, don't go to a forum of volunteers

where it is considered rude to think your requirement is more important than others.

User_FRTCM

Hi Stew,

Thanks for your reply. The Table structure for all products are different.

For example, the table Alpha_XYZ will have different columns than Beta_XYZ.

The Source data is residing in Oracle, not MS Access.

I want the Insert statement to work like "Insert into <Product>_<tablename> values ();" where I can keep the <Product> as a dynamically changing value.

User_FRTCM

Apologies , Dint mean it that way !!

User_FRTCM

Karthik, I am not so worried about getting the Values part, I have just written that as per the normal syntax. I wish to know how to change the table name dynamically.

Am sorry if I could not make you understand the situation.

Thanks Anyways

User_FRTCM

Thanks for your suggestion Nimish, but its the codes also that written in VB which are getting migrated to PL SQL procedures.

I want to append the tablenames dynamically while doing inserts, what thoughts you have about putting up dynamic sql here ?

Thanks

Sergei Krasnoslobodtsev

It isn't absolutely clear that you want to make....

with a certain probability it is possible to assume that it is better for you to use dynamic sql/plsql:

create or replace procedure ins_dsql (p_tab_dest user_tables.TABLE_NAME%type,p_tab_src user_tables.TABLE_NAME%type)
as
  v_cols_src varchar2(400);
  v_cols_dst varchar2(400);
  v_sql varchar2(2000);
begin

     select
          listagg(u.COLUMN_NAME,',') within group (order by u.COLUMN_ID)
     into
         v_cols_src
     from user_tab_cols u where u.TABLE_NAME = p_tab_src;
     select
          listagg(u.COLUMN_NAME,',') within group (order by u.COLUMN_ID)
     into
         v_cols_dst
     from user_tab_cols u where u.TABLE_NAME = p_tab_src;

     v_sql := 'insert into '||p_tab_src||'('||v_cols_dst||')'||' select  '||v_cols_src||' from '||p_tab_src;
      dbms_output.put_line(v_sql);
--      execute immediate ;
end;