0 Replies Latest reply: Sep 5, 2009 6:50 PM by 716310 RSS

    ORA-00600 Calling Stored Procedure From .NET Web Service

    716310
      I'm calling a stored procedure from a .NET web service and I'm getting exception "System.Data.OracleClient.OracleException". When I debug the stored procedure using plsql developer it runs fine. When I copy the SQL from the stored procedure to an SQL window within plsql developer it also runs fine. Below is the C# code I'm using to make the connection and call the stored procedure. Just want to note I have other stored procedures that I'm successfully calling within the same app and database.
                      String sql_query = "pkg_cc.gettrunkbuilddurations";
                      string connString = ConfigurationSettings.AppSettings["ConnectionString.Dweb.Owner"];
                 OracleCommand cmd = new OracleCommand();
                 OracleConnection con = new OracleConnection();
                      con.ConnectionString = connString;
                      con.Open();
                      cmd.Connection = con;
                      cmd.CommandText = sql_query;
                      cmd.CommandType = CommandType.StoredProcedure;
                      cmd.Parameters.Add(new OracleParameter("group_id", OracleType.Number)).Value = group_id;
                      cmd.Parameters.Add(new OracleParameter("build_pc_id", OracleType.Number)).Value = pc_id;
                      cmd.Parameters.Add(new OracleParameter("from_date", OracleType.VarChar)).Value = from_date;
                      cmd.Parameters.Add(new OracleParameter("to_date", OracleType.VarChar)).Value = to_date;
                      cmd.Parameters.Add(new OracleParameter("recordset", OracleType.Cursor)).Direction = ParameterDirection.Output;
                      OracleDataReader reader = cmd.ExecuteReader();
                      
                      while( reader.Read() )
                      {
                          bool b = true;
                      }
      Below is the stored procedure code:
          procedure GetTrunkBuildDurations(group_id in number, build_pc_id in number, 
              from_date in varchar2, to_date in varchar2, recordset out sys_refcursor)  is
                   
              tmp_cursor sys_refcursor;
              
          begin
          
                  -- Don't bound to PC ID.
                  open tmp_cursor for
                  WITH all_builds AS
                      (SELECT
                          mods.cc_mod_revision revision, 
                          projects.cc_projects_name project_name, 
                          mods.file_count file_count,
                          builds.cc_buildpcid build_pc_id, 
                          builds.cc_builds_datetime build_date,
                          builds.cc_builds_timelength build_length, 
                          builds.cc_projectid project_id,
                          builds.cc_builds_forced build_forced, 
                          builds.cc_builds_successful build_successful,
                          builds.cc_builds_id build_id
                      FROM cc_builds builds
                      INNER JOIN 
                          (SELECT 
                              cc_mod_id,
                              cc_mod_revision, 
                              COUNT(cc_mod_revision) file_count
                           FROM 
                              (SELECT 
                                  cc_mod_revision, 
                                  cc_mod_id
                               FROM cc_mod
                               WHERE cc_mod_revision IN 
                                  (SELECT DISTINCT cc_mod_revision FROM cc_mod)
                               ORDER BY cc_mod_id, cc_mod_revision)
                           GROUP BY cc_mod_revision, cc_mod_id) mods
                      ON builds.cc_builds_id = mods.cc_mod_id
                      INNER JOIN cc_projects projects
                      ON builds.cc_projectid = projects.cc_projects_id
                      WHERE projects.cc_group_id = group_id
                      AND builds.cc_builds_datetime BETWEEN 
                          TO_DATE(from_date, 'MM/DD/YYYY HH24:Mi:SS') 
                          AND TO_DATE(to_date, 'MM/DD/YYYY HH24:Mi:SS')),
                  min_build_duration AS
                      (SELECT 
                          all_builds.revision revision, 
                          MIN(all_builds.build_date) build_date
                      FROM all_builds
                      GROUP BY all_builds.revision),
                  max_build_duration AS
                      (SELECT 
                          revision, 
                          MAX(build_date) build_date
                      FROM
                          (SELECT 
                              all_builds.revision revision, 
                              all_builds.project_name project_name, 
                              MIN(all_builds.build_date + 
                                  (all_builds.build_length / 86400)) build_date
                          FROM all_builds
                          GROUP BY all_builds.revision, all_builds.project_name)
                      GROUP BY revision)
      
                  SELECT 
                      t.*, 
                      MAX(build_duration) OVER() max_build_duration
                  FROM
                      (SELECT 
                          all_builds.revision revision, 
                          all_builds.file_count file_count,
                          COUNT(DISTINCT all_builds.build_pc_id) pc_count, 
                          COUNT(distinct all_builds.project_id) project_count,
                          ROUND((max_build_duration.build_date - 
                              min_build_duration.build_date) * 
                              86400, 0) build_duration
                      FROM all_builds
                      INNER JOIN min_build_duration ON 
                          min_build_duration.revision = all_builds.revision
                      INNER JOIN max_build_duration ON 
                          max_build_duration.revision = all_builds.revision
                      GROUP BY all_builds.revision, all_builds.file_count, 
                          max_build_duration.build_date, 
                          min_build_duration.build_date) t;
              
                  recordset := tmp_cursor;
          end;