Error Pl/sql Ora-00913 Too Many Values
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta too many values oracle 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 you,
Ora-00913 Too Many Values Materialized View
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. insert ora-00913 too many values in oracle 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 shoul
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 ora-00913 too many values in case statement this site About Us Learn more about Stack Overflow the company Business Learn
Oracle Too Many Rows
more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question
Ora-00913 Too Many Values Insert All
x 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 Oracle http://stackoverflow.com/questions/18737369/sql-error-ora-00913-too-many-values Trigger error PL/SQL: ORA-00913: too many values up vote 1 down vote favorite I am trying to create a trigger but is having errors.... SQL> CREATE OR REPLACE TRIGGER INV_TOTAL 2 BEFORE INSERT OR UPDATE ON INVOICE 3 FOR EACH ROW 4 BEGIN 5 SELECT 6 NVL((SELECT R.SUBTOTAL FROM HOLIDAY_RESERVATION R WHERE R.RESV_ID = :NEW.INV_ID), 0) + 7 NVL((SELECT R.SUBTOTAL, (R.SUBTOTAL*20)/100 FROM HOLIDAY_RESERVATION http://stackoverflow.com/questions/9850183/oracle-trigger-error-pl-sql-ora-00913-too-many-values R WHERE R.RESV_ID = :NEW.RESV_ID),0) 8 INTO :NEW.INV_TOTAL_PRICE 9 FROM DUAL; 10 END; 11 / Warning: Trigger created with compilation errors. SQL> SHOW ERRORS; Errors for TRIGGER INV_TOTAL: LINE/COL ERROR -------- ----------------------------------------------------------------- 2/4 PL/SQL: SQL Statement ignored 4/9 PL/SQL: ORA-00913: too many values SQL> Where is it finding too many values, all i want is select whats in field subtotal, add 20% and update a field in another table sql oracle triggers ora-00913 share|improve this question edited Feb 12 '13 at 5:15 OMG Ponies 199k36356415 asked Mar 24 '12 at 8:20 J.D 1,11493262 add a comment| 2 Answers 2 active oldest votes up vote 0 down vote accepted The reason behind "Too many values" error is that in your select statements you do the following: NVL((SELECT R.SUBTOTAL FROM HOLIDAY_RESERVATION R WHERE R.RESV_ID = :NEW.INV_ID), 0) ' 1 value NVL((SELECT R.SUBTOTAL, (R.SUBTOTAL*20)/100 FROM HOLIDAY_RESERVATION R WHERE R.RESV_ID = :NEW.RESV_ID),0) ' gives 2 values (subtotal, subtotal*20 / 100) Calling NVL with 2 values throws ORA-00913. Even if it didn't, it'd throw eventually when you try to add 1 value with 2. Also, I'd set fully qualified schema names beside every declaration
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 http://www.dba-oracle.com/sf_ora_00913_too_many_values.htm Ion Excel-DB Don Burleson Blog
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 equal in number. This error occurs when the too many 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 in each set and change the SQL statement to make them equal. too many values 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 experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and se