Hello again, i'm trying tu create and run a procedure in pl/sql and i'm getting some errors that can resolve. I need to create a procedure wich fetch all tables of the schema and then insert the data of each table into a global data table wich contains all the data of the other ones.

CREATE OR REPLACE PROCEDURE DM_DATA IS
CURSOR all_tables IS
SELECT *
FROM all_tables t
WHERE t.owner ='DM' AND table_name like 'RESP_TEST_%';
BEGIN
LOOP
INSERT INTO DM.all_broadcast_responses SELECT * FROM TABLE DM.RESP_TEST_01;

END LOOP;

EXCEPTION
WHEN OTHERS THEN ROLLBACK;

END DM_DATA;

----
And i'm getting: Error(9,6): PL/SQL: SQL Statement ignored
Error(9,70): PL/SQL: ORA-00906: left parentesis missing

What's wrong? I'm in the right way?

>What's wrong?
Invalid syntax.
What exactly are you LOOPing on?
When does LOOP terminate?
Solution can be done with only plain SQL & no PL/SQL

>I'm in the right way?
No

OK, i understand here is the problem:

FOR rec IN all_tables
LOOP
INSERT INTO all_broadcast_responses SELECT * FROM TABLE (how can i refer to the table i'm on?);

END LOOP;


How can you do with plain sql? Can you give me an example or guide me?

Thankyou very much

by the way the design is poor, unprofessional, & not normalized.
You should NEVER have multiple tables with exact same columns!

spool load_abr.sql
SELECT 'INSERT INTO DM.all_broadcast_responses SELECT * FROM ' || table_name || ';'
FROM all_tables t
WHERE t.owner ='DM' AND table_name like 'RESP_TEST_%';
spool off
@load_abr.sql

I know, not my design or rules. I'm just a newbie at oracle creating an etl. Thanks for the reply, i'll try to run this.

Works very well.
Thanks!!!

this technique is called "writing SQL to write SQL"
It comes in quite handy at times!