PL/SQL compiling problem...

[Taoism]

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.

	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:

LINE/COL ERROR
-------- -----------------------------------------------------------------
205/41   PLS-00103: Encountered the symbol "(" when expecting one of the
		 following:
		 , from

Selecting the offending line from USER_SOURCE:

column text format a50
select text 
  from user_source
 where name = upper('scc_strct_chnge')
   and type = upper('package body')
   and line = 205;

Result:

TEXT
--------------------------------------------------
					  ROW_NUMBER() OVER (ORDER BY
stab_yy ASC) AS rn

Anyone have any ideas why?

Cheers,
Keith.

[m1l]

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 :-)

[Taoism]

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:

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.

[m1l]

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.

[Taoism]

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.