Now i want to write a shell script which will check this rows (values) in a table in database (oracle) and if row exists it will delete the row else do nothing.
Now i want to write a shell script which will check this rows (values) in a table in database (oracle) and if row exists it will delete the row else do nothing.
You don't have to check if a row exists, just delete it. Oracle will not raise an error if you delete a not existing row.
The query-then-delete approach amounts to the following:
When the row exists, doing a select is just overhead; when the row is not there, you do only a select but then the cost is about the same as doing straigth a delete.
You didn't specify what are the fields you want to use as a key into the table, so I am assuming that the table has a primary key column F4 to be matched with field 4.
The above code is simple, but it has too much overhead because for every input line it forks a new sqlplus process, which in turn connects to the Oracle database instance and therefore another new process (Oracle server) is forked (unless the Oracle instance is configured as shared server).
Altough a bit more complex, the following approach is better:
I am using :f4 (bind variable) instead of '$f4' (literal value) inside the delete statement to allow the Oracle optimizer to do less parsing.
Read here for more explanations: http://asktom.oracle.com/pls/asktom/...:2444907911913
Executing a delete statemet for each row to be deleted leaves no choice to Oracle besides separately locate each single row (hopefully using a suitable index). We are in fact emulating a join method named nested loop.
There is a break even point where the number of rows to delete is large enough (in respect to the number of rows in the table) that an hash join is more efficient. This point is about 1% of the number of rows in the table, more or less depending on the average size of the rows.
To give to the Oracle optimizer the option to use an hash join, we must execute a single delete statement for all the rows to be deleted. This means that the rows from the input file must be loaded into a table (say SOURCE), so that it can be joined to the target TABLE.
We better also tell the Oracle optimizer about the number of rows in both the tables so that it can choose the best join method. This can be accomplished by collecting statistics using a procedure in the system package DBMS_STATS.
The commit statement is implied by the drop statement.
There is no performance advantage in doing a commit after every insert (this is just a myth); the converse is true, as a matter of fact every commit forces Oracle to flush the redo log buffer.
If ID is the primary key for the table SOURCE then the delete statement can be formulated differently, and this form most of the times is executed by Oracle using a more efficient plan:
If ID is not the primary key for the table SOURCE, Oracle will object to this statement with a rather obscure error message about "non key preserved table". Worse, since F4 is the primary key for TABLE, Oracle will assume that you want to delete from SOURCE instead! You better make sure to have a primary key on ID and to put table TABLE first in the from list. Caveat emptor.
Of course to provide the primary key on ID the create statement above has to be enhanced.
More on this subject: http://asktom.oracle.com/pls/asktom/...:5050838809412
Instead of loading the file into a normal table (heap table), we can create a sort of virtual table whose contents is taken on demand from the external file.
This kind of table is the external table. The SQL code is more involved and some administrative burden is required to make the thing possible, therefore I am omitting a code sample.
Hi Experts,
I need a script in which I need to scp a file /home/chandan/abc.txt to a remote server using scp. Then I need to check whether scp is successful or not.
How am I going to write this code?
My Server Name: myserver
File Name On My Server: /home/chandan/abc.txt
Destination Server... (1 Reply)
Hello,
please help me an the below issue.
i need to check whether data is available or not in a flat file generated by oracle
(sometimes sql didn't any records) to overcome this.
without opening flat file.
Thanks....... (1 Reply)
Hi for all!
I need for my job one shell script very concrete about connection to oracle databases and writing to text file.
Well I tell you:
1.- I must read a file as parameter from linux interface:
> example.sh fileToRead
2.-I must read line to line in "fileToRead" and take a... (9 Replies)
Hi,
I have a file monitor.txt as below...
# Times are converted to local time from GMT.
# Local Timezone: EST (GMT -05:00)
PARAM1
{
TIME 30;
CC1 "xxxxx";
CC2 "xxxxx";
CC3 "xxxxx";
CC4 "xxxxx";
}
PARAM2
{
4061 :... (3 Replies)
Hi all,
I wud like to get ur assistance in retrieving lines containing l1.My excel dataset contains around 8000 lines.I converted it into a text tab delimiter file and got the lines containing l1,My output is a list of lines containing l1 saved in a outfile.Some of d lines from my outfile s... (5 Replies)
I have a flat file with records like
Header
123 James Williams Finance2000
124 Pete Pete HR 1500
125 PatrickHeather Engg 3000
Footer
The structure is:
Eno:4 characters
Name:8 characters
Surname : 9 characters
Dept:7 characters
Sal:4characters
These are sample... (1 Reply)
Hi
I would like to know how to load oracle data to unix flat file using a shell script.
Can we use sqlldr to import data from oracle, if so can anyone help me with it. (2 Replies)
Hi all,
I have a data like,
0,R001,2,D
this wants to be loaded into a oracle database table.
Pl let me know how this has to be done.
Thanks in advance (2 Replies)
Hi,
I want to extract block of data from flat file.
the data will be like this
start of log
One
two
three
end of log
i want all data between start of log to end of log i.e One
two
three to be copied to another file.
This particular block may appear multiple times in same file. I... (4 Replies)