Ora 00913 Error Oracle
Contents |
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 Business ora-00913 too many values subquery Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation
Too Many Values Oracle Subquery
Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like
Java.sql.sqlexception: Ora-00913: Too Many Values
you, helping each other. Join them; it only takes a minute: Sign up SQL Error: ORA-00913: too many values up vote 6 down vote favorite Two tables are identical in terms of table name, column names, datatype
Pl Sql Ora-00913 Too Many Values In Bulk Collect
and size. These tables are located in separate databases, but I am use to current Log in in hr user. insert into abc.employees select * from employees where employee_id=100; I can not give use original query from corporate office. Error starting at line 1 in command: insert into abc.employees select * from employees where employee_id=100; Error at Command Line:1 Column:25 Error report: SQL Error: ORA-00913: too many values 00913. 00000 - "too many values" *Cause: ora-00913 too many values materialized view *Action: sql oracle share|improve this question edited Jan 23 '14 at 10:37 Kent Pawar 79011125 asked Sep 11 '13 at 9:25 user2703444 46123 add a comment| 4 Answers 4 active oldest votes up vote 4 down vote You should specify column names as below. It's good practice and probably solve your problem insert into abc.employees (col1,col2) select col1,col2 from employees where employee_id=100; EDIT: As you said employees has 112 columns (sic!) try to run below select to compare both tables' columns select * from ALL_TAB_COLUMNS ATC1 left join ALL_TAB_COLUMNS ATC2 on ATC1.COLUMN_NAME = ATC1.COLUMN_NAME and ATC1.owner = UPPER('2nd owner') where ATC1.owner = UPPER('abc') and ATC2.COLUMN_NAME is null AND ATC1.TABLE_NAME = 'employees' and than you should upgrade your tables to have the same structure. share|improve this answer edited Sep 11 '13 at 11:14 answered Sep 11 '13 at 9:26 Parado 19.1k73261 Not is possible because original tables in 112 columns and work on large table 800+ so .................. –user2703444 Sep 11 '13 at 9:32 employees table has 112 columns !? –Parado Sep 11 '13 at 9:33 SHOW TO 124317 ROWS –user2703444 Sep 11 '13 at 10:06 @user2703444 I've forgot AND ATC1.TABLE_NAME = 'employees' condition :), try it now please –Parado Sep 11 '13 at 10:08 Usually, Oracle object / schema names are in uppercase (
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 Business Learn more about hiring ora-00913 too many values in oracle insert developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question ora-00913 too many values in case statement x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; oracle too many rows it only takes a minute: Sign up Oracle Update statement with ORA-00913 error up vote 1 down vote favorite I am in the process of updating two columns in a table called "sample" with values 5, if these conditions http://stackoverflow.com/questions/18737369/sql-error-ora-00913-too-many-values satisfies; 1) If TABLE A.MEM_NBR = TABLE B.MEM_NBR AND TABLE B.M_ID = XXXX, AND TABLE B.CLCL = 1 and finally using a join condition like TABLE A.ID = SAMPLE.ID 2) If these conditions do not suffice, then we need not update the table. But I am getting an error; SQL Error: ORA-00913: too many values 00913. 00000 - "too many values". Need some help on the above update script, as to where i am going wrong. The script that i http://stackoverflow.com/questions/21447951/oracle-update-statement-with-ora-00913-error am using is as follows; UPDATE SAMPLE SET SAMPLE.PCB = 5, SAMPLE.PCBP_Q = 5 WHERE ( SELECT * FROM MEMBER_A A,S_NEW B WHERE A.MEM_NBR = B.MEM_NBR AND B.M_ID = 1111 AND B.CLCL = 1 AND B.ID = C.ID); sql oracle share|improve this question edited Jan 30 '14 at 4:50 Nagaraj S 7,19051637 asked Jan 30 '14 at 4:42 user3251591 61 add a comment| 2 Answers 2 active oldest votes up vote 1 down vote The problem is that your subquery can return more than one value. To get around this problem you can use the EXISTS keyword. (I also converted the join to ANSI syntax.) UPDATE SAMPLE C SET C.PCB = 5, C.PCBP_Q = 5 WHERE EXISTS ( SELECT 1 FROM MEMBER_A A JOIN S_NEW B ON A.MEM_NBR = B.MEM_NBR AND B.M_ID = 1111 AND B.CLCL = 1 WHERE A.ID = C.ID); share|improve this answer edited Jan 30 '14 at 5:02 answered Jan 30 '14 at 4:46 bernie 74.1k9124138 Just a doubt, how is the subquery linked with the outer table SAMPLE? I think it will update all records in SAMPLE if the inner query has any result. Correct me if I am wrong. –San Jan 30 '14 at 4:51 @San this is perfect. I assume you are not convinced that join in UPDATE like this will behave properly. I had this too once.. the update happens based on every row in
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle http://www.dba-oracle.com/sf_ora_00913_too_many_values.htm PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog http://www.club-oracle.com/threads/getting-error-as-ora-06550-line-5-column-16-pl-sql-ora-00913-too-many-values.11911/
ORA-00913: too many valuestips Oracle Error Tips by Burleson Consulting Oracle docs note this about ORA-00913: ORA-00913 too many values Cause: The SQL statement requires two sets of values too many equal in number. This error occurs when the second set contains more items than the first set. For example, the subquery in a WHERE or HAVING clause may return too many columns, or a VALUES or SELECT clause may return more columns than are listed in the INSERT. Action: Check the number of items too many values in each set and change the SQL statement to make them equal. To resolve case like these you should first be sure to have nls_numeric_characters='.,' where DBMS_REPCAT_RGT was executed. For SQLPLUS, use: SQL> alter session set nls_numeric_characters='.,'; For OEM, you may need to change the language that has been set in default in Windows locale using: Start> Control Panel>Regional Options It is important tat this behavior with ORA-00913 is associated with bug 2227755, and has been fixed in some versions. Also, on the Oracle Technology Forums, a user is reminded that in order to avoid ORA-00913, you have to keep in mind that "you can update as many records as you want, as many as your undo can support." Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum. Verify expeand also get a Premium Badge! Getting error as ORA-06550: line 5, column 16: PL/SQL: ORA-00913: too many values Discussion in 'Oracle Apps Technical' started by Venkiibm, Jun 3, 2014. Venkiibm Active Member Messages: 14 Likes Received: 0 Trophy Points: 80 hi guys I was trying to insert the table based on the below select statement and getting error as toomany values .Could you please let me know how to resolve this issue. DROP TABLE Sumtemp; CREATE TABLE Sumtemp ( temp_DIV_LOC_NBR NUMBER(6), temp_RECV_LOC_NBR NUMBER(6), temp_ZL_DIVN_NBR number(5), temp_var1 Number(01)); Declare temp_var number (01); Begin LOOP inseRt INTO sumtemp (temp_DIV_LOC_NBR,temp_RECV_LOC_NBR,temp_ZL_DIVN_NBR,temp_var1) SELECT RQ.DIV_LOC_NBR,rq.RECV_LOC_NBR,rq.ZL_DIVN_NBR, CASE when rq.COLLATERAL_F = 'N' and FOREIGN_F = 'N' AND EXCLUDED_F ='N' THEN 1 END AS TEMP_VAR, CASE when rq.COLLATERAL_F = 'N' and FOREIGN_F = 'Y' AND EXCLUDED_F ='N' THEN 2 END AS TEMP_VAR, case when rq.COLLATERAL_F = 'N' and FOREIGN_F = 'N' AND EXCLUDED_F ='Y' THEN 3 END AS TEMP_VAR, case when rq.COLLATERAL_F = 'N' and FOREIGN_F = 'Y' AND EXCLUDED_F ='Y' THEN 4 END AS TEMP_VAR, case when rq.COLLATERAL_UNITS > 0 and rq.COLLATERAL_F = 'Y'and FOREIGN_F = 'N' AND EXCLUDED_F ='N' THEN 5 END AS TEMP_VAR, -- SUM(RQ.CALC_PHYSICAL_CNTR_COUNT) AS CARTON_UNTS , -- SUM(RQ.TTL_BOOKED_QTY) AS TTL_BK_QTY FROM RCPT_QTY rq where rq.div_loc_nbr =12 GROUP by rq.DIV_LOC_NBR, rq.RECV_LOC_NBR, RQ.RCPT_MTHD_CD, TEMP_VAR; END LOOP; End; Venkiibm, Jun 3, 2014 #1 rajenb Forum Expert Messages: 361 Likes Received: 114 Trophy Points: 655 Location: Mauritius Hi, In your INSERT statement (and table), you have 4 columns only: inseRt INTO sumtemp (temp_DIV_LOC_NBR,temp_RECV_LOC_NBR,temp_ZL_DIVN_N BR,temp_var1) Click to expand... while in your SELECT statement you have 8 columns (and may be more if I include the 2 commented lines): Code (SQL): SELECT RQ.DIV_LOC_NBR, -- col1 rq.RECV_LOC_NBR, -- col2 rq.ZL_DIVN_NBR, -- col3 CASE WHEN rq.COLLATERAL_F = 'N' AND FOREIGN_F = 'N' AND EXCLUDED_F ='N' THEN 1 END AS TEMP_VAR, -- col4 CASE WHEN rq.COLLATERAL_F = 'N' AND FOREIGN_F = 'Y' AND EXCLUDED_F ='N' THEN 2 END AS TEMP_VAR, -- col5 CASE WHEN rq.COLLATERAL_F = 'N' AND FOREIGN_F = 'N' AND E