Re: Bind variables

Thanks!!

Got it to work. As you said, a couple of issues.

Igor Neyman wrote:
> 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
>
>
>
> -

-

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