REM oracur.bas
REM
REM This script generates an SQL Cursor processing block for Oracle.
REM
REM Usage:
REM Select the code (SQL statement) you want to use as a cursor.
REM After that, start the script.

'---------------------------------------------------------------
' (C) 2003 Alligator Company Software GmbH
' http://www.alligatorsql.com
' Author : Manfred Peter
'---------------------------------------------------------------
SUB PrintOut(sString AS STRING)
    DIM nLocalIndex AS INTEGER
    nLocalIndex = GetActualViewIndex()
    View(nLocalIndex).AppendLine(sString)
END SUB

DIM nViewIndex AS INTEGER
DIM sOut       AS STRING
DIM nCount     AS INTEGER
DIM sSelect    AS STRING
DIM sOut       AS STRING



'------------------------
' check if a view is open
nCount = GetViewCount()

IF nCount = 0 THEN
   OUTPUT("No statement selected ...")
   END
END IF

nViewIndex = GetActualViewIndex()
sSelect = View(nViewIndex).GetSelection()
IF sSelect = "" THEN
   DIM nStartRow AS INTEGER
   DIM nStartCol AS INTEGER
   DIM nEndRow AS INTEGER
   DIM nEndCol AS INTEGER

   nStartRow = 1
   nStartCol = 1
   
   nEndRow = View(nViewIndex).GetLineCount()
   nEndCol = View(nViewIndex).GetLineLength(nEndRow)

   View(nViewIndex).SetSelection(nStartRow, nStartCol, nEndRow, nEndCol)

   sSelect = View(nViewIndex).GetSelection()
   IF sSelect = "" THEN
      OUTPUT("Please enter or select a valid SQL statement ...")
      END
   END IF
END IF

DIM sCursor AS STRING
DIM sRecord AS STRING

DIALOG "Enter name of cursor"
"Name : ", sCursor
END DIALOG

IF sCursor = "" THEN
   sCursor = "cuCursor"
END IF

sRecord = " r" + sCursor;

PrintOut("---------------------- BEGIN of cursor template -------------------")
PrintOut("DECLARE")
PrintOut("")
PrintOut("CURSOR " + sCursor + " IS")
PrintOut("   " + sSelect + ";")
PrintOut("")
PrintOut("  " + sRecord + " " + sCursor + "%ROWTYPE;")
PrintOut("   nCount PLS_INTEGER;")
PrintOut("BEGIN")
PrintOut("")
PrintOut("   OPEN " + sCursor + ";")
PrintOut("   FETCH " + sCursor + " INTO " + sRecord + ";")
PrintOut("")
PrintOut("   <<BeginLoop>>")
PrintOut("   WHILE " + sCursor + "%FOUND LOOP")
PrintOut("      nCount := nCount + 1;")
PrintOut("      -- access your fields with ...")
PrintOut("")
PrintOut("      FETCH " + sCursor + "INTO " + sRecord + ";")
PrintOut("   END LOOP BeginLoop;")
PrintOut("")
PrintOut("   CLOSE " + sCursor + ";")
PrintOut("")
PrintOut("END")
PrintOut("---------------------- END of cursor template -------------------")