6 Replies Latest reply on Nov 3, 2004 3:22 PM by 82532

    VB code for executing pl/sql procedure, and displaying TABLE OUT parameter

    213738
      Does anybody have any sample VB code (using OO4O) for executing a PL/SQL stored procedure, and then displaying (in Excel preferably) the following OUT parameters from the procedure:
      (1) variable(s) of TYPE PL/SQL RECORD
      (2) variable of TYPE PL/SQL TABLE defined as TABLE%ROWTYPE
      (3) n variables of TYPE PL/SQL TABLE'S defined as table.column%TYPE;

      Thanks.
        • 1. Re: VB code for executing pl/sql procedure, and displaying TABLE OUT parameter
          82532
          1 - nope.
          2 - nope.
          3 is then moot.

          Tables of records (or rowtypes) can't be used outside the PL/SQL engine.
          • 2. Re: VB code for executing pl/sql procedure, and displaying TABLE OUT parameter
            213738
            So, if we are executing a PL/SQL stored procedure from a VB program, is ref cursor THE ONLY WAY to return data to the client ?

            Thanks!
            • 3. Re: VB code for executing pl/sql procedure, and displaying TABLE OUT parame
              82532
              It is the only way to return datasets. You can return scalar variables and arrays of simple scalar variables. You can also return collections and collections of objects, but not record types or rowtypes. This is just a limitation of the PL/SQL engine interface that you can't return PL/SQL specific datatypes (boolean is another example, but it is easy to map these to a number--1 or 0).
              • 4. Re: VB code for executing pl/sql procedure, and displaying TABLE OUT parameter
                213738
                So Mark - if I had to execute a stored procedure (VBA using OO4O) and have it return 6 columns and 60,000 records into Excel, what would be the best (performance-wise) PL/SQL OUT method to use. Assume that users will be executing this "macro" from a remote location and hence network round trips needs to be considered.

                Thanks!
                • 5. Re: VB code for executing pl/sql procedure, and displaying TABLE OUT parameter
                  cenwdmr
                  Here is what we use to bring in arrays of data from plsql. Here will be some sample vba code and plsql.

                  What the plsql need to run in an array of input dates and an array of ids to lookup for the dates (also needs the number of values in the arrray.

                  vba code...
                  Sub Bull()
                  Dim oDoc As Object
                  Dim oTable As Object
                  Dim oCell As Object
                  Dim ArrCtr As Integer
                  Dim iCount As Integer 'Counter
                  Dim DataDynaset As Object 'Dynaset for retrieved values
                  Dim DischDataDynaset As Object 'Dynaset for retrieved values
                  Dim TWDataDynaset As Object 'Dynaset for retrieved values
                  Dim PDDataDynaset As Object 'Dynaset for previous day retrieved values
                  Dim MSTssCodes As Variant ' TS Codes Array
                  Dim NumRows As Integer 'Number of rows in the table
                  Dim NumColumns As Integer 'Number of columns in the table
                  Dim NumArrayVals As Integer 'Number of array values (actual number - 1)
                  OraLogin
                  NumRows = 83
                  NumColumns = 11
                  'One less than number because we start at 0
                  'NumArrayVals = 5
                  NumArrayVals = 117
                  ReDim DateArr(NumArrayVals)
                  MSTssCodes = Array(3398, 3406, 3391, 3385, 3413, 3378, _
                  2254, 2254, 2254, _
                  3398, 3406, 3391, 3385, 3413, _
                  3460, 3455, 3450, 3447, 3505, 3444, 2254, 2254, _
                  4974, 2803, 4735, 4719, 2837)

                  ****** NOW THIS WILL GET READY TO SEND TO PLSQLGetData MSTssCodes, DateArr, DataDynaset, NumArrayVals + 1
                  .... more suff in the sub
                  Sub GetData(Tssids, DateArray, TmpDataDynaset, NumRetVals)
                  'Retrieves data from the Oracle database
                  Dim I As Integer 'Counter
                  Dim TssidDynaset As Object 'Temporary array for Tssids
                  Dim DateDynaset As Object 'Temporary array for dates

                  OraDatabase.Parameters.addTable "TSCODES", 1, 68, NumRetVals, 0
                  OraDatabase.Parameters("TSCODES").ServerType = ORATYPE_UINT
                  OraDatabase.Parameters.addTable "DATES", 1, 12, NumRetVals, 0
                  OraDatabase.Parameters("DATES").ServerType = ORATYPE_DATE
                  OraDatabase.Parameters.addTable "VALS", 3, 2, NumRetVals, 0
                  OraDatabase.Parameters("VALS").ServerType = ORATYPE_NUMBER
                  OraDatabase.Parameters.Add "NUM_VALS", NumRetVals, 3
                  OraDatabase.Parameters("NUM_VALS").ServerType = ORATYPE_NUMBER
                  OraDatabase.Parameters.Add "ERR_NUM", 0, 2
                  OraDatabase.Parameters("ERR_NUM").ServerType = ORATYPE_NUMBER

                  Set TssidDynaset = OraDatabase.Parameters("TSCODES")
                  Set DateDynaset = OraDatabase.Parameters("DATES")
                  'Fill the arrays
                  For I = 0 To NumRetVals - 1
                  TssidDynaset.Put_Value Tssids(I), I
                  DateDynaset.Put_Value DateArray(I), I
                  Next I

                  OraDatabase.DbExecuteSQL ("Begin DBCALLS.SELECT_VALUES (:TSCODES, :DATES, :VALS, :NUM_VALS, :ERR_NUM); End;")
                  If OraDatabase.LastServerErr <> 0 Or OraDatabase.LastServerErrText <> "" Then
                  MsgBox "Error Getting Data"
                  End If

                  Set TmpDataDynaset = OraDatabase.Parameters("VALS")

                  'MsgBox (OraDatabase.Parameters("NUM_VALS") & " " & OraDatabase.Parameters("ERR_NUM"))

                  OraDatabase.Parameters.Remove "TSCODES"
                  OraDatabase.Parameters.Remove "DATES"
                  OraDatabase.Parameters.Remove "VALS"
                  OraDatabase.Parameters.Remove "NUM_VALS"
                  OraDatabase.Parameters.Remove "ERR_NUM"
                  End Sub


                  Now here is the plsql called from the DBCALL package......
                  --Select time series values for give ts_codes.
                  PROCEDURE select_values (
                  ts_codes IN IntArrayTyp,
                  dates IN DateArrayTyp,
                  vals IN OUT NumArrayTypIB,
                  --quality   IN  OUT RawArrayTyp,
                  num_vals IN INTEGER,
                  err_num OUT INTEGER) IS
                  BEGIN
                  --initialize variables
                  err_num := 0;
                  FOR i IN 1..num_vals LOOP
                  BEGIN
                  SELECT value INTO vals(i)
                  FROM table_name
                  WHERE ts_code = ts_codes(i)
                  AND date_time = dates(i);
                  EXCEPTION
                  WHEN OTHERS THEN
                  --dbms_output.put_line (ts_codes(i) || ' ' || SQLCODE);
                  vals(i) := -1.0;
                  err_num := SQLCODE;
                  END;
                  END LOOP;
                  EXCEPTION
                  WHEN OTHERS THEN
                  --dbms_output.put_line (tssid || ' ' || SQLCODE);
                  err_num := SQLCODE;
                  END select_values;
                  • 6. Re: VB code for executing pl/sql procedure, and displaying TABLE OUT parameter
                    82532
                    Probably the most performant way would be to use six scalar arrays. I am not certain that this would be faster than a single array of say a custom type that included the same 6 fields, but it requires less work (since you are not creating and using a custom type).

                    If the column count were to go up a lot then it becomes more of a maintenance headache to deal with a lot of procedure parameters and a ref cursor starts to look good :)