Home » SQL & PL/SQL » SQL & PL/SQL » execute immediate with cursor problem
execute immediate with cursor problem [message #309397] Thu, 27 March 2008 05:51 Go to next message
miaomiao
Messages: 4
Registered: March 2008
Junior Member
hi,

I have an insert procedure, in which I loop through the cursor to generate insert statements and then 'execute immediate' them.

Another method calls the insert procedure twice. Everything works fine until I changed my cursor to include a where clause.

The problem is instead of calling the insert procedure twice, its only called once. The funny thing is if I use 'dbms_output.put_line' instead of 'execute immediate' the insert method still be called twice. So its a combination of the new cursor which includes the where clause and the execute immediate.

Is there anything obvious that I missed? Can someone point me to the right direction please? Thanks in advance.

The sudo codes:

cursor view_old_cur is
select
m.ID,m.VIEW_NAME, m.PLOT_COLUMN,m.xaxis_offset
from
REP_GRAPH_METADATA m
order by m.id
;

cursor view_new_cur is
select
m.ID,m.VIEW_NAME, m.PLOT_COLUMN,m.xaxis_offset
from
REP_GRAPH_METADATA m
where not exists
(select null from REP_GRAPH_REFRANGES r where r.GRAPH_METADATA_ID = m.id
)
order by m.id
;


/* ====================================

Insert method

==================================== */
procedure make_refranges_inserts(gender in varchar2) is

plsql_block VARCHAR2(1000);
begin
for view_rec in view_new_cur
loop
plsql_block := 'insert into REP_GRAPH_REFRANGES ' plur plur plur

execute immediate plsql_block;
end loop;

end make_refranges_inserts;

* ====================================
Call insert methods twice

==================================== */
procedure make_all_refranges_inserts is

begin

make_refranges_inserts('1');
make_refranges_inserts('2');

end make_all_refranges_inserts;
Re: execute immediate with cursor problem [message #309402 is a reply to message #309397] Thu, 27 March 2008 06:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

2/ How can we debug a code we can't see?

3/ Copy and paste your session, don't just describe what you do

4/ Always post your Oracle version (4 decimals).

Regards
Michel
Re: execute immediate with cursor problem [message #309409 is a reply to message #309402] Thu, 27 March 2008 06:15 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
So, you're selecting from REP_GRAPH_REFRANGES (the new cursor for the loop) and whithin that loop you're inserting into the same table.

What happens if you
1. bulk collect the new cursor into an array
2. loop through that array (you could use a forall loop)
3. perform the insert in that loop

And the obvious question: why dynamic SQL?

MHE
Re: execute immediate with cursor problem [message #309418 is a reply to message #309409] Thu, 27 March 2008 06:44 Go to previous messageGo to next message
miaomiao
Messages: 4
Registered: March 2008
Junior Member
Hi Maaher,

Thanks for being helpful. The cursor has been read correctly and the loop goes through all elements in the new cursor in the insert method.

The problem is the insert method only been called once even I actually called it twice.
make_refranges_inserts('1');
make_refranges_inserts('2');

I am really curious why this is happening because if i just use normal 'dbms_output.put_line' I got all insert statements.Only when I use execute immediate, make_refranges_inserts('2') is not called at all.



Its very bad idea to use dynamic SQL. My situation is the table names and the data columns I need fetch to do the calculation are store in the REP_GRAPH_METADATA. The data in the data columns can be added or updated any time, and the entry in REP_GRAPH_METADATA can be changed anytime too. It will be great appreciated if you can suggest otherwise a better way to do the calculation based on the dynamic meta table and data tables.

Thanks again Maaher.

Re: execute immediate with cursor problem [message #309422 is a reply to message #309418] Thu, 27 March 2008 06:53 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
miaomiao wrote on Thu, 27 March 2008 12:44
Thanks for being helpful. The cursor has been read correctly and the loop goes through all elements in the new cursor in the insert method.
..if there is no insert.

I already hinted at the fact that you are inserting in the table you're selecting from. I suspect that this has something to do with your problem. However, I can't be sure because I don't know what you're inserting.

MHE
Re: execute immediate with cursor problem [message #309425 is a reply to message #309422] Thu, 27 March 2008 06:59 Go to previous messageGo to next message
miaomiao
Messages: 4
Registered: March 2008
Junior Member
mmmmm

cursor view_new_cur is
select
m.ID,m.VIEW_NAME, m.PLOT_COLUMN,m.xaxis_offset
from
REP_GRAPH_METADATA m
where not exists
(select null from REP_GRAPH_REFRANGES r where r.GRAPH_METADATA_ID = m.id
)
order by m.id
;


I am selecting from REP_GRAPH_METADATA and inserting to REP_GRAPH_REFRANGES.

The only condition I added is the where clause. If its the problem with the select, how come the dbms_output.put_line works perfect fine?

[Updated on: Thu, 27 March 2008 07:24]

Report message to a moderator

Re: execute immediate with cursor problem [message #309427 is a reply to message #309425] Thu, 27 March 2008 07:02 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
miaomiao wrote on Thu, 27 March 2008 12:59
mmmmm

cursor view_new_cur is
select
m.ID,m.VIEW_NAME, m.PLOT_COLUMN,m.xaxis_offset
from
REP_GRAPH_METADATA m
where not exists
(select null from REP_GRAPH_REFRANGES r where r.GRAPH_METADATA_ID = m.id
)
order by m.id
;


I am selecting from REP_GRAPH_METADATA and inserting to REP_GRAPH_REFRANGES.

The only condition I added is the where clause.

MHE
Re: execute immediate with cursor problem [message #309434 is a reply to message #309427] Thu, 27 March 2008 07:28 Go to previous message
miaomiao
Messages: 4
Registered: March 2008
Junior Member
you absolutely right!MHE. Thanks!

sophie
Previous Topic: default tablespace of dba user
Next Topic: How do you remove CrLf from strings in pl/sql?
Goto Forum:
  


Current Time: Sun Dec 11 06:09:10 CST 2016

Total time taken to generate the page: 0.09024 seconds