PL/SQL Insert INTO FROM SELECT type query. Simple Question.

I have a query, much like the one below, that I'm trying to convert to PL/SQL.  I've tried to wrap the whole thing up into an EXECUTE IMMEDIATE, but that's didn't work either.  Could someone please convert this into PL/SQL please?  Thanks.

INSERT INTO MONTHLY_VOLUMES_CBW
       (pid, api14, api12, api10, name, ptype, prod_inj_date, scenario, DSSSOURCE, daysinmonth, days_prod)
SELECT DISTINCT TRIM(w.PID),
       substr(w.pid,1,14),
       substr(w.pid,1,12),
       substr(w.pid,1,10),
       w.NAME,
       'COMP',
       m.prod_inj_date,
       'SCHEDULER',
       decode(m.source,'TOW',1,'COVS',1,'ZLA',2,99),
       to_char(last_day(m.prod_inj_date),'DD'),
       nvl(m.days_prod,0),
FROM   ods.monthly_volumes m, compmaster w
WHERE  w.PID = m.API_NO14 AND m.prod_inj_date is not null;
COMMIT;

 
CalDudeAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
I'm not sure what you mean.

Do you want a simple stored procedure with no parameters that executes that single insert statement?
----------------------------
--set up test
drop table tab1;
create table tab1(col1 char(1));

drop table tab2;
create table tab2(col1 char(1));

insert into tab1 values('a');
commit;

--create procedure
create or replace procedure junk as
begin
      insert into tab2 (col1) (select col1 from tab1 );
      commit;
end;
/

show errors

--test it
select * from tab2;

exec junk;

select * from tab2;


0
 
YANN0SCommented:
I don't know if I'm missing something, but it is simple. The following is a PL/SQL block:

BEGIN
INSERT INTO MONTHLY_VOLUMES_CBW
       (pid, api14, api12, api10, name, ptype, prod_inj_date, scenario, DSSSOURCE, daysinmonth, days_prod)
SELECT DISTINCT TRIM(w.PID),
       substr(w.pid,1,14),
       substr(w.pid,1,12),
       substr(w.pid,1,10),
       w.NAME,
       'COMP',
       m.prod_inj_date,
       'SCHEDULER',
       decode(m.source,'TOW',1,'COVS',1,'ZLA',2,99),
       to_char(last_day(m.prod_inj_date),'DD'),
       nvl(m.days_prod,0),
FROM   ods.monthly_volumes m, compmaster w
WHERE  w.PID = m.API_NO14 AND m.prod_inj_date is not null;
COMMIT;
END;
/
0
 
CalDudeAuthor Commented:
Ok, I think where I'm running into problems has to deal with the column named 'name', SQL Developer is showing the following errors:

Error(67,1): PL/SQL: SQL Statement ignored
Error(79,9): PL/SQL: ORA-00923: FROM keyword not found where expected

Line 67:        (pid, api14, api12, api10, name, ptype, prod_inj_date, scenario,
Line 79:        'SCHEDULER'
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
change:
 nvl(m.days_prod,0),
to
 nvl(m.days_prod,0)
0
 
YANN0SCommented:
If you have problems with the column named 'name' then enclose it in double quotes in your code:

INSERT INTO MONTHLY_VOLUMES_CBW
       (pid, api14, api12, api10, "name", ptype, prod_inj_date, scenario, DSSSOURCE, daysinmonth, days_prod)

and maybe in the select part as well
0
 
slightwv (䄆 Netminder) Commented:
just to add that double quotes force case sensitivity so the column would probably be: "NAME".

I also don't think 'name' has really ever been a reserved word in Oracle.  At least not in the last few versions.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.