Reading Specific Line In Util File

Jul 1, 2013

I would like to read specific line in the file in the util file.

View 1 Replies


SQL & PL/SQL :: Reading From A Excel File

Dec 1, 2011

I am looking for a code/script to read values from excel file and perform PLSQL script.

Now i have the PLSQL script to generate report which takes two value which i have to change all the time to generate new report .All i wanna do create a script to read from a excel file and perform the other script.

I have been searching from a long time and only found UTL_file Package which use CREATE OR REPLACE FUNCTION and create some virtual table.

The problem is i don't have create authorization in database so i m not able to use UTL_File command . Is there any simple way to read value from excel file?

View 16 Replies View Related

SQL & PL/SQL :: Reading File With UL_FILE?

Jul 8, 2013

I'm trying to utilize the utl file to read a txt file and import the data into a table in Oracle. I've read in various forums and have researched a lot on oracle documentation site and on the internet but can not find the answer to the problem.

The source follows:

Set serveroutput on
arquivo_ler UTL_File.File_Type;
Linha Varchar2 (1000);
arquivo_ler: UTL_FILE.FOPEN = ('INTRANET_LOAD', 'carga_intranet.txt', 'R', 32767);
UTL_File.Get_Line (arquivo_ler, Linha);
dbms_output.put_line (Linha);
End Loop;
UTL_File.Fclose (arquivo_ler);
DBMS_OUTPUT.PUT_LINE ('File processed with sucesso.');

The errors:

ORA-29283: invalid file operation

ORA-06512: at "SYS.UTL_FILE", line 633

ORA-29283: invalid file operation

ORA-06512: at line 5

What has been done:

Created DIRECTORY (INTRANET_LOAD) and given the GRANT read, write to the user

On Linux where Oracle is installed, was given full access to the Oracle user folder: / u01/app/oracle/product/11.2.0/db_1/adp

When writing the query;



WHERE table_name = 'INTRANET_LOAD';

The data are returned.



View 10 Replies View Related

SQL & PL/SQL :: Reading Doc File Stored In Database

Jun 8, 2011

A doc file stored in a database having data type blob. To read this file I have written Following procedure

create or replace procedure XX_read_blobfile1 as
b blob;
c clob;
n number;
SELECT file_data INTO b FROM fnd_lobs WHERE file_id = 322420;
if (b is null) then

With the data displaying some boxes before and after data

View 11 Replies View Related

SQL & PL/SQL :: Reading Flat File With Header?

Jul 3, 2012

working on loading the data from flat file into table and below given is the validation condition given.I checked the UTL_FILE build in package but not able to figure out, how to identify the column header in flat file.

1. Skip the header, if any. The header is the first record, and starts with '000'
2. Skip the trailer, if any. The Trailer is the last record, and starts with '999'
3. Log an error, but continue if a line exceeds 512 characters
4. Log an error, but continue if a line is blank

View 5 Replies View Related

Forms :: Reading And Writing Data In A Text File

Mar 8, 2011

I need to read data from text file(located on application or db server or on some other server, however path is known to me.) and then append some data in it.

Data will be read and written on daily basis so i want to clear all data on date change.

View 3 Replies View Related

Forms :: Import CSV Directly Into D2kwutil / Reading From File?

Jan 29, 2008

I know that this topic in not new, but maybe my case is a bit different than common.My form has multi-record block (4 columns) in which i want to upload CSV file.

The form will have "Browse..." button to piont CSV file (on local users disc, not where Forms server resides) after pointing the CSV file, user will press UPLOAD button which will import CSV data into block. After that data is visible in block. But whole form must not be refreshed.

I'm studying about UTL_FILE package, but it seems it allows manipulating files on server not local user's machines. Do Forms have such "Browse... -> UPLOAD" possibility or I need to learn Java and create Java funcionality inside Forms?

Or it is impossible to load data directly into form? (I have to use SQLoader and load data into temp table then into block on form????)

View 7 Replies View Related

SQL & PL/SQL :: Ftp File To Remote Server By Reading Data From Table?

May 1, 2012

I need a way to ftp file to remote server by reading data from table. I searched a couple of sites which asked me to use Chris xutl_ftp package..but unfortunately the site is no accessible..

Here is the code

* LICENSE: GNU Lesser General Public License (LGPL)
* Copyright (C) 2003-2006 Russ Johnson (


View 3 Replies View Related

Forms :: Slow Performance Using OLE2 Reading Xls File

Oct 10, 2011

I just trying to import some informations from excel to Oracle using OLE2 over Oracle Forms 6i, but It´s very slow when I have import under then 10k lines. anything to optimize that ? Follow the code used...

application OLE2.Obj_Type;
workbooks OLE2.Obj_Type;
workbook OLE2.Obj_Type;


View 2 Replies View Related

Security :: Audit Specific Statement On Specific Table By Specific User?

May 29, 2012

I'd like to know if it is possible to track DML actions issued on a specific table by a specific user, for example , i tried :


I get an error, where is my syntax error ?

i want to know if it's possible to do it without trigger ?

View 7 Replies View Related

Forms :: Reading Data From Excel File And Load Into Form

Oct 25, 2013

I have a Excel File which contains some columns and rows, i need to load that excel into a form and import that form data to Database Table, using DDE Method.

In simple i say; Just to read the excel and load into a form, which can be imported into a table later.

How to achieve this? only dde method.

View 10 Replies View Related

Backup & Recovery :: ORA-01115 - IO Error Reading Block From File 11?

May 17, 2013

During the scheduled backup we observed that, backup got failed. Error is "ORA-01115: IO error reading block from file 11 (block # 118736)
unable to rectify it. Below are the part of log file -

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.MAIN [TABLE_DATA:"DW_REP"."DW_MOTOR_CLAIM_INFO_DTLS"]
ORA-24795: Illegal ROLLBACK attempt made
ORA-06512: at "SYS.KUPW$WORKER", line 8976
ORA-24795: Illegal ROLLBACK attempt made


This is development server. Dont have RMAN configured.But when we connected to RMAN and issued block recovery, it started process and shows media recovery done. Since no RMAN backup was taken before, what it does here?

Recovery Manager: Release - Production on Fri May 17 10:48:40 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> CONECT target /
connected to target database: MAGMADEV (DBID=2469200049)


how to resolve this problem?

View 6 Replies View Related

Client Tools :: How To Get Rid Of First EMPTY Line Of Spooled File

May 30, 2011

how to get rid of the first EMPTY line generated by SQL*Plus when spool'ing to a file.

I tried using set pagesize 0 and while that get rid of the empty blank line, it also disabled the heading ...

Currently using sed as a work around. But unfortunately, can't use sed on Windows?

set pagesize 200
set heading on
set trimspool on
set lines 200
set verify off
set echo off
set feedback off


As mentioned, using pagesize 0 remove the blank line but I also lost the heading.

View 4 Replies View Related

SQL & PL/SQL :: Creating Text File On Specific Path Of Database Server?

Nov 28, 2012

using pl/sql code i am creating text file on specific path of database server. i need to compress this file .how to compress the text file using pl/sql?

View 1 Replies View Related

Windows :: How Search Specific String In PDF File From Command Prompt

Nov 21, 2012

I have a requirement in which i have list pdf files(from thousands of pdf files) having particular string from a command prompt.I have tried find command but that has some limitation. its working upto 3 digit string can i acheive this.remember i have to do this through command prompt only.

View 1 Replies View Related

Spatial :: Convert Multi-line To Simple Line

Feb 13, 2013

Oracle Database 10g Enterprise Edition Release

We are processing spatial data from another source to display in our GIS environment. The data is a set of multi lines. The gtype is 2006. A typical geometry looks like:

SDO_GTYPE     2006
SDO_SRID      31370
SDO_ELEM_INFO (1,2,1, 7,2,1)
SDO_ORDINATES (105094.84, 195084.96,
105094.54, 195080.22,
105094.84, 195084.96,
105094.84, 195084.96,
105094.68, 195082.47 )

Now, this is not an actual multiline... it's just encoded as a multi line, but if you look at the coordinates you'll see that the end point of the first line is the same as the beginning of the second line (105094.84, 195084.96).

A better way to encode this geometry would be:

SDO_GTYPE     2002 <---
SDO_SRID      31370
SDO_ORDINATES (105094.84, 195084.96,
105094.54, 195080.22,
105094.84, 195084.96, <---
105094.68, 195082.47 )

Is there a standard function in Oracle that does this conversion for me? Or do I have to write my own :)

View 4 Replies View Related

Displaying Records Between Specific Times On Specific Dates?

Apr 11, 2011

I have a database which consists of various orders and various field.

I have a variable called createddatetime . I want that whenever i should run the database it should display records from

Yesterday 06:00:00 am to Current Date 05:59:59 am

Now to implement this i tried to put this syntax

and to_char(Createddatetime,'dd/mm/yyyy HH24:mi:ss') between 'sysdate-1 06:00:00' and 'sysdate 05:59:59'

But nothing comes up

where as definitely there are records between times because when i do and Createddatetime between sysdate-1 and sysdate I see valid records coming up.

View 3 Replies View Related

Reading AWR Reports

Jul 22, 2012

We are facing the problem of slow performance in the production system and lots of complaints from the clients for resource busy or system hang related issue. We are finding the locked object in the system which are mostly inactive in nature so we kill them manually and the problem gets resolve.

We have some observation in AWR reports which are as below :

Instance Efficiency Percentages (Target 100%)
Execute to Parse %: -26.96
Parse CPU to Parse Elapsd %: 4.83
2) Total time in database user-calls (DB Time): 55700.6s
TCP_RECEIVE_SIZE_MAX 9,223,372,036,854,775,807
TCP_SEND_SIZE_MAX 9,223,372,036,854,775,807
Wait Class Waits %Time -outs Total Wait Time (s) Avg wait (ms) %DB time
Network 45,423,119 0 2,122 0 3.81
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn % DB time
SQL*Net message to client 45,030,511 0 55 0 127.77 0.10

The Top Five events are :

db file sequenctial Read
enq TX -row lock contention
Virtual circuit wait
Read by other session

Is there a any oracle doc id or any other document ,which can be referred to get better understanding of AWR Reports and gives the steps of resolution to be taken for various top timed events

View 3 Replies View Related

SQL & PL/SQL :: Error In Reading XML Massage

Aug 16, 2013

I am not able to parse this XML message.

with t as (select xmltype('sku_list xmlns:xsi="" xmlns:xsd="">
<sku vk_unit="213-13085" model="M14573" />
<sku vk_unit="227-10036" model="M14573" />
<sku vk_unit="227-10037" model="M14573" />
<sku vk_unit="213-13086" model="M14573" />

View 1 Replies View Related

PL/SQL :: Reading 2 HTTP Headers?

Jul 15, 2013

I'm planning to do a single-sing-on in one of my applications. To get to my application, the user has to log in to a portal-application befor. From this application i receive 2 headers, to verify this person has authenticated successfully. But how can I read these 2 headers? It doesn't work with the UTL_HTTP package, because i don't do an active request on an url.

View 8 Replies View Related

SQL & PL/SQL :: Converting XLS With Korean Data To CSV And Reading It

Mar 3, 2011

oracle Database 11g Enterprise Edition Release - 64bit Production
PL/SQL Release - Production
"CORE Production"

I have a .xls file, which has few columns with Korean data. I have problem converting it into .csv. I converted it to UNICODE TEXT, which was TAB separated. I replaced tabs with commas. but still saved it as .txt file. Now I am using utl_file package to open and read.

utl_file.fopen_nchar(l_dir, l_filename , 'R',8000);

I outputted the first header line, and instead of Korean fieldnames, it displayed some different characters.. cannot even copy-paste..

I am uploading the converted .txt as I cannot upload original .xls file here...

View 18 Replies View Related

SQL & PL/SQL :: Reading Data From Clob Field

Apr 15, 2010

I have one table with clob field. The data in this field will contain string having transaction record data. Now I want to read data from this clob filed and insert different record in other oracle table.

example ->
Data in clob field will be->

I have one master table and one detail table. I want to insert record -> H|12|1233|fff|sss in master table
and records->
in detail table.

End of excercise will redult-> 1 record in header and 5 records in detail table.

I need to implement it in my project.

View 7 Replies View Related

SQL & PL/SQL :: Reading BLOB Field To REF Cursor

May 27, 2011

I have a procedure which will be called my front end team get the reports stored in the database as BLOB. These reports are mostly PDF files.

I need to read this BLOB field as a part of OUT PARAMETER REF CURSOR which also contains other two columns.

View 13 Replies View Related

LPAD - Reading Values From One Table And Inserting Into Another

Apr 20, 2012

LPAD not behaving as expected. the main thing I'm trying to accomplish here is reading values from one table, and inserting them into another... but in the "other" table, they need to be inserted as 11 characters long, with leading zeros. in it's most basic form, this is the cursor I'm using:

CURSOR update_mpi_cur IS
    select distinct A.epn_nbr, A.mrn_nbr, B.mpi_nbr
      from table1 A, table2 B
     where B.external_id = A.epn_nbr and B.identifier_type = 'EPN';

should have mentioned that this is Oracle, on HPUX. not sure if that matters for this issue or not, but wanted to throw that out there.

View 2 Replies View Related

Server Utilities :: Reading From Oracle Dump

Jun 14, 2006

Is there is any way of reading from oracle dump file?

View 16 Replies View Related

Performance Tuning :: Loading And Reading At Same Time

Feb 13, 2013

For example, we have a table ACCOUNT (snowflake dimension containing other dimension keys) and I have many fact tables based on this dimension. Normally data warehouse load happens like first dimensions needs to be loaded and then facts. Our frequency of loads is 30 mins.

To increase the rate in which the data will be available in the facts (as its a financial application), am considering to have two batches one with dimension and another one with fact (came to this conclusion as there is no dependency like first dimensions to be loaded then only fact) just the update might get missed sometimes. But if I do that, when dimension gets loaded, it will be read in the facts in another session. Will this affect the performance ?

LOADING (insert/update) and selecting data from table at the same time. Will it affect the performance in any way.

View 1 Replies View Related

SQL & PL/SQL :: Mail Reading / Saving Through Package Or Procedure

Nov 14, 2011

sample code to read attachments in inbox(from POP3 mail server) using plsql package or procedure.

View 6 Replies View Related

SQL & PL/SQL :: Reading Oracle Statements From A Procedure / Function?

Sep 15, 2010

I want to read the oracle procedure/function and find out the no of valid sql statment lines in a procedure:--

Consider the procedure is like :--

l_local number;
i integer;
j integer;
SELECT real_id_number


Now i want to read this procedure in such a way that it should return me below output :-

Seq_No Sql_Statements

1 SELECT real_id_number
2 into l_local
3 FROM master_var v
4 where v.variable_type = i

how to achieve this output.

View 5 Replies View Related

SQL & PL/SQL :: Storing And Reading Xml Data From XMLType Table

Aug 21, 2010

Since I need to do something along the lines of inserting an xml document into the database for later to use.

Anyway, the problem I get with the below is

Error at line 95
ORA-00984: column not allowed here

Script Terminated on line 95.

It's on the INSERT line


xmlInput clob;
xmlInput :=('<?xml version="1.0" encoding="windows-1256"?>


View 2 Replies View Related

JDeveloper, Java & XML :: Procedure For Reading Xml Element Value

May 25, 2011

In my pl/sql procedure I'm calling a webservice and it is returning me a varchar2 which contains xml tags.I want to pull values for each element. any one can provide me procedure/function to pull data from it.Below is the output from webservice.

<ResponseEnvelope xmlns:xsd="URL....xmlns="URL....> <ResponseHeader xmlns="URL......

ResponseId, MessageVersion etc are element name. the value between element names are to be retrived.

View 13 Replies View Related

Copyrights 2005-15, All rights reserved