sql - Making a procedure to take values from multiple tables into one common table -

sql - Making a procedure to take values from multiple tables into one common table -

sorry question might basic searched online , found basic ways of making procedure want here below:

table1 table2 id1 id2 name number id1 id2 name number name2 number2 1 1 abc 123 1 1 xyz 345 can adf ................. ................................. ................ ................................. ................. .................................

id1 , id2 unique rest fields in both tables different.now want create new table here:

create test_table ( run_id number, --this made primary key new table randomly generated after each run run_date date, --the date on added info test_table_id number,--the thought of using table id identify table running query table_name varchar2(80),--the table got info id varchar2(80 char), --unique id number(10,0), --unique field1 varchar2(4000), --i can store necessary fields different tables in these fields field2 varchar2(4000), field3 varchar2(4000), field4 varchar2(4000), field5 varchar2(4000), query_desc varchar(4000) );

now running queries on multiple tables table1,table2 thing mutual unique id's have store there results in 1 test_table.

so thought create procedure below not right because not expert @ procedures can suggest hwo correctly create procedure this.the wrong procedure thought of below , trying create procedure runs 1 table end goal create work multiple tables please reply accordingly multiple tables:

create or replace procedure proc_testing (test_table_id in number default 0) --table_id=0 run queries : total procedure --table_id=1 table1 queries --table_id=2 ... queries --table_id=3 ... queries --table_id=4 ... queries --table_id=5 ... queries v_run_id number; v_run_on_dt date; v_table_name varchar(500); v_id1 varchar(100); v_id2 number; v_field_name1 varchar(4000); v_field_name2 varchar(4000); v_field_name3 varchar(4000); v_field_name4 varchar(4000); v_field_name5 varchar(4000); v_query_desc varchar(4000); begin dbms_output.put_line( 'starting testing proc on ' || sysdate ); select run_id.nextval v_run_id dual; --run_id sequence select sysdate v_run_on_dt dual; if (test_table_id>=2) dbms_output.put_line( 'conditions not met' || sysdate ); return; end if; if (test_table_id=1 or test_table_id=0) select distinct distinct trunc(sysdate) run_date v_run_on_dt, 'table_1' table_name v_table_name, d1.id1 v_id1,d1.id2 v_id2 , d1.number field1 v_field_name1, d1.prod_name field2 v_field_name2, 'adding results new table' query_desc v_query_desc table1 d1 insert test_table ( values v_run_id , v_run_on_dt , v_table_name , v_integration_id , v_datasource_num_id , v_field_name1 , v_field_name2 , v_field_name3 , v_field_name4 , v_field_name5 , test_table_id , v_query_desc ) end if; end;

this procedure might wrong please ignore , tell me how new procedure needs build.

sql oracle10g