| How Do I Concatenate SQL String in a procedure? [message #217528] |
Fri, 02 February 2007 12:05  |
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 #217536 is a reply to message #217530] |
Fri, 02 February 2007 12:28  |
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 #217824 is a reply to message #217739] |
Mon, 05 February 2007 14:57 |
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 #217896 is a reply to message #217882] |
Tue, 06 February 2007 00:38 |
Frank
Messages: 7901
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
