asked on

calling a package function in oracle that has in and out parameter in a script

I have a package called
PKG_ESTIMATED_FREIGHT. This has a function called GET_ESTIMATED_FREIGHT
Function parameters are in_ship_id,in_recshp_id ( both are in parameters) one out parameter is out_method

I have a script and I need to call this function. How do I call that has an out parameter?

script is order_freight.

INSERT INTO adwaram.order_freight(ship_id,
estimated_freight
-
-
-)
values(in_ship_id,
,DSS.PKG_ESTIMATED_FREIGHT.GET_ESTIMATED_FREIGHT(NULL,c_inbound_rec.recshp_id,v_method)
-
-
-
-
)
v_method I have declared as varchar2(2000);

getting an error

ORA-06572: Function GET_ESTIMATED_FREIGHT has out arguments
ORA-06512: at line 111

ASKER CERTIFIED SOLUTION

Geert G

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ganesh Gurudu

light change in Greet code.

declare

  v_method varchar2(2000);
begin
  -- set value of c_inbound_rec.recship 
  -- set value of v_method
select DSS.PKG_ESTIMATED_FREIGHT.GET_ESTIMATED_FREIGHT(NULL,c_inbound_rec.recshp_id,v_method) from dual;
  INSERT INTO adwaram.order_freight(ship_id,estimated_freight) values (in_ship_id, v_method);
end;
/

Open in new window

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ganesh Gurudu

@slightwv
Thanks for the correction.

In a case this function will return 2 values ( 1 is return value and the out parameter)

fval := DSS.PKG_ESTIMATED_FREIGHT.GET_ESTIMATED_FREIGHT(NULL,c_inbound_rec.recshp_id,v_method);
and the insert should have v_method variable as per the requirement. if the user needs return value to insert then this is good.

INSERT INTO adwaram.order_freight(ship_id,estimated_freight) values (in_ship_id, v_method);

anumoses

ASKER

thanks