Re: What's wrong in my code, Any idea

Areon Ku wrote:

> My code may not be complete... Its just a sample --Actually What i,m trying
> to achieve here is to pick one of the value from category table and use that
> value to calculate distinct values from another table and do some
> calcualions on that and insert these calculated values into another summary
> table and repeat the same thing for next value in the category table..
> The problem i,m facing is i,m not able to use that variable as input to
> cursor and then process other statements based on that variable.
> Hope i,m able to explain the situation here.
>
> Thanks
> !!
> "Karsten Farrell" <kfarrell_at_belgariad.com> wrote in message
> news:MPG.197f49e5594832439897f0_at_news.la.sbcglobal.net...
> > Hi Areon Ku, thanks for writing this:
> > > Can any one give suggesstion here :
> > >
> > >
> > >
> > > CREATE OR REPLACE procedure TestCount_sum(
> > > Organ_tx varchar2,
> > > temp_name varchar2,
> > > category_TX varchar2)IS
> > > v_category_tx varchar2(32);
> > > v_ORGAN_TX VARCHAR2(32);
> > > v_value_tx varchar2(32);
> > > v_datestamp date;
> > > sqlstatement varchar2(300);
> > > I VARCHAR2(1000);
> > > begin
> > > V_organ_tx :=XXXXX;
> > > for n in (select COLUMN_NAME from category where column_name in
> (x,y,z,r,t)
> > > loop
> > > declare cursor i is
> > > select distinct n.column_name from v_sum_demo_surg_block where
> > > v_category_tx=v_category_tx;
> > > begin
> > > open i;
> > > loop
> > > fetch i into v_value_tx;
> > > exit when i%notfound;
> > > --Want to do some additional calculation( count, percentages etc etc) on
> > > these distinct values in insert into the table--
> > > insert into sum_DISC_TX (organ_tx, sum_id, datestamp, value_tx)
> > > values (v_Organ_tx,seq_sum_id.nextval, to_char(sysdate,
> > > 'MM/DD/YYYY'),v_value_tx);
> > > end loop;
> > > end loop;
> > > end loop;
> > > end;
> > > /
> > >
> >
> > What error are you getting? Is this the actual code? Well, I see 3
> > 'begin' statements and one 'end' statement. I see 3 'end loop'
> > statements and only 2 'loop' statements. You 'open' your cursor, but
> > never 'close' it. Why do you have an inner begin-(no)end pair?
> > --
> > [:%s/Karsten Farrell/Oracle DBA/g]

We can not look over your shoulder. If you want help you must give us either the exact code that is causing the problem or a reasonable facsimile. Bad code demonstrates little other than sloppinesss and makes it impossible to offer help of any kind.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)