Hi All,
This is Ravi. I am new in Unix platform. I need unix script for executing oracle query.
Requirement:-
1. We had 2 tables 1 is source table 2 is target table.

Example:- Ravi1(Source Table) 100000 records
Ravi2(Target Table) 50000 records
Fallouts 50000
I was successfully got this result in in sqlplus.

But now i want to run this script in unix mechine. While executing this script in unix the fallouts should automatically it will store in fallouts table(Major issue for me).

So i don't know how to execute this query so i would appreciate if any one send that unix AWK script to me.

Code:

#!/bin/bash
sqlplus -s /nolog << EOF

CONNECT $DB_USER/$DB_PASSWORD

Your sql query here
................
.............

end;
/
EOF

Hi Ravi,

You can execute any SQL Statement from HERE..

##############################################

sqlplus -l USERNAME/PASSWORD@DB_SCHEMA <<EOF
insert into ODS_T_CFS_DUMP(FILE_NAME, REGION_ID, SDP_ID, SEQUENCE_ID, TOTAL_RECORDS) values ('$file','$region_id','$sdp_id','$sequence_id', '$cos_records');

Select 1 from dual;
select "Hello Ravi" from dual;
EXIT
EOF

###############################################

I will explain my total requirement:-
We are developed script in PL/SQL. In the PL/SQL script i was got the fallout report(no. of rows records not extracted well).

Now client he was asking us like we want to execute the script in unix mechine. He was asking like while executing the PL/SQL script in unix the fallouts should be automatically store in Fallout table.Is it possible.

give me your mail id i will send my PL/SQL that will easy for both to understand.

I will explain my total requirement:-
We are developed script in PL/SQL. In the PL/SQL script i was got the fallout report(no. of rows records not extracted well).

Now client he was asking us like we want to execute the script in unix mechine. He was asking like while executing the PL/SQL script in unix the fallouts should be automatically store in Fallout table.Is it possible.

give me your mail id i will send my PL/SQL that will easy for both to understand.

DECLARE CURSOR np IS
SELECT * FROM np_classes;

total_rows_np NUMBER;

CURSOR idb IS
SELECT * FROM dl_class;

total_rows_idb NUMBER;

expFallouts NUMBER;

BEGIN
FOR x IN np LOOP
total_rows_np := np% ROWCOUNT ;
END LOOP;

FOR y IN idb LOOP
total_rows_idb := idb% ROWCOUNT;
END LOOP;

IF total_rows_np > 0 AND total_rows_idb >0 THEN

expFallouts := total_rows_np - total_rows_idb;
dbms_Output.new_line;
dbms_output.put_line('DL_ CLASS');
dbms_Output.new_line;
dbms_output.put_line('total rows np :' ||To_Char(total_rows_np ));
dbms_output.put_line('total rows idb :' ||To_Char(total_rows_idb));
dbms_output.put_line('Expected Fallouts :' ||To_Char(expFallouts));
Dbms_Output.new_line;
ELSE
Dbms_Output.new_line;
dbms_output.put_line('DB Empty');
END IF;
END;


This is a Snippet code so please give idea how i want to execute this query in unix mechine.

DECLARE CURSOR np IS
SELECT * FROM np_classes;

total_rows_np NUMBER;

CURSOR idb IS
SELECT * FROM dl_class;

total_rows_idb NUMBER;

expFallouts NUMBER;

BEGIN
FOR x IN np LOOP
total_rows_np := np% ROWCOUNT ;
END LOOP;

FOR y IN idb LOOP
total_rows_idb := idb% ROWCOUNT;
END LOOP;

IF total_rows_np > 0 AND total_rows_idb >0 THEN

expFallouts := total_rows_np - total_rows_idb;
dbms_Output.new_line;
dbms_output.put_line('DL_ CLASS');
dbms_Output.new_line;
dbms_output.put_line('total rows np :' ||To_Char(total_rows_np ));
dbms_output.put_line('total rows idb :' ||To_Char(total_rows_idb));
dbms_output.put_line('Expected Fallouts :' ||To_Char(expFallouts));
Dbms_Output.new_line;
ELSE
Dbms_Output.new_line;
dbms_output.put_line('DB Empty');
END IF;
END;


Please check this query and tell me how i want to execute this code in unix mechine.