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?
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 DECLARE arquivo_ler UTL_File.File_Type; Linha Varchar2 (1000); BEGIN arquivo_ler: UTL_FILE.FOPEN = ('INTRANET_LOAD', 'carga_intranet.txt', 'R', 32767); Loop 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.'); END; /
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
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
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????)
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...
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?
C:UsersAdministrator>RMAN Recovery Manager: Release 220.127.116.11.0 - 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)
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 only.how can i acheive this.remember i have to do this through command prompt only.
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).
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 :
1) 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 ================================================================= 3) TCP_RECEIVE_SIZE_DEFAULT 16,384 TCP_RECEIVE_SIZE_MAX 9,223,372,036,854,775,807 TCP_RECEIVE_SIZE_MIN 4,096 TCP_SEND_SIZE_DEFAULT 16,384 TCP_SEND_SIZE_MAX 9,223,372,036,854,775,807 TCP_SEND_SIZE_MIN 4,096 ================================================================= 4) Wait Class Waits %Time -outs Total Wait Time (s) Avg wait (ms) %DB time Network 45,423,119 0 2,122 0 3.81 ================================================================= 5) 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 DB CPU 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
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.
oracle Database 11g Enterprise Edition Release 18.104.22.168.0 - 64bit Production PL/SQL Release 22.214.171.124.0 - Production "CORE 126.96.36.199.0 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.
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-> H|12|1233|fff|sss L|1234|gggg|tttt|yyyyyy|rrrrr L|1094|gggg|tttt|yyyyyy|rrrrr L|1344|gggg|tttt|yyyyyy|rrrrr L|1666|gggg|tttt|yyyyyy|rrrrr L|188|gggg|tttt|yyyyyy|rrrrr
I have one master table and one detail table. I want to insert record -> H|12|1233|fff|sss in master table and records-> L|1234|gggg|tttt|yyyyyy|rrrrr L|1094|gggg|tttt|yyyyyy|rrrrr L|1344|gggg|tttt|yyyyyy|rrrrr L|1666|gggg|tttt|yyyyyy|rrrrr L|188|gggg|tttt|yyyyyy|rrrrr in detail table.
End of excercise will redult-> 1 record in header and 5 records in detail table.
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:
CODEDECLARE 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'; [code]....
should have mentioned that this is Oracle 10.2.0.3, on HPUX. not sure if that matters for this issue or not, but wanted to throw that out there.
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.
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.