 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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
