Unable to update unix shell variable in sql plus

Question

I have a shell script that stores an array of values picked up as input from its parent script.

This array needs to be used within a block of PL/SQL code. I need to iterate through the unix array and store the elements in a pl/sql collection variable. But I am unable to increment the index of the array. Below is my code:

#!/bin/sh
# local variables declaration and initialization
v_username="$1"
v_password="$2"
v_database="$3"
v_vdsl_file="$4"

IFS=$'\n'
set -f

counter=0
declare -a v_vdsl_id
for i in $(cat $v_vdsl_file); do
v_vdsl_id[$counter]=`echo "$i"`
counter=$(($counter+1))
done;

p=0

conn1=`sqlplus -s ${v_username}/${v_password}@${v_database}<<THEEND

SET HEAD OFF
SET AUTOPRINT OFF
SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET LINESIZE 3000
SET WRAP OFF 
SET serveroutput on

DECLARE

counter integer :=0;
type table_vdsl_id is table of integer;
vdsl_oids table_vdsl_id := table_vdsl_id();

begin

for i in 1..${#v_vdsl_id[*]}
loop
counter := counter +1;
vdsl_oids.extend;
vdsl_oids(counter) := ${v_vdsl_id[$p]};
${p} := ${p} +1;
end loop;
end;
/
THEEND`

The error is faced while trying to increment the unix variable counter:

0 := 0 +1;
    *
ERROR at line 88:
ORA-06550: line 88, column 9:
PLS-00103: Encountered the symbol "0" when expecting one of the following:
( begin case declare end exit for goto if loop mod null

The ${p} basically picks up the value of p, but how do I change the value of this unix counter in the sql block? Please advise.

Answer 1

A here document is just a static string. The shell will (optionally) substitute any variables in the here document before it runs the program which consumes this here document, but once that's done, there is no interaction between the shell and the generated string, or the program you are passing this string to.

A simple workaround is to loop the entries in the shell, so that the generated script which you pass to PL/SQL just contains a sequence of static statements. Perhaps like this:

conn1=$(sqlplus sqlplus -s "${v_username}/${v_password}@${v_database}"\
 "$(cat <<'____HEADER';
    SET HEAD OFF
    SET AUTOPRINT OFF
    SET ECHO OFF
    SET VERIFY OFF
    SET FEEDBACK OFF
    SET LINESIZE 3000
    SET WRAP OFF 
    SET serveroutput on

    DECLARE

    type table_vdsl_id is table of integer;
    vdsl_oids table_vdsl_id := table_vdsl_id();

    begin
____HEADER

  nl -ba "$v_vdsl_file" |
  while read -r lineno i ; do
      cat <<_________RECORD
        vdsl_oids.extend;
        vdsl_oids($lineno) := $i;
________RECORD
  done )
  cat <<'____FOOTER'
    end;
    /
____FOOTER
)")
# for debugging
echo "$conn1"

I had to guess some bits and pieces of the SQL syntax, so there will probably be details which you will need to correct. Replace sqlplus with cat to see what is being generated, and check whether it looks correct.

Note the double quotes around the variable interpolations, and the preference for while over for for looping over the lines in a file. I used nl to generate a line number so I wouldn't need to use the clunky shell arithmetic operators for that.

Answer 2

In PL/SQL you cannot assign something to a constant. And since you used $p:=$p+1, it does so: 0 := 0+1. Not possible.

Another possible solution, aside from triplee's one would be to store the array in a variable, with a separator that must not be inside one of the array's values of course - say _THE_SEP_="_THE_IMPOSSIBLE_SEP_".

1. concatenate array values

for i in $(cat $v_vdsl_file); do
  _LIST=${_LIST}${_THE_SEP_}${i}
  counter=$(($counter+1))
done

2. build a collection (here a simple TABLE OF VARCHAR2) out of the ${_LIST} string directly in PL:

CREATE OR REPLACE TYPE t_my_list AS TABLE OF VARCHAR2(100);
/
DECLARE
  l_string VARCHAR2(32767) := '${_LIST}' || '${_THE_SEP_}';
  l_sep_index PLS_INTEGER;
  l_index PLS_INTEGER := 1;
  l_l_thesep PLS_INTEGER  := length('${_THE_SEP_}');
  l_tab t_my_list     := t_my_list();
BEGIN
  LOOP
    l_sep_index := INSTR(l_string, '${_THE_SEP_}', l_index);
    EXIT
  WHEN l_sep_index = 0;
    l_tab.EXTEND;
    l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string,l_index,l_sep_index - l_index));
    l_index            := l_sep_index + l_l_thesep;
  END LOOP;

3. Then your collection is in l_tab. Do what you want with it.

You can use the whole thing as:

    _THE_SEP_=_THE_IMPOSSIBLE_SEP_
    for i in $(cat $v_vdsl_file); do
      _LIST=${_LIST}${_THE_SEP_}${i}
      counter=$(($counter+1))
    done

    conn1=`sqlplus -s ${v_username}/${v_password}@${v_database}<<THEEND

    SET HEAD OFF
    SET AUTOPRINT OFF
    SET ECHO OFF
    SET VERIFY OFF
    SET FEEDBACK OFF
    SET LINESIZE 3000
    SET WRAP OFF 
    SET serveroutput on

    CREATE OR REPLACE TYPE t_my_list AS TABLE OF VARCHAR2(100);
/
    DECLARE
      l_string VARCHAR2(32767) := '${_LIST}' || '${_THE_SEP_}';
      l_sep_index PLS_INTEGER;
      l_index PLS_INTEGER := 1;
      l_l_thesep PLS_INTEGER  := length('${_THE_SEP_}');
      l_tab t_my_list     := t_my_list();
    BEGIN
      LOOP
        l_sep_index := INSTR(l_string, '${_THE_SEP_}', l_index);
        EXIT
      WHEN l_sep_index = 0;
        l_tab.EXTEND;
        l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string,l_index,l_sep_index - l_index));
        l_index            := l_sep_index + l_l_thesep;
        dbms_output.put_line(l_tab.COUNT||'-'||l_tab(l_tab.COUNT)||'/');
      END LOOP;
    END;
/
THEEND`
# see your output then:
echo $conn1