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

#1

Hi Areon Ku, thanks for writing this:

Quote:

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]

#2

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 (DOT) com> wrote

Quote:

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]

#3

Areon Ku wrote:

Quote:

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 (DOT) com> wrote in message
news:MPG.197f49e5594832439897f0 (AT) news (DOT) 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)

#4

Hi Areon Ku, thanks for writing this:

Quote:

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

Quote:

| 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]

#5

Originally posted by Karsten Farrell

Quote:

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/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]
And if possible, avoid doing it all record-by-record in cursor loops,

like this:

PHP Code:

   CREATE OR REPLACE PROCEDURE TestCountSum (
p_organ_tx varchar2,
p_temp_name varchar2,
p_category_tx varchar2
) IS

BEGIN

insert into sum_disc_tx
( organ_tx
, sum_id
, datestamp
, value_tx
)
select t2.some_num_col * 10
, seq_sum_id.nextval
, sysdate
, t2.some_text_col || to_char(t2.some_num_col * 10)
from   category t1
, sum_demo_surg_block t2
where  t2.some_other_column = t1.some_cat_col
and    t1.category_tx = p_category_tx;

END;
/

--
Posted via http://dbforums.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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

comp.databases.oracle.misc comp.databases.oracle.misc

Re: What's wrong in my code, Any idea - 07-16-2003 , 02:39 PM

Re: What's wrong in my code, Any idea - 07-16-2003 , 03:08 PM

Re: What's wrong in my code, Any idea - 07-16-2003 , 04:16 PM

Re: What's wrong in my code, Any idea - 07-16-2003 , 04:47 PM

Re: What's wrong in my code, Any idea - 07-17-2003 , 06:33 AM