Home » SQL & PL/SQL » SQL & PL/SQL » multiple values in a string causing stored procedure to hang even after commiting the data
multiple values in a string causing stored procedure to hang even after commiting the data [message #239829] Wed, 23 May 2007 06:58 Go to next message
chandanbhamra
Messages: 84
Registered: April 2005
Location: India
Member
Hi

I have one stored procedure that accepts multiple states('PB,KA,GJ') as one string and then execute one insert select statement in execute immediate command just like below statements.

v_sql1 := 'insert into select * from t1 where state in ';
v_state := '(''' || REPLACE (p_state, ',', ''',''') || ''')';
Execute Immediate v_sql1||v_state;

Insert t2 statement;

Update t2 statement

Update t2 statement;

Commit;

Open refcursor for 'select * from t1,t2 where using joins';

After this insertion into table t1, there is one more insert & 2 update statements and at the end there is COMMIT. Please note that there is no commit in between update & insert statements. When i execute the stored procedure for one state, it works fine, but when i ran the stored procedure for multiple states(say 'PB,KA,GJ') it inserts the data in table t1 and also executes the other statements and also commit it(as i can see this data thru another session) but problem is it never comes out and never shows the message "PL/SQL Procedure Completed Successfully" and window is completely blank. When i checked V$Session table's sql_hash_value it shows 0. Even v$transaction is not showing anything. I tried to kill the session, its shows the status in V$Session as killed but that session is still completely blank and nothing happens even after killing the session.

I don't why this strange behavior is coming up when i executed the stored procedure for mutiple states?

I hope i am clear what i want to say.

Thanks

Re: multiple values in a string causing stored procedure to hang even after commiting the data [message #239935 is a reply to message #239829] Wed, 23 May 2007 12:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
insert into select * from t1 where state in ';

No tablename. This code would not run withut an error.


[Edit: sorry, saw now you posted sort of pseudocode]

[Updated on: Wed, 23 May 2007 12:44]

Report message to a moderator

Re: multiple values in a string causing stored procedure to hang even after commiting the data [message #239936 is a reply to message #239935] Wed, 23 May 2007 12:45 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
So, actually what you are saying is that the first part is not of interest whatsoever, it just takes a while to open the ref-cursor.
How many rows are there in your tables? indexes?
Previous Topic: SQL query tunning
Next Topic: Replicate Table Structure
Goto Forum:
  


Current Time: Sat Dec 03 14:09:09 CST 2016

Total time taken to generate the page: 0.15212 seconds