| How to spool output of PL/SQL procedure/function to file? [message #248279] |
Thu, 28 June 2007 09:30  |
F7502
Messages: 3
Registered: June 2007
Location: Stuttgart / Germany
|
Junior Member |
|
|
Hi together,
i'm using a script for generating html-reports out of an oracle-db. The script looks like following:
-- first create function
1* select text from dba_source where name='GO'
CREATE OR REPLACE FUNCTION GO(in_uid NUMBER) RETURN varchar2 IS
TYPE EmpCurTyp IS REF CURSOR;
emp_cv EmpCurTyp;
tmp_str varchar2(4000);
ret_str varchar2(4000);
BEGIN
sql_cur := 'SELECT x.status FROM table x';
OPEN emp_cv FOR statement sql_cur;
LOOP
FETCH emp_cv INTO tmp_str
EXIT WHEN emp_cv%NOTFOUND;
ret_str := ret_str ||', '|| tmp_str;
END LOOP;
CLOSE emp_cv;
RETURN ret_str;
END;
/
set pagesize 0
set verify off
set lines 1024
set trimspool on
set feedback off
set termout on
SPOOL test.htm
prompt <html> -
<head><title>My Report</title>
prompt <table>
SELECT DISTINCT
'<tr><td>'
, x.doc_id
, '</td><td>'
, GO(x.uid)
, '</td></tr>'
FROM some_table x
WHERE x.doc_id IS NOT NULL
ORDER BY x.doc_id ASC;
prompt </tbody> -
</table> -
</body> -
</html>
SPOOL OFF
quit
The result is a table with a structure like:
--------------------
| ID | Some Values |
--------------------
| 01 | a, b, c, d |
| 02 | a, g, h, |
| 05 | t, a, h, e |
...
--------------------
The PL/SQL-function is a little more complex than shown here, and the problem ist that the string returned by the function is getting too long!
ORA-06502: PL/SQL: numeric or value error
I changed the function to produce shorter output and everything worked.
So my idea was, that instead of fetching the results from the cursor and building a long string, i could fetch the results from the cursor and then output them:
CREATE OR REPLACE FUNCTION GO(in_uid NUMBER) RETURN varchar2 IS
...
BEGIN
...
LOOP
FETCH emp_cv INTO tmp_str
EXIT WHEN emp_cv%NOTFOUND;
output(tmp_str || ', ');
END LOOP;
...
END;
How do i do this output? I need a function to output a string, so that it gets spooled into the html-file.
I would appreciate any kind of help!
By the way:
I'm working with this report, but i didn't create it. Does anyone know, what all these "set ..." statement mean? I wasn't able to find any good documantation on that...
set pagesize 0
set verify off
set lines 1024
set trimspool on
set feedback off
set termout on
...
Thanks!
F7502
|
|
|
|
|
|
| Re: How to spool output of PL/SQL procedure/function to file? [message #248294 is a reply to message #248283] |
Thu, 28 June 2007 10:06  |
F7502
Messages: 3
Registered: June 2007
Location: Stuttgart / Germany
|
Junior Member |
|
|
Hi,
thanks for the fast reply.
I tried replacing varchar2 with clob like:
-- first create function
1* select text from dba_source where name='GO'
CREATE OR REPLACE FUNCTION GO(in_uid NUMBER) RETURN clob IS
TYPE EmpCurTyp IS REF CURSOR;
emp_cv EmpCurTyp;
tmp_str varchar2(4000);
ret_str clob;
BEGIN
...
LOOP
FETCH emp_cv INTO tmp_str
EXIT WHEN emp_cv%NOTFOUND;
ret_str := ret_str ||', '|| tmp_str;
END LOOP;
...
RETURN ret_str;
END;
...
Didn't work...
Do i have to change anything else than "varchar2" to "clob"? Maybe i missed any conversions or something like that?
cu
F7502
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to spool output of PL/SQL procedure/function to file? [message #248369 is a reply to message #248279] |
Thu, 28 June 2007 14:26 |
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
|
Senior Member |
|
|
Frank.
I couldn't resist a little test. I took some lunch hour time here is what I did:
create table varchar2_test_tab
( v_col_length number);
create or replace procedure varchar2_test_prc as
(v1 varchar2)
begin
insert into varchar2_test_tab
(v_col_length)
values
length(v1);
end;
declare
v1 varchar2(32000);
begin
select lpad(' ',3000,'*') into v1 from dual;
varchar2_test_prc(v1);
commit;
end;
declare
v1 varchar2(32000);
begin
select lpad(' ',4000,'*') into v1 from dual;
varchar2_test_prc(v1);
commit;
end;
declare
v1 varchar2(32000);
begin
select lpad(' ',5000,'*') into v1 from dual;
varchar2_test_prc(v1);
commit;
end;
SQL> select * from varchar2_test_tab;
V_COL_LENGTH
------------
3000
4000
4000
So it appears to be only passing 4000 bytes unless I am missing something very silly.
[Updated on: Thu, 28 June 2007 14:27] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: How to spool output of PL/SQL procedure/function to file? [message #248384 is a reply to message #248382] |
Thu, 28 June 2007 15:38 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
I think I have a way to test the hypothesis, and you are correct about dual.
FOO SCOTT> set long 8000
FOO SCOTT> set trunc off lines 100
FOO SCOTT> select lpad(' ',5000,'*') from dual;
LPAD('',5000,'*')
----------------------------------------------------------------------------------------------------
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
****************************************************************************************************
***************************************************************************************************
40 lines of 100 characters = 4000 bytes (3999 asterisks and one space). Good one anacedent.
[Updated on: Thu, 28 June 2007 15:41] Report message to a moderator |
|
|
|
| Re: How to spool output of PL/SQL procedure/function to file? [message #248412 is a reply to message #248384] |
Thu, 28 June 2007 23:39 |
 |
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
LPAD/RPAD works with a buffer of 4000 bytes.
Note BYTES not CHAR.
If you work with multi-byte character set it will be less characters.
And you get some strange as the following one answering the question "what is the place of A when you left pad the string with 4999 B?". Of course it is 4000!
SQL> select instr(lpad('A',5000,'B'),'A') from dual;
INSTR(LPAD('A',5000,'B'),'A')
-----------------------------
4000
1 row selected.
I raised several TAR several years ago about this kind of things that can happen with other functions like REPLACE.
The answer from Oracle was:
| Quote: | i asked a bit around and there are currently no plans to change this behavior, development fears that there will be too much existing applications who would break if this is changed.
|
You just have to remember that SQL functions works with a 4000 byte buffer.
Regards
Michel
|
|
|
|
|
|
|
|
|
|
| Re: How to spool output of PL/SQL procedure/function to file? [message #248492 is a reply to message #248279] |
Fri, 29 June 2007 03:32 |
F7502
Messages: 3
Registered: June 2007
Location: Stuttgart / Germany
|
Junior Member |
|
|
Hi together,
back in office, just read all the posts, thanks.
Now that we all know, that size is the problem, what about an solution?
Is there a way, to do an output inside the PL/SQL function, so that it's spooled into the file?
@Bill B
Let Oracle do the html creation is an option. I didn't create the reports, i only change them from time to time. But changing this needs time and testing...
I would appreciate any help. Please have a look at the code in my initial posting.
What i got (simplified):
function create_long_string return varchar2
my_long_string;
begin
for ... loop
my_long_string := my_long_string || shorter_string;
end loop;
return(my_long_string); // THIS IS GETTING TOO LONG
end
spool on
create_long_string;
spool off
What i want:
procedure output_long_string return varchar2
my_long_string;
begin
for ... loop
output(shorter_string); // HOW TO DO THIS??
end loop;
end
spool on
output_long_string;
spool off
How can i do this?
Thanks for your help
F7502
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
