Home » SQL & PL/SQL » SQL & PL/SQL » How Do I Concatenate SQL String in a procedure?
How Do I Concatenate SQL String in a procedure? [message #217528] Fri, 02 February 2007 12:05 Go to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
I have this Qry I started very small and it worked fine except loop thru cursor issue (see my previous post).

declare
sql_text varchar2(32767);
sql_textA varchar2(32767);
...
sql_text := 'select';
sql_text := sql_text ||' t_num,';
sql_text := sql_text ||' l_num,' ;
sql_text := sql_text ||' p_num,';
-- several lines missing
sql_textA := sql_textA ||' AND d.l_num LIKE d_Str ' ;
sql_textA := sql_textA ||' AND d.l_num = t.l_num ';
...
37 dbms_output.put_line (sql_text || sql_textA);
38 open :c1 for sql_text || sql_textA;


I am getting the following errors:
declare
*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 133
ORA-06512: at line 37

Thanks in advance.
Re: How Do I Concatenate SQL String in a procedure? [message #217530 is a reply to message #217528] Fri, 02 February 2007 12:16 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
what part of ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line do you NOT understand?
Re: How Do I Concatenate SQL String in a procedure? [message #217536 is a reply to message #217530] Fri, 02 February 2007 12:28 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Please! No need to be that condescending.

I do get the limitation part. what I am asking is, perhaps I phrased it incorrectly, can I or is there a way to do
something like so :

dbms_output.put_line (sql_text);
dbms_output.put_line (sql_textA);
and somehow continue on w/ the qry.

Thks
Re: How Do I Concatenate SQL String in a procedure? [message #217537 is a reply to message #217528] Fri, 02 February 2007 12:32 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
A SQL statement can span multiple lines.
DBMS_OUTPUT has a limit of 255 characters per line.
Exactly what you do with these capabilities & restrictions is up to you.
Re: How Do I Concatenate SQL String in a procedure? [message #217539 is a reply to message #217536] Fri, 02 February 2007 12:34 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
search this forum for dbms_output.enable
Re: How Do I Concatenate SQL String in a procedure? [message #217739 is a reply to message #217539] Mon, 05 February 2007 03:39 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Here's an alternative.

MHE
Re: How Do I Concatenate SQL String in a procedure? [message #217824 is a reply to message #217739] Mon, 05 February 2007 14:57 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Thanks Maaher. So far I am not getting the 255 char limitation error anymore. But something is not quite right. When I try to open the cursor as such:
...
my_dbms_output(sql_text);
open :c1 for sql_text;

I am getting the following:

ORA-00903: invalid table name -- it is referring to my sql string as a table.
ORA-06512: at line 46

Re: How Do I Concatenate SQL String in a procedure? [message #217882 is a reply to message #217528] Mon, 05 February 2007 23:46 Go to previous messageGo to next message
rawat_me
Messages: 45
Registered: September 2005
Member
Hi,
From my understanding of your problem:
You are creating a Dynamic query.

SO for that you have to use Execute Immediate.

This would be the syntax for opening it .

Execute Immediate ' Open c1 for ' || sql_text ;

Where c1 will be a ref cursor.

Thanks
Re: How Do I Concatenate SQL String in a procedure? [message #217896 is a reply to message #217882] Tue, 06 February 2007 00:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rawat_me wrote on Tue, 06 February 2007 06:46
Hi,
From my understanding of your problem:
You are creating a Dynamic query.

SO for that you have to use Execute Immediate.

This would be the syntax for opening it .

Execute Immediate ' Open c1 for ' || sql_text ;

Where c1 will be a ref cursor.



This is not true. Read anacedent's reply, it explains where the error occurs (dbms_output, NOT the opening of the ref cursor).
While we are at it, repeat this after me:
I probably will never have to use execute immediate in production code
I probably will never have to use execute immediate in production code
I probably will never have to use execute immediate in production code
I probably will never have to use execute immediate in production code

And now remember it!
Re: How Do I Concatenate SQL String in a procedure? [message #218032 is a reply to message #217896] Tue, 06 February 2007 08:26 Go to previous message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Thank you all for your input. Actually Frank, rawat is right I used one of the alternative suggested by the member before rawat and wrote a procedure called my_dbmsoutput where the 255 char limitation would not be an issue at least that is mu understanding of it. aftet I called that new proc I get the last msg. refering to my sql_string as a table @ the open cursor juncture. Here is the proc:

create or replace procedure my_dbms_output(sql_string in varchar2 )
is
l_string long default sql_string;
begin
loop
exit when l_string is null;
dbms_output.put_line( substr( l_string, 1, 250 ) );
l_string := substr( l_string, 251 );
end loop;
end;
/
I am really in a bind here, Please any help will be appreciated.
and Thank you Frank for your advice about execute immediate. I did try it too and no luck.

Thks
Previous Topic: Setting CURRENT_SCHEMA doesn't influence the stored procedure
Next Topic: Connect by Prior - Performance Issue
Goto Forum:
  


Current Time: Mon Dec 05 09:07:50 CST 2016

Total time taken to generate the page: 0.09426 seconds