RE: Bind variables
- From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
- To: <lkemnitz@xxxxxxxx>
- Date: Wed, 7 Jun 2006 16:24:36 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of LeRoy Kemnitz
Sent: Wednesday, June 07, 2006 4:10 PM
To: lkemnitz@xxxxxxxx
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
Other related posts:
