Oracle 11g Insert Statement into Multiple Tables

Question

I am currently having trouble trying to execute multiple statements at the same time. I keep getting this error when trying to run the following INSERT statements:

INSERT INTO report_header ( report_number, company_id, user_id, entry_date) VALUES ( 6797, 15967, 84, TRUNC(SYSDATE));
INSERT INTO report_detail (part_id, condition_id, uom_id, dvc_id, cqh_id, alt_part_id, entry_date, qty_quoted, qty_req, unit_cost, unit_price, customer_price, route_code) VALUES ((SELECT part_id from parts where pn = '2366'),15,1,3,(select max(report_id) from report_header), (SELECT part_id from parts where pn = '2366'),'11-JUN-2015',1,1,0,1895,1895,'O');
SELECT * from Dual;  

So when I split up the commands and run them one by one they run fine but in the same statement I get the 'command not properly ended' error. I have been trying all the various threads and this is the closes I can get. Any help is appreciated.

Thanks in advance everyone.

Answer 1

Based on your comment, I'm still not clear on what tool you are using to submit the statements to the database.

It's quite possible that your query tool can only handle one statement at a time.

And even if you batch up the 3 statements the way you did, those are still 3 distinct statements, and maybe the tool can't handle that.

A technique that can be used to turn it into a single statement, is by submitting the SQL as part of a single anonymous PL/SQL block. Not sure if your tool will support that. But if it does, you can try this (I left out the select from dual part, as I don't know what the point of it is):

begin
    INSERT INTO report_header ( report_number, company_id, user_id, entry_date) VALUES ( 6797, 15967, 84, TRUNC(SYSDATE));
    INSERT INTO report_detail (part_id, condition_id, uom_id, dvc_id, cqh_id, alt_part_id, entry_date, qty_quoted, qty_req, unit_cost, unit_price, customer_price, route_code) VALUES ((SELECT part_id from parts where pn = '2366'),15,1,3,(select max(report_id) from report_header), (SELECT part_id from parts where pn = '2366'),'11-JUN-2015',1,1,0,1895,1895,'O');
end;

The key is to having the wrapping begin and end; keywords like above. Hopefully it will work for you.

Answer 2

What sstan is pointing out is that you are lacking the semicolon at the end of each statement.

Try:

INSERT INTO report_header ( report_number, company_id, user_id, entry_date) VALUES ( 6797, 15967, 84, TRUNC(SYSDATE));
INSERT INTO report_detail (part_id, condition_id, uom_id, dvc_id, cqh_id, alt_part_id, entry_date, qty_quoted, qty_req, unit_cost, unit_price, customer_price, route_code) VALUES ((SELECT part_id from parts where pn = '2366'),15,1,3,(select max(report_id) from report_header), (SELECT part_id from parts where pn = '2366'),'11-JUN-2015',1,1,0,1895,1895,'O');
SELECT * from Dual;

Note the ";" delimiting each separate statement.