locked
Count Function in Store Procedure Oracle Toad RRS feed

  • Question

  • User-410257276 posted

    Hai Friends,

                   I have a problem with COUNT Function in oracle store Procedure This is Procedure

    CREATE OR REPLACE PROCEDURE ONDAYDELIVER.SP_REGISTER_CUSTOMER(ClientId Varchar2,Email Varchar2,Passwordone Varchar2,Retypepassword varchar2,Mobileno Number,Cur_Count OUT NUMBER)IS
    REGCOUNT NUMBER;
    BEGIN
     SELECT COUNT(*) INTO Cur_Count FROM REGISTER WHERE EMAIL =Email OR MOBILENO=Mobileno;
     REGCOUNT:=Cur_Count;
    IF Cur_Count='0' THEN
    INSERT INTO REGISTER(CLIENTID,EMAIL,PASSWORD,RETYPEPASSWORD,MOBILENO) VALUES (ClientId,Email,Passwordone,Retypepassword,Mobileno);
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
    WHEN OTHERS THEN
    RAISE;
    COMMIT;
    END;
    /
    

    When i run select count query it will get the all the row in the table

    This is the normal count query

    SELECT COUNT(*) AS COUNT1 FROM REGISTER WHERE EMAIL='' OR MOBILENO=''

    This is working in normal query but when i use in store procedure its not working....

    Friday, April 1, 2016 7:27 AM

All replies

  • User-271186128 posted

    Hi vivekbuv,

    I suggest you could check the parameter value, and make sure they are same.

    Besides, I suggest you could declare some temporary variables, then use it to test the following code without using stored procedure.

    --declare Email Mobileno  Cur_Count
    SELECT COUNT(*) INTO Cur_Count FROM REGISTER WHERE EMAIL =Email OR MOBILENO=Mobileno;

    Since this issue is related to Oracle, I suggest you could post your problem to this forum: https://community.oracle.com/community/database/developer-tools/sql_and_pl_sql

    Best regards,
    Dillion

    Friday, April 1, 2016 9:53 AM
  • User-410257276 posted

    Dear Friend,

                        I just tried through query its working good... When i tried in store procedure its not working.....

                        If i type directly means its showing correct count eample,,,,

                     

    SELECT COUNT(*) INTO Cur_Count FROM REGISTER WHERE EMAIL ='xxxxxxx'OR MOBILENO='65499999';

    its working and Why? When i pass  through parameter its not working.....

    Please Give me some example

    Friday, April 1, 2016 10:50 AM
  • User269602965 posted

    Need to specify IN and OUT for parameters

    Notice how I specify the parameter direction both in the .NET side and the Oracle PL/SQL side.

    Example:

    /* VB.NET code in ASP.NET application */
    
    ' Get data from stored procedure '
    Try
    	Dim connstr As String = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString
    	Using conn As New OracleConnection(connstr)
    		Using cmd As New OracleCommand("{SCHEMANAME}.{PACKAGENAME}.rcGET_BIRD_DATA", conn)
    			cmd.CommandType = CommandType.StoredProcedure
    			cmd.Parameters.Clear()
    			cmd.Parameters.Add("p_DATE_BAND", OracleDbType.Date, ParameterDirection.Input)
    			cmd.Parameters.Add("p_AOU_CODE", OracleDbType.Decimal, ParameterDirection.Input)
    			cmd.Parameters.Add("p_BIRD_CLASS", OracleDbType.Varchar2, ParameterDirection.Input)
    			cmd.Parameters.Add("ret_REFCUR", OracleDbType.RefCursor, ParameterDirection.Output)
    			conn.Open()
    			Using oda As New OracleDataAdapter(cmd)
    				Dim ds As New DataSet()
    				oda.Fill(ds)
    				Me.RadGrid1.MasterTableView.DataSource = ds.Tables(0)
    			End Using
    		End Using
    	End Using
    Catch ex As Exception
    End Try
    
    /* CS.NET code in ASP.NET application */
    
    ' Get data from stored procedure '
    try 
      {
    	string connstr = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString;
    	using (OracleConnection conn = new OracleConnection(connstr)) 
    	  {
    		using (OracleCommand cmd = new OracleCommand("{SCHEMANAME}.{PACKAGENAME}.rcGET_BIRD_DATA", conn)) 
    		  {
    			cmd.CommandType = CommandType.StoredProcedure;
    			cmd.Parameters.Clear();
    			cmd.Parameters.Add("p_DATE_BAND", OracleDbType.Date, ParameterDirection.Input);
    			cmd.Parameters.Add("p_AOU_CODE", OracleDbType.Decimal, ParameterDirection.Input);
    			cmd.Parameters.Add("p_BIRD_CLASS", OracleDbType.Varchar2, ParameterDirection.Input);
    			cmd.Parameters.Add("ret_REFCUR", OracleDbType.RefCursor, ParameterDirection.Output);
    			conn.Open();
    			using (OracleDataAdapter oda = new OracleDataAdapter(cmd)) 
    			  {
    				DataSet ds = new DataSet();
    				oda.Fill(ds);
    				this.RadGrid1.MasterTableView.DataSource = ds.Tables[0];
    		  	}
    		  }
    	  }
      } 
    catch (Exception ex) 
    {}
    
    
    /* Oracle PL/SQL code */
    
    CREATE OR REPLACE PACKAGE {SCHEMANAME}.{PACKAGENAME}
    AS
    PROCEDURE rcGET_BIRD_DATA(p_DATE_BAND   IN  DATE
                             ,p_AOU_CODE    IN  NUMBER
                             ,p_BIRD_CLASS  IN  VARCHAR2
                             ,ret_REFCUR    OUT SYS_REFCURSOR);
    END PKG_BIRDS;
    /
    
    CREATE OR REPLACE PACKAGE BODY {SCHEMANAME}.{PACKAGENAME}
    AS
    PROCEDURE rcGET_BIRD_DATA(p_DATE_BAND   IN  DATE
                             ,p_AOU_CODE    IN  NUMBER
                             ,p_BIRD_CLASS  IN  VARCHAR2
                             ,ret_REFCUR    OUT SYS_REFCURSOR)
    BEGIN
    	OPEN ret_REFCUR FOR
    		SELECT
    			AOU_CODE,
    			BIRD_CLASS,
    			BIRD_NAME,
    			DATE_BAND,
    			WING_LENGTH,
    			TAIL_LENGTH,
    			EYE_COLOR,
    			PLUMAGE_CODE
    		FROM
    			{SCHEMANAME}BANDED_BIRDS
    		WHERE
    			DATE_BAND  > p_DATE_BAND
    			AND
    			AOU_CODE   = p_AOU_CODE
    			AND
    			BIRD_CLASS = p_BIRD_CLASS
    END;
    
    END {SCHEMANAME}.{PACKAGENAME};
    /
    
    

    Monday, April 4, 2016 2:12 AM