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]

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@belgariad.com> wrote in message
news:MPG.197f49e5594832439897f0@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]

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@belgariad.com> wrote in message
> news:MPG.197f49e5594832439897f0@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/e...ad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

Hi Areon Ku, thanks for writing this:
> 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
> !!

If I understand you correctly, why not try something like this (I
probably haven't even come close to the table and column names you'll
need):

CREATE OR REPLACE PROCEDURE TestCountSum (
p_organ_tx varchar2,
p_temp_name varchar2,
p_category_tx varchar2
) IS
--
cursor c_category is
select some_cat_col
from category
where category_tx = p_category_tx;
--
cursor c_second_table (
p_some_param varchar2
) is
select some_num_col, some_text_col
from sum_demo_surg_block
where some_other_column = p_some_param;
--
v_organ_tx number;
v_value_tx varchar2(32);
BEGIN
--
-- for each category record...
for r_category in c_category loop
--
-- read corresponding row in second_table
for r_second_table in c_second_table (
r_category.some_cat_col
) loop
--
-- calculate some stuff
v_organ_tx := r_second_table.some_num_col * 10;
v_value_tx := r_second_table.some_text_col
|| to_char(v_organ_tx);
--
-- insert calc'd values in table.
insert into sum_disc_tx (
organ_tx
, sum_id
, datestamp
, value_tx
)
values (
v_organ_tx
, seq_sum_id.nextval
, sysdate
, v_value_tx
);
end loop;
end loop;
END TestCountSum;

Just don't let Tom Kyte (see http://asktom.oracle.com) see this because
he'll have a stroke since I'm not using bind variables ... but it's just
an example to show the general layout.
--
[:%s/Karsten Farrell/Oracle DBA/g]