Home » Developer & Programmer » Forms » sql loader with forms (Oracle forms 10.1.20.2,windows 7 -32bit)
sql loader with forms [message #571987] Wed, 05 December 2012 02:51 Go to next message
Akarsh
Messages: 16
Registered: November 2012
Location: India
Junior Member
I am using oracle forms 10g on windows 7 32 bit.

i want to load data from oracle form to oracle database.
i have written the following code on Push Buttons When-Button-Pressed trigger.

declare
usid varchar2(10):= get_application_property(username);
pwd varchar2(10):= get_application_property(password);
db varchar2(10):= get_application_property(connect_string);
v_ctl_file text_io.file_type;
v_live_path varchar2(1000):='D:\';
v_bat_file text_io.file_type;

begin

v_ctl_file := text_io.FOPEN(v_live_path||'test.ctl', 'w');
text_io.PUT_LINE (v_ctl_file, 'LOAD DATA');
text_io.PUT_LINE (v_ctl_file, 'INFILE '''||v_live_path||'data.csv''');
text_io.PUT_LINE (v_ctl_file, 'INTO TABLE test');
text_io.PUT_LINE (v_ctl_file, 'fields terminated by "," optionally enclosed by ''"'' ');
text_io.PUT_LINE (v_ctl_file, '(id,name,salary)') ;
text_io.FCLOSE (v_ctl_file);

v_bat_file := text_io.FOPEN(v_live_path||'load_data.bat', 'w');
text_io.PUT_LINE (v_bat_file, 'sqlldr userid = '|| usid || '/' ||pwd || '@' ||db ||
' ERRORS=1000000 control = '|| v_live_path||'test.ctl log = '|| v_live_path||'test.log');
text_io.FCLOSE(v_bat_file);

host(v_live_path||'load_data.bat');

Exception when others then
message(sqlerrm||dbms_error_text);
message(' ');
end;

SO my control file content is

LOAD DATA
INFILE 'D:\data.csv'
INTO TABLE test
fields terminated by "," optionally enclosed by '"'
(id,name,salary)

Batch File content:
sqlldr userid = SCOTT/TIGER@orcl ERRORS=1000000 control = D:\test.ctl log = D:\test.log

But on my command prompt i am getting the following messsage

SQLLDR is not recognised as an internal or external command,operable prgram or batch file.

Thanks

Report message to a moderator

Re: sql loader with forms [message #571988 is a reply to message #571987] Wed, 05 December 2012 02:58 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
HOST runs files located on application server. It appears that SQL*Loader is not installed on that computer, is it? Furthermore, control file and DATA.CSV are most probably on your (local) PC. IAS can't access them, which means that both of these files should also be on IAS.

Perhaps you should try with CLIENT_HOST instead which would allow you to use locally stored files (also, research use of WEBUTIL; I didn't use it so I can't assist here).

[Updated on: Wed, 05 December 2012 02:59]

Report message to a moderator

Re: sql loader with forms [message #572014 is a reply to message #571988] Wed, 05 December 2012 06:37 Go to previous messageGo to next message
Akarsh
Messages: 16
Registered: November 2012
Location: India
Junior Member
yes i am running my oracle form on the server not on client machine.
and in tnsnames.ora file SID is set to orcl.
When i run sqlldr directly from command prompt it perfectly runs and load data in to table.

But when i run through Orcle forms it shows the message.------->

Message 2100 not found;No message file for product=RDBMS,facility=ULMessage 2100 not found;No Message file for product=RDBMS,fcility=UL

Thanks

[Updated on: Wed, 05 December 2012 06:38]

Report message to a moderator

Re: sql loader with forms [message #572016 is a reply to message #572014] Wed, 05 December 2012 06:44 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Akarsh
When i run sqlldr directly from command prompt it perfectly runs and load data in to table.

Yes, because you have it installed on your computer. HOST doesn't see your SQLLDR executable (i.e. the one installed on your computer) - it sees files located on the server, not on your PC.

Report message to a moderator

Re: sql loader with forms [message #572077 is a reply to message #572016] Wed, 05 December 2012 21:55 Go to previous messageGo to next message
Akarsh
Messages: 16
Registered: November 2012
Location: India
Junior Member
so i have to mention sqlddr.exe path with HOST command??
I dont understand properly as i am new to this.

Report message to a moderator

Re: sql loader with forms [message #572081 is a reply to message #572077] Thu, 06 December 2012 00:01 Go to previous messageGo to next message
Akarsh
Messages: 16
Registered: November 2012
Location: India
Junior Member
whn i run SET command on command prompt it shows follwing output:


ALLUSERSPROFILE=C:\Documents and Settings\All Users
APPDATA=C:\Documents and Settings\hhhh\Application Data
CLIENTNAME=Console
CommonProgramFiles=C:\Program Files\Common Files
COMPUTERNAME=DDD
ComSpec=C:\WINDOWS\system32\cmd.exe
FP_NO_HOST_CHECK=NO
HOMEDRIVE=C:
HOMEPATH=\Documents and Settings\hhhh
LOGONSERVER=\\DDD
NUMBER_OF_PROCESSORS=2
OS=Windows_NT
Path=C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\system32\Wbem;C:\orant\bin;c:\ora
cle\product\10.2.0\db_1\bin;c:\DevSuiteHome_1\jdk\jre\bin\classic;c:\DevSuiteHom
e_1\jdk\jre\bin;c:\DevSuiteHome_1\jdk\jre\bin\client;c:\DevSuiteHome_1\jre\1.4.2
\bin\client;c:\DevSuiteHome_1\jre\1.4.2\bin;c:\DevSuiteHome_1\bin;c:\DevSuiteHom
e_1\jlib;C:\Program Files\Intel\DMIX
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH
PERL5LIB=c:\oracle\product\10.2.0\db_1\perl\5.8.3\lib\MSWin32-x86;c:\oracle\prod
uct\10.2.0\db_1\perl\5.8.3\lib;c:\oracle\product\10.2.0\db_1\perl\5.8.3\lib\MSWi
n32-x86;c:\oracle\product\10.2.0\db_1\perl\site\5.8.3;c:\oracle\product\10.2.0\d
b_1\perl\site\5.8.3\lib;c:\oracle\product\10.2.0\db_1\sysman\admin\scripts;
PROCESSOR_ARCHITECTURE=x86
PROCESSOR_IDENTIFIER=x86 Family 6 Model 15 Stepping 2, GenuineIntel
PROCESSOR_LEVEL=6
PROCESSOR_REVISION=0f02
ProgramFiles=C:\Program Files
PROMPT=$P$G
SESSIONNAME=Console
SystemDrive=C:
SystemRoot=C:\WINDOWS
TEMP=C:\DOCUME~1\hhhh\LOCALS~1\Temp
TMP=C:\DOCUME~1\hhhh\LOCALS~1\Temp
USERDOMAIN=DDD
USERNAME=hhhh
USERPROFILE=C:\Documents and Settings\hhhh
windir=C:\WINDOWS

Report message to a moderator

Re: sql loader with forms [message #572086 is a reply to message #572081] Thu, 06 December 2012 01:07 Go to previous message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You don't have to specify path to SQL*Loader executable because it is not installed on IAS anyway (and you can't specify path to your computer).

Once again, as far as I know (maybe someone else knows better): HOST is capable of using programs installed on an application server. It means that it can NOT use programs installed on your PC. Therefore, you have three options I know about:
  • install SQL*Loader on IAS. It means that CSV files (you plan to load) have to be located on IAS as well
  • use WebUtil which will remove a barrier of you being unable to use files on a client computer (i.e. your own PC)
  • instead of SQL*Loader, switch to "external tables" feature. It requires access to your database server (i.e. the CSV file has to be located there) as well as access to a directory (an Oracle object) which points to a database server file system directory that contains CSV file(s).

Report message to a moderator

Previous Topic: What does forms_ole.get_interface_pointer do?
Next Topic: list item becomes inactive after using once
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 09:38:37 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.