I am getting ORA-04043: object does not exist error in SQL loader. When command is being executed ge-CodePudding

CSV FILE CONTENT

portal,,
ex portal,,
,,
i_id,i_name,risk
1,a,aa
2,b,bb
3,c,cc
4,d,dd
5,e,ee
6,f,ff
7,g,gg
8,h,hh
9,i,ii
10,j,jj

CONTROL FILE CONTENT

options  ( 
  skip=4,
  PARALLEL=true,
  DIRECT=true
)
LOAD DATA
INFILE 'E:\sqlloader\testfile.csv'
APPEND
INTO TABLE LOADER_TAB
FIELDS TERMINATED BY ","
(
    i_id,
    i_name,
    risk
)

I am getting object does not exist but table name does exist in the schema system

select tab.owner, tab.STATUS
from dba_tables tab
where tab.TABLE_NAME = 'LOADER_TAB';

Also tried by giving scema_name.table_name but no luck.

options  ( 
  skip=4,
  PARALLEL=true,
  DIRECT=true
)
LOAD DATA
INFILE 'E:\sqlloader\testfile.csv'
APPEND
INTO TABLE SYSTEM.LOADER_TAB
FIELDS TERMINATED BY ","
(
    i_id,
    i_name,
    risk
)

Can someone help me on this I had searched for the answer and did all possible way but not getting the solution.

CodePudding user response：

You're almost there - here's a top to bottom run of the code, the only change being I've created a schema to hold the table and the path names for the CSV file. So follow the demo below and if yours does not get the same result, edit the question with the full output similar to below. Also, if you still get issues, try it without DIRECT/PARALLEL which will help us dig deeper into the "why".

SQL> create user myuser identified by mypassword;

User created.

SQL> alter user myuser quota unlimited on users;

User altered.

SQL> grant connect, resource to myuser;

Grant succeeded.

SQL> create table myuser.LOADER_TAB(i_id number(10),i_name varchar2(30),risk varchar2(30));

Table created.



x:\tmp>sqlldr userid=myuser/mypassword@db19_pdb1 control=loader.ctl

SQL*Loader: Release 19.0.0.0.0 - Production on Tue Nov 2 11:38:58 2021
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct

Load completed - logical record count 10.

Table LOADER_TAB:
  10 Rows successfully loaded.

Check the log file:
  loader.log
for more information about the load.



x:\tmp>sqlplus myuser/mypassword@db19_pdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 2 11:39:21 2021
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Tue Nov 02 2021 11:38:58  08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> select * from loader_tab;

      I_ID I_NAME                         RISK
---------- ------------------------------ ------------------------------
         1 a                              aa
         2 b                              bb
         3 c                              cc
         4 d                              dd
         5 e                              ee
         6 f                              ff
         7 g                              gg
         8 h                              hh
         9 i                              ii
        10 j                              jj

10 rows selected.