首页 \ 问答 \ oracle 12g无效数字错误(oracle 12g invalid number error)

oracle 12g无效数字错误(oracle 12g invalid number error)

create table WeeklySaleReport
( ReportID varchar(20) primary key
, StartDate date
, EndDate date
, SaleAmount number (8,2)
, ComRate number (8,2)
, ComAmount number (8,2)
, EmployeeID int
, EName varchar(70)
, BrandID varchar(20)
, BName varchar(70)
, BSaleAmount number(8,2)
, TypeID varchar(20)
, TName varchar(20)
, TSaleAmount number(8,2)
, ESaleAmount number(8,2)
, EBonus number(8,2)
, DepartmentID references Department(DepartmentID) );

此表已成功完成

但是当试图插入这些字段时,它给我错误“无效数字”:

insert into WeeklySaleReport
values
  ( 'R1', TO_DATE ('2018/06/16','yyyy/mm/dd')
  , TO_DATE ('2018/07/11','yyyy/mm/dd')
  , '9020.00', '2324.21', '20.00', 'E1'
  , 'Bassi', 'B1', 'Brand One', '123.00'
  , 'Type1', 'T One', '500.00', '123.00'
  , '555.00', 'D1' );

create table WeeklySaleReport
( ReportID varchar(20) primary key
, StartDate date
, EndDate date
, SaleAmount number (8,2)
, ComRate number (8,2)
, ComAmount number (8,2)
, EmployeeID int
, EName varchar(70)
, BrandID varchar(20)
, BName varchar(70)
, BSaleAmount number(8,2)
, TypeID varchar(20)
, TName varchar(20)
, TSaleAmount number(8,2)
, ESaleAmount number(8,2)
, EBonus number(8,2)
, DepartmentID references Department(DepartmentID) );

This table is made successfully

but when trying to insert these fields, it gives me error "Invalid Number":

insert into WeeklySaleReport
values
  ( 'R1', TO_DATE ('2018/06/16','yyyy/mm/dd')
  , TO_DATE ('2018/07/11','yyyy/mm/dd')
  , '9020.00', '2324.21', '20.00', 'E1'
  , 'Bassi', 'B1', 'Brand One', '123.00'
  , 'Type1', 'T One', '500.00', '123.00'
  , '555.00', 'D1' );

原文:https://stackoverflow.com/questions/50086031
更新时间:2019-07-13 09:05

最满意答案

我强烈建议您编写更多的插入内容:

insert into WeeklySaleReport (ReportID, StartDate, EndDate, SaleAmount, ComRate, ComAmount,
                              EmployeeID, EName, BrandID, BName, BSaleAmount,
                              TypeID, TName, TSaleAmount, ESaleAmount, EBonus)
     values 'R1',                   -- ReportID
             DATE '2018-06-16',     -- StartDate
             DATE '2018-07-11',     -- EndDate
             9020.00,               -- SaleAmount
             2324.21,               -- ComRate
             20.00,                 -- ComAmount
             'E1'                   -- EmployeeId
    ---------^ ERROR
             'Bassi',               -- EName
             'B1',                  -- BrandId
             'Brand One',           -- BName
             123.00,                -- BSaleAmount
             'Type1',               -- TypeId
             'T One',               -- TName
             500.00,                -- TSaleAmount
             123.00,                -- ESaleAmount
             555.00,                -- Bonus
             'D1'                   -- DepartmentId
            );

笔记:

  • 列出更新中的列。 在这里,我还将它们添加为注释,以便您和其他人可以跟随正在发生的事情。
  • EmployeeId显然是一个错误。
  • Oracle建议将VARCHAR2()而不是VARCHAR()用于字符串。
  • 了解你的类型! 不要在数字常量周围添加单引号。 这是误导。
  • 使用DATE关键字。 这是ANSI标准,更易于阅读。

我刚刚学到了一些东西。 DepartmentId声明在Oracle中有效的,即使它没有类型。 是一个SQL小提琴。 大多数数据库都需要表中的类型定义。 但是,我仍然不知道该值是否有效,因为问题中的类型不明确。


I would strongly advise that you write your inserts more like this:

insert into WeeklySaleReport (ReportID, StartDate, EndDate, SaleAmount, ComRate, ComAmount,
                              EmployeeID, EName, BrandID, BName, BSaleAmount,
                              TypeID, TName, TSaleAmount, ESaleAmount, EBonus)
     values 'R1',                   -- ReportID
             DATE '2018-06-16',     -- StartDate
             DATE '2018-07-11',     -- EndDate
             9020.00,               -- SaleAmount
             2324.21,               -- ComRate
             20.00,                 -- ComAmount
             'E1'                   -- EmployeeId
    ---------^ ERROR
             'Bassi',               -- EName
             'B1',                  -- BrandId
             'Brand One',           -- BName
             123.00,                -- BSaleAmount
             'Type1',               -- TypeId
             'T One',               -- TName
             500.00,                -- TSaleAmount
             123.00,                -- ESaleAmount
             555.00,                -- Bonus
             'D1'                   -- DepartmentId
            );

Notes:

  • List the columns in the update. Here, I've also added them as comments so you and others can follow what is going where.
  • EmployeeId is obviously an error.
  • Oracle recommends using VARCHAR2() rather than VARCHAR() for strings.
  • Know your types! Don't put single quotes around numeric constants. It is misleading.
  • Use the DATE keyword. It is ANSI standard and easier to read.

And I just learned something. The DepartmentId declaration is valid in Oracle, even though it does not have a type. Here is a SQL Fiddle. Most databases require the type definition in the table. However, I still don't know if the value is valid, because the type is not clear in the question.

2018-04-29

相关问答

更多

Oracle错误的数字或类型错误(Oracle wrong number or types error)

存储过程和应用程序代码参数中的Varchar长度不同。 设置适当的长度 一般代码看起来正确,问题可能在于使用ora_cmd。 您可能在实际调用中有更多参数,因为之前添加了一些其他参数(例如,如果您将此方法调用两次)。 在新的电话会议之前不要忘记清除params。 Varchar lengths in stored proc and application code parameters are different. Set proper lengths In general code looks ...

ORA-01749 - Oracle 12c vs 11g(ORA-01749 - Oracle 12c vs 11g)

该问题与Oracle版本有关。 该错误显示在11.2.0.1上,但在应用11.2.0.4后,它不存在。 Oracle在这些版本之间的某处更改/修复了该问题。 The problem is related to Oracle version. The error is shown on 11.2.0.1 but after applying of 11.2.0.4 it does not exist. The issue was changed/fixed by Oracle somewhere b ...

ORACLE:带有动态查询的游标 - 为游标字段抛出错误“无效标识符”(ORACLE: Cursor with dynamic query - throws error “invalid identifier” for cursor field)

您将emp_rec定义为本地PL / SQL变量。 PL / SQL数据都不在动态SQL执行的范围内。 执行时,就好像您尝试运行该语句一样 - 因为dbms_output独立显示在单独的SQL上下文中。 如果你这样做,很明显查询不存在emp_rec 。 你引用它需要使用绑定变量: WHERE emp_id=:dynamic_col_3'; 然后执行它: execute immediate stmt using emp_rec.emp_id; 但是你不能在using子句中使用x_dynamic_ ...

计算机横机 的12G和14G是什么意思?

14G指的是针板上面每1英寸里面有14支织针,针越粗呢,这个数值就越小,针种涵盖16针-3针,如果去到18针的话就是织布机了,至于产量呢就要看你的工时价钱到多高了,以每分钟的工价为0.5元计算的话,8小时的产值就应该到240元左右了,当然要看市场的价钱怎么样了,这个是一直在浮动的。如果你要买电脑横机的话,我建议你买双系统的,这个做货快,产值高。

PKCS#12密码无效(PKCS#12 Invalid Password)

请尝试以下方法。 它显示了应检查某些键返回值的位置,并避免了额外的数据副本: BIO *bio = BIO_new(BIO_s_mem()); ASSERT(bio != NULL); int ret = i2d_PKCS12_bio(bio, p12); ASSERT(ret == 1); BUF_MEM *buffer; BIO_get_mem_ptr(bio, &buffer); ASSERT(buffer != NULL); NSData *data = [NSData dataWi ...

如何从Oracle 11g中的对象类型列中选择?(How to SELECT from object type column in Oracle 11g?)

您需要使用TREAT函数来获取数据库引擎,以将VEHICLE作为卡车对待,如下所示: SELECT ID, TREAT(vehicle AS TRUCK).DOORS FROM VEHICLES 分享并享受。 You need to use the TREAT function to get the database engine to treat VEHICLE as a TRUCK, as in: SELECT ID, TREAT(vehicle AS TRUCK).DOORS FROM V ...

11g上的Ora-29283错误(Ora-29283 error on 11g)

PL / SQL在数据库系统中运行,即在服务器上运行。 它只能访问服务器上的文件。 它不能是您PC上的文件。 即使您已在Oracle( CREATE DIRECTORY ... )中定义了目录并授予了对用户足够的访问权限,它也会失败,因为Oracle将尝试访问服务器上的C:\LOB而不是PC上的C:\LOB 。 您要实现的目标无法在服务器端解决,即无法在PL / SQL中解决。 您必须编写在客户端(即在您的PC上)运行的代码,例如某些Java或.NET程序。 PL/SQL is run in th ...

如何解析Oracle DB查询结果中的无效标识符?(How can I resolve an invalid identifier on an Oracle DB query result?)

你需要将所有四个表连接在一起,从这个角度来看,这对我来说是直截了当的。 这些方面的东西应该有效: SELECT r.region_id, COUNT(d.department_id) "Number of Department" FROM department d INNER JOIN location l ON d.location_id = l.location_id INNER JOIN country c ON l.country_id = c.country_id INN ...

相关文章

更多

最新问答

更多
  • Unity着色器错误;(Unity shader error; presumably in if statement)
  • 如何在Liferay portlet中设置Cookie?(How to set a Cookie in Liferay portlet?)
  • C#只读访问List <>或其他集合,或ToArray()魔术(C# readonly access to List<> or other collection, or ToArray() magic)
  • 如何使用Google Web工具包创建登录应用程序?(How to create a login application using Google web tool kit? [closed])
  • 如何使我的函数返回结果作为全局变量?(How to make my function return results as a global variable?)
  • 为什么我的网站需要“启用32位应用程序”?(Why does my website need “Enable 32-bit applications”?)
  • 红宝石数组具有相同的值(ruby array of hash with same value)
  • Android(在Scala中):StackOverflowError取决于何时启动线程?(Android (in Scala): StackOverflowError depends on when to start a thread?)
  • 适用于iOS应用的通用链接(Universal Links for iOS apps)
  • 创建并打开文件linux编程(create and open file linux programming)
  • Datepicker,第2个日期是从第1个日期开始的X天(Datepicker, 2nd date is X days from 1st date)
  • 背景大小过渡不起作用(Transition on background-size doesn't work)
  • 在React / Redux中的On Click函数中传递Prop(Passing a Prop in an On Click Function in React/Redux)
  • 关键字在代码中做了什么,是否有没有此关键字的替代方法?(what does the keyword this does in the code and are there any alternate methods without this keyword? [duplicate])
  • 反向设计FoxPro / dBsae数据库以创建EER模型(Reverse Engineer a FoxPro/dBsae database to create the EER model)
  • 在R中的变量中组合具有相同值的行(Combine rows that have same value in a variable in R [duplicate])
  • SQL Server:非空唯一主键(SQL Server: Non-null unique vs. Primary Key)
  • 将扩展ASCII字符代码转换为ISO-8859-1(Convert extended ASCII character codes to ISO-8859-1)
  • 如何在.NET中读取m4a文件中的标签?(How to read tags out of m4a files in .NET?)
  • 呼叫链接的性能提升?(Performance gain on call chaining?)
  • 风景名胜区规划设计文本里面都包含哪些内容?推荐一家旅游规划设计公司?谢谢!
  • 如何在String中将字符串转换为枚举?(How do I convert a string to enum in TypeScript?)
  • SQLite SELECT出现异常如何解决它?(SQLite SELECT gives exception How to fix it?)
  • NSLog不会从ViewController类输出,而是来自AppDelegate类的WILL。(NSLog will NOT output from the ViewController class, but WILL from the AppDelegate class. iOS:Objective C)
  • 更改UIPicker突出显示的标签宽度(change UIPicker highlighted tab width)
  • 空心倒五角形(Hollow inverted pentagon)
  • 如何设置仅使用OpenID的Plone站点(How to setup Plone sites working only with OpenID)
  • Paperclip缺少Amazon S3的协议(https)(Paperclip is missing the Protocol (https) with Amazon S3)
  • 从宏中部署netbeans中的ANT文件(Deploy ANT file in netbeans from macro)
  • XMLReader是未知的(XMLReader is unknown)