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]....
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]...
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]....
I have a question about referencing simultaneously multiple tables and I have the following tables
CREATE TABLE TEAM(team_id VARCHAR2(20), ...);
CREATE TABLE AGENT(agent_id VARCHAR2(20), ...);
(I put ... because there are a lot of columns which are not interesting for this question.)
Remark: team_id and agent_id are neither primary keys nor UNIQUE.
In a third table I have a column with exactly the same data type, that is, VARCHAR2(20). What I need is to make sure that its value is at least in one of the tables TEAM or AGENT (so the only thing that is important is to check that the specified value exists no matter in which of the two tables, but just to make sure that it exists somewhere).
The solution that I have found so far, is to create a BEFORE INSERT trigger on the table. Within the trigger I create a cursor
CURSOR c IS
SELECT team_id FROM TEAM
UNION
SELECT agent_id FROM AGENT;
And then within a loop I check the value.
I am trying to spool data from tables into flat files. I am using the following scripts to accomplish it
1. A cmd file (windows) that makes a call to a sql file
2. The SQL file which generates another query file at the run time, depending upon the table name passed to it
3. The run time query file , that executes the final query and spools the data into a txt file | delimited
For e.g. :
Actual command passed C:Spool_utilityspool_utility TABLE_NAME
E.g. of the spool utility file :
@echo off
SET dbuser=XX@YY
SET dbpw=xxxx
echo %date% - %time% - Start > %1%log.txt
echo START
sqlplus -s %dbuser%/%dbpw% @spool_utility.sql %1>%1.txt
echo %date% - %time% - Done >> %1%log.txt
echo DONE
E.g. of the spool_utility.sql
set echo off
SET newpage 0
SET feedback off
SET linesize 32767
set pagesize 0
[code]........
The above file generates a table_name.sql file with the actual table name at run time and gets executed and the output is written to the table_name.txt file.
This works perfectly fine. But the issue is when someone passes some wrong table name or if there is a actual run time error while executing the query , the error with details itself itself gets written to the end spool file.
For e.g. : if i do this just to generate an error and execute it from command line, the query generates an error and writes the error to the spool file , but at the command prompt where I executed the command I do not see any error and the process seems to have run perfectly well
set xxx on xxx off as above
spool &1.sql;
Prompt Select * from &1 where rownum><10---this will cause the issue
spool off
set termout ON
@ &1
EXIT
Eg of spool file generated :
from table_name WHERE rownum><=10 *
ERROR at line 62:
ORA-00936: missing expression
My question is, is there any way i can capture this runtime error and return this error to my calling sql script spool_utility.sql and then propagate it to the calling command file and do some tasks for eg removing the spool file and writing the actual error to a log file . Basically any way to know at my OS calling level that the entire spooling operation was unsuccessful.
I want to load data into more tables from many files ,based on first column value,which is FILLER field.i am trying to test this scenario with two oracle tables with similar definition. and load one record on each table using WHEN/POSITION keywords. for this , i added first column as reference column in the data which i have in ctl file itself.
1st table loaded with 1st record. But, 2nd record not loading.if i missed anything with WHEN/POSITION keyword ?
This is the error in log file for 2nd table(WD1):
Record 2: Rejected - Error on table WD1, column TAB.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Table WD1:
0 Rows successfully loaded.
1 Row not loaded due to data errors.
1 Row not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
[code]....
I created a data warehouse in oracle 10g n with three Dimension and one cube after that it crates 4 tables . How to use an insert sql statement to insert data in those tables n how to access them.
View 7 Replies View Relatedi have source table having 1000 records, i want insert first 100 rows in table1,second 200 rows in table2 and remaining row in table3.
View 12 Replies View RelatedI have a form which has three detail portions. I want that when I press SAVE, it should insert data in two tables & then run the specific code & then insert data in other two tables.
I am using Developer 6i. Couldn't find out the proper trigger or related thing.
I want to select data from different tables and insert this into one table based on some conditions:
SELECT *
FROM welltest_msr
WHERE well_s = 3419740
AND check_ind = 1
[Code]....
So I tried doing this with selecting the data and looping through it to do the insert.
DECLARE
--
-- WELLS
--
CURSOR c_well
[code].....
I'm writing a Procedure which Updates or Inserts data in Multiple tables. Selected fields of 10 tables need to be updated or Inserted. For this I created a table which comprises of fields related to all 10 tables. Then I write Procedure. Under this I create a Cursor which uploads the data from the newly created table which contains different fields of 10 tables. Then I write Update and Insert statements one by one for all 10 tables.
Sample Procedure below.
-------------------------------------------
Create or replace procedure p_proc as
spidm spriden.spriden_pidm%type;
cursor mycur is select * from mytable;
begin
for rec in mycur
[code]......
----------
Note: I created table on my server because data is coming from different server. They will upload the data in the table from there I pick and update the tables. Is updating or Inserting data in different tables one by one is correct?
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 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.
is it possible to upload very large files in oracle's tables. For example 1-2 gigabyte video file or even more. In other words is it possible to use oracle as file server to upload very large files and store them?
View 2 Replies View RelatedI'm not sure if this should go on this topic.
Anyway, I've loaded 5 .csv files through an external table and after doing it I tried to delete them.
But this error comes "Cannot delete 'filename': It is being used by another person or program".
I closed Oracle Developer and tried again deleting them manually, and the result was the same.
Tried restarting and deleting one .csv and it worked, but when I open sql dev and tried deleting the other files couldn't do it.
The question is: files that were used on external tables can't be deleted if developer is working?
The thing is that I've created a Stored Procedure that delete the files and obviously can't work. So, I should delete every time I load a csv file after restarting the computer.
I have a bunch of data in 50 excel files. I need to load all these 50 files into 50 different tables. I would like to do this in one script. I went through the forum to get this information, people suggested create a shell script etc or list the sqlldr command multiple times etc.
provide some clarity on this as to what's the best approach.If it is through shell scripting provide the shell script and instructions to execute it. Iam new to shell scripting.
Oracle procedure can run simultaneously?
Here is my concern, my procedure is already running manually and as per schedule time the same procedure will call by other application in the same time, So now procedure can run simultaneously or it will show any error message?
I have 780(12*65) csv files generated from 65 databases.Now I have to load this 780 csv files into 12 tables created in my database for some monitoring and reporting purpose.to call the sql loader I am plannig to create 780 lines like below.
sqlldr abc@tns/pwd control='E:htmlctlhtml_broken_jobs_rpt.ctl' log='E:htmlreportloghtml_broken_jobs_rpt.log'
sqlldr abc@tns/pwd control='E:htmlctlhtml_db_size_rpt.ctl' log='E:htmlreportloghtml_db_size_rpt.log'
sqlldr abc@tns/pwd control='E:htmlctlhtml_fragmentation_rpt.ctl' log='E:htmlreportloghtml_fragmentation_rpt.log'
sqlldr abc@tns/pwd control='E:htmlctlhtml_index_stats_rpt.ctl' log='E:htmlreportloghtml_index_stats_rpt.log'
sqlldr abc@tns/pwd control='E:htmlctlhtml_invalid_object_rpt.ctl' log='E:htmlreportloghtml_invalid_object_rpt.log'
sqlldr abc@tns/pwd control='E:htmlctlhtml_long_running_queries_rpt.ctl' log='E:htmlreportloghtml_long_running_queries_rpt.log'
we know creating 780 control files is the difficult task.So I have created only 12 control files. is there any mechanism to pass a varible (planning to declare it in the sqlldr line) to the infile clause like below in sql loader?
infile "E:htmlreportoutput&a_html_broken_jobs_rpt.csv"
here a is the variable name. it will change every 12 csv files once.
or
is there anyother way to achive this?
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 RelatedI have created a form in which I have 2 canvases - content canvas and a tabbed canvas.
I have only 4 fields on the content canvas and the remaining on the tabbed canvas. My requirement is such that the tabbed canvas should appear below the content canvas when I run the form, i.e. the fields on the content canvas will be visible, and below them will be the tabbed canvas.
For the content canvas, I have set the viewportX and viewport Y position as (0,0), and for the tabbed canvas, I have set the viewportX and viewportY position as (0,100). However when I run the form, I only see the tabbed canvas and the area at the top where I should see the contents from the content canvas appear blank.
the following situation, I have a directory named /dat/global/stock/ inside this i will get files named differently for example below.abcdef.112dfgrt.2......
Here i want to load this file one by one into the external tables and generate one more file based on some enrichment.
Step 1. Have to take first file and to load into the ext table.
Step 2. Enrichment
Step 3.File generation.
Now here i am facing a problem that in that particular directory i usually get 1000 files so i need to get file one by one and to put in one more directory. how can i get file one by one and generate file by using oracle loader
when i tried to insert the details from oracle froms..the data inserts twice to the DB..
my table structure:
create table app_sri
(a_id integer primary key,
p_first_name varchar2(30),
p_last_name varchar2(20),
p_age number(3)
);
here a_id can be genarated through simple sequence(pid_seq)...
trigger on app_sri
create or replace trigger pid_trg
[Code]....
form insertion code..
Begin
insert into app_sri(null,'robo','Big',100);
commit
End;
the data inserted...but twice
what is the reason behind the double insertion?
I have problem viewing 2 procedures at the same time... e.g. i open procedure A, edit it, not close it. then i open procedure B from the left pane, and then i cannot see the procedure A... If i look at the File Menu on top left, it has the option of SAVE ALL, and not just SAVE.. that means both the procedures are open. but then why am i not able to see them?
View 6 Replies View Related--here's my set up
CREATE USER schemaowner IDENTIFIED BY schemaowner
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO schemaowner;
CREATE USER user1 IDENTIFIED BY user1
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO user1;
[Code] .....
my desired outcome for user 1
user1> select * from schemaowner.user_data;
user_id column1
1 USER1
my desired outcome for user2 are as follow:
user1> select * from schemaowner.user_data;
user_id column2
2 TESTER 2
the nearest solution is from with reference to [URL]
Quote:
Adding Policies for Column-Level VPD
....
SELECT ENAME, d.dname, JOB, SAL, COMM from emp e, dept d
WHERE d.deptno = e.deptno;
the database returns a subset of rows as follows:
ENAME DNAME JOB SAL COMM
-------------- -------------- ------------ ------------ -------------
ALLEN SALES SALESMAN 1600 300
WARD SALES SALESMAN 1250 500
MARTIN SALES SALESMAN 1250 1400
BLAKE SALES MANAGER 2850
TURNER SALES SALESMAN 1500 0
JAMES SALES CLERK 950
so how do I implement roll level and column level simultaneously?
I have 2 object tables. Location and a department. Department references a location Object. But this wont insert. I get "0 rows created".
CREATE OR REPLACE TYPE location_objtyp AS OBJECT
(
locationID NUMBER,
name VARCHAR2(48)
);
/
CREATE OR REPLACE TYPE dept_objtyp AS OBJECT
(
deptID NUMBER,
name VARCHAR2(48),
locationID_ref REF location_objtyp
);
[code]...
This does not insert. I get no error. Only - "0 rows created".
i have 100 records in table1,like as we have more 15 tables without data. the issue is how can i insert above table1 data(100 records) into different 15 tables in single sql command.
View 22 Replies View RelatedI am considering all of the capabilities and benefits of using Data Pump for exporting and importing extremely large data files. Would like to know if importing to tape is possible? If so, would the data be accessible if needed later?
View 4 Replies View RelatedI am trying to insert records in multiple tables. I know how to view data using joinig, but unable to understand how to insert records in multiple tables using Joining. I searched it on net, but didn't find much. I have also tried to write a code, but it is not working, I have seen some examples on different websites where people are using SELECT in INSERT statement for joining. What is the correct Syntax to INSERT record in Multiple tables.
Insert into library_users, library_users_info
(library_users.username, library_users.password, library_users_info.address, library_users_info.phone_no) VALUES (...)
We get data from our customers which we load into temporary tables.The goal is to consolidate this data into one single table.
Following are the rules:
1) final table should have all the columns from all the tables. If there are common column(s) then add only one column with that name.
2) the join would be based on all the common columns
3) if there is a common row, we merge the row into one (example, the row with DOMAIN = ACME.COM)
4) There could be 'N' number of tables
Following is the most realistic data.
1) T1/T2/T3 has the sample data which cover most of our test cases
2) We are expected to transform the data from T1/T2/T3 as depicted in table T4.
3) we might have more than 3 tables in our production environment, so the query should work for N tables.
4) I have given the explanation of how each row should be derived to be inserted in T4
5) the only information we have to work with is the TABLE_NAME(s) and its metadata from USER_TAB_COLUMNS
DROP TABLE T1;
DROP TABLE T2;
DROP TABLE T3;
DROP TABLE T4;
[code].....
Explanation for each row:
row1) This row comes from T1 and T2 (not T3 because HOSTNAME would not match)
row2) This row comes from T1 and T3 (not T2 because HOSTNAME would not match)
row3) This row comes from T1 and T3
row4) This row comes from T2 and T3
row5) This row comes from T3
I am working in a bank as an system consultant, i have a SAN Storage Area and oracle as below.
SAN 1
This interface includes the DATA FILES of the oracle tablespace
SAN 2
SAN1 Mirrors the DATA FILES of the oracle tablespace to SAN 2
1. Can i rely on real time data recovery from SAN2 ?
2. if SAN1 (Data Files are currupted) will the SAN2 Data Files will be currupted as well.
3. If the SAN2 is currupted then what Oracle Features can be used to have uncurrupted data.