Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2003
    Posts
    148

    Unanswered: Stored Procedure - Not returning correct results

    Hi all. I have a stored procedure with a relatively simple select statement in it. The problem is when I run the stored procedure with parameters that i know should return a count, i get 0. I have the feeling it has something to do with 's or datatypes in general however I have tried everythnig I can think of...I believe the code will speak for itself:

    First, the query returning a count:
    Code:
    SQL> SELECT COUNT (*) FROM TBL_OPENWINDOWREQUESTS 
      2    WHERE CREGION = 'NY'
      3    AND CREQID = 'garrotr'
      4    AND CCHANNEL = 2
      5    AND DSUBMIT BETWEEN To_Date('01-JUN-2002','DD-mon-YYYY') AND To_Date('1-JUL-2004','DD-mon-YYY
    Y');
    
      COUNT(*)
    ----------
            30
    My stored proc:
    Code:
    CREATE OR REPLACE PACKAGE CountSubmittedREP
    AS
    	TYPE ref_cur         IS      	REF CURSOR;
    	PROCEDURE CountSubmittedREP
    	(
    		
    		cREQ				IN		CHAR,
    		cREG				IN		CHAR,
    		cCHAN				IN		CHAR,
    		dtT				IN		DATE,
    		dtFROM				IN		DATE,
    		RC1             		IN  OUT 	REF_CUR
    	);
    END;
    /
    CREATE OR REPLACE PACKAGE BODY CountSubmittedREP
    AS
    	PROCEDURE CountSubmittedREP
    	(	
    
    		cREQ				IN		CHAR,
    		cREG				IN		CHAR,
    		cCHAN				IN		CHAR,
    		dtT				IN		DATE,
    		dtFROM				IN		DATE,
    		RC1             		IN  OUT 	REF_CUR
    	)
    	AS
    	BEGIN 
    		
    		OPEN RC1 FOR 
    SELECT COUNT (*) FROM TBL_OPENWINDOWREQUESTS 
    		WHERE CREGION = '||CREQ||'
    		AND CREQID = '||CREQ||'
    		AND CCHANNEL = '||CCHAN||'
    AND dSUBMIT BETWEEN (dtFROM) AND (dtT);
    
    END;
    
    
    END;
    /
    Running the proc:
    Code:
    SQL> var r refcursor
    SQL> execute CountsubmittedREP.CountsubmittedREP('garrotr','NY','2',To_Date('01-JUN-2002','DD-mon-YY
    YY'),To_Date('30-JUL-2004','DD-mon-YYYY'),:r);
    
    PL/SQL procedure successfully completed.
    
    SQL> print r
    
      COUNT(*)
    ----------
             0
    
    SQL>
    thank you for your help

  2. #2
    Join Date
    Mar 2004
    Posts
    23
    Your IN parameters that you define as CHAR should be VARCHAR2.

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    SELECT COUNT (*) FROM TBL_OPENWINDOWREQUESTS
    WHERE CREGION = '||CREQ||'
    AND CREQID = '||CREQ||'
    AND CCHANNEL = '||CCHAN||'
    ....

    Do you need to have quotes around these

    SELECT COUNT (*) FROM TBL_OPENWINDOWREQUESTS
    WHERE CREGION = '||''''||CREQ||''''||'
    AND CREQID = '||''''||CREQ||''''||'
    AND CCHANNEL = '||CCHAN||'


    Gregg

  4. #4
    Join Date
    Dec 2003
    Posts
    148
    I am stumped guys....

    I have tried changing my CHAR to VARCHAR...and I have tried the last suggestion, with the quotes...They all compile correctly, but none of them return results...

    How can i use DBMS.OUTPUT.LINE to print my query to the screen??? thank you

  5. #5
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Shouldn't that be
    Code:
    OPEN RC1 FOR 
    	SELECT COUNT (*) FROM TBL_OPENWINDOWREQUESTS 
    	WHERE CREGION = CREG
    	AND CREQID = CREQ
    	AND CCHANNEL = CCHAN
    	AND dSUBMIT BETWEEN (dtFROM) AND (dtT);
    btw a common and useful covention in PL/SQL is to prefix preocedure parameters with 'p_', and also to use all lowercase for table, column and variable names etc and uppercase for keywords.

  6. #6
    Join Date
    Dec 2003
    Posts
    148
    hmm..yes i tried that as well...

    Code:
    SQL> 
    SQL> CREATE OR REPLACE PACKAGE CountSubmittedREP
      2  AS
      3   TYPE ref_cur         IS       REF CURSOR;
      4   PROCEDURE CountSubmittedREP
      5   (
      6    
      7    cREQ    IN  VARCHAR2,
      8    cREG    IN  VARCHAR2,
      9    cCHAN    IN  VARCHAR2,
     10    dtT    IN  DATE,
     11    dtFROM    IN  DATE,
     12    RC1               IN  OUT  REF_CUR
     13   );
     14  END;
     15  /
    
    Package created.
    
    SQL> CREATE OR REPLACE PACKAGE BODY CountSubmittedREP
      2  AS
      3   PROCEDURE CountSubmittedREP
      4   ( 
      5  
      6    cREQ    IN  VARCHAR2,
      7    cREG    IN  VARCHAR2,
      8    cCHAN    IN  VARCHAR2,
      9    dtT    IN  DATE,
     10    dtFROM    IN  DATE,
     11    RC1               IN  OUT  REF_CUR
     12   )
     13   AS
     14   BEGIN 
     15    
     16    OPEN RC1 FOR 
     17   SELECT COUNT (*) FROM TBL_OPENWINDOWREQUESTS 
     18   WHERE CREGION = CREG
     19   AND CREQID = CREQ
     20   AND CCHANNEL = CCHAN
     21   AND dSUBMIT BETWEEN (dtFROM) AND (dtT);
     22  
     23  END;
     24  
     25  
     26  END;
     27  /
    
    Package body created.
    
    SQL> execute CountsubmittedREP.CountsubmittedREP('garrotr','NY','2',To_Date('01-JUN-2002','DD-mon-YY
    YY'),To_Date('30-JUL-2004','DD-mon-YYYY'),:r);
    
    PL/SQL procedure successfully completed.
    
    SQL> print r
    
      COUNT(*)
    ----------
             0

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I'm a firm believer in the KISS principal.
    Why are you returning a REF CURSOR?
    I would have implented this as a simple function returning a NUMBER.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Mar 2004
    Posts
    23
    If you just want to return a record count, why not define a function that returns a NUMBER or INTEGER instead of a Proc that uses a refcursor type as an IN/OUT param? I also see you OPEN the cursor but I don't see a FETCH statement anywhere.

  9. #9
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Try this:
    Code:
    exec CountsubmittedREP.CountsubmittedREP(creq => 'garrotr',creg => 'NY',cchan => '2',dtfrom => to_date('01-JUN-2002','DD-mon-YYYY'),dtt => to_Date('30-JUL-2004','DD-mon-YYYY'));
    Your problem is because you have mixed the dates.

  10. #10
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Try using dbms_output.put_line to see what the actual sql is ...
    try putting something like this in your code ...

    declare
    v_sql varchar2(2000);

    v_sql := 'SELECT COUNT (*) FROM TBL_OPENWINDOWREQUESTS
    WHERE CREGION = '||CREQ||'
    AND CREQID = '||CREQ||'
    AND CCHANNEL = '||CCHAN||'
    AND dSUBMIT BETWEEN (dtFROM) AND (dtT);';

    dbms_output.put_line (v_sql);
    ...
    ...

    before running in SQLPlus

    sql> set serveroutput on size 1000000;
    sql> execute your code ...

    HTH
    Gregg

  11. #11
    Join Date
    Sep 2004
    Posts
    16
    Hi,
    The order in which you are passing the parameters is different from the specification. In the specs, you have dtT first, whereas in the procedure call you are putting the values of dtFrom first.
    Put 30-Jul-2004 first in the call, and then 01-JUN-2002.

    --Vinita

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •