使用UTL在表格中插入数据 [英] Inserting data in table using UTL
问题描述
朋友,我写了一段代码,使用我在下面给出的UTL代码在newemp表中上传数据,但是我得到错误
1申报
2 EMPNO NUMBER(4);
3 ENAME VARCHAR2(10);
4 JOB VARCHAR2(10);
5 MGR NUMBER(4);
6 HIREDATE DATE;
7 SAL NUMBER(7,2);
8 COMM NUMBER(7,2);
9 DEPTNO NUMBER(2);
10 line varchar2(100);
11个名称文件UTL_FILE.FILE_TYPE;
12 begin
13 namesfile:= UTL_FILE.FOPEN('DIPRJDIR','empdata.txt','R');
14循环
15 UTL_FILE.GET_LINE(namesfile,EMPNO,4);
16 dbms_output.put_line('EMPNO:'|| EMPNO);
17 UTL_FILE.GET_LINE(namesfile,ENAME,10);
18 dbms_output.put_line('ENAME:'|| ENAME);
19 UTL_FILE.GET_LINE(namesfile,JOB,9);
20 dbms_output.put_line('JOB:'|| JOB);
21 UTL_FILE.GET_LINE(namesfile,MGR,4);
22 dbms_output.put_line('MGR:'|| MGR);
23 UTL_FILE.GET_LINE(namesfile,HIREDATE,5);
24 dbms_output.put_line('HIREDATE:'|| HIREDATE);
25 UTL_FILE.GET_LINE(namesfile,SAL,9);
26 dbms_output.put_line('SAL:'|| SAL);
27 UTL_FILE.GET_LINE(namesfile,COMM,9);
28 dbms_output.put_line('COMM:'|| COMM);
29 UTL_FILE.GET_LINE(namesfile,DEPTNO,2);
30 dbms_output.put_line('DEPTNO:'|| DEPTNO);
31插入newemp值(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO);
32结束循环;
33 utl_file.fclose(namesfile);
34 * end;
SQL> /
EMPNO:7839
ENAME:KING
JOB:PRESIDENT
MGR:0
错误
申报
*
第1行出错:
ORA-01843:不是有效月份
ORA-06512:在第23行
我的数据是低于给定的格式
7839KING PRESIDENT 000017-nov-1981 005000.00 000000.0010所以请帮帮我
这是因为 UTL_FILE.GET_LINE 过程,获取VARCHAR2作为第二个参数。
如果你把其他东西放在varchar2中,oracle会试图隐式地将它转换为正确的类型。
这对数字很有帮助,但使用Date数据类型时,oracle将使用 NLS_DATE_FORMAT
,它可能不符合文件中字符串的格式。
您可以通过在 UTL_FILE.GET_LINE
调用中使用VARCHAR2来修复它,然后使用 to_date
函数将其转换为一个日期数据类型。
但是,有一个更好的方法可以完成这项工作 -
您可以使用外部表读取文件,然后 insert-select
。
friends i had written a code to upload a data in newemp table using UTL code i given below but i get the error
1 declare
2 EMPNO NUMBER(4);
3 ENAME VARCHAR2(10);
4 JOB VARCHAR2(10);
5 MGR NUMBER(4);
6 HIREDATE DATE;
7 SAL NUMBER(7,2);
8 COMM NUMBER(7,2);
9 DEPTNO NUMBER(2);
10 line varchar2(100);
11 namesfile UTL_FILE.FILE_TYPE;
12 begin
13 namesfile :=UTL_FILE.FOPEN('DIPRJDIR','empdata.txt','R');
14 loop
15 UTL_FILE.GET_LINE(namesfile,EMPNO,4);
16 dbms_output.put_line('EMPNO :' || EMPNO);
17 UTL_FILE.GET_LINE(namesfile,ENAME,10);
18 dbms_output.put_line('ENAME :' || ENAME);
19 UTL_FILE.GET_LINE(namesfile,JOB,9);
20 dbms_output.put_line('JOB :' || JOB);
21 UTL_FILE.GET_LINE(namesfile,MGR,4);
22 dbms_output.put_line('MGR :' || MGR);
23 UTL_FILE.GET_LINE(namesfile,HIREDATE,5);
24 dbms_output.put_line('HIREDATE :' || HIREDATE);
25 UTL_FILE.GET_LINE(namesfile,SAL,9);
26 dbms_output.put_line('SAL :' || SAL);
27 UTL_FILE.GET_LINE(namesfile,COMM,9);
28 dbms_output.put_line('COMM :' || COMM);
29 UTL_FILE.GET_LINE(namesfile,DEPTNO,2);
30 dbms_output.put_line('DEPTNO :' || DEPTNO);
31 insert into newemp values(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO);
32 end loop;
33 utl_file.fclose(namesfile);
34* end;
SQL> /
EMPNO :7839
ENAME :KING
JOB :PRESIDENT
MGR :0
ERROR
declare
*
ERROR at line 1:
ORA-01843: not a valid month
ORA-06512: at line 23
and my data is in below given format 7839KING PRESIDENT 000017-nov-1981 005000.00 000000.0010 so Please help me
That's because the UTL_FILE.GET_LINE procedure, gets a VARCHAR2 as a second parameter.
If you put something else than a varchar2 oracle tries to implicitly cast it to the right type.
This worked quite well for you with numbers, but with a Date datatype, oracle will use the NLS_DATE_FORMAT
which might not meet the format of string in the file.
You can fix it by using a VARCHAR2 in the UTL_FILE.GET_LINE
call and then use to_date
function to convert it to a date datatype.
But, there is a better way to do this job-
You can use an external table to read the file and then insert-select
from it.
这篇关于使用UTL在表格中插入数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!