string query = "DECLARE l_cursor  SYS_REFCURSOR;ServiceName la_service_provider_detail.service_name%TYPE;Surname people.surname%TYPE;Forename people.forename%Type;"+
                                  "Action Varchar(50);Rate ey_pupil_premium_log.rate%Type;"+                                
                                  "Funding_Code single_funding_formulas.funding_code%Type;"+
                               "BEGIN EYPupilPremiumPackage.GetEyPupilPremiumLog(pLogId=> 6613, pPreview => 'T',EyPupilPremiumLog=> l_cursor);"+
                                "LOOP"+
                                    "FETCH :l_cursor "+
                                    "INTO  ServiceName, Surname, Forename,Action,Rate,Funding_Code;"+
                /*IF l_cursor%NOTFOUND"+
                  Then 
                    DBMS_OUTPUT.PUT_lINE('NO DATA FOUND');       
                     EXIT WHEN l_cursor%NOTFOUND; 
               END IF;
              "END LOOP;"+ 
                "DBMS_OUTPUT.PUT_LINE(ServiceName || ' | ' || Surname || ' | ' || Forename ||'|' || Action|| '|' ||Rate|| '|' ||Funding_Code);"+ */
                                  "CLOSE l_cursor;" +
                                "End; /";

            var cmd = new OracleCommand(query, connection.OracleConnection);
            cmd.BindByName = true;
            //cmd.Parameters.Add("pLogId", OracleDbType.Int32).Value = 6613;
           // cmd.Parameters.Add("pPreview", OracleDbType.Varchar2).Value ="T"; 
            cmd.Parameters.Add("l_cursor", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
            cmd.ExecuteReader();
            OracleDataAdapter ada = new OracleDataAdapter(cmd);
            DataTable dt = new DataTable();
            ada.Fill(dt);

Hi get exception on excuting as ORA-06550: line 1, column 378:\nPLS-00103: Encountered the symbol \"\" when expecting one of the following:\n\n := . ( @ % ;\nORA-06550: line 1, column 449:\nPLS-00103: Encountered the symbol \";\" when expecting one of the following:\n\n . ( , % from\nORA-06550: line 1, column 470:\nPLS-00103: Encountered the symbol \"/\" when expecting one of the following:\n\n end not pragma final instantiable order overriding static\n member constructor map"}
any idea would be helful .. tried lot..TIA

Hi , Its working for me , now after getting the ouput list what i want to incoporate is DBMS_OUTPUTLINE if the result is empty or doesnt satisfy the where clause inside my sp.can I add as below or should I need write seperate query with just DBMS_OUPTUT?please advice .. TIA

 string query = "DECLARE l_cursor  SYS_REFCURSOR;status integer;test Varchar2(20);ServiceName la_service_provider_detail.service_name%TYPE;Surname people.surname%TYPE;Forename people.forename%Type;" +
                                 "Action Varchar(50);Rate ey_pupil_premium_log.rate%Type;" +
                                 "Funding_Code single_funding_formulas.funding_code%Type;" +
                              "BEGIN EYPupilPremiumPackage.GetEyPupilPremiumLog(pLogId=> 6613, pPreview => 'T',EyPupilPremiumLog=> :l_cursor);" +
                                   "DBMS_OUTPUT.ENABLE;DBMS_OUTPUT.GET_LINE(:test,status); End;";

            var cmd = new OracleCommand(query, connection.OracleConnection);
            cmd.BindByName = true;
            //cmd.Parameters.Add("pLogId", OracleDbType.Int32).Value = 6613;
           // cmd.Parameters.Add("pPreview", OracleDbType.Varchar2).Value ="T"; 
            cmd.Parameters.Add("l_cursor", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("test",OracleDbType.Varchar2).Direction= ParameterDirection.Output;
            cmd.ExecuteReader();
            OracleDataAdapter ada = new OracleDataAdapter(cmd);
            DataTable dt = new DataTable();
            ada.Fill(dt);

@OP, it appears your topic has changed. Maybe that's a new discussion since discussion is marked as solved?

Edited 5 Months Ago by rproffitt: Clarifying

This question has already been answered. Start a new discussion instead.