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)
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:
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]....
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.
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.
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).
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.','',''
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.
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
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.
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.
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.
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.
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?
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]....
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?
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.
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?
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:
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.
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?