Creating a veiw with two functions in the with clause fails in toad but not sqlplus

Toad for Oracle & DevOps Toolkit
1

CREATE OR REPLACE VIEW BROWN.BWNA_PRICE_CURRENT_ACCTG_V_lynn
( INVENTORY_ITEM_ID
, PRODUCT_CONTROL_CODE
, PRODUCT_NUMBER
, DESCRIPTION
, ITEM_STATUS
, GL_SEG
, BRNG_RETAIL
, BRNG_WHOLESALE
, BRNG_CLOSEOUT
, BCAN_RETAIL
, BCAN_WHOLESALE
, BRNG_ECOM
, OUTLET
, FLD_MED_EXCLU
, BRNG_EXPORT
, BRNG_INTERCOMPANY
, BRNG_MANAGER_APPROVAL
, BRNG_BCAN_TRANSFER
, BACO_BRNG_TRANSFER
, LAST_BRNG_WHOLESALE
, LAST_CANADA_WHOLESALE
)
BEQUEATH DEFINER
AS
with function cur_price_date(p_item_id in number, p_num in number, p_char in varchar) return number is
begin
return (to_number(brown.bwna_misc_pkg.current_price_date_f(p_item_id,p_num,p_char)));
end;
function latest_price (p_item_id in number, p_num in number ) return number is
begin
return (brown.bwna_misc_pkg.get_latest_price_f (p_item_id, p_num));
end;
select d.inventory_item_id
,d.attribute7 product_control_code
,d.segment1 product_number
,d.description
,d.inventory_item_status_code item_status
,d.attribute12 gl_seg
,cur_price_date(d.inventory_item_id,1171,'P') as brng_retail
,cur_price_date(d.inventory_item_id,1173,'P') as brng_wholesale
,cur_price_date(d.inventory_item_id,1064,'P') as brng_closeout
,cur_price_date(d.inventory_item_id,1165,'P') as bcan_retail
,cur_price_date(d.inventory_item_id,1167,'P') as bcan_wholesale
,cur_price_date(d.inventory_item_id,2566778,'P') as brng_ecom
,cur_price_date(d.inventory_item_id,47416,'P') as outlet
,cur_price_date(d.inventory_item_id,1224,'P') as FLD_MED_EXCLU
,cur_price_date(d.inventory_item_id,1169,'P') as BRNG_EXPORT
,cur_price_date(d.inventory_item_id,1184,'P') as BRNG_INTERCOMPANY
,cur_price_date(d.inventory_item_id,1170,'P') as BRNG_MANAGER_APPROVAL
,cur_price_date(d.inventory_item_id,1246,'P') as BRNG_BCAN_TRANSFER
,cur_price_date(d.inventory_item_id,1172,'P') as BACO_BRNG_TRANSFER
,latest_price(d.inventory_item_id, 1173) AS LAST_BRNG_WHOLESALE
,latest_price(d.inventory_item_id, 1167) AS LAST_CANADA_WHOLESALE
FROM apps.mtl_system_items_b d
WHERE d.organization_id = 109; fails in toad, works in sqlplus, also I can't select from it in toad

select * from BROWN.BWNA_PRICE_CURRENT_ACCTG_V_lynn; Ora-24374 is there a setting I need to change in toad ?

2

There is no setting in Toad that you need to make so your view will work.

Maybe your client or database version is relevant, or maybe the error is related to the data.

I just created this view with two functions and it works fine for me.

create or replace view double_func_test AS
  with 
  function num1(anum1 in number) return number is
  begin
    return anum1;
  end;
  function num2(anum2 in number) return number is
  begin
    return anum2;
  end;
select num1(1) as one,
       num2(2) as two
from dual;

image
image786×775 45.6 KB

3

hmm I am using the 23.26 instant client, it works using sqlplus 19.31 on the database server. My toad version is 25.3.346.7343. I will try and upgrade to see if that helps thanks

4

Try the view I made as a test before you bother to upgrade. I think it's data-related.

5

So with the update to 26.1.193.8012 I can create the view, but the select still fails ? looks like I can select from all but the last two columns, must be and error in the data or the function. Thanks

6

Well, I'm glad you are narrowing it down. I don't know why you weren't able to create the view with Toad 25.3. I fired up Toad 12.12 and could create my sample view no problem.

Toad just sends SQL to Oracle. Oracle executes it and returns ORA-##### errors if it has problems. Toad will never raise these errors on its own.

I think the problem must be something in data or the brown.bwna_misc_pkg.get_latest_price_f function, as you said.