Cursor re-opens after first select

["Eaton, Robert" <robert.eaton@LMCO.COM>]

oracle 8.0.5
Unix Solaris
SQR 4.3.4
NT 4.0

sorry about the many show statements.

In main select I am building a dynamic CREATE INDEX statement for the tables
selected in the main SQL.  I do Create-Index to execute the begin-sql for
the create.
The first index is created but when I return to the main select, it seems
that the cursor is reopened and I retrieve the first row (index name) again.
This causes an error when trying to create the same index again.

When I comment out the begin-sql statement, the main select fetches each row
properly.

Has anyone seen this?

Thanks,

Robert.

!*********************************************************************
begin-procedure main-processing
!*********************************************************************

let $idxname = '*'

begin-select
A.OWNERID,
B.TARGET_TBLNAME_LM,
C.INDEX_NAME_LM,
C.DDLSPACENAME,
C.IDX_UNIQUE_IND_LM,
C.PARALLEL_DEGREE_LM
C.PERCENT_FREE_LM,
C.INIT_TRANS_LM

  show 'BEFORE IF $idxname=' $idxname
  show 'BEFORE IF INDEX_NAME_LM=' &C.INDEX_NAME_LM
  if $idxname <> &A.OWNERID || '.' || &C.INDEX_NAME_LM

    show ' '
    show 'C.INDEX_NAME_LM=' &C.INDEX_NAME_LM
    let $idxname = '''' || &C.INDEX_NAME_LM || ''''
    show '$idxname=' $idxname
    do Build-FieldList
    show '$fieldlist=' $fieldlist

    let $idxname = &A.OWNERID || '.' || &C.INDEX_NAME_LM
    show '$idxname=' $idxname
    let $tblname = &A.OWNERID || '.SNAP$_' || &B.TARGET_TBLNAME_LM
    show '$tblname=' $tblname

    if &C.IDX_UNIQUE_IND_LM = 'Y'
      let $uniqclause = 'UNIQUE '
    else
      let $uniqclause = ''
    end-if

    let $storageclause = 'NOLOGGING PARALLEL ' ||
TO_CHAR(&C.PARALLEL_DEGREE_LM)
    let $storageclause = $storageclause || ' PCTFREE ' ||
TO_CHAR(&C.PERCENT_FREE_LM)
    let $storageclause = $storageclause || ' INITRANS ' ||
TO_CHAR(&C.INIT_TRANS_LM)

    let $tblspace = 'TABLESPACE ' || &C.DDLSPACENAME

    do Create-Index

  end-if

FROM
PS_DATAMART_SRC_LM A, PS_DATAMART_TBL_LM B, PS_DM_INDEXES_LM C
WHERE A.SOURCE_SYSTEM_LM = B.SOURCE_SYSTEM_LM AND
B.RECNAME = C.RECNAME AND
[$where_clause]
ORDER BY C.RECNAME, C.INDEX_NAME_LM
end-select

end-procedure  main-processing

!*********************************************************************
begin-procedure Build-FieldList
!*********************************************************************

let $fieldlist = ''

begin-select
D.FIELDNAME

  let $fieldlist = $fieldlist || &D.FIELDNAME || ','

FROM
PS_DM_INDX_COLS_LM D
WHERE D.INDEX_NAME_LM = [$idxname]
ORDER BY D.FIELDNUM
end-select

let $fieldlist = SUBSTR($fieldlist,1,LENGTH($fieldlist)-1)

end-procedure Build-FieldList

!*********************************************************************
begin-procedure Create-Index
!*********************************************************************

do Get-Current-DateTime

show 'Creating Index ' $idxname ' on Table ' $tblname ' Starting at '
$SysDateTime

begin-sql
CREATE [$uniqclause] INDEX [$idxname] on [$tblname]
([$fieldlist])
[$storageclause]
[$tblspace]
;
ALTER INDEX [$idxname] PARALLEL 1
;
end-sql

show 'Finished with Index ' $idxname ' on Table ' $tblname ' Starting at '
$SysDateTime

end-procedure Create-Index