This discussion is archived
0 Replies Latest reply: Sep 5, 2009 4:50 PM by 716310 RSS

ORA-00600 Calling Stored Procedure From .NET Web Service

716310 Newbie
Currently Being Moderated
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;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points