RE: Bind variables

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@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of LeRoy Kemnitz
Sent: Wednesday, June 07, 2006 4:10 PM
To: lkemnitz@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

RE: Bind variables

Igor Neyman

2006-06-07