| Sql Loader Problem [message #535964] |
Sun, 18 December 2011 05:37  |
 |
aallan
Messages: 150
Registered: October 2011
|
Senior Member |
|
|
Dear All;
I have a Ctl File as:
Load Data
Infile 'D:\Format.csv'
append
Into table Sale.Import_Emp_Positions_Dtl_Tbl
FIELDS TERMINATED BY ";"
TRAILING NULLCOLS
(User_No, User_Responsility, User_Position)
my csv file have 3 column.
My Code in the button:
Procedure Pu_Uploade_Data_File_Prc Is
Cursor vc_get_emp_data Is
Select User_No, User_Responsility, User_Position
From Import_Emp_Positions_Dtl_Tbl;
LC$Fichier Varchar2(128) ;
vb_client_to_as Boolean;
vn_msg Number;
Begin
Begin
Delete Import_Emp_Positions_Dtl_Tbl;
:System.Message_Level := 25;
Commit;
:System.Message_Level := 0;
End;
LC$Fichier := PKG_FICHIERS.Selection ;
If LC$Fichier is not null Then
:Blk_1.File_Name := LC$Fichier;
End if ;
--vb_client_to_as := WEBUTIL_FILE_TRANSFER.Client_To_AS(:Dept.File_Name, 'C:\FORMAT.Txt');
Host('\\10.1.70.186\D\oracle\product\10.2.0\db_2\BIN\sqlldr.exe USERID=Sale/Sale@Sale Control='||'\\10.1.70.186\D\FORMAT.Ctl');
--Host('D:\oracle\product\10.2.0\db_1\BIN\sqlldr.exe USERID=Scott/Tiger@ORCL Control= '|| 'C:\FORMAT.Ctl'); if the db on the server
--Host('C:\Windows\System32\Cmd.exe /C D:\oracle\product\10.2.0\db_1\BIN\sqlldr.exe USERID=Scott/Tiger@ORCL Control= '|| 'C:\FORMAT.Ctl');
--Client_Host('Cmd.exe /C DEL C:\Test.Txt');
Host('Cmd.exe /C DEL C:\Test.Txt');
Begin
For I In vc_get_emp_data Loop
Insert Into Import_Emp_Positions_Dtl(Import_Id, Import_Date, User_No, User_Position, User_Responsility, User_Id, System_Date)
Values (:Blk_1.Import_Id, :Blk_1.Import_Date, I.User_No, I.User_Position, I.User_Responsility, :Global.User, Sysdate);
End Loop;
:System.Message_Level := 25;
Commit;
:System.Message_Level := 0;
Go_Block('Blk_2');
Execute_Query;
End;
Begin
Delete Import_Emp_Positions_Dtl_Tbl;
:System.Message_Level := 25;
Commit;
:System.Message_Level := 0;
End;
Exception When Others Then
Null;
End;
the case is:
i have the control file at a server and my csv file at my pc...
but nothing happend !!!!
where is the problem ?
[Updated on: Sun, 18 December 2011 05:39] Report message to a moderator |
|
|
|
| Re: Sql Loader Problem [message #535969 is a reply to message #535964] |
Sun, 18 December 2011 07:02  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
aallan wrote on Sun, 18 December 2011 12:37but nothing happend !!!!
where is the problem ?
I see no problem there - the code does what you told it. This part in the end of the procedure: Exception When Others Then
Null;
http://www.orafaq.com/wiki/WHEN_OTHERS
If you care, whether the procedure did something useful and did not fail anywhere, you should firstly remove that exception section at all.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Sql Loader Problem [message #536270 is a reply to message #536266] |
Tue, 20 December 2011 01:53 |
 |
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Wrong database:M:\>sqlplus scott/tiger@db_does_not_exist
SQL*Plus: Release 11.2.0.2.0 Production on Uto Pro 20 08:51:40 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
Wrong password:M:\>sqlplus scott/lion@ora10
SQL*Plus: Release 11.2.0.2.0 Production on Uto Pro 20 08:51:55 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Wrong username:M:\>sqlplus littlefoot/tiger@ora10
SQL*Plus: Release 11.2.0.2.0 Production on Uto Pro 20 08:52:56 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Correct username, password and a database:M:\>sqlplus scott/tiger@ora10
SQL*Plus: Release 11.2.0.2.0 Production on Uto Pro 20 08:53:27 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> |
|
|
|
|
|
|
|
|
|
| Re: Sql Loader Problem [message #536308 is a reply to message #536277] |
Tue, 20 December 2011 03:59 |
cookiemonster
Messages: 13926
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Looks like you have multiple oracle client installs. Sqlplus is version 10.1.0.4.2 and sqlloader is 10.2.0.1.0.
Presumably each is looking at a different tnsnames.ora file and one of those files is using the wrong DB.
You should use the TNS_ADMIN registry setting to ensure all oracle client installs are using the same tnsnames.ora
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
