Home » SQL & PL/SQL » SQL & PL/SQL » How to spool output of PL/SQL procedure/function to file?  () 1 Vote
How to spool output of PL/SQL procedure/function to file? [message #248279] Thu, 28 June 2007 09:30 Go to next message
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 #248283 is a reply to message #248279] Thu, 28 June 2007 09:39 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
When passing a varchar2 field as a parameter, you are limited to a size of 4000.

Investigate the use of CLOB.


[Updated on: Thu, 28 June 2007 09:40]

Report message to a moderator

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 Go to previous messageGo to next message
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 #248296 is a reply to message #248279] Thu, 28 June 2007 10:13 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
AFAIK, a PL/SQL VARCHAR2 variable can exceed 32000 in size.
Re: How to spool output of PL/SQL procedure/function to file? [message #248316 is a reply to message #248279] Thu, 28 June 2007 11:06 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Not when being passed as a parameter though, at least the last I knew.
Re: How to spool output of PL/SQL procedure/function to file? [message #248320 is a reply to message #248279] Thu, 28 June 2007 11:21 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
I distinctly remember writing a PL/SQL package on V8.1.6 which was the 1st version to provide sending emails that accepted a VARCHAR2(32000); which was the email body.
This package allowed me to send email messages from my Production V7.3.4.5 system.

I don't have time to provide a test case today.
Re: How to spool output of PL/SQL procedure/function to file? [message #248324 is a reply to message #248279] Thu, 28 June 2007 11:33 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
I'm strictly going off of memory too, I know I encountered a 4000 byte limit at some point in my past.

Maybe it wasn't an Oracle restriction, but a 3rd party restriction.

Not a huge deal.

[Updated on: Thu, 28 June 2007 11:36]

Report message to a moderator

Re: How to spool output of PL/SQL procedure/function to file? [message #248328 is a reply to message #248279] Thu, 28 June 2007 11:47 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
There is STILL a 4000 limit on VARCHAR2 in TABLES, but this is separate & distinct from PL/SQL variables.
Re: How to spool output of PL/SQL procedure/function to file? [message #248329 is a reply to message #248279] Thu, 28 June 2007 12:10 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Just curious, why do you want to build your own tables when sql*plus will automatically generate HTML code. See the following link

http://download.oracle.com/docs/cd/B10501_01/server.920/a90842/ch13.htm#1012748
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 Go to previous messageGo to next message
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 #248372 is a reply to message #248279] Thu, 28 June 2007 14:49 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Frank?
Frank is not here.
SQL> @tester2
SQL> create or replace procedure varchar2_test_prc (v1 varchar2)
  2  as
  3  begin
  4  dbms_output.enable(100000);
  5  dbms_output.put_line(length(v1));
  6  end;
  7  /

Procedure created.

SQL> declare
  2  v1 varchar2(32000);
  3  v2 varchar2(40) := '1234567890123456789012345678901234567890';
  4  n1 number;
  5  n2 number;
  6  begin
  7  dbms_output.enable(100000);
  8  for n1 in 1..500 loop
  9     v1 := v1 || v2;
 10  end loop;
 11  dbms_output.put_line(length(v1));
 12  varchar2_test_prc(v1);
 13  end;
 14  /
20000
20000

PL/SQL procedure successfully completed.

From where I sit, 20000 is greater than 4000
Re: How to spool output of PL/SQL procedure/function to file? [message #248381 is a reply to message #248372] Thu, 28 June 2007 15:24 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Strange ..

Re: How to spool output of PL/SQL procedure/function to file? [message #248382 is a reply to message #248279] Thu, 28 June 2007 15:30 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>select lpad(' ',5000,'*') into v1 from dual;
I suspect the "discrepency" occurs above.
As I stated earlier a database/table VARCHAR2 is limited to 4000.
I suspect that the "dual table" is enforcing the limitation; but have NO way to prove or disprove this hypothesis.

Regardless, I KNOW that PL/SQL VARCHAR2 variable can exceed 32000; even when being passed between procedures/packages.
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 Go to previous messageGo to next message
joy_division
Messages: 4644
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
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 #248463 is a reply to message #248412] Fri, 29 June 2007 02:17 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel, Ana, that's another thing I've learnt today. I never thought about it, I must admit. I always had the reflex to link SQL with a 4000 byte limit on character variables. Michel, do you have references on MetaLink that document this behavior?

MHE
Re: How to spool output of PL/SQL procedure/function to file? [message #248469 is a reply to message #248463] Fri, 29 June 2007 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, TAR are not published so I can't give you more details.
But I made some researches and found this in SQL Reference (9.2), Chapter 6 Functions, section "SQL Functions", subsection "Character Functions Returning Character Values":
Quote:
Functions that return VARCHAR2 values are limited in length to 4000 bytes.

This is not clear but is the only reference in documenation and it disappears in 10.2 (but the behaviour is still the same).

You can also have a look at one of the bugs I raised and is now published: 4106505.

Regards
Michel
Re: How to spool output of PL/SQL procedure/function to file? [message #248473 is a reply to message #248469] Fri, 29 June 2007 02:43 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Thanks Michel. I'll have a look.

MHE
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 Go to previous message
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
Previous Topic: How can I bifurcate result set into 2?
Next Topic: Query to rank
Goto Forum:
  


Current Time: Sun Dec 11 04:00:05 CST 2016

Total time taken to generate the page: 0.16537 seconds