Forms :: Data Upload Using SQL Loader
Jun 8, 2011
Iam working with a form thru which I need to port nearly 7 laks of data into table. Earlier I had created a form thru which I read the data from the text file and inserting into the table. This was taking lot of time and as well after an hour or so, after porting 50k rows the program got terminated and shows an error like Network Inturpted.
So I have decided to use some other option and found that I can use either SQL Loader or external Tables. I had choosed SQL loader option and created a form along with a control file and batch file based on some forum posting.
Control File
LOAD DATA
INFILE 'D:SethuPayClock DumpCLK_050611clock_dump.txt'
INTO TABLE ARS_CLOCK_DUMP
(TDATE POSITION(01:08) DATE 'YYYYMMDD',
VER POSITION(09:10) CHAR,
EMPNO POSITION(11:15) CHAR,
TTIME POSITION(16:19) CHAR,
BRADD POSITION(21:22) CHAR
)
[code]....
With above all the form works perfectly in local system which is the development evironment and also client PC. And I was able to port those 7 laks rows in 3 miniutes. Now the real problem, If I need to move this to live application server, I had to move three files [ FMB, CTL and BAT ]. I have some problems in moving the other two files to the application server [ waiting for approval from bozz ]. And more over, I had to hard code the user id and password in the BAT file, i think which may not be a best practice and also not safe.
So I have decieded to do all from forms and found same sort of script. I took it and modified to my needs.Code from form's when button pressed which is not working
declare
usid varchar2(10):= get_application_property(username);
pwd varchar2(10):= get_application_property(password);
db varchar2(10):= get_application_property(connect_string);
msqlldr varchar2(300);
ctrl_filename varchar (300) := 'D:SethuPayModifiedFormsars_clock_dump.ctl';
data_filename varchar2(300) := 'D:oracleproduct10.2.0db_1BINabc.dat';
fname varchar2(1000);
[code]....
View 32 Replies
ADVERTISEMENT
Dec 6, 2010
i have create database link with msaccess2000 its working fine in forms 6i i am trying to upload data by using forms 6i while compiling form is automatically closed my code is this
begin
insert into baba1
(select *
from baba1@access_db
where dte between to_date(:block3.fdate,'dd-mon-yyyy')
and to_date(:block3.ldate,'dd-mon-yyyy')
[code]....
View 8 Replies
View Related
Apr 15, 2013
I'm using Oracle Database 11g R2 need to upload Telecom CDRs to the database on daily basis , it's huge data and changeable , an example of my file in linux Redhat 5 server as below ,
INDRtotalduration = 00:00:00
origin_matrix = 4186603ec003ef01
triggering_key = 665000207
Start_Date_And_Time = 03/04/2013 09:24:10
IMSI = 418666651000207
[code]......
there is no problem with this i think i can use SQLLDR to upload this file , but the problem here the positions of the columns in the file could change depending on user behavior it could be the first row comes in the third row or any row and maybe more rows appears ,
locind = 0
origin_matrix = 4186603ec003ef01
Start_Date_And_Time = 03/04/2013 09:24:10
INDRtotalduration = 00:00:00
IMSI = 418666651000207
triggering_key = 665000207
[URL].......
this is sample of the file i could be more than 100 rows , and the position of the field and field names could be change every time depending on the Subscriber usage , is there any way to upload the file but after checking the field name in the file and matching to corresponding column name in the table .
View 5 Replies
View Related
Mar 18, 2010
I have to load .csv file contents to table . I a m using Oracle Forms 10g, I have kept .csv file and .ctl file in application server c: DIRECTORY. In Form i have called host command to execute the batch file . But data is not loading as well as no error. Even i have batch file also in Application Server C: path .
Code i have used in Form is
HOST('C:TEST.BAT');
contents of the batch file are
C:oraclexeapporacleproduct10.2.0serverBINsqlldr.exe csd/a@iismig control=c:GL2009_TEST.ctl bad=C:GL2009_TEST.bad skip=1 log=c:gl2009_test.log
contents of the .ctl file is
LOAD DATA
INFILE 'C:GL2009.CSV'
REPLACE
INTO TABLE F_GL_SMRY_TEMP
FIELDS TERMINATED BY ","
(ref_id,tran_date,dr_amt,cr_amt,acct_code,sub_code,cctr_code,sundry_code,dept_code)
if i run test.bat in manual mode without Forms Application .. it works fine with no error. sqlloader is installed in application server .
View 6 Replies
View Related
Oct 1, 2009
I need to upload data from XML file to oracle database table.For this I have referred some websites in which solutions given in a such a way that loading entire XML file into LOB column inside the table.But I want only data to be extracted from XML file.
Will SQL Loader be best option?
View 1 Replies
View Related
Jul 25, 2013
I am new to the Upload data from excel to Table..... how to implement on this.....I need code for UpLoad CSV/XLS Files to the Table ....Table name T_UPLOAD have contains 40 columns....
View 3 Replies
View Related
Sep 20, 2010
oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
I am uploading the data file which is a .csv, into one of the tables.. Few columns of the data file, contains sentences in German - like- Lötstelle Zinnspritzer Zinnschluß .. and while loading the problem that i encounter is - it doesnt recognize the commas, and doesnt know where the field ends..
e.g., in this line 1540,Lötstelle Zinnspritzer Zinnschluß,solder short,45230,94885,0,0,0,0,0,0,0,0,0,0,0,0
sometimes it assumes comma after 1540,Lö,tstelle and then no commas till Lö, tstelle Zinnspritzer Zinnschlußsolder short,
and sometimes, the whole as one line Lötstelle Zinnspritzer Zinnschlußsolder short4523094885,0,0
View 2 Replies
View Related
Jul 1, 2010
I have a problem with my DB agent. it doesn't upload data correctly, so I can't see updated information in first page of EM ( it doesn't show cpu usage, Active Sessions, Listener properties,...). when I checked the logs, I found that emagent.trc shows this error: "exceed max amount of upload data: 77 files, 52M, disabling collections warn : collector : disable collector"
( I use Oracle Database 10g on Suse Linux 10.2)
View 3 Replies
View Related
Dec 14, 2012
Is it possible for Access to extract data from an Oracle database and upload it directly?
Currently we have a business process where data is being extracted in scheduled queries (30+) to Excel spreadsheets, then manually edited to remove heading lines and imported to an Access database. I see an opportunity to automate a time consuming manual activity by having the Access db extract the data and directly upload it.
View 3 Replies
View Related
Oct 8, 2013
oracle forms 6i
oracle ebs 11.5.10
database 9i
i create new custom application in my ebs 11i and i want to upload file in our custom application and save it as file system in my server lunix
how i attach my file and i want to save it not as blob but just upload to my server and after download it?
View 2 Replies
View Related
Jun 12, 2012
I have to do upload into the table through a csv file . The table's primary key i have to load the rest through user's uploaded file. Is it possible to do the data loading to the table only to required columns and fill the other columns from backend. Or is there any other way to do this?
View 1 Replies
View Related
Jan 18, 2011
I have upload an image in forms6i, in oracle apps
On button press event, code is...
DECLARE
V1_PATH VARCHAR2(50);
BEGIN
V1_path := get_file_name('C:IMAGE', NULL,
file_filter=> 'JPEG Image (*.JPG,*.JPEG,*.JPE,*.JFIF)|*.JPG|Bitmap Image (*.bmp)|*.bmp|GIF Image (*.GIF)|*.GIF|TIFF Files (*.tif)|*.tif|All Files (*.*)|*.*|');
DECLARE
[code]....
But it giving error message when preesing the button i.e. FRM-47105: NO image name specified.
View 2 Replies
View Related
Jan 23, 2013
I'm using oracle 10g and Forms 9i . I need to upload a PDF file from client to server no to database, just like File, is there any way to get it, no webutil using.
View 1 Replies
View Related
Nov 6, 2013
Do you have sample codes to upload file from client to server using 11g forms?
View 2 Replies
View Related
Jun 1, 2010
I'm new to using Webutil to upload and view files and am receiving the error msg 'WUT-113 Too many rows matched the supplied where clause' The process works fine for uploading photos but not when I try and upload documents and pdf files.
The code example (for documents) is outlined below but think that the issue must be to do with some sort of incorrect Webutil configuration on my machine.
declare
vfilename varchar2(3000);
vboolean boolean;
[Code]...
The :blob_id column is populated on the form with the when-create-record trigger. Why is the program at time of upload to the db saying that more than 1 row is being written to the db table?
View 4 Replies
View Related
Aug 30, 2010
Using Forms 10g and Webutil. I am uploading my file using the following code. The Status shows file upload is Successful.. but NO Record exists in the Table...!
Here is my Table Structure:-
CREATE TABLE ST_ATTACHMENT_ACCOUNT
( ACCOUNT_CDE NUMBER(8,0) NOT NULL,
FILE_NAME VARCHAR2(100),
FILE_BLOB BLOB)
TABLESPACE PRODTABLES;
Here is my -
PROCEDURE UPLOAD_DB IS
lb_success boolean;
ls_full_filename varchar2(200);
ls_filenm varchar2(100);
BEGIN
[Code]...
It shows me the progress bar for upload also but I cannot see any record on the table.
SELECT dbms_lob.getlength(FILE_blob)
FROM ST_ATTACHMENT_ACCOUNT
WHERE ACCOUNT_CDE = 2;
0 rows selected... what is wrong...?
View 8 Replies
View Related
Apr 19, 2010
I want upload file to server and i know i can use Java Bean but i don't know how i use java beans?
View 3 Replies
View Related
Oct 29, 2010
I want to create a form in oracle 10 g . In this form i want download & upload any excel file.
View 2 Replies
View Related
Nov 24, 2010
download / upload data from / to database through oracle forms 6i which is deployed in oracle apps 11i.I am using DDE/OLE2 but it is downloading if form is stand alone but when i post this form in my UNIX server, it is not downloading.
Oracle apps is in UNIX and i want excel to be in client (windows).what procedure to be used.
View 1 Replies
View Related
Mar 27, 2011
I have a major issue because I have to finish this project by tomorrow and I don't know how I could create a button on a form that will pop a window where the user can browse his system for and Image, select it and upload it on the Database.
The idea is that I have this table called APPLICANTS with columns like:
applicant_id
name
surname
housenumber
street
town
photo
I have created a registration form for the applicant inorder to register to the system but I just can't figure out how to upload a photo of the applicant.
View 5 Replies
View Related
Jul 16, 2013
I need to upload a file from the client to app server in runtime.
Opening some kind of explorer to choose the file then upload the file and safe the path in a database column.
View 3 Replies
View Related
Oct 3, 2010
I have used webutil_file_transfer.Client_To_AS_with_progress to upload files from client to Application Server using Forms 10g.However, now i want to save file in database and not upload to database as blob.I mean I want to save the file from client TO a folder available in the database server.I was wondering, there is no documentation available on WEBUTIL.
View 6 Replies
View Related
Mar 12, 2013
i have succusffly install webutil and also i did configuration also using this url
[URL]...
and i succussfuly upload and download .pdf using my form on my test database server my test database its a single database its version also 10.2.0.4.0 .but when i upload and download my .pdf file on my live database server using this same form its give me error while upload
Quote:FRM-40735: WHEN - CUSTOM -ITEM -EVENT trigger raised unhandled exception ORA-06509
my live database server its a RAC Database server its version 10.2.0.4.0 . i serach on google on this ora-06509 they told me
Cause: This indicates a version clash between some package distributed with an Oracle product and the product executable.
Action: Contact Oracle Support Services.
but my question its my test database server and live database both server version are same its 10.2.0.4.0 just only its differece its test database its a single database server and live its a rac database server.
View 14 Replies
View Related
Jul 11, 2013
I have a requirement to import text files which are generated from 3d modelling software xsteel where it records all geometric information and i want to import this information into oracle table.
CREATE TABLE dstv_head ( wo_no VARCHAR2(12),struct VARCHAR2(12),rev_no NUMBER,
mark VARCHAR2(12),pos VARCHAR2(12),grade VARCHAR2(12),qty NUMBER,PROFILE VARCHAR2(24),TYPE VARCHAR2(12),
len NUMBER,width_web NUMBER,width_bottom NUMBER,flange_thk NUMBER,web_thk NUMBER,radius NUMBER,kgm NUMBER,
kgm1 NUMBER,kgm2 NUMBER,bevel_plus NUMBER,bevel_minus NUMBER,holes_yn VARCHAR2(1),holes_v_yn VARCHAR2(1),
hole_x_dim NUMBER,hole_y_dim NUMBER,hole_dia NUMBER,no_of_holes NUMBER)
-- All the data which has to go under specific field for example **9005.nc1 will go into wo_no field, 1239401A will go under struct.
ST
** 9005.nc1 --WO_NO
1239401A - STRUCT
1 -REV_NO
9005 -MARK
9005 --POS
S275JR --GRADE
2 --QTY
[code]....
View 24 Replies
View Related
Sep 26, 2010
i want to create a simple form in this form i want to upload the image on the image item with browse button that open a diaglog box that go to the image i select the image and this image is display on image item on the form.
View 1 Replies
View Related
Feb 7, 2012
We are using Oracle 10g, on redhat linux (version not known to me).I'm running a Perl script that reads in data from Visual FoxPro tables, creates datafiles on the fly from these FP tables, then SQL-Loader runs and uploads all the data to matching tables in Oracle.
Problem:My upload runs, but I'm not getting the data in some of the fields that I should be. It is being uploaded to the wrong fields.
I had to add more columns to the Oracle tables in order to capture the data coming from FoxPro. These columns were/are in FoxPro. In the past, they were not needed in Oracle. I was using the FILLER command in the Control files to skip these. Now they are needed in Oracle. The new columns get added to the end of the table in Oracle, so they are not in the same exact order they are in FoxPro. It is these new columns that are not getting the correct data.
I've learned in the past that I need the fields/columns in the control files to match the order they are in the FoxPro tables from which the data comes. My Question is, Does the Oracle table column order have to match the Control file column order. Like so: FoxPro table column order = control file column order = Oracle table column order
View 1 Replies
View Related
Jun 25, 2008
I have a data file that looks like:
REC001;TO_NAME;TO_ADDR;TO_PHONE
REC002;ITEM_ID1;DATE_DELIVERED1
REC002;ITEM_ID2;DATE_DELIVERED2
REC002;ITEM_ID3;DATE_DELIVERED3
REC002;ITEM_ID4;DATE_DELIVERED4
i want to load this in the Database using SQL LOADER in this format:
NAME | ADDR | PHONE | ITEM | DATE DELIVERED
-------------------------------------------------
TO_NAME TO_ADDR TO_PHONE ITEM_ID1 DATE_DELIVERED1
TO_NAME TO_ADDR TO_PHONE ITEM_ID2 DATE_DELIVERED2
TO_NAME TO_ADDR TO_PHONE ITEM_ID3 DATE_DELIVERED3
TO_NAME TO_ADDR TO_PHONE ITEM_ID4 DATE_DELIVERED4
Basically i want the name, addr, phone from REC001 to be repeated every time i load REC002.
View 1 Replies
View Related
Aug 19, 2010
Is it Possible doing SQL LOAD into Varray table having two inner objects.
find the structure of the table and its types for your reference
CREATE OR REPLACE TYPE OB_TEST_INFO AS OBJECT (
AGE NUMBER ( 3 ),
NAME VARCHAR2 ( 14 )
);
/
CREATE OR REPLACE TYPE OB_TEST_INFO_VARRAY AS VARRAY( 400 ) OF OB_TEST_INFO ;
/
CREATE OR REPLACE TYPE OB_TEST_MAINTENANCE AS OBJECT (
BREAKOUT_TYPE NUMBER ( 1 ),
EXISTING_STRIPS_FLAG NUMBER ( 1 ),
OB_TEST_INFO OB_TEST_INFO_VARRAY
);
/
[Code]....
View 12 Replies
View Related
Aug 3, 2012
I want to populate totale number of record in the file. Usually i get 10000 records per file and i load them using sql loader.I want to also insert the number of records in file while loading the data in table.
How can i achive it.
structure of control file is
load data
BADFILE '/backup/temp/rajesh/RIO/BadFiles/FILENAME'
append into table ERS_RIO_SRC
TRAILING NULLCOLS
(
INSTALLATION_ID CHAR
[code]...
data
V5_RIO_5K7C|78967|172.16.43.153|RioLoginSrc.asp|0.375|01/08/2012 07:44:44.623|01/08/2012 07:44:45.000|V5_RIO_5K7C||||||||||
V5_RIO_5K7C|78968|172.16.43.150|RioLoginSrc.asp|0.187|01/08/2012 08:22:32.813|01/08/2012 08:22:33.000|V5_RIO_5K7C||||||||||
[code]...
table structure
CREATE TABLE ERS_RIO_SRC
(
INSTALLATION_ID VARCHAR2(50 BYTE) NULL,
TRANSACTION_ID VARCHAR2(50 BYTE) NULL,
SERVER_ID VARCHAR2(50 BYTE) NULL,
CLINICAL_TRANSACTION_ID VARCHAR2(255 BYTE) NULL,
[code]...
View 4 Replies
View Related
Oct 15, 2010
I have a problem with loading data using SQL loader.I have such control file
LOAD DATA
INFILE 'D:oracleFIRMAS.txt'
INSERT INTO TABLE FIRMAS
FIELDS TERMINATED BY ","
(FIRMAS_REG_NR char,FIRMAS_NOS char,ID_PRODUCT char)
and data file "firmas.txt"
444557562, Avotoni SIA, 1332
445575627, Avotoni SIA, 1332
444557562, Avotoni SIA, 1332
When I try to execut this command
sqlldr userid=system/a1331 control=d:oraclefirmas.ctrl an error occured (sql*loader-350) what should I do?
View 16 Replies
View Related