SQL & PL/SQL :: Insert PDF Files Into Oracle Database
Mar 2, 2006
Here I am explaining the process of how I am trying to insert pdf file into oracle database.
create or replace directory files as 'c:/welcome/';
(physical directory is created in the system also., both in server and client machine)
Create or replace PROCEDURE procloadMetaPdf (Filename IN VARCHAR2) is
temp_blob blob:=empty_blob();
location BFILE;
Bytes_To_Load Integer:=0;
auto_Id number;
Begin
[Code]...
procudure creating successfully
but when executing
exec procloadMetaPdf('help.pdf');
displaying the following error:
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 605
ORA-06512: at "SCOTT.PROCLOADMETAPDF", line 14
ORA-06512: at line 1
(line 14 is : DBMS_LOB.OPEN(location , DBMS_LOB.LOB_READONLY)
View 8 Replies
ADVERTISEMENT
Apr 25, 2010
I have a big problem that came up latly which is importing XML files into oracle database.The point is that I have extracted whole PostgreSQL database into XML files - 236 tables - 1 XML file for every table and now I'm about to import them into Oracle tables. First of all, I would like to point out that I already have the structure of all the tables in oracle database, the files only carry the data (records) that need to be imported into oracle.
I've been trying to make it running and I can't do anything more serious about it for over a week..I will show You all example:
insert into ps_sprawozdania(miesiac, umowa_rok, umowa_nr, nr_korekty, nazwa, data_potw, data_exp, status_potw)
select
extractvalue(column_value,'/NewDataSet/Cust/miesiac'),
extractvalue(column_value,'/NewDataSet/Cust/umowa_rok'),
extractvalue(column_value,'/NewDataSet/Cust/umowa_nr'),
extractvalue(column_value,'/NewDataSet/Cust/nr_korekty'),
extractvalue(column_value,'/NewDataSet/Cust/nazwa'),
extractvalue(column_value,'/NewDataSet/Cust/data_potw'),
extractvalue(column_value,'/NewDataSet/Cust/data_exp'),
extractvalue(column_value,'/NewDataSet/Cust/status_potw')
from table(xmlsequence(xmltype(bfilename('c: est','ps_sprawozdania.xml'))));
That was one of my attempts to import data from file "ps_sprawozdania.xml" into table "ps_sprawozdnaia" into oracle. Here are 2 records from the XML file to show you Its structure
<NewDataSet>
<Cust>
<miesiac>7</miesiac>
<umowa_rok>2008</umowa_rok>
<umowa_nr>051/210412/01/000/08</umowa_nr>
<nr_korekty>0</nr_korekty>
<nazwa>Sprawozdanie z realizacji umowy nr 051/210412/01/000/08 za miesiąc Lipiec</nazwa>
[code]....
handle with XML data, not XML files.
View 39 Replies
View Related
Mar 2, 2011
I have a question in Recovering the database.
E.g. Backup has done yesterday and today evening all files are lost (control, redologs, archive, etc.)
We are in 24/7 environment then how we need to recover the database?
View 4 Replies
View Related
Oct 5, 2010
code to put files such doc ,xls , jpg
into db using lob
i am developing application where user will be browsing the file from local pc which then will be loaded into oracle db not At OS DIRECTORY .
View 1 Replies
View Related
Jun 6, 2013
I would like to ask you if you know which built-in can I use for transferring a excel file from our Unix box to a table in oracle database, right now we are using webutil_file_transfer.Client_To_DB_with_progress using forms developer, but I need to run as an automatic process uploading form unix into oracle directly without using forms.
View 22 Replies
View Related
Oct 11, 2012
How can I know if database files are on file system or raw files?
View 4 Replies
View Related
Jan 22, 2013
I am trying to setup my environment to prepare for hands on DBA exercises from an OCM handbook for 10g -OCM:Oracle Database 10g Administrator Certified Master Exam Guide
I need access to the following files
gc_10205_part1of2.zip, gc_10205_part2of2.zip for 32 bit linux
Apparently the above along with other Grid software was once attainable from the link below but sometime between this year and the last Oracle decided to remove this and other software from public access. URL.....
I doubt Oracle will hear my plea - educational purposes only - so given I want access to the software only.
View 2 Replies
View Related
Jul 4, 2012
I need to write a procedure to migrate files from Collaboration plum tree server to oracle database server into a table using a BLOB column .
View 2 Replies
View Related
Jun 12, 2013
I am trying to insert from 3 different files in 3 tables and I am finding that the same id is being inserted for each row.
DECLARE
F UTL_FILE.FILE_TYPE;
V_LINE VARCHAR2 (1000);
V_Name varchar2(512 char);
V_ParentUID raw(16);
V_Path varchar2(1024 char);
[code]...
View 5 Replies
View Related
Dec 4, 2011
I created a music database, and I'm having trouble inserting the audio, video, and lyrics (.doc) into their respective tables. I searched through the forums and found some example code, but I'm not sure how to modify it to fit my purposes.
What I need is a procedure that can insert a complete record into the track table (including an .mp3 file for each row), one that can insert a record into the lyrics table (including .doc file for each row), and a procedure that can insert a single record into the Video table (including an .mv4 file).
Here's the DDL:
CREATE TABLE Artist(
artist_id number(9),
artist_name varchar(30),
country char(2),
num_albums number(3),
num_songs number(5)
);
[Code]....
View 39 Replies
View Related
Aug 14, 2012
I am migrating data from a Solid Database to Oracle, I am using Flat Files to do that.
1.- I download the data to flat files from Solid
2.- I move the files to Oracle server
3.- I upload the data to Oracle
Now, I have done the 90% of the data base, but I have found some tables that has description columns and in this description the users writes enters, so when I try to upload the data to Oracle SQL loader cannot recognize this characters.
Example:
'25','0.','5.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
'26','0.','2.','0.','0.','0.','0.','3.','0.','0.','0.','0.','0.','',''
'27','0.','1.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
'28','0.','1.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
'29','0.','38.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
'30','0.','13.','0.','0.','0.','0.','0.','6.','0.','6.','0.','0.','|SE RECHAZA B20CS50SNW ^M
^M
SE RECHAZAN CINCO PZAS ^M
DOS MOD. HSC15I41EH,DOS MOD. HSK15I41EH |Agregó: 06/06/2009 12:22:50
|','DEV. A PROV.'
'31','0.','50.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
'32','0.','9.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
'33','0.','2.','0.','0.','0.','0.','0.','0.','0.','0.','0.','0.','',''
How can I solve this ?
View 3 Replies
View Related
Jun 12, 2013
I am trying to insert data in three tables from three csv files simultaneously. This is what I have so far:
---insert all data from three csv files
DECLARE
--zenobject
F UTL_FILE.FILE_TYPE;
[Code]....
View 5 Replies
View Related
Mar 3, 2011
I am trying to insert an image files into a blob column using the following code.
CREATE TABLE MY_IMAGE_TABLE (
ID NUMBER,
NAME VARCHAR2(20),
IMAGE BLOB);
CREATE OR REPLACE DIRECTORY MY_FILES AS '\dppdb-dev est';
GRANT ALL ON DIRECTORY MY_FILES TO PUBLIC;
[code]....
However, I am getting the this error
ORA-22288: file or LOB operation FILEOPEN failed
I've done some tracing on toad and it seems that the file am trying to insert exists yet am unable to open it.
View 4 Replies
View Related
Mar 8, 2013
how can i insert .pdf file into my oracle database.
insert statement how can i insert this pdf file into my oracle database using insert statement.
View 24 Replies
View Related
Mar 8, 2013
I am trying to take data from text box and list box and then insert them to Oracle database:
Text box data:
Oracle = EMPLOYEE_NAME
C# = tbEmployeeName
List box data:
Oracle = EMPLOYEE_GENDER
C# = lbEmployeeGender
Here is my code in C#, with insert statement:
string oradb = "Data Source= oraDB;User Id=sm;Password=mypassword;";
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
[code]...
View 1 Replies
View Related
Aug 23, 2012
i'm working in an Oracle 10g database on an IBM AIX server.
I have 3 tables (tables A, B and C).
Table A has columns -- product, rate and expiration date.
Table B has columns -- product, rate and deductible.
Table C has columns -- product, rider, gender, age and rate.
I also have a Master table which is used to store the data from Tables A, B and C via the insert statement.
I'm trying to create a dynamic SQL insert statement using a shell script to insert data from the columns in Tables A, B and C into my Master table. Master table does contains all columns from Tables A, B and C, although a column name could be spelled differently. For example, Master table contains a column named "deduct", while Table B has the same column spelled as "deductible".
I build the dynamic query using a for loop in my shell script (see below).
The problem is that i can't get the correct columns in the Master table in the dynamic SQL for the insert because depending on the table i'm selection from, the columns are different. So how do i get the correct columns in the SQL for the Master table?
Example Shell Script
--Archive_Rates.txt contains: Table A, Table B, Table C (but the next time my process runs, Archive_Rates might contain Table D, Table E and Table F -- each which have different column...but all columns are still in the Master table)
for tbl in `more Archive_Rates.txt`
do
echo 'BEGIN WORK; ' > rc1.sql
echo ' ' >> rc1.sql
echo 'insert into Master' >> rc1.sql
echo '(prod, rate, rate_exp) ' >> rc1.sql
[code].....
View 5 Replies
View Related
Jun 14, 2013
We are getting problem with the Chinese character set. My current character set is as follows.
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
[code]....
My column description for the table product is as follows.
PART_NBRVARCHAR2 (30 Byte)
PART_DESCNVARCHAR2 (2000)
when trying to insert Chinese character using the insert command below
insert into product(part_nbr,part_desc,cust_name) values('322341',unistr('功'),'test');
I am getting the value when selecting the same record using the select command
select a.part_nbr,a.part_desc,a.cust_name from product a where a.part_nbr='322341'322341¿test
When I running this command on TOAD
select a.rowid,a.part_nbr,a.part_desc,a.cust_name from product a where a.part_nbr='322341'
and manually editing/inserting '功' character in output from select command above. After that I am able to get the same Chinese character when I am running select next time.
View 7 Replies
View Related
May 15, 2010
how to insert data in oracle table without writing insert statement in oracle 9i or above. i am not going to write insert all, merge, sqlloder and import data.
View 2 Replies
View Related
Apr 12, 2013
I have task. I am using oracle forms 6i. I want to import excel data to oracle forms(its common task using ole2 package). But this time I want to map the columns i.e my database table having 5 columns. and the excel file is having 2 or 3 columns then i suppose to map those columns and accordingly insert it to my table.
So far i have import column heading of excel to oracle forms, then i've provide list item for mapping each column. so that user can map excel column to database columns. Now I am confuse how to write the code so that selected columns should get inserted into database.
-more details
I have table with columns id, name, location, address, plan. in those columns i need to insert records form excel. user having a excel with 3 columns col1, col2, col3. on the form i've fetch column headers of excel and in front of that i've provide database column list , so user can match excel column with database column. e.g.
COL1 --> list value of database column
COL2 -->list value of database column
COL3 -->list value of database column
Once user map those column i want to insert those values into my database table (table with columns id, name, location, address, plan). and i am confuse about this code.
View 1 Replies
View Related
May 20, 2013
I recovered dbf and ctl files, how to mount that database again. Using oracle 9i.
View 1 Replies
View Related
Oct 4, 2012
We have a customer that was backing up his data by copying the oracle data XE folder (all .dbf files) to a backup drive. His server crashed and he formatted it and reinstalled the database 11g from scratch.
Now he needs to re-attach the .DBF files, is there a way?
View 1 Replies
View Related
Jun 10, 2013
I am trying to retrieve info from multiple DBs and insert into a central DB via DB LINKS.The links are retrieved via a cursor.
However I keep coming up against 'PL/SQL: ORA-00942: table or view does not exist'..how to handle db_links using a cursor in a pl/sql block? The code is as follows:
DECLARE
db_link_rec VARCHAR2(30);
CURSOR db_link_cur IS
SELECT DB_LINK
from MESSAGING_PROD_LIST;
BEGIN
OPEN db_link_cur;
LOOP
FETCH db_link_cur INTO db_link_rec;
EXIT when db_link_cur%NOTFOUND;
[code]....
View 1 Replies
View Related
Aug 3, 2011
I am an absolute neophyte at this. I'm fairly good at SQL, but have not administrated a database before.
I am trying to take a backup from our production database and restore it to a fresh install of Oracle 10.2 on a Windows 2003 server using RMAN - but I am not getting very far. Software versions are all the same. I have access to the manuals, but it seems like every link I follow adds more questions than it answers.
I have gone through the wizard and set up oracle on the new box - that all seemed to go smoothly. In order for this to work, do I need to set up the database with the same DBID as the one in the backup files? Or is there a way to import the data from the backup and have it build the database from those files?
I'm not sure I'm even asking the right questions. Do any of y'all know of a tutorial that would walk me through this process step by step?
View 4 Replies
View Related
Dec 20, 2012
I have a requirement. My Front end is Oracle Application. If any user deletes the data from front end screen. One log file should be generated. That file will save in one folder in my server. And That log file consists which code is deleted. And the user name who deleted the code, time and deleting status..
Now my requirement is i have to develop a report to display the log file data. But i don't have a database table to retrieve the data. The data consists Log files. How to transfer the Log files in DB table.
View 4 Replies
View Related
Oct 3, 2012
In my project I am storing everything in database BLOB column.Everything includes images,documents,videos and any thing uploaded from form.
video should not be save in database.I want to know it is good idea to store everything in database BLOB column?
View 4 Replies
View Related
Jul 24, 2012
: I noticed three data files on my standby show's they are corrupted. What will be the easy and fast way to sync them up from my production database?
View 1 Replies
View Related
Oct 19, 2011
We're currently in a situation where the primary database server fs size does not match the standby database server fs size.
Standby database filesystem is almost 100% utilized, and we suggested to move some of the datafiles first to avoid threshold alerts and archive gaps.
Now, if we're gonna move datafiles on the physical standby, I believe the process would be stop managed redo apply -> shutdown standby -> OS move -> startup mount -> alter rename -> start managed redo apply. Is this correct? If not, how?
Also, would it have an effect if the controlfiles of primary and standby do not match because of the movement?
View 1 Replies
View Related
Oct 15, 2013
I have the following situation: a customer sent me a group of files, that he said to be a "backup" of an old database, that is no longer online (he doesn´t use our software anymore). However, he needs to recover the data in this database. The person at the company who generate these files doesn´t work there anymore, so we don´t have any of the following information:
- Database version, connection details, service names, user names neither tablespace names.
We were just told that these files were recorded in a DVD, and is que only source remaining.
The files he sent are the following:
ctrlcf_satp_01.ctl
ctrlcf_satp_02.ctl
ctrlcf_satp_03.ctl
daddf_satp_dad_01.dbf
[code]......
Is there a way to access this data? Or can I tell our costumer that your data is lost forever..
View 4 Replies
View Related
Sep 27, 2011
I wanted to copy some archive log files between 2 DB Server.Steps:
1) Edited the Local and Remote DB's TNSNAMES file and modifyied like below.
bookstore =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.80.100.28)(PORT = 1521))
)
(CONNECT_DATA =
(SID = BOOKSTORE)
)
)
2) Created a Source Directory --> DR1
3) Created a Target Directory --> DR2
4) Created a DB Link
CREATE DATABASE LINK LINK1 CONNECT TO <username of the Remote DB> IDENTIFIED BY <Password> USING '<Remote DB Name taken in the TNSFILE>.
5) In the Local Server I had written the below command.
create or replace procedure proc1 is
cursor c1 is select recid,substr(name,37) "ARCH_FILE" from v$archived_log;
var1 c1%rowtype;
begin
open c1;
fetch c1 into var1;
[code]...
It is working, but not coping the Files from Local To the Remote.
View 22 Replies
View Related
Oct 19, 2012
Oracle Database 11.2.0.3
OEL 5.7
We have a host for restore purposes. We execute monthly or quarterly restore operations to verify that I am able to restore a subset of the data in the given amount of time or to other purposes. I have a automated script to Clone the Database, but to Clone Database we need remove the old database from ASM before start this operation. I want remove only Database files and keep the configuration (such as oratab/network/ocr and so on).
Question: There is a easy way to remove these files without connect on ASM or by using DBCA?
View 2 Replies
View Related
