| Oracle Reports 6i [message #250681] |
Tue, 10 July 2007 16:51  |
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 752 times)
|
|
|
|
| Re: Oracle Reports 6i [message #250684 is a reply to message #250681] |
Tue, 10 July 2007 17:42  |
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)
/
|
|
|
|
 Re: Oracle Reports 6i [message #250853 is a reply to message #250684] |
Wed, 11 July 2007 10:40 |
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 771 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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
