Home » SQL & PL/SQL » SQL & PL/SQL » Performance Issue in Query ? (Oracle 9i)  () 1 Vote
Performance Issue in Query ? [message #307813] Thu, 20 March 2008 01:16 Go to next message
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
Member
SQL> desc test_report_profiles;
Name Type Nullable Default Comments
--------- -------------- -------- ------- --------
INSTANCE NUMBER
SCOPE VARCHAR2(100)
COMMENTS VARCHAR2(100) Y
USER_DATA VARCHAR2(2000) Y

SQL> select user_data from test_report_profiles where rownum=1
SQL> /

begin test_report_profile; end;

ORA-20000: ORU-10028: line length overflow, limit of 255 bytes/chars per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 34
ORA-06512: at "SYS.DBMS_OUTPUT", line 110
ORA-06512: at "SYS.DBMS_OUTPUT", line 81
ORA-06512: at "PACE_MASTERDBO.TEST_REPORT_PROFILE", line 10
ORA-06512: at line 1

SQL> select user_data from test_report_profiles where rownum=1;

USER_DATA
--------------------------------------------------------------------------------
ANALSRC!-1,ASOFDT!11/30/2005,AUTO_BMARK!0,CLIENT_TYPE!CLIENT,CUST1!11/30/2005,DA

SQL> exec test_report_profile;

begin test_report_profile; end;

ORA-20000: ORU-10028: line length overflow, limit of 255 bytes/chars per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 34
ORA-06512: at "SYS.DBMS_OUTPUT", line 110
ORA-06512: at "SYS.DBMS_OUTPUT", line 81
ORA-06512: at "PACE_MASTERDBO.TEST_REPORT_PROFILE", line 10
ORA-06512: at line 1

SQL> exec test_report_profile;

begin test_report_profile; end;

ORA-20000: ORU-10028: line length overflow, limit of 255 bytes/chars per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 34
ORA-06512: at "SYS.DBMS_OUTPUT", line 110
ORA-06512: at "SYS.DBMS_OUTPUT", line 81
ORA-06512: at "PACE_MASTERDBO.TEST_REPORT_PROFILE", line 10
ORA-06512: at line 1

SQL>


and This is My Procedure


create or replace procedure test_report_profile
as
cursor c1 is select user_data from pace_masterdbo.report_profiles where rownum=1;
v1 varchar(2000);
begin
open c1;
loop
fetch c1 into v1;
--select * from tabe(pace_masterdbo.split(v1,',');
dbms_output.put_line(v1);
exit when c1%notfound;
end loop;
close c1;
end;


how to get result for this?
Re: Performance Issue in Query ? [message #307816 is a reply to message #307813] Thu, 20 March 2008 01:22 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
The error your are getting is because of the limitations on DBMS_OUTPUT package.Search this forum there are many examples to overcome this problem.


regards,
Re: Performance Issue in Query ? [message #307819 is a reply to message #307813] Thu, 20 March 2008 01:28 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This has already been told you: please read OraFAQ Forum Guide.
Have a closer look to "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.
Also always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: how to know any table is being used by any mviews
Next Topic: Materialized view creation with subquery
Goto Forum:
  


Current Time: Wed Dec 07 22:30:21 CST 2016

Total time taken to generate the page: 0.10266 seconds