Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2007-08-03
6
Medium Priority
?
39,875 Views
Last Modified: 2013-12-19
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;

 
0
Comment
Question by:CalDude
  • 3
  • 2
6 Comments
 
LVL 8

Expert Comment

by:YANN0S
ID: 19626035
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
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1100 total points
ID: 19626037
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
 

Author Comment

by:CalDude
ID: 19626168
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1100 total points
ID: 19626267
change:
 nvl(m.days_prod,0),
to
 nvl(m.days_prod,0)
0
 
LVL 8

Expert Comment

by:YANN0S
ID: 19626315
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 19626420
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question