Error Ora-00947
Contents |
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog
ora 00947 bulk collect into ORA-00947: not enough values tips Oracle Error Tips by Burleson ConsultingOra 00947 Not Enough Values
Oracle docs note this about ORA-00947: ORA-00947 not enough values Cause: This error occurs when a SQL statement requires two sets ora-00947 not enough values bulk collect of values equal in number, but the second set contains fewer items than the first set. This can occur in a WHERE or HAVING clause in which a nested SELECT returns too few columns as in:
Pl Sql Ora 00947 Not Enough Values In Bulk Insert
WHERE (A,B) IN (SELECT C FROM ...) Another common cause of this error is an INSERT statement in which the VALUES or SELECT clause does not contain enough values needed for the INSERT, as in INSERT INTO EMP(EMPNO,ENAME) VALUES('JONES') Action: Check the number of items in each set and change the SQL statement to make them equal. On Oracle DBA-Forums, has a great example of ORA-00947, and a resolution. Question: How do pl/sql ora-00947 not enough values select into I resolve ORA-00947 in this situation? SQL> run 1 1 merge into aim m1 2 using flataim m2 on (m2.sn = m1.snam) 3 when matched then update set m1.snam = m2.sn, 4 m1.fnam = m2.fn, m1.lnam = m2.ln, m1.midnam = m2.mn, 5 m1.maidn = m2.md, m1.cntry = m2.na, m1.st = m2.st, 6 m1.city = m2.cy, m1.empt = m2.mt, m1.nick = m2.nk, 7 m1.zip = m2.zp, m1.street = m2.rd 8 when not matched then insert ((select aimsq.nextval from dual), 9 m1.fnam, m1.lnam, m1.midnam, 10 m1.maidn, m1.cntry, m1.state, m1.city, m1.nick, 11 m1.zip, m1.street, m1.snam, m1.empt) 12 values ( m1.fn, m2.ln, m2.mn, m2.md, m2.na, m2.st, m2.cy 13* m2.nk, m2.zp, m2.rd, m2.sn, m2.mt); when not matched then insert ((select aimsq.nextval from dual), * ERROR at line 8: ORA-00947: not enough values Answer: Because the ORA-00947 error signifies that there are not enough values passing through INSERT, the insert in the aim table should match all columns. Here is an example, using snam: when not matched then insert (m1.snam, m1.fnam, m1.lnam, m1.midnam, m1.maidn, m1.cntry, m1.state, m1.city, m1.nick, m1.zip, m1.street, m1.snam, m1.empt) values (imsq.nextval, m1.fn, m2.ln, m2.mn, m2.md, m2.na, m2.st, m2.cy, m2.nk, m2.zp, m2.rd, m2.sn, m2.mt); Also, keep in mind while troubleshooting for ORA-00947, that the (select aimsq.nextval from dual) needs to be on the VALUES side. MOSC also offers information
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company
Ora-00947 Not Enough Values In Sql Loader
Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs
Ora-00947 Not Enough Values In Oracle Insert
Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just pl/sql ora-00947 not enough values procedure like you, helping each other. Join them; it only takes a minute: Sign up Oracle: INSERT values from SELECT…JOIN, SQL Error: ORA-00947: not enough values up vote 1 down vote favorite I'm trying to do the following: http://www.dba-oracle.com/sf_ora_00947_not_enough_values.htm INSERT INTO MyTable(a, b, c) SELECT a FROM source1 JOIN source2 ON ... Where source2 contains columns B and C. However Oracle doesn't seem to like this and is telling me "SQL Error: ORA-00947: not enough values". Am I doing something wrong here? Is this syntax even possible? Or do I have to rewrite it as: SELECT a, b, c FROM source1, source2 WHERE .... Thanks! oracle join insert ora-00947 share|improve this question asked http://stackoverflow.com/questions/11414177/oracle-insert-values-from-select-join-sql-error-ora-00947-not-enough-value Jul 10 '12 at 13:17 Matt Szaro 842723 add a comment| 2 Answers 2 active oldest votes up vote 6 down vote accepted Use as many identifiers in the SELECT clause as in the INSERT clause, as in: INSERT INTO MyTable(a, b, c) SELECT s1.a, s2.b, s2.c FROM source1 s1 JOIN source2 s2 ON ... share|improve this answer answered Jul 10 '12 at 13:20 Vincent Malgrat 51.2k768117 This might be what I need...let me give it a try. –Matt Szaro Jul 10 '12 at 13:25 Worked perfectly. Thanks Vincent! –Matt Szaro Jul 10 '12 at 13:38 add a comment| up vote 0 down vote The select needs to return the same number of columns as you listed in the INSERT statement. So: yes, you need to rewrite the query to SELECT a,b,c FROM ... share|improve this answer answered Jul 10 '12 at 13:20 a_horse_with_no_name 185k24234311 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email and Password Post as a guest Name Email Post as a guest Name Email discard By posting your answer, you agree to the privacy policy and terms of service. Not the answer you're looking for? Browse other questions tagged oracle join insert ora-00947 or ask yo
values) - FORALL [message #307297] Tue, 18 March 2008 06:19 mafc73 Messages: 38Registered: November 2006 Member Hi, I have this code and I get the error ora-947 in values line. I have http://www.orafaq.com/forum/t/99177/ searched in metalink and the only advice is to put VALUES var_sent1(i); instead of VALUES (var_sent1(i)); I have changed but it doesn't works. Thanks CREATE OR REPLACE procedure proc_example( a_date IN Varchar2 ) IS cursor cursor_sent1 IS select c.id_num, r.url_id, r.url_id2, trunc(date_dt,'IW')+6, sum(new), sum(cnt), sum(flows) from tab1 a, tab2 d, tab3 c, tab4 r where a.event_date_id = d.date_id group by c.id_num, url_id, url_id2, 00947 not trunc(date_dt,'IW')+6; TYPE tipo_sent1 IS TABLE OF cursor_sent1%ROWTYPE; var_sent1 tipo_sent1; BEGIN OPEN cursor_sent1; FETCH cursor_sent1 BULK COLLECT INTO var_sent1; CLOSE cursor_sent1; FORALL i IN var_sent1.FIRST .. var_sent1.LAST INSERT into t_dest (imm_id_num, url_id, url_id2, date_dt, new, cnt, flows) VALUES var_sent1(i); END; / Report message to a moderator Re: Error ORA-00947(not enough values) - FORALL [message #307299 is a reply to message #307297] Tue, 00947 not enough 18 March 2008 06:34 S.Rajaram Messages: 1027Registered: October 2006 Location: United Kingdom Senior Member http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/collections.htm#i20483 Check the above link and search for forall keyword. Regards Raj Report message to a moderator Re: Error ORA-00947(not enough values) - FORALL [message #307302 is a reply to message #307299] Tue, 18 March 2008 07:08 mafc73 Messages: 38Registered: November 2006 Member I have been seeing this link and I have seen this example Inserting PL/SQL Records into the Database A PL/SQL-only extension of the INSERT statement lets you insert records into database rows, using a single variable of type RECORD or %ROWTYPE in the VALUES clause instead of a list of fields. That makes your code more readable and maintainable. If you issue the INSERT through the FORALL statement, you can insert values from an entire collection of records. The number of fields in the record must equal the number of columns listed in the INTO clause, and corresponding fields and columns must have compatible datatypes. To make sure the record is compatible with the table, you might find it most convenient to declare the variable as the type table_nam