I have a file list containing some accounts and respective value which I need to deduct from their promo account and update thier new promo account with the new value greater than zero and update to zero if the value is less than zero.
fileA
0112345 11636
01233224350
0122331 43885
0155582 13825
01334423339
I have an application that creates files using the utl_file package. It works fine but one of the lines in the file should hold the number of bytes in the file (on a line formatted like 'FileSize: 2104'). Unfortunately this line is not the last line of the file and the lines that follow it are variable in length.
My approach therefore is as follows:
1. Write the 'FileSize: ' line during file creation.
2. Write the remaining lines of data to the file (but don't close it).
3. Use utl_fgetattr to find the file size.
4. Go back and find the 'FileSize' line I need to update, using get_line to read it into the buffer.
5. Append the filesize (plus the number of characters that the variable I use to store the filesize value) to the buffer string.
6. Write the line back to the file using put_line.
However I keep getting invalid file operation errors however I try to re-access the file...
PROCEDURE setUtlFileSize (pFileName IN VARCHAR2,
pFileHandle IN utl_file.file_type) IS
vbFileExists BOOLEAN;
viFileLen NUMBER;
viFileBlockSize NUMBER;
vsFileRecord VARCHAR2(2000);
[code].......
This gives me the following output.... (for two files)... currently I am using some generic exception handling just to show me the error.
File Length: 2106
File Position: 0
-29283,ORA-29283: invalid file operation
File Length: 497
File Position: 0
-29283,ORA-29283: invalid file operation
Process exited.
Two things here concern me: The File Position suggests that the current offset position is set to the start of the file... but since I had not closed or performed any other operation since the last put_line and fflush (which are used to add lines to the file) I had expected that the offset position would be the same as the file length?
Secondly: Even if the position had been reset to the start of the file I don't understand why the get_line gives me the oracle error.
I am using OLE2 to update an excel spreadsheet from Oracle Forms 6i. If the excel spreadsheet is already open, the code runs through fine, and even asks if you want to replace the file when running the lines: OLE2.INVOKE(worksheet, 'Save');
OLE2.INVOKE(workbook, 'Save');
However, the file never gets updated. There is no error that appears. I would like to have the program check if the file is open before running the program;
I'm an absolut Oracle beginner and have problems with loading csv files into an Oracle DB. I get 2 files in the morning, the first one has provisionally information like that:
03.11.2011;BFS;03:00:00;00:29:25;0,00572090;0,22408220;56,71504000;
03.11.2011;BFS;03:30:28;00:30:29;0,00676938;0,29812120;55,06820000;
03.11.2011;BFS;04:04:56;00:28:54;0,00315933;0,15332050;61,68312000;
03.11.2011;BFS;04:33:50;00:59:33;0,00093421;0,04295300;59,57108000;
I load them with sqlload into my table. Table keys are the frist three fields. Now in the second file there are updates in the numbers, f.e. like that:
03.11.2011;BFS;03:00:00;00:27:25;0,00572090;0,22408220;56,71504000;
03.11.2011;BFS;03:30:28;00:31:29;0,00676938;0,29812120;55,06820000;
03.11.2011;BFS;04:04:56;00:29:54;0,00315933;0,15332050;61,68312000;
03.11.2011;BFS;04:33:50;00:60:33;0,00093421;0,04295300;59,57108000;
I can't load the with sqlload, because the program says, the key already exists. My control file looks like that:
load data
infile 'example.csv'
badfile 'example.bad'
append
into table mf_sendung
[code]...
i have one directory in this directory contains 100 file these files are related to table structured creation with dependence
so how to read file names from directory and executed in scheme with logs also
procedure or script
Is it possible to list pdf file having user provided string using oracle forms? if yes then how? How can I achieve this target?
View 2 Replies View RelatedI just switched my STBDY DB to a PRIMARY DB and I am ran the following:
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
new RMAN configuration parameters are successfully stored
ORA-00245: control file backup operation failed
RMAN-08132: WARNING: cannot update recovery area reclaimable file list
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORARZULB are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
[code]....
what causes this warning and how to get rid of it?
I am creating the Dynamic list but when i am compiling the form it gives the compilation error "No list elements defined for the list item".
I can eliminate it by entering the dummy list element but this dummy value will be displayed at form run time.
Im trying to list the products list of a client grouped by type of the product. Ex:
product type
prod.A acid
prod.B flavour
prod.C acid
prod.D cleaner
prod.E flavour
I want to list something as:
Acid
Prod.A
Prod.C
Cleaner
prod.D
Flavour
prod.B
prod.E
DECLARE
CURSOR GRP IS
SELECT RowNum rn, Letter_Group_ID||'-'||A_Desc AName,Letter_Group_ID
FROM Hrs_Group;
BEGIN
Clear_list('Letter_Group_ID');
FOR I IN GRP LOOP
Add_List_Element('Letter_Group_ID',I.rn,I.AName,I.Letter_Group_ID);
end loop;
END;
FRM-30351: No list elements defined for list item.
List LETTER_GROUP_ID
I just want to list and group all my tables that are linked together by constraints. I just want my tables to be able to be listed together as one particular database. my tables are , CUSTOMER, ORDER_INFO, ORDER_LINE, PRODUCT. They're all linked together by way of constraint and I want to list and print them all together as one DB. HOW DO I put them all in one schema and then also list them all together and print/illustrate them as one. also, I tried to import them into their own scheme but i ran into a series of probs regaurding the .dmp file being read.
View 2 Replies View RelatedI want to get the list of values which is not the given list .
I have the below data in my emp table
SQL> select emp_id from emp;
EMP_ID
----------
101
102
103
104
105
106
108
110
120
220
225
11 rows selected.
But I need to display the values from the list of values which are not in the emp table . So the result will be '3000,3002,3003'.
SQL> select emp_id from emp where emp_id ?;
EMP_ID
----------
3000
3002
3003
I am using oracle 9i, and having trouble with updating a table.
I get ORA-00001(unique constraint) Error on executing the sql below; I know sql below is little strange( which use unique key in 'SET' statement) . but It worked on My Oracle Server. but it didn't on Client's.
why this error occurs or why this error did not occur on my PC,
[Update sql](key is CD and SDATE)
Update TBL1
set CD = 'A',
[Code]....
i was just working on one of my SQL assignments from my database management course, and thus far, this is the first that I just can't figure out. The question is:
Quote: Increase the credit limit of any customer who has any order that exceeds their credit limit. The new credit limit should be set to their maximim order amount plus $1,000. This must be done in 1 SQL statement
The bolded part is what I'm having trouble with.
What I have thus far:
UPDATE Customers
SET CreditLimit = 1000 + (SELECT MAX(Amount) FROM Orders, Customers WHERE Cust = CustNum)
WHERE CustNum IN (
SELECT Cust
FROM Orders
WHERE Cust = CustNum
AND CreditLimit < Amount);
So there's two tables that I'll be working with, Customers (the table I'm updating), and Orders (the table where the order amount is found). With the code I have so far, it does seem to be updating the correct tables at the very least, but not with the correct values. It's essentially updating the CreditLimit column with the new value of 1000 + the maximum amount in the order table, which is very close to what I want it to do, but I want it to be 1000 + the maximum amount for that specific customer.
CustNum is the primary key for the Customers table, and Cust is the foreign key that links each together.
(about the formatting, it looked much prettier in SQL Worksheet Plus)
create table top_uid(oldUID number,newUID number);
select * from top_uid;
OLDUID NEWUID
---------------------- ----------------------
1 1001
2 1002
3 1003
4 1004
create table topdUIDXML (uidinfo clob);
insert into topdUIDXML select '<filter name="test" topologyUID="1">' from dual;
insert into topdUIDXML select '<filter name="test2" topologyUID="2">' from dual;
insert into topdUIDXML select '<filter name="ftest" topologyUID="3">' from dual;
insert into topdUIDXML select '<filter name="qtest" topologyUID="4">' from dual;
select * from topdUIDXML
UIDINFO
---------------------------------------
<filter name="test" topologyUID="1">
<filter name="test" topologyUID="2">
<filter name="test" topologyUID="3">
<filter name="test" topologyUID="4">
the topdUIDXML table will contain the oldUID's in the clob XML. need to update the topologyUID in that topdUIDXML with the newUID from the top_uid.
I am trying to find a way to update all of the rows in a table for a column EXCEPT for the very first row. I am not sure if this can be done while I enter my SET parameter or not. I have also thought about using an EXCEPTION in a stored procedure. For example, say I have the table listed below:
SQL> select * from example1;
CODE1 I_ID CODE2 J_ID NAME1 DATE1
----- -------------------- ----- -------------------- -------------------------------- ---------------
A 100 A 200 John 20111225
A 100 A 300 John 20111225
A 100 A 500 John 20111225
A 100 A 400 John 20111225
A 100 A 250 John 20111225
A 100 A 700 John 20111225
A 100 A 800 John 20111225
A 100 A 900 John 20111225
A 100 A 1000 John 20111225
A 100 A 1150 John 20111225
A 100 A 1275 John 20111225
A 100 A 3000 John 20111225
12 rows selected
I am wanting to update the table so that if there were more than 3 J_id's on the table for the same I_id then it will set all of the code1's and code2's to a C except for the very first one. Such as:
SQL> select * from example2;
CODE1 I_ID CODE2 J_ID NAME1 DATE1
----- -------------------- ----- -------------------- -------------------------------- ----------------
A 100 A 200 John 20111225
C 100 C 300 John 20111225
C 100 C 500 John 20111225
C 100 C 400 John 20111225
C 100 C 250 John 20111225
C 100 C 700 John 20111225
C 100 C 800 John 20111225
C 100 C 900 John 20111225
C 100 C 1000 John 20111225
C 100 C 1150 John 20111225
C 100 C 1275 John 20111225
C 100 C 3000 John 20111225
12 rows selected
I have done some searches and haven't seen any results.
Ok, CLOB columns are such a hassle.
I have a variable in my script: v_field1 VARCHAR2(32000);
This is part of a cursor record:
v_mf_table IS TABLE OF mf_detail%ROWTYPE INDEX BY BINARY_INTEGER;
v_mf_record v_mf_table;
I use a FORALL to insert the data into a table:
FORALL x IN v_mf_record.FIRST .. v_mf_record.COUNT
INSERT INTO monthly_mf_snapshot VALUES v_mf_record(x);
BUT! v_field1 is > 4000 characters. Does this trash my changes of using FORALL? Do I need to deal with 4k chunks in an UPDATE instead?
I have two tables,
CREATE TABLE repos
(
rep_key VARCHAR(10) NOT NULL,
base_term VARCHAR(100) NOT NULL,
blt_key INTEGER NOT NULL
[code]...
gloss table has the unique set of base_term as in repos. BLT_KEY will be primary_key in gloss and foreign key in repos.
Data in gloss table
BLT_KEY BASE_TERM
1 base1
2 base2
3 base3
Now, I need to update the BLT_KEY in gloss to matching entries in repos. Can I do that in a update on select statement? like,
UPDATE repos
SET blt_key = (SELECT gloss.blt_key
FROM repos,
gloss
WHERE repos.base_term = gloss.abase_term) This throws subquery returns more than one row.
And the end of update the repos table should look like,
REP_KEY BASE_TERM BLT_KEY
M001 base1 1
M002 base1 1
M003 base2 2
M004 base1 1
[code]....
Also I need a single query which can update on select as the no of records to be updated are more than 90000 in repos. So two step process would slow down the process
Im looking for the posibility to update some records using new id with the column values with another id
example
the table contains these records:
id gross net
========================
7 0,1 0,0507749
8 0,2 0,1015499
9 0,5 0,2538748
10 0,83 0,4214
11 0,85 0,4315873
[Code]....
and I would like insert the same gross and net column values of ids 7 to 16 into columns with the ids 40 to 49 in the same order. therefore I would like to obtain the result that I describe below:
id gross net
========================
7 0,1 0,0507749
8 0,2 0,1015499
9 0,5 0,2538748
10 0,83 0,4214
11 0,85 0,4315873
[Code]....
I am trying to write a control file that will read information from two INFILES and update two tables with the different information via SQL Loader. I am using Oracle 11g on Linux. i am not sure how to take the result from the first insert query and use it as input to the second insert query. Currently I have the following control file:
LOAD DATA
INFILE 'table1.dat'
INFILE 'table2.dat'
APPEND
INTO TABLE table1
WHEN tid='1'
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
(pid "pid.nextval", p_fname, p_lname, tid)
[code]....
The pid in the second insert query should be taken from the pid generated in the first insert query. However, I do not know how to do this. Does SQL Loader run the first insert query for all entries in the table1.dat file and then the second insert query or will it do one record at a time? Here are my INFILES:
table1.dat
,John,Doe,1
,Joe,Smith,1
table2.dat
10,,abc,1,
11,,xyz,1,
The second field in table2.dat should be taken from the result of creating a record in the table1.dat. Is this something that can be achieved using SQL Loader? The first part of the control file is successful, I can see the table being updated with the contents on table1.dat, but the second query fails.
i'm using the below query to update a VOTER table with over 15million records but it's taking ages to finish. i am using 11gr2 on linux
the query:
MERGE INTO voter dst
USING (
SELECT voterid,
pollingstation || CASE
WHEN ROW_NUMBER () OVER ( PARTITION BY pollingstation
[code]........
I have two different tables having similar structure but data is coming from different source.finally i want to update the view so that the it should affect the base table.
create table emp1 as
select empno,ename,job,deptno,sal from emp where deptno=10;
create table emp2 as
select empno,ename,job,deptno,sal from emp where deptno=20;
create view emp_view as select * from emp1 union all select * from emp2;
[code].......
I have made a correlated update statement using rowid. Find my attachment. Its updating all columns which i wanted but issue is that its not updating in 1st commit.
Suppose 6 rows is to be updated, then in 1st commit its updating 1 record, then in 2nd commit its updating 2nd record and so on. And in Toad its showing 6 rows updated in 1st commit, then 5 rows updated in 2nd commit and 1 rows updated in last record. I want that all records to be updated in first commit only.
I had created a table which have 100s' of entries in it.
create table reg_user
(
USERNAME VARCHAR2(50),
PASSWORD VARCHAR2(20)
)
***
[Code]..
1. In this table, how to update the Nth row, how can I do it
2. Now I need to add USERID in this table,which will get value from 1 to max no. of rows. I do not want to drop the table and again re create it adding USERID or update each row manually. Is there any other way to add USERID and have IDs from 1 to max IDs.
Oracle 11.2 - The goal is to create a trigger on table and anytime an update, delete or insert is done on the table, write values to a second table. I have the trigger and it works except it is not loading my col1/PK values. I understand I need to do a new/old value. Col1 is my PK on Table that I want to load anytime there is an update/delete/insert on the table. How do I code the old/new variable?
My
CREATE OR REPLACE TRIGGER TRIGGER_NAME
AFTER INSERT OR UPDATE OR DELETE
ON TABLE_NAME
FOR EACH ROW
DECLARE
v_col1 TABLE_NAME.COLUMN%TYPE;
BEGIN
[code]...
I am learning oracle trigger, i have one query.
Can i increment the old column value in trigger.
eg: :new.cid := :old.cid+1;
is this is correct.
I'm trying to write procedures to make updating account owners and the like easier for a group of DBA's.
What I want to do, is create a procedure that displays the changes live.
e.g. If I changed the owner of 5 users from owner 100 to owner 200 it will display:
User test1 owner changed from 100 to 200
User test2 owner changed from 100 to 200
User test3 owner changed from 100 to 200
User test4 owner changed from 100 to 200
User test5 owner changed from 100 to 200
I can not get a loop to work to save my life. Here's what I have to update the account so far...
PROCEDURE UPDATE_OWNER (OWNER NUMBER, NEW_OWNER NUMBER) IS
BEGIN
UPDATE ACCOUNT_TRACKING
SET ACCOUNT_OWNER=NEW_OWNER WHERE ACCOUNT_OWNER = OWNER
AND ACCOUNT_TYPE !='P';
DBMS_OUTPUT.PUT_LINE ('Account Owner '||OWNER||' Changed to '||NEW_OWNER);
END UPDATE_OWNER;
PM_KEY.. PCN....... JOBNO...PM_VERNO
20......... 137....... XX23..... 0
21......... 137....... XX24..... 1
22......... 137....... XX17..... 2
23......... 137....... XX81..... 3
22......... 137....... XX90..... 2
I have a dilemma......the constraint was disable somehow in my table of about 900,000 records which allowed the insertion on duplicate primary keys as well as duplicate records. I've managed to get rid of the duplicate records, but I haven't quite figured out how to update the primary key and version number. I've tried the following but
UPDATE TABLE
SET PM_PM_KEY=(MAX(PM_KEY)+1), PM_VERNO=(MAX(PM_VERNO)+1)
WHERE TBLDATE= MAX(TBLDATE)
ORDER BY TBLDATE ASC
GROUP BY PCN;
The query is failing with
Error at Command Line:2 Column:19
Error report:
SQL Error: ORA-00934: group function is not allowed here
00934. 00000 - "group function is not allowed here"
*Cause:
*Action:
UPDATE PMAPS_Z_DEMAND_MASTER_DATA_TMP C
SET PACKAGE_NAME =
(SELECT DISTINCT
A.PACKAGE
[Code]...
i wanted to update data in PMAPS_Z_DEMAND_MASTER_DATA_TMP table but i kept getting "more than 1 value returned in sub query" error, i know what it is but i don't know why it is happening here.
I have a table structure as follows
Student(Id,First_Name, Last_Name, email, Contact, Address1,Address2,City,Edit_Date,Create_Date,Archived)
Now if there is more than one row with same email the one with the latest edit date should be updated with missing fields by using same field value other rows (if the field is present in more than one row, the one with the next latest edit date is to be considered) and the archived status of all rows with same email except this master row must be set to 1.
The Create_Date must be set to the minimum of all the create_date values of rows with same email value The create table would be as follows:
CREATE TABLE student(Id NUMBER PRIMARY KEY,first_name VARCHAR2(30) NOT NULL,last_name VARCHAR2(30) NOT NULL,email VARCHAR2(30) NOT NULL,contact NUMBER,adress1 VARCHAR(30),adress2 VARCHAR(30),city VARCHAR(30),edit_date DATE,create_date DATE,archived CHAR(1))
Sample insert statements would be: insert into student values