looping nested cursors?

[daddypost]

Okay, I'm in a major crunch here. Here's what I have. There is a gui that allows users to select filter criteria. Each product group can have multiple groups with individual filters in it (ie. Gold month could have a gold rings shop,a gold necklace shop, etc each with their own filters). These filters are place in a CLOB inside a seperate oracle table as an entire select statement. Basically what I have to do is first pull in all of the product groups, then loop through the groups within that and run the CLOB sql statement to insert the selected items into a table, making sure not to duplicate items. If anyone has time and is willing to help, I can email you specific details, but right now I'm completely stuck.

 

[Dagon]

What exactly is your problem ? Where precisely are you stuck ?

 

[daddypost]

Here are the two procedure I have currently.
create or replace procedure test_dynamic_procedure
is

begin

DECLARE
CURSOR ao_cur IS
select pg.id,to_char(iss.where_statement) where_clause from report.product_group pg,
report.intelligent_shops iss
where pg.id=iss.group_id and
((start_date<trunc(sysdate)+5 and end_date>sysdate) or
(start_date is null and end_date>sysdate));
BEGIN
FOR ao_rec IN ao_cur
LOOP

execute immediate 'create_where_clause2(id,where_clause)';

END LOOP;
COMMIT;
END;
end;


and

create or replace procedure Create_Where_clause2 (v_groupid varchar2,
v_clause varchar2)


is

v_sql_statement varchar2(1000);

begin

v_sql_statement:='insert into test_dynamic_pgm
select(select dbms_random.value rand_val from dual),vc.PRODUCT_ID,vc.MASTER_ID,500,3,'||V_GROUPID||'
from ('||ltrim(rtrim(v_clause,'''),''')||') vc where not exists (select * from test_dynamic_pgm tdp where vc.product_id=tdp.product_id and
vc.master_id=tdp.master_id and tdp.group_id='||v_groupid||')';


execute immediate v_sql_statement;




end;



I keep getting an 'invalid sql statement error' when I execute the procedure?

 

[SantaMufasa]

In addition to resolving Dagon's excellent questions, I would like to know what rough, or draft, solution(s) you have tried so far.

Also, if you can post sample "CREATE TABLE..." and "INSERT INTO..." statements that give us a basis from which to more quickly test our suggestions, it will be very helpful to those of us wishing to help.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[daddypost]

I don't know if you saw the post I just sent out. If you did, I'm not sure what else I can tell you. The to_char(iss.where_statement) is to convert from a CLOB.

 

[daddypost]

Also, if I pull out the insert statement and run it outside the procedure replacing the parameters with values, it runs fine. So I'm thinking the v_clause is the issue somehow.

 

[SantaMufasa]

Our posts did cross in the ether. (Note our timestamps were within seconds of one another.)

But it would be, nonetheless, very helpful to have a couple/few lines of representative INSERT statements of data against which we could run your code and assist you.

I'm not sure what else I can tell you.

Actually, it would be helpful to know if your challenge is:[ul][li]Syntax - Oracle doesn't like the way you specify your code. In such case, please post the syntax-error message.[/li][li]Run-time - Your code compiles, but during the running of the code, Oracle throws an error because there is a problem it encountered with your data. In such case, please post the run-time error.[/li][li]Logic - You receive results from your code, but the results do not match your expectations. In such case, please post an image of your results and what you expected to see differently.[/li][/ul]Looking forward to your reply.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ChrisHunt]

Well, you're pulling the filter out of a database column called [tt][red]where[/red]_statement[/tt], and using it to populate the [red]FROM[/red] clause of your dynamic SQL statement, so that's where my suspicions would lie.

I'd start by adding this line to create_where_clause2, just before the [tt]execute immediate[/tt]:

dbms_output.put_line(v_sql_statement)

Once you know what SQL statement it's trying to run, it should be clearer why it isn't working.

You should also check to see whether every row fails, or whether it's just one row that's craching out. You should really code it to cope gracefully with rogue values.

Stop Press

I've just seen what's going wrong (I think), it's this line in the first procedure:

execute immediate 'create_where_clause2(id,where_clause)';

Oracle's not going to know what to make of that [tt]id[/tt] and [tt]where_clause[/tt] inside an [tt]execute immediate[/tt] string. It ceratinly isn't going to magically put the values of your similarly-named variables in there. I don't know why you're doing an [tt]execute immediate[/tt] at all in the first proc, just do this:

FOR ao_rec IN ao_cur LOOP
   create_where_clause2(ao_rec.id,ao_rec.where_clause);
END LOOP;

If you have further problems once you've fixed this issue, my initial comments may be of use in finding a solution.



-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[sem]

Multiple mistakes, should be

DECLARE
  CURSOR ao_cur IS
    select pg.id, to_char(iss.where_statement) where_clause
      from report.product_group pg, report.intelligent_shops iss
     where pg.id = iss.group_id
       and ((start_date < trunc(sysdate) + 5 and end_date > sysdate) or
           (start_date is null and end_date > sysdate));
BEGIN
  FOR ao_rec IN ao_cur LOOP
  
    execute immediate 'begin create_where_clause2(:1, :2); end;'
      using ao_rec.id, ao_rec.where_clause;
  
  END LOOP;
  COMMIT;
END;

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Dima,

? ???, ??? ?? ????????? ??????. ????????????? ?????.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]