Table Functions as Source and Target

Using table functions is a useful way to both boost performance and extend the source/target capabilities in OWB. Here we will see how to setup table functions as a source and a target in OWB. Table functions let you define a set of PL/SQL statements that when queried, will behave like a standard SQL query to a table. We will do this by simple examples to illustrate how it hangs together.

Firstly let's define 2 maps, both use the table function as a source, one uses a table function which takes a scalar parameter, the other takes a REF CURSOR, so an arbitrary SQL query can be passed as a parameter. The functions are skeletal just to illustrate how it all works. You can also have a table function as a target so can write to systems using APIs for example, we will look at this later.

Scalar Table Function Example

The scalar table function example uses a constant (a scalar) as a parameter to the table function, and the results are returned from the table function and then the map writes to rows returned to the target table.

Table Function Scalar Source:

Here is the OMB script to create the table function as source example (script 1).

# ===============================================
# Source: Scalar TF Map:
# ===============================================
OMBCREATE MAPPING 'TF_SCALAR_PARAM' ADD TABLE_FUNCTION OPERATOR 'GET_EMPS' SET PROPERTIES (TABLE_FUNCTION_NAME) VALUES ('SCOTT.SCALARF')

OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD INPUT_PARAMETER OPERATOR 'INPUTS'
OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD ATTRIBUTE 'FILTERREC' OF GROUP 'OUTGRP1' OF OPERATOR 'INPUTS' SET PROPERTIES (DATATYPE,DEFAULT_VALUE) VALUES ('NUMBER', '0')

OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD ATTRIBUTE 'FILTER_EMP' OF GROUP 'INGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')
OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD ATTRIBUTE 'EMPNO' OF GROUP 'OUTGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')
OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD ATTRIBUTE 'ENAME' OF GROUP 'OUTGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE, LENGTH) VALUES ('VARCHAR2', '256')
OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD ATTRIBUTE 'LVL' OF GROUP 'OUTGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')


OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD CONNECTION FROM ATTRIBUTE 'FILTERREC' OF GROUP 'OUTGRP1' OF OPERATOR 'INPUTS' TO ATTRIBUTE 'FILTER_EMP' OF GROUP 'INGRP1' OF OPERATOR 'GET_EMPS'

OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD TABLE OPERATOR 'EMPLOYEES'
OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD CONNECTION FROM GROUP 'OUTGRP1' OF OPERATOR 'GET_EMPS' TO GROUP 'INOUTGRP1' OF OPERATOR 'EMPLOYEES'


Ref Cursor Table Function Example

An arbitrary SQL query can also be passed in as a ref cursor into the table function, you define the group with this information in order to get the correct SQL generated;

Table Function Ref Cursor Source:

The table function which is referenced is defined via the property below, if it is in a different schema you prefix the name with the schema name;

Table Function Setup:

Here is the OMB script to create the table function as source example using a ref cursor (script 2).

# ===============================================
# Source: Ref Cursor TF Map:
# ===============================================
OMBCREATE MAPPING 'TF_REFCURSOR_PARAM' ADD TABLE_FUNCTION OPERATOR 'GET_EMPS' SET PROPERTIES (TABLE_FUNCTION_NAME) VALUES ('SCOTT.REFCURSORF')

OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD TABLE OPERATOR 'SRC'
OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'C1' OF GROUP 'INOUTGRP1' OF OPERATOR 'SRC' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')
OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'C2' OF GROUP 'INOUTGRP1' OF OPERATOR 'SRC' SET PROPERTIES (DATATYPE, LENGTH) VALUES ('VARCHAR2', '256')
OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'C3' OF GROUP 'INOUTGRP1' OF OPERATOR 'SRC' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')

OMBALTER MAPPING 'TF_REFCURSOR_PARAM' MODIFY GROUP 'INGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (GROUP_TYPE) VALUES ('REF_CURSOR')
OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'EMPNO' OF GROUP 'INGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')
OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'ENAME' OF GROUP 'INGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE, LENGTH) VALUES ('VARCHAR2', '256')
OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'LVL' OF GROUP 'INGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')

OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'EMPNO' OF GROUP 'OUTGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')
OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'ENAME' OF GROUP 'OUTGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE, LENGTH) VALUES ('VARCHAR2', '256')
OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'LVL' OF GROUP 'OUTGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')


OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD CONNECTION FROM ATTRIBUTE 'C1' OF GROUP 'INOUTGRP1' OF OPERATOR 'SRC' TO ATTRIBUTE 'EMPNO' OF GROUP 'INGRP1' OF OPERATOR 'GET_EMPS'
OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD CONNECTION FROM ATTRIBUTE 'C2' OF GROUP 'INOUTGRP1' OF OPERATOR 'SRC' TO ATTRIBUTE 'ENAME' OF GROUP 'INGRP1' OF OPERATOR 'GET_EMPS'
OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD CONNECTION FROM ATTRIBUTE 'C3' OF GROUP 'INOUTGRP1' OF OPERATOR 'SRC' TO ATTRIBUTE 'LVL' OF GROUP 'INGRP1' OF OPERATOR 'GET_EMPS'

OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD TABLE OPERATOR 'EMPLOYEES_SAL'
OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD CONNECTION FROM GROUP 'OUTGRP1' OF OPERATOR 'GET_EMPS' TO GROUP 'INOUTGRP1' OF OPERATOR 'EMPLOYEES_SAL'

Types and Table Functions for Source Examples

Here are the types and table functions created in SCOTT, you will have to grant execute on your functions to the target schema;

CREATE OR REPLACE TYPE emp_obj AS OBJECT(
  empno NUMBER,
  ename VARCHAR2(255),
  lvl number
  );
/

CREATE OR REPLACE TYPE tb_emp_obj AS TABLE OF emp_obj;
/

CREATE OR REPLACE FUNCTION REFCURSORF(input_values sys_refcursor) RETURN tb_emp_obj pipelined AS
  out_pipe emp_obj := emp_obj(null,null,null);
  ename VARCHAR2(255):=null;
  empno number; lvl NUMBER;
BEGIN
  LOOP
    FETCH input_values INTO empno, ename, lvl;
    EXIT WHEN input_values%NOTFOUND;
    out_pipe.empno:=empno;
    out_pipe.ename:=ename;
    out_pipe.lvl:=lvl;
    PIPE ROW(out_pipe);
  END LOOP;
  CLOSE input_values;
  RETURN;
END;
/

CREATE OR REPLACE FUNCTION SCALARF(input_val NUMBER) RETURN tb_emp_obj pipelined AS
  out_pipe emp_obj := emp_obj(null,null,null);
  ename VARCHAR2(255):=null;
  empno number; lvl NUMBER;
  cursor INPUT_VALUES is select empno, ename, sal from emp;
BEGIN
  open INPUT_VALUES;
  LOOP
    FETCH INPUT_VALUES INTO empno, ename, lvl;
    EXIT WHEN input_values%NOTFOUND;
    out_pipe.empno:=empno;
    out_pipe.ename:=ename;
    out_pipe.lvl:=lvl;
    PIPE ROW(out_pipe);
  END LOOP;
  CLOSE input_values;
  RETURN;
END;
/

Target Table Function Example

With the target table function example there is a flag on the operator to define that the table function is being used as a target in the mapping. The table function as target in OWB must return one row, in the example below I use OWB to aggregate EMP then the ref cursor is passed to the table function, the table function inserts the aggregated values into another table. You will not get the audit details written to the OWB runtime and needs some error handling, but these kind of things are possible. Also the table function scenario as target would be possible if you use the autonomous pragma transaction within the table function as below.

Table Function Targets:

Here is the OMB script to create the table function as target example (script 3).

set map_name LOAD_WITH_TABFUN
OMBCREATE MAPPING '$map_name' ADD TABLE_FUNCTION OPERATOR 'WRITE_TO_ANYTHING' SET PROPERTIES (TABLE_FUNCTION_NAME,TABLE_FUNCTION_IS_TARGET) VALUES ('TG_TGT', 'true')

OMBALTER MAPPING '$map_name' ADD TABLE OPERATOR 'EMP' BOUND TO TABLE '../SCOTT/EMP'
OMBALTER MAPPING '$map_name' ADD ATTRIBUTE 'DEPTNO' OF GROUP 'INGRP1' OF OPERATOR 'WRITE_TO_ANYTHING' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')

OMBALTER MAPPING '$map_name' ADD ATTRIBUTE 'SALARY' OF GROUP 'INGRP1' OF OPERATOR 'WRITE_TO_ANYTHING' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')

OMBALTER MAPPING '$map_name' ADD ATTRIBUTE 'STATUS' OF GROUP 'OUTGRP1' OF OPERATOR 'WRITE_TO_ANYTHING' SET PROPERTIES (DATATYPE) VALUES ('NUMBER')
OMBALTER MAPPING '$map_name' MODIFY GROUP 'INGRP1' OF OPERATOR 'WRITE_TO_ANYTHING' SET PROPERTIES (GROUP_TYPE) VALUES ('REF_CURSOR')

OMBALTER MAPPING '$map_name' ADD AGGREGATOR OPERATOR 'AGG'

OMBALTER MAPPING '$map_name' ADD CONNECTION FROM ATTRIBUTE 'DEPTNO' OF GROUP 'INOUTGRP1' OF OPERATOR 'EMP' TO GROUP 'INGRP1' OF OPERATOR 'AGG'
OMBALTER MAPPING '$map_name' ADD CONNECTION FROM ATTRIBUTE 'SAL' OF GROUP 'INOUTGRP1' OF OPERATOR 'EMP' TO GROUP 'INGRP1' OF OPERATOR 'AGG'

OMBALTER MAPPING '$map_name' MODIFY OPERATOR 'AGG' SET PROPERTIES (GROUP_BY_CLAUSE) VALUES ('INGRP1.DEPTNO')

OMBALTER MAPPING '$map_name' ADD ATTRIBUTE 'SAL' OF GROUP 'OUTGRP1' OF OPERATOR 'AGG' SET PROPERTIES (DATATYPE, EXPRESSION) VALUES ('NUMBER', 'SUM(INGRP1.SAL)')

OMBALTER MAPPING '$map_name' ADD CONNECTION FROM ATTRIBUTE 'DEPTNO' OF GROUP 'OUTGRP1' OF OPERATOR 'AGG' TO ATTRIBUTE 'DEPTNO' OF GROUP 'INGRP1' OF OPERATOR 'WRITE_TO_ANYTHING'

OMBALTER MAPPING '$map_name' ADD CONNECTION FROM ATTRIBUTE 'SAL' OF GROUP 'OUTGRP1' OF OPERATOR 'AGG' TO ATTRIBUTE 'SALARY' OF GROUP 'INGRP1' OF OPERATOR 'WRITE_TO_ANYTHING'

OMBALTER MAPPING '$map_name' MODIFY GROUP 'OUTGRP1' OF OPERATOR 'WRITE_TO_ANYTHING'
  SET PROPERTIES (RETURN_TABLE_OF_SCALAR) VALUES ('true')

OMBALTER MAPPING '$map_name' MODIFY OPERATOR 'WRITE_TO_ANYTHING' SET PROPERTIES (TABLE_FUNCTION_IS_TARGET) VALUES ('true')

#Work around bug with target table function and generation.
catch {OMBCOMPILE MAPPING '$map_name'}
OMBALTER MAPPING '$map_name' SET PROPERTIES (GENERATION_MODE, DEFAULT_OPERATING_MODE) VALUES ('SET_BASED', 'SET_BASED')
catch {OMBCOMPILE MAPPING '$map_name'}

Here is the demo table function used;

create or replace function TF_TGT(RC IN SYS_REFCURSOR
                          ) return TBL_OF_STRINGS PIPELINED
as
 PRAGMA AUTONOMOUS_TRANSACTION;
 DEPTNO number;
 SALARY number;
begin
 loop
   FETCH RC into DEPTNO,SALARY;
   INSERT INTO TFTGTTAB VALUES (DEPTNO,SALARY);
   EXIT WHEN RC%NOTFOUND;
 end loop;
 COMMIT;
 PIPE ROW('SUCCESS');
end;
/

Here we have seen how the table function is setup in OWB and how we can extend the source and targets using an API based approach. If you would like to see a sample that leverages table functions integrating to web services see the post here - the jpublisher component generates functions and table functions around the web services defined in a WSDL.

Comments:

I can not get this to work: -I am using owb 11.2.0.1 and can not find a Input Parameter Type for a Input Group of a Table Function Operator. -In fact when i run the supplied OMB code it ends with: OMB02922: Group INGRP1 of operator GET_EMPS does not exist. When i then manually add the group to the Table Function Operator and run the OMB code for this group it barfs and says the property doesn't exist: OMBALTER MAPPING 'TF_REFCURSOR_PARAM' MODIFY GROUP 'INGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (GROUP_TYPE) VALUES ('REF_CURSOR') OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'EMPNO' OF GROUP 'INGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE) VALUES ('NUMBER') OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'ENAME' OF GROUP 'INGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE, LENGTH) VALUES ('VARCHAR2', '256') OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'LVL' OF GROUP 'INGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE) VALUES ('NUMBER') OMB02902: Error setting property GROUP_TYPE of INGRP1: MMM1034: Property GROUP_TYPE does not exist.

Posted by Michael Reitsma on April 25, 2011 at 05:33 PM PDT #

if i look in the OWB documentation for OMB commands i can find the reference for group_type in the 11.1 documentation but in the 11.2 omb plus refe here: http://download.oracle.com/docs/cd/E11882_01/owb.112/e14406/chap3006.htm#GDBBAFBF The group_type reference is gone. And indeed i can not find it anymore in my 11.2 client. Is this because of the newly added odi stuff ? If so how do i connect table operators with ref_cursors in it to other items ?

Posted by Michael Reitsma on April 25, 2011 at 08:17 PM PDT #

Hi Michael I just posted this, hopefully it'll help fill in some of the holes... http://blogs.oracle.com/warehousebuilder/2011/04/owb_11gr2_table_functions.html Cheers David

Posted by David Allan on April 26, 2011 at 02:17 AM PDT #

Hi David, Thx a lot for this, this works great. I guess it was just a RTFM for me ...

Posted by Michael Reitsma on April 26, 2011 at 06:30 PM PDT #

Hi,

I've tried to get a table function going as a target. However owb generates the wrong code and I can't find the switch that makes it generate the right code. Here's what I did.

I've created a mapping. In this mapping I'm forced to use a table function as a target. So I created a pipelined function. The build of this is something like:

-- create a type
create or replace type sa_string_type as object( string varchar2(450));
-- create a table of this type
create or replace type sa_string_table as table of sa_string_type;
function abcd( c_cursor in sys_refcursor )
return sa_string_table pipelined
is

type t_varchar2s is table of varchar2(4000)
index by pls_integer;

return_value sa_string_type := sa_string_type( null );

t_value t_varchar2s;

begin
fetch c_cursor\bulk collect
into t_value; -- this works perfect
for i in 1..t_value.count
loop
if t_value(i) = 'this'
then
do_this;
else
do_that
end;
end loop; -- this works perfect
delete.t_value; -- this works perfect
return_value.string := 'great';
pipe row( return_value ); -- this works great if the generated code is correct, which it isn't
end;

In the mapping I entered ABCD in the table function name box & I checked the table function is target box.
The input parameter type is Ref Cursor.
The Return Table Of Scalar box is unchecked, although I tried the mapping with the box checked and unchecked.
Both configuration items, Default operating mode and Generation mode have been set to set based.

The code then generated gives the following warning:

MAPPING_NAME
Create
Warning
ORA-06550: line 102, column 7:
PL/SQL: SQL Statement ignored
MAPPING_NAME
Create
Warning
ORA-06550: line 105, column 3:
PL/SQL: ORA-00904: "ABCD"."RETURN_VALUE": invalid identifier

The code looks like this:

SELECT 1 INTO owb_temp_variable1
FROM
(SELECT
"ABCD"."RETURN_VALUE" RETURN_VALUE
FROM
TABLE ( ABCD (
CURSOR ( -- ETC

with the Return Table Of Scalar box unchecked

and

SELECT 1 INTO owb_temp_variable1
FROM
(SELECT
"ABCD"."COLUMN_VALUE" COLUMN_VALUE
FROM
TABLE ( ABCD (
CURSOR ( -- ETC

with the Return Table Of Scalar box checked

If I edit the generated code on the database and enter a * where either return_value or column_value were
the mapping runs perfect.

SELECT 1 INTO owb_temp_variable1
FROM
(SELECT
*
FROM
TABLE ( SA_SEND_EMAIL (
CURSOR ( -- ETC

So, my question is: How can I get OWB to generate code that makes the mapping run?

Thanks for your time & answer,

Greetz,

Rob

Posted by rob heinen on February 27, 2012 at 10:12 PM PST #

I have a serious question on this topic. The question is, however, marked as spam by this site. Could anyone with in depth knowledge about table functions as target contact me at rob.heinen@aon.nl

Posted by rob heinen on February 27, 2012 at 10:14 PM PST #

Hi Rob

If you create your return type as;
CREATE OR REPLACE TYPE tbl_of_strings AS TABLE OF VARCHAR2(4000);

and use this in your table function you should be able to use it. Also use 'Return Table Of Scalar box unchecked' and you should be OK.

For example...the following should work within the SCOTT schema;
CREATE OR REPLACE TYPE tbl_of_strings AS TABLE OF VARCHAR2(4000);
/

create or replace function TF_TGT(RC IN SYS_REFCURSOR
) return TBL_OF_STRINGS PIPELINED
as
PRAGMA AUTONOMOUS_TRANSACTION;
DEPTNO number;
SALARY number;
begin
loop
FETCH RC into DEPTNO,SALARY;
NULL;
EXIT WHEN RC%NOTFOUND;
end loop;
COMMIT;
PIPE ROW('SUCCESS');
end;
/

SELECT "TF_TGT"."COLUMN_VALUE"
FROM TABLE ( TF_TGT (CURSOR (select deptno,sal from emp) )) TF_TGT;

Cheers
David

Posted by David on February 28, 2012 at 09:18 AM PST #

Typo in above ... I meant to say 'Return Table Of Scalar box CHECKED' and you should be OK.

Posted by David on February 29, 2012 at 09:19 AM PST #

I have to make a correction on my previous post. The error is generated by the utl_mail.send_attach_raw routine. The attachment is defined as a blob. I've also tried to define it as a long raw. Is there a size limit to input?

Cheers Rob

Posted by rob heinen on March 28, 2012 at 08:56 PM PDT #

I'm using a table function as the last stage in a mapping. The purpose of this mapping is to send an email. The email is in html format.

Because the content of the email is rather large I can't send it using the utl_mail.send procedure but have to make use of the utl_mail.send_attach_raw procedure. To this end I gather data and store them in a clob. next I convert the clob to a blob( or long raw ). This this generates a numeric or value error. See the example routine below.

Is it something I did? Is there another way to accomplish my goal, that you know of?

p.s. the error message generated:

ORA-06502: PL/SQL: numeric or value error at convert_v_clob_to_blob
ORA-06502: PL/SQL: numeric or value error at convert_v_clob_to_long_raw

p.p.s.s. the emails are all sent, but only the email sent after the varchar2 conversion has the requested attachment.

Greetings,

Rob

declare

v_clob clob;
v_blob blob;
v_long_raw long raw;
v_varchar2 varchar2(45) := 'hello email';

procedure get_lots_of_data
is

begin
for i in 1..40000
loop
v_clob := v_clob||'q';
end loop;
exception
when others then
dbms_output.put_line( sqlerrm||' at get_lots_of_data' );
end get_lots_of_data;

procedure convert_v_clob_to_blob
is

begin
v_blob := null;
v_blob := utl_raw.cast_to_raw( v_clob );
exception
when others then
dbms_output.put_line( sqlerrm||' at convert_v_clob_to_blob' );
end convert_v_clob_to_blob;

procedure convert_v_clob_to_long_raw
is

begin
v_long_raw := null;
v_long_raw := utl_raw.cast_to_raw( v_clob );
exception
when others then
dbms_output.put_line( sqlerrm||' at convert_v_clob_to_long_raw' );
end convert_v_clob_to_long_raw;

procedure convert_varchar2_to_blob
is

begin
v_blob := null;
v_blob := utl_raw.cast_to_raw( v_varchar2 );
exception
when others then
dbms_output.put_line( sqlerrm||' at convert_varchar2_to_blob' );
end convert_varchar2_to_blob;

procedure send_email_v_blob
is

begin
utl_mail.send_attach_raw ( sender => 'tdwh3'
, recipients => 'rob.heinen@aon.nl'
, subject => 'test v_blob'
, message => 'message'
, mime_type => 'text/html; charset=us-ascii'
, attachment => v_blob
, att_mime_type => 'text/html; charset=us-ascii'
, att_filename => to_char( sysdate , 'yyyymmddhh24miss' )||chr(95)||'email_v_two.htm' );

exception
when others then
dbms_output.put_line( sqlerrm||' at send_email_v_blob' );
end send_email_v_blob;

procedure send_email_v_long_raw
is

begin
utl_mail.send_attach_raw ( sender => 'tdwh3'
, recipients => 'rob.heinen@aon.nl'
, subject => 'test v_long_raw'
, message => 'message'
, mime_type => 'text/html; charset=us-ascii'
, attachment => v_long_raw
, att_mime_type => 'text/html; charset=us-ascii'
, att_filename => to_char( sysdate , 'yyyymmddhh24miss' )||chr(95)||'email_v_three.htm' );

exception
when others then
dbms_output.put_line( sqlerrm||' at send_email_v_long_raw' );
end send_email_v_long_raw;

procedure send_email_v_varchar2
is

begin
utl_mail.send_attach_raw ( sender => 'tdwh3'
, recipients => 'rob.heinen@aon.nl'
, subject => 'test v_varchar2'
, message => 'message'
, mime_type => 'text/html; charset=us-ascii'
, attachment => v_blob
, att_mime_type => 'text/html; charset=us-ascii'
, att_filename => to_char( sysdate , 'yyyymmddhh24miss' )||chr(95)||'email_v_three.htm' );

exception
when others then
dbms_output.put_line( sqlerrm||' at send_email_v_varchar2' );
end send_email_v_varchar2;

begin
get_lots_of_data;
convert_varchar2_to_blob;
send_email_v_varchar2;
convert_v_clob_to_blob;
send_email_v_blob;
convert_v_clob_to_long_raw;
send_email_v_long_raw;
end;

Posted by rob heinen on March 28, 2012 at 10:40 PM PDT #

Hi Rob

Did you resolve this? Wondering if the latest patched Oracle version would help, when I tried your code on 11.2.0.3 I didn't get any error.

Cheers
David

Posted by David on April 03, 2012 at 04:15 AM PDT #

Is there another variable declared as input in the utl_mail.send_attach_raw package in Oracle 11?

Cheers,

Rob

Posted by rob heinen on April 09, 2012 at 05:47 PM PDT #

Hi Rob

Looks like a new 'REPLY_TO' parameter was added in 11g which has a default value of NULL.

Cheers
David

Posted by David on April 10, 2012 at 03:50 PM PDT #

Hi DAvid,

I solved the problem I had using utl_smtp. It's a bit more of a hassle, but it works, even using html, so I'm satisfied.

Cheers,

Rob

Posted by rob heinen on April 10, 2012 at 08:30 PM PDT #

It appears that Oracle Warehouse Builder has a problem importing table_functions into its repository. Trying to implement table functions in a package and subsequently import them in the owb repository doesn't help since owb doesn't recognize the functions - and owb doesn't correctly import packages on the whole. Has this problem been solved already?

Posted by rob heinen on June 28, 2012 at 01:16 AM PDT #

Hi Rob

Which specific problem do you see? Are you using the latest patched 11.2 release?

Cheers
David

Posted by David on June 29, 2012 at 08:32 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today