00913 Error
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta ora-00913 too many values subquery Discuss the workings and policies of this site About Us Learn java.sql.sqlexception: ora-00913: too many values more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us pl sql ora-00913 too many values in bulk collect Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like too many values oracle subquery 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 and size. These tables are located in separate databases, but I am use to current Log in in hr user.
Ora-00913 Too Many Values In Case Statement
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: *Action: sql oracle share|improve this question edited Jan 23 '14 at 10:37 Kent Pawar 79211125 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
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
Ora-00913 Too Many Values Materialized View
site About Us Learn more about Stack Overflow the company Business Learn ora-00913 too many values in oracle insert more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x oracle too many rows Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up “ORA-00913: http://stackoverflow.com/questions/18737369/sql-error-ora-00913-too-many-values too many values” error when I run SQL query up vote 4 down vote favorite I'm getting a "too many values" error and I'm not sure how to approach it. The problem starts at the first nested SELECT SELECT p.name, p.phone_number FROM person p WHERE (SELECT ks.title, ks.ks_code FROM required_skills rs JOIN knowledge_skills ks USING (ks_code) WHERE rs.pos_code = 'CS1') IN (SELECT ks.title, ks.ks_code http://stackoverflow.com/questions/8353165/ora-00913-too-many-values-error-when-i-run-sql-query FROM courses_taken ct JOIN course_learning_units clu USING (c_code) JOIN learning_unit_skills lus USING (lu_code) JOIN knowledge_skills ks USING (ks_code)) I looked up info about this error and I believe it's because the subquery in the WHERE returns too many columns. The table data is below. But how can I whittle that down? I appreciate any pointers, Thanks required_skills POS_CODE VARCHAR2(10 BYTE) Yes 1 KS_CODE VARCHAR2(10 BYTE) Yes 2 learning_unit_skills LU_CODE VARCHAR2(10 BYTE) Yes 1 KS_CODE VARCHAR2(10 BYTE) Yes 2 person PER_ID NUMBER(38,0) No 1 NAME VARCHAR2(255 BYTE) Yes 2 STREET VARCHAR2(255 BYTE) Yes 3 CITY VARCHAR2(60 BYTE) Yes 4 ZIP_CODE VARCHAR2(60 BYTE) Yes 5 PHONE_NUMBER VARCHAR2(255 BYTE) Yes 6 GENDER VARCHAR2(6 BYTE) Yes 7 course learning units C_CODE VARCHAR2(10 BYTE) Yes 1 LU_CODE VARCHAR2(10 BYTE) Yes courses taken C_CODE VARCHAR2(10 BYTE) Yes 1 PER_ID NUMBER(38,0) Yes 2 sql oracle oracle11g ora-00913 share|improve this question edited Feb 12 '13 at 5:16 OMG Ponies 198k36356415 asked Dec 2 '11 at 7:40 Coffee 5311962119 in your query after the first where condition there is no field or column is mentioned –Rashmi Kant Shrivastwa Dec 2 '11 at 7:46 1 How a
CommunityOracle User Group CommunityTopliners CommunityOTN Speaker BureauJava CommunityError: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work correctly without it enabled. Please turn JavaScript back on and reload this page. Please enter a title. You can not post a blank message. Please type your message https://community.oracle.com/thread/2555152 and try again. More discussions in General Database Discussions All PlacesDatabaseGeneral Database Discussions This discussion is archived 1 2 Previous Next 18 Replies Latest reply on Jun 28, 2013 12:50 PM by Billy~Verreynne Insert ORA-00913: too many values http://www.club-oracle.com/threads/getting-error-as-ora-06550-line-5-column-16-pl-sql-ora-00913-too-many-values.11911/ -- urgent help user530956 Jun 27, 2013 3:50 PM Hi thereIts pretty urgent, got stuck up....To avoid the undo snapshot error, I am using this procedure to migrate the smaller chunks of huge volume of table data too many into new tables. This below code works well if the columns are very less. And this procedure is not working if the tables columns are morethan 30 columns and throwing the error PL/SQL: ORA-00913: too many valuesCREATE OR REPLACE PROCEDURE migration AS TYPE array_tp IS TABLE OF tranproc%ROWTYPE; l_array array_tp; CURSOR c IS select * from tranproc p where trunc(date)<=trunc(sysdate)-180; l_cnt1 NUMBER :=0; l_cnt2 NUMBER :=0; l_cnt3 NUMBER :=0;BEGIN OPEN c; LOOP FETCH c too many values BULK COLLECT INTO l_array LIMIT 10000; EXIT WHEN l_array.COUNT = 0; l_cnt1 := c%ROWCOUNT; FORALL i IN 1 .. l_array.COUNT INSERT INTO TMP_Transpoc VALUES l_array(i); l_cnt2 := l_cnt2 + SQL%rowcount; END LOOP; l_cnt3 := c%ROWCOUNT; CLOSE c; END;16 22 PL/SQL: ORA-00913: too many valuesits fallingline 16: INSERT INTO TMP_Transpoc VALUES l_array(i);Above table i.e tranproc has around 80 columns .i am not pl/sql expert, kindly advise how to resolve it.. i am fine with alternative approach, just i need a smaller chunk commit. 14354Views Tags: none (add) This content has been marked as final. Show 18 replies 1. Re: Insert ORA-00913: too many values -- urgent help sb92075 Jun 27, 2013 4:05 PM (in response to user530956) do not do in PL/SQL that which can be done in plain SQLINSERT INTO ... SELECT ? FROM ...... Like Show 0 Likes(0) Actions 2. Re: Insert ORA-00913: too many values -- urgent help Oracle Maniac Jun 27, 2013 4:14 PM (in response to sb92075) As he mentioned , if the data is too large ,the undo tablespace will be become full and the entire database is going to be affected . Its my personal observation .Suggestions are welcome . Like Show 0 Likes(0) Actions 3. Re: Insert ORA-00913: too many values -- urgent help sb92075 Jun 27, 2013 4:17 PM (in response to Oracle M
and 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 EXCLUDED_F ='Y' THEN 3 END AS TEMP_VAR, -- col6 CASE WHEN rq.COLLATERAL_F = 'N' AND FOREIGN_F = 'Y' AND EXCLUDED_F ='Y' THEN 4 END AS TEMP_VAR, -- col7 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, -- col8 -- SUM(RQ.CALC_PHYSICAL_CNTR_COUNT) AS CARTON_UNTS , -- SUM(RQ.TTL_