1.1Purpose of the Document
Virtually every Oracle Applications system will have legacy or interface data loaded into it at some stage, either during a data migration when the system goes live or on a regular basis after go-live. Oracle only provides open interfaces or APIs for certain data areas, however even where these are provided a large amount of development time is usually required to build and test an interface. Where no open interface or API exists the development may be considerably more complex and the data loaded won't be supported by Oracle.
Data Load is a tool that loads data into any application running in Windows, and contains extra functionality for loading data and setup into Oracle Applications and other systems built using Oracle Forms and Oracle databases. Data Load loads data through the forms. This means the data is indistinguishable from that entered by users.
To load data using Data Load you setup Data Load to load into the forms and the forms load the data into your system. This non-technical approach means the load can be built and run by non-technical users, and by not having to write conventional interfaces into the database a huge amount of time and money can be saved.
1.2Document Usage
If we want to load data or setup information into a system we have usually got two options. Either enters the data through the forms or directly into the database which is 'behind' the forms. This means you either employ someone to enter data into the forms or write an interface to load data into the database. Entering data into a form is straightforward and you are using the method the applications' developers intended for entering data into the database - the forms. However, manually loading large amounts of data through forms can be time consuming and error prone. For this reason data is often loaded straight into the database, but this can involve a large amount of technical work to build a load into the database's tables.
Data Load provides a solution to this problem. It loads the data into the forms for you. The data is loaded into the database using the method intended by the applications' developers - the forms - yet the data is loaded much faster and more reliably than can be done manually. Being a non-technical product anyone can build a load into any form(s) in any application.
Loads can also be built and tested much faster than is possible with conventional database interfaces. Therefore, loads built with Data Load cost a fraction of conventional database interfaces, are ready to use in much less time than is possible for technical with technical solutions and can be built and run by anyone, not just developers. Data Load can be used to load data into any application that is accessible from Windows. It has been used to load data into major ERP & CRM systems such as Oracle Application.
2Data Load - Oracle Application
Two ways of loading data into applications using Data Load:
Copy & Paste Loads - Load data into any application.
Forms Playback - An even faster way of loading data that is exclusive to Oracle Forms.
2.1Copy & Paste Loads
The easiest way to load data into a form is using a keyboard and mouse and this is what Data Load does for Copy & Paste loads. You enter the data, keystrokes and mouse clicks in Data Load that you want loaded into your form(s). When you run the load Data Load effectively presses the keys and clicks the mouse as you specified. Any keys on your keyboard can be used by Data Load, in any combination, and the left and right mouse buttons can also be used. And because Data Load calls the low-level Windows code that is called when you actually press a key or use your mouse, the application you are loading data into cannot tell the difference between data entered by you at the keyboard or Data Load.
To build a Data Load write down what keys and mouse clicks you use to enter one record in your application. Now enter that into a Data Load row and copy the instructions down into as many rows as the number of records you are going to load. Finally, paste your data into cells in Data Load that correspond to the fields in your form. Your load is now built!
To load data as quickly as possible Data Load uses Windows copy & paste to copy data into form fields. Where the actual keystrokes should be sent instead the data just needs to be preceded with a backslash, ''. To send mouse clicks use the *ML or *MR commands to send left or right mouse click respectively. Please read the User Guide for more information about keystrokes, Data Load commands and mouse clicks.
2.1.1Controlling the Load
Data Load can send data, key strokes and mouse clicks far faster than applications can handle them. To slow down Data Load there is a comprehensive set of Delay options, using both the Delays form and the *SL (Sleep) command which cause Data Load to wait at key points.
For the fastest, most reliable copy & paste loads use the Load Control functionality in Data Load Professional. Load Control enables Data Load to monitor a log file written by the application you are loading data into. Having configured Data Load to use the log file your load will not run faster than the application is processing the data. No delays are required and Data Load will run as fast as possible without overloading the target application. Another great benefit of Load Control is that if an error occurs in the application Data Load will stop sending data. Therefore loads can be run unsupervised.
2.2Forms Playback
All versions of Oracle Forms contain 'record' and 'playback' functionality. When you run Forms in record mode a file is written detailing everything you did in the forms session. Run forms in playback mode and the forms will do whatever is specified in the file being played back. So, if you enter an customer's order in your order entry system when in record mode then playback that file on another system that order will be loaded into the second system. Because the playback is run by the forms software the load runs as fast as the forms can possibly work. This is even faster than is possible with Copy & Paste loads using Load Control because the forms run the load internally and what you see on screen is the form only showing you what is happening rather than actually driving the load. Along with the speed the load is also totally reliable and stops if an error or warning message is shown in the form.
Data Load can edit the files record by Oracle Forms and save a new playback file containing the data you want to load. To create a load to enter a supplier into your system all you need to do is enter a supplier yourself while the forms are in record mode. Next edit that file in Data Load and enter the supplier data you want to load. Save the file and run Oracle Forms in playback mode specifying the file you just saved. Your suppliers will now be loaded. Using this technology you can build a load in the time it takes you to manually enter the data in your form(s).
Data entered using forms playback is loaded seriously quickly. The load runs many times faster than is possible using Copy & Paste and is usually only slightly slower than conventional interfaces loading data directly into the database. The data is loaded through the forms so you know it will be entered into the database correctly, in the same way as if you had entered the data in the form yourself. Best of all, this functionality can be used by non-technical users. Now end users can build and run loads that are almost as powerful as those built by developers, and in a fraction of the time it takes a developer to build a load.
Forms playback is provided as a demonstration in Data Load Classic and loads are restricted to 5 records. However, this functionality is fully enabled and unrestricted in Data Load Professional.
2.3Data Load Command
TAB <Tab>
ENT <Enter>
*UP <Up Arrow>
*DN <Down Arrow>
*LT <Left Arrow>
*RT <Right Arrow>
*SP Save & Proceed
*FE Field Editor
*PB Previous Block
*NB Next Block
*PF Previous Field
*NF Next Field
*PR Previous Record
*NR Next Record
*ER Erase Record
*DR Delete Record
*FR First Record
*LR Last Record
*SAVE Save Record
*SB Send a single space character
*ST Select entire field text.
*SLN or *SL(N) Pause for N seconds. Note 1
*BM Block Menu
*AX Alt + X where X is a single letter (A-Z). Note 2
*FI Find +
*FA Find All +
*QE Query Enter +
*QR Query Run +
*CL Clear Field +
*IR Insert record +
*CW(window) Change to window. +
*ML(coordinates) Position the mouse at coordinates and press the left button. ++
*MR(coordinates) Position the mouse at coordinates and press the right button. ++
3Data Load – Live Example
Depending on the navigation flow of cursor in Oracle Apps, data obtained should be column wise organized in the data load file. (Optionally, data in Microsoft Excel may be exported to a Data Load file)
Set Data Load commands between the columns of data, to replicate the keyboard navigation, with care to include errors due to server response time.
Example:
Following example illustrates a typical application flow
Requirement: To upload select Messages from one instance to other.
Open up the Messages form.
If data were entered manually, one would have typed “XXI_GLG_PROJ_NUM_NV” in Name field, then pressed the Tab button and then typed “US” in Language filed and so on and so forth.
If the data to be uploaded were in an excel sheet as below, one may highlight the relevant data in the Excel window, and then used the data import from Data load.
Imported data would appear as follows, in the Data Load window.
To add the tabs and save operation, right click on the data columns and columns from data columns to keystroke columns.
This would render the data load file as below
Highlight the records you want to import. (Selective Importing) and click “Send Data to Form” button with the Messages form open.
Data in the sheet will be transferred to the application.
Note: Since the process is heavily dependant on external factors, and is not completely error free, while using data load for data migration, the consultant should verify the following.
Presence of at least one keystroke column between two adjacent data columns.
Trial check of the navigation intended using a few dummy records, with out save.
Check out the server response time and based on the same, provide fro sleep time (in seconds) between fields / records
4Pitfalls / Issues with Data Loader Tool and How to Over come those
4.1Copy and Paste in Data Loader Classic version
The Copy and Paste within data Loader is some times funky. It is the same experience of one of consultant working with us in this project. He told me this solution is always prepare whatever commands and keystroke we want to type in Data Loader in MS Excel Sheet first. Then copy the rows from MS Excel Sheet to Data Loader Tool.
4.2Key stokes are starting before forms are invoked
This is happening because of there is not enough delays commands given in the data loader tool. While using data loader tool for multiple forms e.g. after data entry in one form we will invoke another form. The forms to pop up on screen sometime takes one second or more. After first row trial run we will come to know which form / column takes more time. According we need to add sufficient delays in the data loader. This is achieved using *SL1 command (For One second delay), *SL2 (For two second delay) etc.
Also data loader running on one PC / Machine might not run well in another machine.
There are reasons like other PC might be having lower CPU speed OR memory etc.
Due to this forms / fields are processed slower. In such Case only way to correct this is increase the delays in data loader tool.
4.3Avoid using Mouse Clicks
As mouse click has got hard coded co-ordinates, it is some time causes problem if form position gets moved bit. Also across PC / Machine the desktop area may not be same. In such cases data loader is dependant will work fine on one PC but not other. To avoid this do not use mouse click, try to use alternate window commands instead. For example you can close form using mouse click by clicking standard window X button to Close the form, but there is alternate command that is nothing but CTRL+F4, Use ^{F4} to close the form.
4.4Monitoring the Data Load
Data loader is monotonous data entry tool. It does not recognise any exceptions, network failure or any other problems. It is always safe to Monitor the data loader during the data loading process. For example if network fails in between data loading process, data loader go on keying the all rows although form is not responding etc. In such case it will be mess. Better to monitor it.
5FAQ
Q. Where can I get a trial version of Data Load Professional?
A. Data Load Classic is the trial version for Data Load Professional. As well as containing the original Copy & Paste load functionality, Data Load Classic also contains a demonstration of the Data Load Professional forms playback functionality. The forms playback functionality is limited to 5 record loads in Data Load Classic, which should be enough to prove the speed and effectiveness of forms playback.
Q. How much does Data Load cost?
A. There are two versions of Data Load, Classic and Professional. Data Load Classic is absolutely free of any charges to download, use and distribute. Data Load Professional is a commercial product containing advanced functionality for loading data via the forms server using the Oracle Forms playback technology. See also Buy Data Load.
Q. Why isn't data being sent to my form(s) (Copy & Paste mode, not Forms playback)?
A. First you need to establish whether it is Data Load that isn't sending the data or Oracle that isn't pasting into the forms. Run the following tests to identify the issue:
Use Data Load to send your data to something other than Oracle, E.g. try sending your data to notepad.
Try to copy and paste data into Oracle yourself.
Send your data from Data Load as simulated keystrokes, i.e. prefix the data with a ''.
If 1 and 3 succeed while 2 fails then Copy and Paste isn't working in the Oracle forms. Two common reasons for this are:
When your applet is unsigned, i.e. your windows have yellow bars at their base copy and paste doesn't work. Run 'appscert.bat' to create the 'identitydb.obj' file.
Copy & Paste won't work in flexfields in release 11.0.x until 11.0.3 and patch 857097 has been applied.
If test 1 didn't work or test 2 does work then contact Data Load support.
Q. Why don't my mouse clicks work (Copy & Paste mode, not Forms playback)?
A. Follow the same testing procedure as described in the previous answer but with mouse clicks rather than data.
Q. Why can't I use Excel-like formulas and functions in Data Load?
A. Data Load doesn't support Excel-like functions and formulas and we don't intend to introduce this functionality because we would just be replicating Excel. We recommend that users who need to use formulas build their loads in Excel then transfer the data to Data Load when the load is ready. To help facilitate Data Load V4.2 and above includes fast Excel Import functionality. This allows the user to import data from Excel in one mouse click and there is also the option to start the load as soon as the data has been imported too.
Q. Does Oracle support data loaded into Oracle Applications using Data Load?
A. Although Oracle does not support the Data Load product they do appear to support data loaded via Oracle. Indeed, Oracle Support have been known to recommend Data Load to customers in certain situations. This has been recently confirmed by the posting of a Note on Metalink, Note 147380.1, which again appears to give Oracle's blessing to Data Load.
Q. Why doesn't *PB or *NB work in Oracle Applications 11i?
A. A number of users have reported issues with *PB and *NB in 11i, and while we have not been able to reproduce these we have identified two possible causes of this problem:
Only JInitiator 1.1.8.x should be used with 11i. Companies using 1.1.7.x or the Appletviewer are not using an Oracle certified version so some functionality may not work. At then time of writing, the latest certified version of JInitiator is 1.1.8.19 and we recommend that version is tried.
Developer/2000 6i had problems with the PgUp and PgDn keys before Patch 2. Please ensure you test Data Load on a system with the highest Developer/2000 patch possible and at least Patch 3, since Patch 2 is not certified for use with Applications. The current latest certified patch for most platforms is Patch 10 and we recommend that is used.
If you are unsure what versions of software you are using please ask your Applications DBA.
Q. Why doesn't the *CW() command change forms in 11i?
A. The *CW() command is to change the active window not the form. The command was actually introduced with the intention of it being used to change forms, and this works fine in 10.7 NCA and 11.0.x because each form is a separate window. However, we knew that 11i returned to the single parent window approach so named the command *CW rather than something like *CF (Change Form) because it does actually change the window not the form.
In order to change the active form in 11i you will need to enter the keystrokes and/or mouse clicks in Data Load that will open or activate the other form for you. In other words, make a note of how you would do this when using the Applications and setup Data Load to action the same sequence of keystrokes.
Q. In forms playback, how can we easily check what data has been loaded?
A. During the playback you can also record by adding a record parameter to the command line. In 11i this needs to be added to the URL. For example, add the following to the URL, replacing '[filename]' with the name of a file to record to:
&record=[filename]
The whole load will be recorded to that file. When the load completes open this file in Data Load and you will be able to see what the load did.
Q. Can I use Data Load with Oracle Applications 11i?
A. Data Load works with any Windows based applications and there are no issues using Data Load with 11i. A number of changes have been made to shortcuts in 11i, therefore the commands.dat file does need to be updated to reflect these changes when using the Copy & Paste functionality. The new commands file is included in Data Load V4.2.2 and above, while the revised commands file is available to download for all older versions.
Q. How do I toggle a checkbox in Keystroke simulation mode?
A. Use a single space in the cell or the *SB command
Q. How do I select an item from a pick drop down list?
A. Values can be selected by the first letter of the value. Where 2 values begin with the same letter, repeating the letter moves down the list. For example:
Column containing I would select Indirect
Column containing C would select Contract
Column containing CC would select Capital
Q. Is Data Load Classic open source/can we have the source code?
A. No! Even though Data Load Classic is free it is not open source software and the source code is not available.
Q. The Data Load spreadsheet isn't big enough, how do I make it bigger?
A. Data Load versions prior to V4.1 had a fixed grid size, but inserting rows or column using "Edit, Insert…" where data has already been entered would add rows or columns to the grid. V4.1 and beyond have grids whose size is limited only by the PC's memory.
Q. How do I send non-printing keys like the function keys (F1…F12) to the target window?
A. First, precede your data with a (backslash) to indicate you want to send keystrokes. Then use the appropriate character code - see Sending Keystrokes.
Q. What is the latest version of Data Load?
A. V4.3.5.0.
Q. When I start the load the first 1 or 2 fields are missed but this only happens at the start, why?
A. This appears to be a forms issue. You have to work around this by adding keystrokes at the start of your load to compensate for this.
Q. Why isn't the window I want listed in the Window Name drop down box?
A. Prior to V4.1 only running Oracle NCA forms were listed in this list. If you want to use a window which isn't Oracle NCA, or an Oracle form which isn't currently running, type the window name in this box manually. V4.1 and beyond lists all active widows.
Q. Why doesn't the Oracle pick list respond when I send data to it?
A. You must send data to a pick list as keystrokes, not the default Data Load cut and paste. Prefix your data with a (backslash) to force Data Load to simulate keystrokes.
Q. Will Data Load start automatically when I double click on a .dld, .fld or tab delimited file?
A. From V4.2 onwards Data Load automatically configures Windows to use Data Load to open .dld and .fld files. Prior to this release you would have to do the following. Double click on a .dld file (or your tab delimited file) and in the "Open With" dialog box click on the "Other" button and select Data Load.exe in the directory when it was unloaded. Enter a description for .dld files and press OK.
Q. I am using 10SC and commands like *PB and *SAVE won't work. Why?
A. Before V4.1 the built in commands were scripted for NCA and wouldn't work with the different keyboard shortcuts used in 10SC and other applications. You could, however, write powerful keyboard control statements in Data Load which give you access to all combinations of keyboard characters. Use these to access new or unsupported commands. See Sending Keystrokes. V4.1 introduced "Command groups" that allow you to specify which command definitions should be used.
Q. I have a large amount of data that I need to send as keystrokes, not using Copy & Paste. How can I quickly prefix this data with the necessary ''?
A. Data Load has functionality to change single or multiple cells to & from keystroke and Copy & Paste cells. Use "Convert To…" from the Edit or grid pop-up menus to access this functionality. See Convert To.
Q. Can I change the names of the command groups in V4.1, or add or delete groups.
A. Yes. The commands.dat file holds the command definitions in a TAB delimited format. Simply edit this file and you can have as many command groups as you want with names of your choice.
Q. Can I use Data Load with Windows 2000?
A. Yes, Data Load has been extensively tested with Windows 2000 and can be used without any issues. Indeed, Data Load is now developed on Windows 2000 and this operating system is the primary testing platform so there should be no issues particular to this OS.
Post a Comment