999355 wrote:That may be the data, but it's not CREATE TABLE and INSERT statements, so I still can't test anything.
That looks good to me, but I still don't have any sample data, so I can see if the output is correct or not.
Here's the Sample Data:
SCHEMA MAX_LOAD PROD_LINE MEMBERSHIP
TEST_15@DATABASE_NAME 16-Apr-13 Commercial 1111
I want the scrip to search for all the schemas and pull the schemas which has those tables, and extract the data and put it into another separate table. (That's the problem). I am not getting a method as to how to do this.....Looks too cumbersome......Okay, to INSERT, change the outer loop that I suggested earlier to:
Do you want dbms_output.put_line to print the results,or would you like the results to be inserted into another table? (INSERTing weill be simpler and more efficient.)
I want the results to be inserted into another table........
You no longer need the cursor r, or the local v_ variables.
... LOOP sql_txt := 'INSERT INTO results_table (schema_name, max_load, prod_line, membership) ' || 'SELECT ' || c.owner || ', ' || '( ' || 'SELECT MAX (load_dt) ' || 'FROM ' || c.owner || '.load_status ' || ') AS max_load, ' || 'l_p_l.prod_line, ' || 'COUNT (DISTINCT enrollment.mem_nbr) AS MEMBERSHIP ' || 'FROM ' || c.owner || '.enrollment, ' || c.owner || '.prdct, ' || c.owner || '.L_P_L ' || 'WHERE enrollment.product_id = prdct.product_id ' || 'AND product.prod_line_id = l_p_l.id ' || 'GROUP BY l_p_l.prod_line'; dbms_output.put_line (query_txt || ' = query_txt'); -- For debugging EXECUTE IMMEDIATE sql_txt; END LOOP;