RE: Bind variables

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Wed, 7 Jun 2006 16:24:36 -0400
Message-ID: <F4C27E77F7A33E4CA98C19A9DC6722A2010194BB@EXCHANGE.corp.perceptron.com>

There couple problems with your code.  

First, you want probably pass the values for "year" and "major" as a parameters to your procedure:  

create or replace procedure ddh_test2(pYear IN uwsias.i_maj_credits_cpc.year%TYPE, pMajorCode IN uwsias.i_maj_credits_cpc.major%TYPE)
AS ...  

then it becomes:  

WHERE

                    uwsias.i_maj_credits_cpc.year        =    pYear
AND
                    uwsias.i_maj_credits_cpc.major    =    pMajorCode

Second, when you do "select" inside stored procedure, you have to "select INTO" something, so that you can return those values from (using "out" parameters or REF Cursor).  

Read on PL/SQL in Oracle docs.  

Igor

---

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of LeRoy Kemnitz Sent: Wednesday, June 07, 2006 4:10 PM
To: lkemnitz_at_uwsa.edu
Cc: oracle-l
Subject: Re: Bind variables

Sorry code. I was trying different things, here is the orig, code that doesn't work.
###########
create or replace procedure ddh_test2 as begin
SELECT

                    uwsias.i_maj_credits_cpc.year,
                    uwsias.i_maj_credits_cpc.term,
                    uwsias.i_maj_credits_cpc.unit,
                    uwsias.i_maj_credits_cpc.unit_name,
                    uwsias.i_maj_credits_cpc.major,
                    uwsias.i_maj_credits_cpc.major_name,
                    DECODE (
                        SUM (uwsias.i_maj_credits_cpc.off_credits_lvl3),
                        0,    0,
                        SUM (uwsias.i_maj_credits_cpc.off_credits_lvl3

* uwsias.i_maj_credits_cpc.cost_per_credit_lvl3) / SUM
(uwsias.i_maj_credits_cpc.off_credits_lvl3)) major_cpc

                FROM
                    uwsias.i_maj_credits_cpc
                WHERE
                    uwsias.i_maj_credits_cpc.year        =    :year
AND
                    uwsias.i_maj_credits_cpc.term        =    '1'
AND
                    uwsias.i_maj_credits_cpc.major    =    :major_code 
                GROUP BY
                    uwsias.i_maj_credits_cpc.year,
                    uwsias.i_maj_credits_cpc.term,
                    uwsias.i_maj_credits_cpc.unit,
                    uwsias.i_maj_credits_cpc.unit_name,
                    uwsias.i_maj_credits_cpc.major,
                    uwsias.i_maj_credits_cpc.major_name;

end;

#################################

LeRoy Kemnitz wrote:

        Below is a query I am trying to compile in 10.2.0.1. I keep getting PLS-00049 Bad bind Variable. I don't see anything wrong with this one. Can anyone help me?                  

	############################################## 
	create or replace procedure ddh_test2 as 
	begin 
	SELECT 
	                   uwsias.i_maj_credits_cpc.year, 
	                   uwsias.i_maj_credits_cpc.term, 
	                   uwsias.i_maj_credits_cpc.unit, 
	                   uwsias.i_maj_credits_cpc.unit_name, 
	                   uwsias.i_maj_credits_cpc.major, 
	                   uwsias.i_maj_credits_cpc.major_name, 
	                   DECODE ( 
	                       SUM


(uwsias.i_maj_credits_cpc.off_credits_lvl3), 

	                       0,    0, 
	                       SUM


(uwsias.i_maj_credits_cpc.off_credits_lvl3    *

uwsias.i_maj_credits_cpc.cost_per_credit_lvl3) / SUM
(uwsias.i_maj_credits_cpc.off_credits_lvl3)) major_cpc

	               FROM 
	                   uwsias.i_maj_credits_cpc 
	               WHERE 
	                   uwsias.i_maj_credits_cpc.year        =
:year using year     AND 
	                   uwsias.i_maj_credits_cpc.term        =    '1'
AND 
	                   uwsias.i_maj_credits_cpc.major    =
:major_code using major_code 
	               GROUP BY 
	                   uwsias.i_maj_credits_cpc.year, 
	                   uwsias.i_maj_credits_cpc.term, 
	                   uwsias.i_maj_credits_cpc.unit, 
	                   uwsias.i_maj_credits_cpc.unit_name, 
	                   uwsias.i_maj_credits_cpc.major, 
	                   uwsias.i_maj_credits_cpc.major_name; 
	end; 
	
	###################################################33    
	
	
	

-- 
LeRoy Kemnitz
UW System Administration
Database Administrator
780 Regent Street, #246
Madison, WI 53714
Phone: (608) 265 -5775
Fax: (608) 265 - 2090

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 07 2006 - 15:24:36 CDT