0

What i am trying is to read data from csv and insert it into external table after insert i have to update my orginal table with the values of extrernal table

because couldn't update table using sqlloader for that i have followed as per the link

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:70109473088345

create table external_table
    (EMPNO NUMBER(4) ,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
     MGR NUMBER(4),
    HIREDATE DATE,
     SAL NUMBER(7, 2),
     COMM NUMBER(7, 2) ,
     DEPTNO NUMBER(2)
   )
   ORGANIZATION EXTERNAL
   ( type oracle_loader
     default directory data_dir
     access parameters
     ( fields terminated by ',' )
     location ('emp.dat')
   )



 merge into EMP e1
    using EXTERNAL_TABLE e2
    on ( e2.empno = e1.empno )
    when matched then
            update set e1.sal = e2.sal
    when not matched then
            insert ( empno, ename, job, mgr, hiredate, sal, comm, deptno )
            values ( e2.empno, e2.ename, e2.job, e2.mgr, e2.hiredate, e2.sal, e2.comm, 
e2.deptno )

i have to do this job for that i have to create stored procedure to combine both.

How can i do? is it the correct way?

is it good to delete the external table?

jackyesind
  • 3,343
  • 14
  • 47
  • 74
  • Have you tried what you have here? What error messages, if any are produced? Also, where is 'emp.dat' located? – Shannon Severance Mar 04 '14 at 14:45
  • 1
    Within a stored procedure, a `create table` statement has to be handled as dynamic SQL, using either `execute immediate` or the DBMS_SQL package. – Shannon Severance Mar 04 '14 at 14:49
  • Create on temporary table inside the stored procedure with CSV file structure and load the data into the temporary table and update the Main table by joining or whatever the condition you need.. – Roshan Nuvvula Mar 04 '14 at 14:54

0 Answers0