SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    Aug 2002
    Location
    Ottawa, Ontario, Canada
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PL/SQL compiling problem...

    Maybe one of you can open my eyes to what is going on.

    I have a procedure I wrote that won't compile unless it is dynamic SQL. The statement works. I know it does since I tested it with hard-coded values in SQL*Plus worksheet before I wrote the proc. But for some reason Oracle won't compile the statement.

    Code:
    	PROCEDURE sgovmrgn3(p_stab_yy			IN scc_gov_mrgn_nsr.stab_yy%TYPE,
    						p_prov_code		  IN scc_gov_mrgn_nsr.prov_code%TYPE, 
    						p_prov_mncpty_code   IN scc_gov_mrgn_nsr.prov_mncpty_code%TYPE, 
    						p_gov_mrgn_type_code IN scc_gov_mrgn_nsr.gov_mrgn_type_code%TYPE, 
    						p_gov_mrgn_code	  IN scc_gov_mrgn_nsr.gov_mrgn_code%TYPE,
    						p_non_cal_op		 IN NUMBER,
    						p_gov_mrgn_amt	   IN OUT scc_cur) IS 
    	v_mncpty scc_gov_mrgn_nsr.prov_mncpty_code%TYPE;
    	v_non_cal NUMBER;
    	BEGIN
    	  IF NOT use_mncpty_in_select(p_stab_yy,p_prov_code,p_prov_mncpty_code,p_gov_mrgn_type_code,p_gov_mrgn_code) THEN
    		v_mncpty:=0;
    	  ELSE
    		v_mncpty:=p_prov_mncpty_code;
    	  END IF;
    	  
    	  IF p_non_cal_op <> 0 THEN
    		v_non_cal:=0;
    	  ELSE
    		v_non_cal:=1;
    	  END IF;
    	  
    	  -- this block works
    	  -- remove double dashes below to comment out block
    	  --/*
    		 OPEN p_gov_mrgn_amt FOR
    		' SELECT inner.gov_inv_mrgn_amt '
    	  ||'   FROM (SELECT gov_inv_mrgn_amt,'
    	  ||'				ROW_NUMBER() OVER (ORDER BY stab_yy ASC) AS rn'
    	  ||'		   FROM scc_gov_mrgn_nsr'
    	  ||'		  WHERE stab_yy			= p_stab_yy'
    	  ||'			AND prov_code		  = p_prov_code'
    	  ||'			AND prov_mncpty_code   = p_prov_mncpty_code'
    	  ||'			AND gov_mrgn_type_code = p_gov_mrgn_type_code'
    	  ||'			AND gov_mrgn_code	  = p_gov_mrgn_code) inner'
    	  ||'  WHERE inner.rn '
    	  ||'BETWEEN 1+v_nc '
    	  ||'	AND 5+v_nc '
    		   USING v_non_cal,
    				 v_non_cal;
    	  --*/
    	  -- this block won't compile... WHY?
    	  -- this block will work in SQL*Plus worksheet
    	  -- add double dashes below to enable block
    	  /*
    	   SELECT inner.gov_inv_mrgn_amt 
    		 FROM (SELECT gov_inv_mrgn_amt,
    					  ROW_NUMBER() OVER (ORDER BY stab_yy ASC) AS rn
    				 FROM scc_gov_mrgn_nsr
    				WHERE stab_yy			= p_stab_yy
    				  AND prov_code		  = p_prov_code
    				  AND prov_mncpty_code   = p_prov_mncpty_code
    				  AND gov_mrgn_type_code = p_gov_mrgn_type_code
    				  AND gov_mrgn_code	  = p_gov_mrgn_code) inner
    		WHERE inner.rn 
    	  BETWEEN 1+v_non_cal 
    		  AND 5+v_non_cal;
    	  --*/
    	END sgovmrgn3;
    The error message reported by Oracle:
    Code:
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    205/41   PLS-00103: Encountered the symbol "(" when expecting one of the
    		 following:
    		 , from
    Selecting the offending line from USER_SOURCE:
    Code:
    column text format a50
    select text 
      from user_source
     where name = upper('scc_strct_chnge')
       and type = upper('package body')
       and line = 205;
    Result:
    Code:
    TEXT
    --------------------------------------------------
    					  ROW_NUMBER() OVER (ORDER BY
    stab_yy ASC) AS rn
    Anyone have any ideas why?

    Cheers,
    Keith.

  2. #2
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    location location
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'd define an explicit cursor, IN PL/SQL, you can't just put a select statement, there has to be some sort of cursur, either implicit or explicit.

    Hope this helps a little, if you need more info, say and post the code again with returns :-)

  3. #3
    SitePoint Addict
    Join Date
    Aug 2002
    Location
    Ottawa, Ontario, Canada
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It isn't the cursor at issue. Yes, I would use a cursor for the second block, but that won't prevent the compile from failing.

    New version of the proc:

    Code:
    PROCEDURE sgovmrgn3(p_stab_yy			IN scc_gov_mrgn_nsr.stab_yy%TYPE,
    						p_prov_code		  IN scc_gov_mrgn_nsr.prov_code%TYPE, 
    						p_prov_mncpty_code   IN scc_gov_mrgn_nsr.prov_mncpty_code%TYPE, 
    						p_gov_mrgn_type_code IN scc_gov_mrgn_nsr.gov_mrgn_type_code%TYPE, 
    						p_gov_mrgn_code	  IN scc_gov_mrgn_nsr.gov_mrgn_code%TYPE,
    						p_non_cal_op		 IN NUMBER,
    						p_gov_mrgn_amt	   IN OUT scc_cur) IS 
    	v_mncpty scc_gov_mrgn_nsr.prov_mncpty_code%TYPE;
    	v_non_cal   PLS_INTEGER;
    	BEGIN
    	  IF NOT use_mncpty_in_select(p_stab_yy,
    								  p_prov_code,
    								  p_prov_mncpty_code,
    								  p_gov_mrgn_type_code,
    								  p_gov_mrgn_code) THEN
    		v_mncpty:=0;
    	  ELSE
    		v_mncpty:=p_prov_mncpty_code;
    	  END IF;
    	  
    	  IF p_non_cal_op <> 0 THEN
    		v_non_cal:=0;
    	  ELSE
    		v_non_cal:=1;
    	  END IF;
    		 OPEN p_gov_mrgn_amt FOR
    	  -- This works
    	  --/*
    	   SELECT middle.gov_inv_mrgn_amt
    		 FROM (SELECT inner.*,
    					  ROWNUM AS rn
    				 FROM (SELECT gov_inv_mrgn_amt
    						 FROM scc_gov_mrgn_nsr
    						WHERE stab_yy			= p_stab_yy
    						  AND prov_code		  = p_prov_code
    						  AND prov_mncpty_code   = v_mncpty
    						  AND gov_mrgn_type_code = p_gov_mrgn_type_code
    						  AND gov_mrgn_code	  = p_gov_mrgn_code
    						ORDER BY stab_yy ASC
    					  ) inner
    			   ) middle
    		WHERE middle.rn 
    	  BETWEEN 1 + v_non_cal 
    		  AND 5 + v_non_cal;
    	  --*/
    	  
    	  -- this block won't compile... WHY?
    	  -- this block will work in SQL*Plus worksheet
    	  -- the compile error centres around the 
    	  -- (ORDER BY stab_yy ASC) subclause
    	  -- add double dashes below to enable block
    	  /*
    	   SELECT inner.gov_inv_mrgn_amt 
    		 FROM (SELECT gov_inv_mrgn_amt,
    					  ROW_NUMBER() OVER (ORDER BY stab_yy ASC) AS rn
    				 FROM scc_gov_mrgn_nsr
    				WHERE stab_yy			= p_stab_yy
    				  AND prov_code		  = p_prov_code
    				  AND prov_mncpty_code   = p_prov_mncpty_code
    				  AND gov_mrgn_type_code = p_gov_mrgn_type_code
    				  AND gov_mrgn_code	  = p_gov_mrgn_code) inner
    		WHERE inner.rn 
    	  BETWEEN 1+v_non_cal 
    		  AND 5+v_non_cal;
    	  --*/
    	END sgovmrgn3;
    I am not sure what it is about the (ORDER BY stab_yy ASC) that makes PL/SQL choke...

    The proc works as noted above, but it still bugs me that the simpler version won't compile.

    Cheers,
    Keith.

  4. #4
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    location location
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The code with returns would still be nice ;-) If you post it with returns, I'll be happy to have a good think, where now I'm just guessing !

    I'd take the line number Oracle gives you with a pinch of salt, the source will hold the comments, lines which are completely blank and other stuff, but when oracle gives the line number in errors, it will usually strip them.

  5. #5
    SitePoint Addict
    Join Date
    Aug 2002
    Location
    Ottawa, Ontario, Canada
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have always been under the impression that selecting based on line number from user_source returns the correct line..?

    In any case, I got an answer that I think fits with what is occurring:

    Answer at dbasupport.com

    Thanks for the replies though

    Cheers,
    Keith.


Bookmarks

Posting Permissions

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