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

    Unanswered: SP syntax error(??) Frustrating

    Hi all. I have a stored proc that compiles no problem However does not return the correct values. If I run the query that is in the stored proc, I do get the correct result. This is why Im assuming it is a syntax error on my part.
    This is the stored proc, as well as an attempt to run it:
    Code:
    SQL> 
    SQL> CREATE OR REPLACE PACKAGE CountREP
      2  AS
      3   TYPE ref_cur         IS       REF CURSOR;
      4   PROCEDURE CountREP
      5   (
      6    
      7    FRM    IN  date,
      8    TODATE    IN  date,
      9    REG     IN  VARCHAR2,
     10    REP    IN  VARCHAR2,
     11    RC1               IN  OUT  REF_CUR
     12   );
     13  END;
     14  /
    
    Package created.
    
    SQL> CREATE OR REPLACE PACKAGE BODY CountREP
      2  AS
      3   PROCEDURE CountREP
      4   ( 
      5  
      6    FRM    IN  date,
      7    TODATE    IN  date,
      8    REG     IN  VARCHAR2,
      9    REP    IN  VARCHAR2,
     10    RC1                     IN  OUT  REF_CUR
     11   )
     12   AS
     13   BEGIN 
     14    
     15   OPEN RC1 FOR 
     16   select count (*) from tbl_Requests 
     17   WHERE cRegion LIKE reg  
     18   AND cReqID = REP   
     19   AND dSubmit BETWEEN  frm  AND todate;
     20  END;
     21  
     22  
     23  END;
     24  /
    
    Package body created.
    
    SQL> 
    SQL> 
    SQL> execute CountREP.CountREP(To_Date('01-apr-2002','DD-mon-YYYY'),To_Date('30-ap
    r-2002','DD-mon-YYYY'),'UP','morris',:r);
    
    PL/SQL procedure successfully completed.
    
    SQL> PRINT R
    
      COUNT(*)
    ----------
             0
    
    SQL>
    This is running the just the SQL query:
    Code:
    SQL> SELECT COUNT(*) 
      2  FROM tbl_Requests  
      3  WHERE (cReqID = 'morris') 
      4  AND (cRegion Like 'UP') 
      5  AND (dSubmit BETWEEN ('01-apr-2002') AND ('30-apr-2002'));
    
      COUNT(*)
    ----------
            10
    
    SQL>
    thank you for your help!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >RC1 IN OUT REF_CUR
    What makes you conclude that a REF_CUR is the same as number of rows returned?
    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.

  3. #3
    Join Date
    Dec 2003
    Posts
    148

    well..

    I guess because this SP works fine:

    Code:
    CREATE OR REPLACE PACKAGE CountApproved
    AS
    	TYPE ref_cur         IS      	REF CURSOR;
    	PROCEDURE CountApproved
    	(
    		
    		FRM				IN		date,
    		TODATE				IN		date,
    		REG					IN		VARCHAR2,
    		RC1             		IN  OUT 	REF_CUR
    	);
    END;
    /
    CREATE OR REPLACE PACKAGE BODY CountApproved
    AS
    	PROCEDURE CountApproved
    	(	
    
    		FRM				IN		date,
    		TODATE				IN		date,
    		REG					IN		VARCHAR2,
    		RC1                     IN  OUT 	REF_CUR
    	)
    	AS
    	BEGIN 
    		
    		OPEN RC1 FOR 
    SELECT COUNT(*) 
    FROM tbl_Requests OWR  
    WHERE (cRegion = reg) AND (iApprStatus='-1') AND 
    ((iAppr2Status IS NULL) OR (iAppr2Status='-1'))  AND 
    ((iBatch Is Null) OR (iBatch > 0)) AND 
    (dApprRej BETWEEN (frm) AND (todate)); 
    
    END;
    
    
    END;
    /
    I thought this would be a simple modification to it...but no dice. Any ideas?

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    i think it is your LIKE condition.

    WHERE cRegion LIKE reg

    in this situation you are using LIKE as an equals without quotes
    which oracle considers different.

    you can do a few things:

    1. change WHERE cRegion LIKE reg
    to WHERE cRegion = reg

    2. put quotes around your like condition:
    WHERE cRegion LIKE ''reg''

    3. or something like this:
    PHP Code:
    OPEN RC1 FOR
    'select count (*) from tbl_Requests WHERE cRegion LIKE ''reg''  AND cReqID = REP AND dSubmit BETWEEN  frm  AND todate'
    Last edited by The_Duck; 08-03-04 at 15:06.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Dec 2003
    Posts
    148

    Unfortunately...

    That isn't it :-/. I originally thought it was my lack of quotes as well. However, adding them so the line looks like:
    Code:
    	OPEN RC1 FOR select count (*) from tbl_Requests 
    	WHERE cRegion LIKE ''REG''  
    	AND cReqID = REP 
    	AND dSubmit BETWEEN  frm  AND todate;
    Yields the same result. I tried changing the LIKE to = however, no dice. I also trid adding a single quote to the beginning and end of the statement:
    Code:
    OPEN RC1 FOR 'select count (*) from tbl_OpenWindowRequests 
    	WHERE cRegion = ''reg''  
    	AND cReqID = REP 
    	AND dSubmit BETWEEN  frm  AND todate';
    I tried that with and without the quotes around reg, no dice! This thing is killing me! Any other suggestions? I do appreciate your help

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    surprise!
    you don't need a ref cursor. just use a normal cursor:

    PHP Code:
    topicadm@Topic_Devcreate or replace package pkg_test as
      
    2  --  TYPE ref_cur         IS       REF CURSOR;
      
    3    vValue varchar2(10);
      
    4    
      5    PROCEDURE sp_test 

      
    6    vUp IN varchar2,
      
    7  --  RC1 IN OUT ref_cur,
      
    8    vValue OUT varchar2);
      
    9  end;
     
    10  /

    Package created.

    Elapsed00:00:00.00
    topicadm
    @Topic_Devcreate or replace package body pkg_test as
      
    2  
      3   procedure sp_test 
    (
      
    4   vUp IN varchar2,
      
    5  -- RC1 IN OUT ref_cur,
      
    6   vValue OUT varchar2)
      
    7   as
      
    8   
      9    cursor c1 is 
     10    select count
    (*) 
     
    11      from test where a vUp;
     
    12    
     13   begin
     14  
     15   open c1

     
    16    fetch c1 into vValue;
     
    17   close c1;
     
    18     
     19   end
    ;
     
    20   end;
     
    21  /

    Package body created.

    Elapsed00:00:00.00
    topicadm
    @Topic_Dev
    topicadm@Topic_Dev> DECLARE 
      
    2    VUP VARCHAR2(200);
      
    3    vValue varchar2(10);
      
    4    
      5  BEGIN 
      6    
    --VUP := 'UP';
      
    7    -- RC1 := NULL;  Modify the code to initialize this parameter
      8  
      9    TOPICADM
    .PKG_TEST.SP_TEST 'UP', :V1 );
     
    10    COMMIT
     
    11  END
     
    12  /

    PL/SQL procedure successfully completed.

    Elapsed00:00:00.00
    topicadm
    @Topic_Dev
    topicadm@Topic_Dev> print v1;

    V1
    --------------------------------

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    No need for even an explicit cursor.

    select count(*) into vValue from test where a = vUp;
    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
    Apr 2004
    Posts
    246
    the problem isn't the number of quotes (since it compiles and runs without errors) or the variable type (it is returning a ref cursor with a "name" of count(*) and value of 0). The problem is that the answer is 0, not 10.

    Is the procedure owned by the same schema where the query returns 10? If not, this is probably the problem.

    Do "show sqlcase". It should be "mixed". If not, then someone changed it somewhere, and it's the problem.

    Try changing the package to remove all lines of the where clause, just to make sure that it returns a value greater than zero. If it does, then add the lines back into the where clause one at a time, testing between, and see if you can narrow it down.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

Posting Permissions

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