sqlldr from forms 10g [message #476453] |
Thu, 23 September 2010 07:36 |
summisush
Messages: 19 Registered: September 2005 Location: mumbai
|
Junior Member |
|
|
hi,
we've deployed oracle forms & reports on oracle application server which is on a linux machine, database server is also on linux machine.
Our clients are using windows xp.
I've to make a form which connects to database serevr and run sqlldr.
How to do it?
Please help.
Sushma.
|
|
|
|
Re: sqlldr from forms 10g [message #476560 is a reply to message #476454] |
Thu, 23 September 2010 23:42 |
summisush
Messages: 19 Registered: September 2005 Location: mumbai
|
Junior Member |
|
|
Hi,
Can u tell me how to use dbms_scheduler, as i'm totally new to it. WE've been working in forms 6i and recently have moved to 10g.
Whar exactly was happening till now is:
WE were getting foxpro data in txt format from our user. WE were uploading this data to our oracle table through sqlldr. and later on this table is used for payroll processing.
Now we are planning to make payroll processing through forms 10g. So that we don't have to do this every month. We can hand over the program to Accounts section. Processing scripts are converted in Oracle databas eprocedures and functions. Only step left is loading this foxpro data to our oracle database via. forms 10g.
Thanks in advance.
Sushma.
|
|
|
|
Re: sqlldr from forms 10g [message #476572 is a reply to message #476571] |
Fri, 24 September 2010 01:01 |
summisush
Messages: 19 Registered: September 2005 Location: mumbai
|
Junior Member |
|
|
Hi,
We have only 1 user, who'll be uploading this data.
But our manager doesnot wants to give them access to our database server for security reasons.
Sushma.
|
|
|
|
|
|
Re: sqlldr from forms 10g [message #476638 is a reply to message #476621] |
Fri, 24 September 2010 05:50 |
summisush
Messages: 19 Registered: September 2005 Location: mumbai
|
Junior Member |
|
|
Hi,
I wrote a batch file on my windows machine as
echo starting sqlldr
sqlldr username/password@hostname control=<controlfilename>
echo sqlldr exeuted
but when i execute this from windows cmd prompt it gives error as
sqlldr is not recognised as an internal or external command
i think first we should connect to database server and then run this command.
But how can we connect to the database server from this batch file?
|
|
|
|
Re: sqlldr from forms 10g [message #477870 is a reply to message #476640] |
Tue, 05 October 2010 00:47 |
summisush
Messages: 19 Registered: September 2005 Location: mumbai
|
Junior Member |
|
|
Hi,
Finally i could complete this program.
Following is the code I used.
A text file , which is on client's machine is read and data is saved in oracle table.
Use webutil library to run the following code.
DECLARE
b VARCHAR2(200);
SA NUMBER;
begin
b:= CLIENT_GET_FILE_NAME('c:/', File_Filter=>'Text Files (*.txt)|*.txt|');
SAVEDATA('<tabkename>','<column1>,<column2>,<column3>',B,',');
END;
PROCEDURE SAVEDATA (p_table IN VARCHAR2,
p_cnames IN VARCHAR2,
p_dir IN VARCHAR2,
p_delimiter IN VARCHAR2 DEFAULT ',')
IS
l_input CLIENT_TEXT_IO.file_type;
l_theCursor INTEGER; -- default --exec_sql.open_cursor;
l_buffer VARCHAR2 (4000);
l_lastLine VARCHAR2 (4000);
l_status INTEGER;
l_colCnt NUMBER DEFAULT 0;
l_sep CHAR (1) DEFAULT NULL;
l_errmsg VARCHAR2 (4000);
connect_str VARCHAR2 (100) := 'username/password@sid';
connection_id EXEC_SQL.CONNTYPE;
cursorID EXEC_SQL.CURSTYPE;
sqlstr VARCHAR2 (1000);
BEGIN
--create table badlog( errm varchar2(4000), data varchar2(4000) );
connection_id := EXEC_SQL.OPEN_CONNECTION (connect_str);
cursorID := EXEC_SQL.OPEN_CURSOR (connection_id);
l_input := CLIENT_TEXT_IO.fopen (p_dir, 'r');
l_buffer := 'insert into ' || p_table || ' values ( ';
l_colCnt := LENGTH (p_cnames) - LENGTH (REPLACE (p_cnames, ',', '')) + 1;
FOR i IN 1 .. l_colCnt
LOOP
l_buffer := l_buffer || l_sep || ':b' || i;
l_sep := ',';
END LOOP;
l_buffer := l_buffer || ')';
EXEC_sql.parse (connection_id,
cursorID,
l_buffer,
exec_sql.V7);
LOOP
BEGIN
CLIENT_TEXT_IO.get_line (l_input, l_lastLine);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;
END;
l_buffer := l_lastLine || p_delimiter;
FOR i IN 1 .. l_colCnt
LOOP
EXEC_SQL.bind_variable (
connection_id,
cursorID,
':b' || i,
SUBSTR (l_buffer, 1, INSTR (l_buffer, p_delimiter) - 1));
l_buffer := SUBSTR (l_buffer, INSTR (l_buffer, p_delimiter) + 1);
END LOOP;
BEGIN
l_status := EXEC_SQL.execute (connection_id, cursorID);
EXCEPTION
WHEN OTHERS
THEN
l_errmsg := SQLERRM;
INSERT INTO badlog (errm, data)
VALUES (l_errmsg, l_lastLine);
END;
END LOOP;
EXEC_SQL.PARSE (connection_id, cursorID, 'COMMIT');
l_status := EXEC_SQL.EXECUTE (connection_id, cursorID);
EXEC_SQL.close_cursor (connection_id, cursorID);
CLIENT_TEXT_IO.fclose (l_input);
FORMS_DDL ('commit');
EXEC_SQL.close_connection;
END;
[EDITED by LF: reformatted the code]
[Updated on: Tue, 05 October 2010 01:17] by Moderator Report message to a moderator
|
|
|
|