storing large data in unix array variable

Shell Programming and Scripting
1

Hi,

I have table in sql ..from this table im storing the first coloumn values in shell array variable ...

after this passing this variable as an arugument in SQL procedure.

But the proc. is running fine only for 1024 values in array ...

How to store more than 1024 values in the array so that all the values would be affected after calling the proc.

Plz reply soon.

2

Use loops .. Post your code for better understanding ..

3

Hi,

I'm not sure that using shell arrays is the right answer for the problem you have.

In general, dump of a database table in memory is not a good idea.

For my pov, you should use temporary files.

4

It depends on how you are using the variable in the proc.

If you are using the variable on a IN statement then it would have the maximum input limit. Oracle has some limit which yield you the error ORA-00939

Worth verifying the proc first before the shell.

5 
set -A my_array `sqlplus -s username/pwd@DB <<EOF

set feedback off
SET NEWPAGE 1
SET ECHO OFF
SET FEEDBACK OFF
SET TERM OFF
SET PAGESIZE 50000
Set LINESIZE 300
SET TRIMS ON
set verify on

select * from Icunbarred_$var vv  ;

exit;
EOF`

echo "there are ${#my_array
[*]} elements in the array"
element=2

while [ $element -lt ${#my_array
[*]} ]
    do
        echo "----->>" ${my_array[element]}
        sqlplus -s username/pwd@DB <<EOF
        exec nextbilldtm_change(${my_array[element]});
        exit;
EOF
        let element=element+1;
    done

------------
My table contains around 10000 records. but after updating 1024 records it halts ...

What changes need to be done in this code. Kindly reply soon.

6

Forget the array. I would use a sqlplus spool command to create the output file, then read it back in the subsequent process.

However, I can't see the point of the intermediate file/array unless perhaps there are two independent databases or two different Oracle acconts. Any reason to not just invoke nextbilldtm_change in the first program?