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

From: Karsten Farrell <kfarrell_at_belgariad.com>
Date: Wed, 16 Jul 2003 21:47:28 GMT
Message-ID: <MPG.197f67cac5a5e7e79897f1@news.la.sbcglobal.net>

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]

Received on Wed Jul 16 2003 - 16:47:28 CDT