Home » Developer & Programmer » Reports & Discoverer » Oracle Reports 6i
Oracle Reports 6i [message #250681] Tue, 10 July 2007 16:51 Go to next message
saloni29
Messages: 4
Registered: July 2007
Junior Member
TYPE tabstring IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;

FUNCTION fncgetusrtlatest( pictinvnbr IN inv_invoice.invoice_nbr%TYPE
,pictvernbr IN inv_invoice.version_nbr%TYPE
)
RETURN tabstring;

1) I am trying to call the above defined function as a select in SQL withing Oracle reports, however the reports errors with ORA 00902 - Invalid Datatype.... can i not return a custom table type in oracle roports....

2) Can i make the source of the feilds through a backend procedure instead of datasouce retrived in the data model of the reports?
  • Attachment: error.gif
    (Size: 43.32KB, Downloaded 157 times)
Re: Oracle Reports 6i [message #250684 is a reply to message #250681] Tue, 10 July 2007 17:42 Go to previous messageGo to next message
toyway
Messages: 32
Registered: July 2002
Member
In order to use user-defined datatype in RETURN you have to define type in database level, like:
CREATE TYPE tabstring IS TABLE OF VARCHAR2(4000)
/

icon5.gif  Re: Oracle Reports 6i [message #250853 is a reply to message #250684] Wed, 11 July 2007 10:40 Go to previous messageGo to next message
saloni29
Messages: 4
Registered: July 2007
Junior Member
Thanks

1) In order to use the tabstring at the DB level do i need to make an object of it or can i use it directly as any DB table?

2) After defining the table at the DB level, when i use it in the report 6i, the report lets be save the SQL, however the datatype assigned to the returned values of this is 'Unknown'- any idea what should i be doing here?
  • Attachment: Datatype.gif
    (Size: 46.70KB, Downloaded 167 times)

[Updated on: Wed, 11 July 2007 10:48]

Report message to a moderator

Re: Oracle Reports 6i [message #250855 is a reply to message #250853] Wed, 11 July 2007 10:56 Go to previous message
toyway
Messages: 32
Registered: July 2002
Member
Yes, you need to create this type in database.
Then you can use this type in your function.
In order to use this type as a DB table you need to do something like in this example:
SET SERVEROUTPUT ON SIZE 100000

CREATE TYPE tabstring IS TABLE OF VARCHAR2(4000)
/

DECLARE
my_table tabstring;
lv_column VARCHAR2(4000);
BEGIN

SELECT 'This is a test' BULK COLLECT INTO my_table
FROM dual;

SELECT column_value
INTO lv_column
FROM TABLE( CAST(my_table AS tabstring));

dbms_output.put_line( lv_column );

END;
/

HTH,
Oleg
Previous Topic: report error
Next Topic: Sorting Data in Reports
Goto Forum:
  


Current Time: Sat Dec 03 18:19:00 CST 2016

Total time taken to generate the page: 0.15334 seconds