PL/: ORA-01843:无效月份

Question

我想在oracle数据库版本11.2.0.4的PL/SQL进程中插入带有时间戳列的表中的数据

如果我在蟾蜍中运行“”，就会得到以下SQL命令：

 Insert into xxxx$1
   (ID, ITEM, ITEMSIZE, QUALITY, MATERIAL, 
    COLOUR, IMAGEURL, CREATIONDATE, SAMPLEITEMNUMBER, ITEMNUMBER)
 Values
   (111, 339079775, '1', 'Microfaser PRIMABELLE®', 'TEXTILE', 
    '1 (=creme)', 'url', TO_TIMESTAMP('27.06.2016 15:49:35.000000','DD.MM.YYYY HH24:MI:SS.FF'), 'xxx', 'xxxx');
COMMIT;

在PL/SQL中，我生成一个VARCHAR2 2/字符串，类似于这样：

v_sql :=
                                'INSERT INTO xxxx'
                             || p_importpostfix
                             || '  VALUES ('
                             || seq_xxxx.NEXTVAL
                             || ','
                             || v_rec.item_id
                             || ', '''
                             || v_size
                             || ''','''
                             || v_quality
                             || ''','''
                             || v_material
                             || ''','''
                             || v_colour
                             || ''','''
                             || NULL
                             || ''',to_timestamp('''|| to_char( sysdate, 'DD.MM.YYYY HH24:MI:SS')||''',''DD.MM.YYYY HH24:MI:SS.FF''),'''
                             || v_rec.vid
                             || ''','''
                             || v_rec.vid
                             || ''')';

如果我在整个过程之外运行程序单点，它就能正常工作。没有错误发生。

如果我运行由数据库作业启动的完整进程，我将得到一个ORACLE：

ORA-01843:无效的月份错误。

我读过关于这个问题的另一篇文章，却没有理解我的问题。

Answer 1

不确定是否是问题所在，但使用SYSDATE似乎容易得多。

似乎您插入了字符串'NULL‘。如果要插入空值，则不需要使用多个单引号，因为最后一个字符串不需要单引号来插入空值。

v_sql :=
                                'INSERT INTO xxxx'
                             || p_importpostfix
                             || '  VALUES ('
                             || seq_xxxx.NEXTVAL
                             || ','
                             || v_rec.item_id
                             || ', '''
                             || v_size
                             || ''','''
                             || v_quality
                             || ''','''
                             || v_material
                             || ''','''
                             || v_colour
                             || ''','
                             || 'NULL,'
                             || 'SYSDATE,'
                             || ''''
                             || v_rec.vid
                             || ''','''
                             || v_rec.vid
                             || ''')';

编辑

与其尝试构建这样的查询，不如使用参数化查询。

    v_sql := 'INSERT INTO xxxx' || p_importpostfix || ' VALUES(:1,:2,:3,:4,:5,:7,:8,:9,:10)';
    v_null := NULL;
    EXECUTE IMMEDIATE v_sql USING IN seq_xxxx.NEXTVAL, 
                                  IN v_rec.item_id, 
                                  IN v_size,
                                  IN v_quality,
                                  IN v_material,
                                  IN v_colour,
                                  IN v_null,
                                  IN SYSTIMESTAMP,
                                  IN v_rec.vid,
                                  IN v_rec.vid;

想象一下，如果您的一个值包含一个'..。

更多信息，关于Oracle文档的立即执行。不过，我想你需要一个甲骨文账户。

Answer 2

你能显示目标表的DESC吗？以确保数据类型没有不匹配？

每当您购买这些动态字符串时，我总是发现使用"q“引用格式更容易，以帮助减少所有重复/转义引号.让你更容易读懂：

从蟾蜍的查询开始：

  Insert into xxxx$1                                                                                           
  (ID, ITEM, ITEMSIZE, QUALITY, MATERIAL,                                                                      
     COLOUR, IMAGEURL, CREATIONDATE, SAMPLEITEMNUMBER, ITEMNUMBER)                                             
  Values                                                                                                       
  (111, 339079775, '1', 'Microfaser PRIMABELLE®', 'TEXTILE',                                                   
     '1 (=creme)', 'url', TO_TIMESTAMP('27.06.2016 15:49:35.000000','DD.MM.YYYY HH24:MI:SS.FF'), 'xxx', 'xxxx');

移除分号，并将q‘’围绕每一行：

  v_sql :=
  q'[ Insert into xxxx$1                                                                                            ]'||CHR(10)||
  q'[ (ID, ITEM, ITEMSIZE, QUALITY, MATERIAL,                                                                       ]'||CHR(10)||
  q'[    COLOUR, IMAGEURL, CREATIONDATE, SAMPLEITEMNUMBER, ITEMNUMBER)                                              ]'||CHR(10)||
  q'[ Values                                                                                                        ]'||CHR(10)||
  q'[ (111, 339079775, '1', 'Microfaser PRIMABELLE®', 'TEXTILE',                                                    ]'||CHR(10)||
  q'[    '1 (=creme)', 'url', TO_TIMESTAMP('27.06.2016 15:49:35.000000','DD.MM.YYYY HH24:MI:SS.FF'), 'xxx', 'xxxx') ]'||CHR(10)||

添加CHR(10)只是为了使字符串更具可读性--这是为了我们的利益。接下来，添加一个RTRIM，因为所有这些额外的空格都可以使字符串变得很大，非常快:) (对于较大的字符串，我使用了同样的技术来使用CLOBs )

  v_sql :=
  RTRIM(q'[ Insert into xxxx$1                                                                                            ]')||CHR(10)||
  RTRIM(q'[ (ID, ITEM, ITEMSIZE, QUALITY, MATERIAL,                                                                       ]')||CHR(10)||
  RTRIM(q'[    COLOUR, IMAGEURL, CREATIONDATE, SAMPLEITEMNUMBER, ITEMNUMBER)                                              ]')||CHR(10)||
  RTRIM(q'[ Values                                                                                                        ]')||CHR(10)||
  RTRIM(q'[ (111, 339079775, '1', 'Microfaser PRIMABELLE®', 'TEXTILE',                                                    ]')||CHR(10)||
  RTRIM(q'[    '1 (=creme)', 'url', TO_TIMESTAMP('27.06.2016 15:49:35.000000','DD.MM.YYYY HH24:MI:SS.FF'), 'xxx', 'xxxx') ]')||CHR(10)||

接下来，我们需要为要替换的每个值添加“占位符”：

  v_sql :=
  RTRIM(q'[ Insert into xxxx<tab_name>                                                                                    ]')||CHR(10)||
  RTRIM(q'[ (ID, ITEM, ITEMSIZE, QUALITY, MATERIAL,                                                                       ]')||CHR(10)||
  RTRIM(q'[    COLOUR, IMAGEURL, CREATIONDATE, SAMPLEITEMNUMBER, ITEMNUMBER)                                              ]')||CHR(10)||
  RTRIM(q'[ Values                                                                                                        ]')||CHR(10)||
  RTRIM(q'[ (seq_xxxx.nextval, 339079775, '1', 'Microfaser PRIMABELLE®', 'TEXTILE',                                       ]')||CHR(10)||
  RTRIM(q'[    '1 (=creme)', 'url', TO_TIMESTAMP('27.06.2016 15:49:35.000000','DD.MM.YYYY HH24:MI:SS.FF'), 'xxx', 'xxxx') ]')||CHR(10)||

对于其余部分，请使用绑定变量：

  v_sql :=
  RTRIM(q'[ Insert into xxxx<tab_name>                                         ]')||CHR(10)||
  RTRIM(q'[ (ID, ITEM, ITEMSIZE, QUALITY, MATERIAL,                            ]')||CHR(10)||
  RTRIM(q'[    COLOUR, IMAGEURL, CREATIONDATE, SAMPLEITEMNUMBER, ITEMNUMBER)   ]')||CHR(10)||
  RTRIM(q'[ Values                                                             ]')||CHR(10)||
  RTRIM(q'[ (seq_xxxx.nextval, :1, :2, :3, :4,                                 ]')||CHR(10)||
  RTRIM(q'[    :5, NULL, SYSTIMESTAMP, :6, :7 )                                ]')||CHR(10)||

接下来，在运行时，替换表名“片段”(这里需要动态sql的唯一原因.)

  v_runsql := REPLACE ( v_sql, '<tab_name>', p_importpostfix );

现在你可以做几件事..。通过显示SQL来验证SQL：

  dbms_output.put_line ( v_runsql );

然后执行它，使用绑定变量“正确”：

  execute immediate v_runsql USING v_rec.item_id, v_size, v_quality, v_material, v_colour, v_rec.vid, v_rec.vid;