Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_SQL.describe_columns2-session disconnect (oracl 10g)
DBMS_SQL.describe_columns2-session disconnect [message #582855] Tue, 23 April 2013 02:31 Go to next message
shiva_blr
Messages: 3
Registered: March 2012
Junior Member
Hi ,
I am using dbms_sql to generate report.getting query from db building cursor (variable binding etc..) and most of the reports works fine. Some big quires works fine for some time (may be 1 day or 2) after that it will stop working.
I debugged the program I found that when I call DBMS_SQL.describe_columns2(l_cursor, l_col_cnt, g_desc_t) session getting disconnecting and no exception raised

report will work again after changing query (i.e any column name ,little change in the query )

before used DBMS_SQL.describe_columns this also same problem


sample code
-----

SELECT srm_query, srm_no_of_para, srm_columns_head,
NVL (srm_rep_format, 'CSV'), srm_pre_command
INTO l_query, l_no_of_para, l_heading,
l_rep_format, l_pre_command
FROM sym_voy_rep
WHERE srm_rep_id = p_rep_id;

IF p_heading IS NOT NULL
THEN
l_heading := p_heading;
END IF;

IF l_pre_command IS NOT NULL
THEN
EXECUTE IMMEDIATE ( 'begin '
|| l_pre_command
|| p_parm_values_pc
|| '; end ;'
);
END IF;

l_cursor := build_cursor;
DBMS_SQL.describe_columns2(l_cursor, l_col_cnt, g_desc_t);

FOR i IN 1 .. g_desc_t.COUNT
LOOP
DBMS_SQL.define_column (l_cursor, i, g_cvalue, 32765);
g_sums (i) := 0;
END LOOP;

l_status := DBMS_SQL.EXECUTE (l_cursor);
Re: DBMS_SQL.describe_columns2-session disconnect [message #582864 is a reply to message #582855] Tue, 23 April 2013 03:00 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Check if no trace file has been generated.
What is the complete error message?

Regards
Michel
Re: DBMS_SQL.describe_columns2-session disconnect [message #582883 is a reply to message #582864] Tue, 23 April 2013 03:46 Go to previous messageGo to next message
shiva_blr
Messages: 3
Registered: March 2012
Junior Member
no compile error procedure giving correct output (a csv file).
Some time when try to execute procedureno error but session get disconnected. Once this disconnection and it will continue disconnect.
I debugged procedure and found DBMS_SQL.define_column (l_cursor, i, g_cvalue, 32765);
is the problem
Re: DBMS_SQL.describe_columns2-session disconnect [message #582886 is a reply to message #582883] Tue, 23 April 2013 03:55 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Check if no trace file has been generated.
What is the complete error message (when the error occurs)?


Regards
Michel
Re: DBMS_SQL.describe_columns2-session disconnect [message #582932 is a reply to message #582886] Tue, 23 April 2013 08:51 Go to previous messageGo to next message
shiva_blr
Messages: 3
Registered: March 2012
Junior Member
Please find trace file
Re: DBMS_SQL.describe_columns2-session disconnect [message #582934 is a reply to message #582855] Tue, 23 April 2013 08:54 Go to previous messageGo to next message
BlackSwan
Messages: 22690
Registered: January 2009
Senior Member
submit Service Request to Oracle Support
Re: DBMS_SQL.describe_columns2-session disconnect [message #582936 is a reply to message #582932] Tue, 23 April 2013 09:22 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
shiva_blr wrote on Tue, 23 April 2013 15:51
Please find trace file


No trace file but as Blackswan said you shoud submit a SR to Oracle support.

Regards
Michel
Re: DBMS_SQL.describe_columns2-session disconnect [message #583103 is a reply to message #582936] Thu, 25 April 2013 17:44 Go to previous message
Barbara Boehmer
Messages: 7969
Registered: November 2002
Location: California, USA
Senior Member
Supposedly describe_columns2 was created to fix a bug in describe_columns. I see that 11g now has a describe_columns3, so perhaps that fixed a bug in describe_columns2.
Previous Topic: Merge is not working properly
Next Topic: FIFO Store Consumption
Goto Forum:
  


Current Time: Fri Aug 22 15:05:32 CDT 2014

Total time taken to generate the page: 0.07667 seconds