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;
begin
SELECT file_data INTO b FROM fnd_lobs WHERE file_id = 322420;
if (b is null) then
[code]......
With the data displaying some boxes before and after data
I am using jasper integration package ..from www.opal-consulting.de using apex 4 and i Am currently able to generate pdf invoice..using jasper and store in a database.. table.
I need to generate the pdf invoice in the background ..without displaying in my screen..Without downloading on my machine..And store directly into the database table..
Below is my existing part of code..
DECLARE
l_blob BLOB;
l_mime_type VARCHAR2 (100);
l_blob2 BLOB;
l_mime_type2 VARCHAR2 (100);
mport varchar2(200);
[code]....
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;
/
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;
SELECT *
FROM ALL_TAB_PRIVS
WHERE table_name = 'INTRANET_LOAD';
The data are returned.
1 FOLLOW INTRANET INTRANET_LOAD SYS READ NO NO
2 FOLLOW INTRANET IN NO WRITE SYS INTRANET_LOAD
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 would like to read specific line in the file in the util file.
View 1 Replies View RelatedI 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.
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 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
CREATE OR REPLACE PACKAGE UTL_FTP
AUTHID CURRENT_USER
AS
/**
* LICENSE: GNU Lesser General Public License (LGPL)
* Copyright (C) 2003-2006 Russ Johnson (john_2885@yahoo.com)
[code].....
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;
[Code]....
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.
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
[code]...
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 11.2.0.1.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)
[code]...
how to resolve this problem?
I have the following bit of code that reads data from the an Oracle table (Note: This is running on Jdk 1.4.2)
ResultSet message = messageStatement.executeQuery(getMsgSql);
String messageData = message.getString("MESSAGE_DATA");
The data in the MESSAGE_DATA column contains text but also control characters that separate data elements in the message (i.e (char)31, (char)29) and (char)28) . What i am finding is that for some reason message.getString() is sometimes truncating the message. I can read the majority of messages but some of them are truncated. Am i supposed to be reading the data using a different method? If so how?
I have tried to use sqlplus to look at the data in the database and it is all there it is just truncated by the message.getString() method. I saw this when i tried to output the result i.e. System.out.println(message.getString()).
I ran an sql query using the length() function in Oracle and length("MESSAGE_DATA") returns 2032 whereas in java message.getString(1).length() returns 2000.
I want to call an exe file through DB Procedure. How it is possible?
1create or replace procedure proc3
2is
3begin
4 host('calc');
6* end;
from FORMS it works fine but it does not work through DB. I found same question through this link
[URL].........
but no answer is given.
I need to execute a .sql file in an oracle stored procedure.
View 9 Replies View RelatedI am trying to clone a database on another server with different direcory structure. So that path on the source db server are /u04 whereas on on target db server it would be /u03.
Since this I am testing it on small database initially I have kept all datafiles, backup and archivelogs at /u04 and /u03 on the source and target db servers respectively Now I have copied the backups from source db server to target and since path is changed, I cataloged it
However during restore I am getting
Quote:ORA-19870: error reading backup
Here are the session details
RMAN> catalog start with '/u03/oradata/db7fra';
searching for all files that match the pattern /u03/oradata/db7fra
List of Files Unknown to the Database
=====================================
File Name: /u03/oradata/db7fra/DB7/archivelog/2011_03_15/o1_mf_1_16_6qyvpb3w_.arc
File Name: /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_annnn_TAG20110315T123018_6qypyv5v_.bkp
[code].....
I have altered permissions on the backup files as well but of no use
oracle@dev-biz:/u03/oradata/db7fra/DB7/backupset/2011_03_15 $ls -ltr
total 545260
-rwxrwxrwx 1 oracle dba 12419072 Mar 15 13:52 o1_mf_ncsnf_TAG20110315T123008_6qypyrz2_.bkp
-rwxrwxrwx 1 oracle dba 3072 Mar 15 13:52 o1_mf_annnn_TAG20110315T125043_6qyr54to_.bkp
-rwxrwxrwx 1 oracle dba 426496 Mar 15 13:52 o1_mf_annnn_TAG20110315T125006_6qyr3zk4_.bkp
-rwxrwxrwx 1 oracle dba 14336 Mar 15 13:52 o1_mf_annnn_TAG20110315T123018_6qypyv5v_.bkp
[code].....
We are running a store procedure by calling it from shell script.The shell script is running in Server1 and the stored procedure is running in server2(which is the data base server.).
Functionality of the stored procedure:Generating a tab delimited file and place in a directory.
Requirement: The requirement is that the store should be modified in such a way that it should drop the output file to Server1 itself(The server from where it is getting called).
Attaching the store procedure for reference. mIn the store procedure,we have a variable "lv_file_dir" which currently refers to the directory location in Databse server(Server 2) We have to change this SP such that it drops the generated file in Server1 itself(From where the shell script is running).
Attached File(s)
Procedure.txt ( 3.28K )
Number of downloads: 4
I need to create an Oracle Stored Procedure to read a Flat file(pipe delimited) and load the data into an Oracle table. I believe the file should be located in any of the path as logged in dba_directories table or it can be anywhere on the local client machine?
View 14 Replies View Relatedwhere does the control file auto-backup information gets stored? or how does a RMAN knows from where to restore a controlfile in auto backup?
View 12 Replies View Relatedwhen i am calling stored procedure with input and output parameters from batch file .. I am getting the following message ..
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 4 11:48:51 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 14
code which i have written ...
DEClARE
RETCODE NUMBER;
RETMSG VARCHAR2(200);
EXEC SP_SELCT_EMPLOYEE_DATA(277080,'EMPNAME ','EMAIL@EMAIL.COM', 9028045686,111333,25000,'PUNE',35,RETCODE ,RETMSG );
EXIT
Procedure Name :
PROCEDURE SP_SELCT_EMPLOYEE_DATA (
-- A_EMPLOYEE_ID IN VARCHAR2,
--A_JOB_ID IN EMPLOYEES.JOB_ID%TYPE,
P_EMPLOYEE_ID IN EMPLOYEES.EMPLOYEE_ID%TYPE,
P_EMPLOYEE_NAME IN EMPLOYEES.EMPLOYEE_NAME%TYPE,
P_EMAIL IN EMPLOYEES.EMAIL%TYPE,
[code]....
There is one DBMSJOB i created schedule to run every day at 22:00 hrs. The jobname is IT_TO_DUM_LOADING and the job type is Stored procedure.
I queried all_source and found stored procedure does not exist in database.
SQL> select text from all_source
2 where type = 'PROCEDURE'
3 and name ='IT_TO_DUM_LOADING';
no rows selected
But i could see the job details in dba_scheduler_jobs view. I cannot use WHERE clause in dba_scheduler_jobs to restrict and find only the job row as i receive time zone error. How it is possible to view the job in dba_scheduler job view whereas i could not find the stored procedure of its in all_source?
I want to display a pdf file (or other types of documents like word or excel) that is already stored in a blob column in the database, in a form control and be able to view the records one by one just like an image.
Is this possible? if so, how? If not, can you direct me to additional code, technology, package or whatever solution that can achieve the desired results. sing Oracle forms 6i and database 10g.
For external tables does not require any database space.But where the external tables are storing.
View 3 Replies View RelatedI have 2 databases:
Database A
Database B
In Database A I have a series of config tables and stored procedures/functions In Database B I have a lot of tables.
I would like to execute my stored procedures and all associated functions in database A on my data in database B.
Now I've figured out that creating a database link enables me to do SQL selects on data in both A and B...but how do I run SP/Funcs ? I've read something about packages but not sure if I'm heading in the right direction.
Do I need to create a simple synonym ? Can I use the existing DB link ? or is there a special way of calling them, or...
I like the A/B database set up since I can keep battle tested code in one location and have it work across multiple dbs...saves me having to create everything in every database.
I have successfully Stored image file in Oracle 10g Database Table using Forms 6i. But I cannot retrieve that image and display on Forms 6i. Image Item on Form is not a database Item.
View 8 Replies View RelatedHow to call stored procedures or functions from a database trigger?
View 1 Replies View RelatedI want to insert multiple records on a database using a stored procedure.
View 11 Replies View RelatedOracle database version is : 10G.
When we try to query a column from a table the output is shown as "?????". Seems the data in that column is stored in a different character set, which is not recognised while displaying the output. The column size is 35byte.
The nls parameter table shows the character set as "we8mswin1252".
get this issue resolved.
There are 2 Oracle databases with pseudo names Remote and Local. I have a function in Remote called FUS.F_Return_10 which simply returns 10 for testing purposes, where FUS is a schema name. In Local I want to create a procedure that will call the above function. Here's the PL/SQL:
CREATE OR REPLACE PROCEDURE TEST
(
V_COUNT OUT NUMBER
)
AS
V_FOO NUMBER(2,0);
BEGIN
[Code]...
There's a Public Database Link called PER_ACC in Local. When I try to create this procedure I get: Encountered symbol "@" when expecting one of the following: .(*%&................
where my mistake is?
I would like to send a raw command to the Oracle server. from .NET (System.Data.OracleClien), but I got invalid statement (ORA-00900) exception
.Excecute string:string SQL = "EXECUTE SP_THREADFILE_INSERT('" + Guid.NewGuid() + "','" + filename + "',utl_raw.cast_to_raw('" + content + "'))";
