I need to
1.Open a csv
2.Process the csv i.e. Modify 2 column in the csv.
To modify the column the value needs to be passed to a pl/sql function and the return value should be updated
For eg:
If column 2 E,then E will be passed in database function
which will return Employee.
3. Write a temprray file with the changed value b.csv
I am trying to use the awk command but its not working
RET_VALUE=`sqlplus -s ${FCP_LOGIN} <<EOF
SET TERM ON
SET ECHO ON
SET SERVEROUT ON SIZE 10000
SET LINESIZE 250
Begin
foo(A);
end;
exit;
EOF`
awk -F "," '$2=$RET_VALUE' a.csv > b.csv
exit
But in my function foo,
I need to pass the value of 2nd column of csv
and after modification it need to be saved in the same field that us 2nd column.
Also foo is a function not a procedure
---------- Post updated 09-25-12 at 12:52 AM ---------- Previous update was 09-24-12 at 09:10 PM ----------
In th below code,
I ab able to call a procedure test_x which inturn calls the function and returns the desried value.
The portion which is left is,I need to pass the second column of my csv file
and return the value in OUTPUT cased on that which I can save in my csv
OUTPUT=`sqlplus -s apps/apps@APPS <<EOF
set pages 0 lines 120 trimout on trimspool on tab off echo off verify off feed off serverout on
var mavar varchar2(100);
exec test_x(:mavar);
print mavar;
exit;
EOF`
echo "OUT = ${OUTPUT}"
awk -F "," -v value="${OUTPUT}" '$2=value' a.csv > c.csv
exit
create or replace procedure test_proc ( i in varchar , j out varchar )
is
begin
j:='Outcome';
end;
/
and the shell script is
#!/bin/sh
inp='Sample';
OUTPUT=`sqlplus -s saidata/saidata <<EOF
set pages 0 lines 120 trimout on trimspool on tab off echo off verify off feed off serverout on
var mavar varchar2(100);
exec test_proc ( '${inp}' , :mavar);
print mavar;
exit;
EOF`
[sai@sai26 ~]$ ./junk.sh
OUT = Outcome
Check the one in Bold. The above is just a template and do the changes in your code accordingly.
which reads a.csv having 7 column seperated by delimeter
Modify the column 2,3,5 in the csv
To modify I need to call a pl/sql function which takes in the value of the column
and modifies it as per business logic and returns the value.
The value returned needs to be saved in the column 2