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

Forms :: Upload Data Using 6i While Compiling Form Automatically Closed

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

Export/Import/SQL Loader :: How To Upload Linux File To DB Depending On Column Name

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

Forms :: Data Is Not Importing To Table Through SQL Loader?

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

Upload XML Data To Table

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

Application Express :: Upload Data From CSV / XLS To Table

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

SQL & PL/SQL :: German Data Upload Special Characters

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

Enterprise Manager :: Doesn't Upload Data Correctly?

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

Can Access Extract Data From Oracle DB For Automated Upload

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

Forms :: Upload File In Ebs 11i?

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

Application Express :: Possible To Upload Few Column In Table Through Data Loading

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

Forms :: How To Upload Image In 6i In Apps

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

Forms :: Upload PDF File To Server

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

Forms :: Upload File Using Oracle 11g

Nov 6, 2013

Do you have sample codes to upload file from client to server using 11g forms?

View 2 Replies View Related

Forms :: WUT-113 - Too Many Rows Error Using Webutil To Upload Doc Or PDF

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

Forms :: File Upload Show No Record

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

Forms :: Upload File Using Java Bean In 10g?

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

Forms :: 10g - Upload And Download Excel File

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

Forms :: Download / Upload To Excel Through 6i In Oracle Apps

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

Forms :: How To Create A Browse For File To Upload Window

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

Forms :: Upload A File From The Client To App Server In Runtime?

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

Forms :: Upload Files From Client To Application Server Using 10g

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

Forms :: FRM-40735 Upload And Download On Database Server Give Error?

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

Server Utilities :: Geometric Data From Text To Table And Wrong CTL Upload Into Table

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

Forms :: Create Dialog Box / Upload Image On Image Item

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

Loader - Not Getting Data In Some Of Fields?

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

Load Data In Database Using LOADER?

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

Loading Data Via Sql Loader Into Varray Tables

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

Server Utilities :: Count Of Data In Sql Loader?

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

Server Utilities :: Loading Data Using SQL Loader

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







Copyrights 2005-15 www.BigResource.com, All rights reserved