Home » Developer & Programmer » Reports & Discoverer » Pass multiple rows of data in user parameter
icon10.gif  Pass multiple rows of data in user parameter [message #208453] Sun, 10 December 2006 23:44
Mike_23
Messages: 11
Registered: December 2006
Location: Australia
Junior Member
Hi guys,

I'm using Oracle Reports Builder 9i and need to somehow pass multiple rows of data into a user parameter (in the 'beforereport' trigger). I use the following code in functions successfully but it doesnt work in 'beforereport':

-----------------------------------------
function BeforeReport return boolean is
retval VARCHAR2(2000);
ctr NUMBER(5) := 0;

cursor c1 is

SELECT docu.docu_code || ' / ' || docu.revision || ' / ' || TO_CHAR(docn.description) "Document"
FROM m_sys.m_documents docu
, m_sys.m_document_names docn
WHERE docu.docu_id = docn.docu_id(+)
AND docu.docu_code = :DC_DOC_ID

begin

FOR x IN c1 LOOP
ctr := ctr + 1;

IF ctr = 1 THEN
retval := x."Document";
ELSE
retval := retval || CHR(10) || CHR(10) || x."Document";
END IF;
END LOOP;

RETURN retval;

EXCEPTION WHEN OTHERS THEN

RETURN NULL;

end;
-----------------------------------------------------

I have managed to get this working by doing the following:

function BeforeReport return boolean is
ctr NUMBER(5) := 0;

cursor c1 is

SELECT docu.docu_code || ' / ' || docu.revision || ' / ' || TO_CHAR(docn.description) "Document"
FROM m_sys.m_documents docu
, m_sys.m_document_names docn
WHERE docu.docu_id = docn.docu_id(+)
AND docu.docu_code = :DC_DOC_ID

begin

FOR x IN c1 LOOP
ctr := ctr + 1;

IF ctr = 1 THEN
:UP_Doc := x."Document";
ELSE
:UP_Doc := :UP_Doc || CHR(10) || CHR(10) || x."Document";
END IF;
END LOOP;

RETURN TRUE;

EXCEPTION WHEN OTHERS THEN

RETURN NULL;

end;
-----------------------------------------------------

This can now be closed off.

[Updated on: Mon, 11 December 2006 17:59]

Report message to a moderator

Previous Topic: calling report within report (d2k 6i)
Next Topic: Water Mark Text Coloring Problem
Goto Forum:
  


Current Time: Thu Dec 08 10:13:56 CST 2016

Total time taken to generate the page: 0.10806 seconds