locked
DAAB, Oracle, .NET 2.0 Issue with calling SP RRS feed

  • Question

  • User1221987322 posted

    I am using Client 8.1.7 of Oracle.

    My environment :

    .NET2.0, Windows 2K Pro, Oracle Server is 9i, Oracle Client is 8.1.7. Development machine and ORacle server are two different physical machines.

    I have created a system DSN and referring to that in the web.config file.

    Following are the problems I am facing:

    1. Using DAAB, I am not able to execute SPs (can execute direct script from the code - something like putting commandtext select * from category will work but if I put same thing in SP it wouldnt work..

    Its giving me the below error:

    ORA-12571: TNS:packet writer failure

    PS: I am trying to return a REF Cursor (again didnt find any easy way of using with DAAB hence I am using ORaclePArameter of OracleClient when I ahve to deal with Cursors.  Below is the sample code:


     Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbc = db.GetStoredProcCommand("ACA_SELECT.SPGETCATEGORY");

            db.AddInParameter(dbc, "PLANG_ID", DbType.String, "en");
            db.AddInParameter(dbc, "PSESSION_ID", DbType.Int32, 100);
            OracleParameter prm3 = new OracleParameter("cur_OUT", OracleType.Cursor);
            prm3.Direction = ParameterDirection.Output;
            dbc.Parameters.Add(prm3);

            DataSet ds = db.ExecuteDataSet(dbc);

    2. If in the above I dont use Cursor but just use output parameters with ExecuteNonQuery, I receive no error.. the browser page keeps trying to load.. no error nothing happens neither the browser shows anything.

    3. Using ORacleClient, I can execute SPs and queries without any issue till the time I dont use a NVARCHAR field in the backend. If I use NVARCHAR field, I am able to execute the command but not able to see the data from the NVARCHAR field. PS: I cannot even see data from toad however can see from SQL*Plus console.

    Now my questions are:

    1. What is minimum version of the oracle client I need for DAAB (latest version of DAAB).

    2. IS there anything else that could be causing that issue? As the queries are running directly from the page but not the SPs.

    3. What is the best way to receive Ref Cursors from Oracle into .NET?

    4. If I am returning a NVARCHAR2 field from ORacle and if I specify the OracleType.NVARCHAR, it throws an exception.. any clues?

    5. Why I dont see the values of NVARCHAR field in ORacle even in case of OracleClient (lets say if there are 3 fields in a table.. one of it is NVARCHAR), OracleClient will show all the 3 values but will display blank for NVARCHAR field even though it has value. It shows the value fine for other two fields.

    6. What is the minimum version of oracle client needed to use DAAB, .NET2.0 for multi lingual application.

    PS: This issue remains whether I use SELECT * FROM CATEGORY OR SP which returns the same.

    Will greatly appreciate your help.

    Let me know if I missed giving any details.

    Friday, June 9, 2006 6:30 PM

All replies

  • User1416329745 posted

    The reason you are getting the error is because the Microsoft Provider does not accept input parameters with Oracle RefCursor. You could change your provider to Oracle and use input parameters with RefCursor but you have to change your Oracle client to 9i because I think 9i is required to use the Oracle provider.  Try the thread below for links related to restrictions and limitation with Oracle RefCursors.  Hope this helps.

    http://forums.asp.net/thread/1304557.aspx

    Monday, June 12, 2006 9:51 AM
  • User-1481118128 posted

    I am having the same problem.

    I would prefer to use the DAAB instead of the Oracle .Net adapter, sice I wouldn't have to change a bunch of code.  Here is my .NET code:

      Public Shared Function GetAllPropertysGeneralDataOnly() As DataSet
        Dim ds As New DataSet
    
        'Send Data to Database stored procedure
        Dim db As Database = DatabaseFactory.CreateDatabase("YardiOracle")
        Dim dbCommand As DbCommand = db.GetStoredProcCommand("SelectPropertyByMode")
        db.AddInParameter(dbCommand, "p_Mode", DbType.String, "PROPERTY")
    
        Dim curParam As OracleParameter
        curParam = New OracleParameter("p_PropertyOut", OracleType.Cursor)
        curParam.Direction = ParameterDirection.Output
        dbCommand.Parameters.Add(curParam)
    
        ds = db.ExecuteDataSet(dbCommand)
        Return ds
      End Function

    This generates the ORA-12571: TNS:packet writer failure error.  Originally, the windows server 2003 machine only had the Oracle 8 client, but we have installed the Oracle 9i client release 2, and this still happens.

     It seems to happen because of the output cursor, since this code works fine:

      Public Shared Function TestConnection() As Boolean
        Dim db As Database = DatabaseFactory.CreateDatabase("YardiOracle")
        Dim dbCommand As DbCommand = db.GetStoredProcCommand("SelectPropertyByMode")
        db.DiscoverParameters(dbCommand)
        If dbCommand.Parameters(0).ParameterName = "" Then 
          Return False
        Else
          Return True
        End If
      End Function
     Any thoughts?
    Thursday, February 1, 2007 2:14 PM
  • User1416329745 posted

    If you are running Oracle 8i I don't think you can use Oracle provider because I think Oracle supports only 9i release 2.0 and above, that is the reason I always ask what was needed with 8i.  Oracle 11 g is already in beta 1 so see if you can download the 10 Express free and use it for development and later move it to 8i.  I am not saying it is going to be easy but I think you could try it.  Hope this helps.

    Thursday, February 1, 2007 3:40 PM