Table Types.

[James Good]

Hi!

Thank you for the great DOA lib. I have used it with great success for years now. However, I have some new functionality to implement on my app server, which I'm not sure I'm able to handle using DOA.

From my searching here, I don't think I can do this (since OCI doesn't support table types), but is it possible to call the create_quote function below? Specifically, is the l_quote_line_tbl parameter workable using DOA?

Thank you for any help you can give,

-James.

create or replace TYPE xxwfs_wqte_quote_line_rec_type AS OBJECT
(
line_number NUMBER , -- line_index
fueling_location_id NUMBER ,
etc etc etc
);

create or replace
TYPE xxwfs_wqte_quote_line_tbl_type AS TABLE OF xxwfs_wqte_quote_line_rec_type;

DECLARE
l_quote_header_rec xxwfs_wqte_quote_head_rec_type;
l_quote_line_rec xxwfs_wqte_quote_line_rec_type;
l_quote_line_tbl xxwfs_wqte_quote_line_tbl_type;
x_quote_header_id NUMBER := NULL;
x_quote_number NUMBER := NULL;
x_return_status VARCHAR2( 255 );
x_return_message VARCHAR2( 2048 );
BEGIN
l_quote_header_rec := xxwfs_wqte_quote_head_rec_type
(
1 , -- customer_account_id
2 , -- customer_site_id
'a' , -- customer_requested_by
'b' , -- notes
3 -- salesperson_id
);
l_quote_line_rec := xxwfs_wqte_quote_line_rec_type
(
1 , -- line_number
2 , -- fueling_location_id
'a' , -- flight_type
'b' , -- equipment_registration_number
'c' , -- equipment_category
SYSDATE , -- fueling_date_time
SYSDATE + 1 , -- arrival_date_time
SYSDATE + 2 , -- departure_date_time
3 , -- origin_location_id
4 , -- intermediate_location_id
'd' , --

etc, etc, etc
);
l_quote_line_tbl := xxwfs_wqte_quote_line_tbl_type( l_quote_line_rec );

xxwfs_webquote_quoting_wrap.create_quote
(
1251 , -- p_user_id
50500 , -- p_responsibility_id
697 , -- p_application_id
126 , -- p_ou_organization_id
l_quote_header_rec , -- p_wqte_quote_header_rec
l_quote_line_tbl , -- p_wqte_quote_line_tbl
x_quote_header_id , -- x_quote_header_id
x_quote_number , -- x_quote_number
x_return_status , -- x_return_status
x_return_message -- x_return_message
);

dbms_output.put_line( 'x_quote_header_rec = ' || nvl( to_char( x_quote_header_id ) , 'NULL' ) );
dbms_output.put_line( 'x_quote_number = ' || nvl( to_char( x_quote_number ) , 'NULL' ) );
dbms_output.put_line( 'x_return_status = ' || nvl( x_return_status , 'NULL' ) );
dbms_output.put_line( 'x_return_message = ' || nvl( x_return_message , 'NULL' ) );

END;

 

[James Good]

OK, I think I have the answer - I can't do what I wanted. I will have to change that 'TYPE xxwfs_wqte_quote_line_tbl_type AS TABLE OF xxwfs_wqte_quote_line_rec_type' into an Oracle Object...

Hmmm....

-James.

 

[Marco Kalter]

That is correct. The TOracleObject class allows you to access all object types, including collections.

 

[James Good]

Thank you, Marco. I think I have a solution now.

-James.

 

[James Good]

Hi Marco,

One of the elements in my Oracle Object is an Oracle DATE type. How do I get a date from my TDateTime in there, through SetAttr?

Thank you for your time,

-James.

 

[James Good]

OK, I guess SetAttr converts a TDateTime automatically... Sorry for yet another question!

-James.