0 Replies Latest reply on Apr 29, 2002 9:12 AM by 145355

    Need to get and updatable cursor into OraDynaset using SP

    145355
      Hi,

      The problem is that when i use SP that return cursor i get a read only cursor into the dynaset, but when i get the cursor using Select SQL sentence directly i am getting the required updatable cursor (I am actually needs it because i need the ROWID because one of the columns is of type LONG ROW)

      On the VB side I tried 2 ways to get the cursor, both returned read-only cursor :

      *******
      ***A***
      *******

      Public Function GetRsFromSP(ByVal sProcedure As String, _
      Optional DropParams As Boolean = True) As ciRowSet

      On Error GoTo HandleIT

      Dim sParametersBakup As String
      Dim sSQL As String
      Dim ds As OraDynaset
      Set GetRsFromSP = New ciRowSet

      sParametersBakup = m_sParameters

      AppendParameter "dsCursor", ds, ptCURSOR, pmBOTH

      sSQL = "begin " & sProcedure & "(" & m_sParameters & "); end;"

      Dim SqlStmt As OraSqlStmt
      Set SqlStmt = m_OraDb.CreateSql(sSQL, 0&)
      Set ds = m_OraDb.Parameters("dsCursor").Value --> read only cursor
      .
      .
      .
      End Function

      *******
      ***B***
      *******

      Public Function GetRsFromSP(ByVal sProcedure As String, _
      Optional DropParams As Boolean = True) As ciRowSet

      On Error GoTo HandleIT

      Dim sParametersBakup As String
      Dim sSQL As String
      Dim ds As OraDynaset
      Set GetRsFromSP = New ciRowSet

      sParametersBakup = m_sParameters
      End If
      If m_sParameters <> "" Then m_sParameters = m_sParameters & ", "
      m_sParameters = m_sParameters & ":" & "dsCursor"
      sSQL = "begin " & sProcedure & "(" & m_sParameters & "); end;"

      Set ds = m_OraDb.CreatePlsqlDynaset(sSQL, "dsCursor", ORADYN_DEFAULT) --> read only cursor

      .
      .
      .
      End Function


      When on the Oracle side :
      the SP in the package defined as :

      -- Cursor and Cursor type definition for the MNG_GROUP_DEFINITION table structure
      cursor crMngDefinition is
      SELECT *
      FROM MNG_GROUP_DEFINITION;
      Type MngDefinitionCur is ref cursor return crMngDefinition%Rowtype;
      Procedure GET_GROUPS (pSourceSite_Id IN NUMBER DEFAULT NULL,
      pGroupId IN NUMBER DEFAULT NULL,
      MngDefinition in out MngDefinitionCur);

      And the SP content in the package body :

      Procedure GET_GROUPS (pSourceSite_Id IN NUMBER DEFAULT NULL,
      pGroupId IN NUMBER DEFAULT NULL,
      MngDefinition in out MngDefinitionCur)
      IS

      BEGIN
      --MNG_GROUP_DEFINITION.ROWID, MNG_GROUP_DEFINITION.
      open MngDefinition FOR
      SELECT MNG_GROUP_DEFINITION.* FROM
      MNG_GROUP_DEFINITION
      WHERE
      (
      SOURCE_SITE = pSourceSite_Id
      OR pSourceSite_Id IS NULL)
      AND (
      U_ID = pGroupId
      OR pGroupId IS NULL)
      ORDER BY
      SOURCE_SITE,
      U_ID;

      END GET_GROUPS;


      If you see the problem, please help me and i will be thankful

      Thank you,
      Ofir