How to run a stored procedure (1 Viewer)

[aziz rasul]

I have never used or run a stored procedure before so hence I'm really stuck. All I have is an ORACLE expert who has given me the following cryptic clue

The call will be pm1_prices. get_cost_prices, and below is the list of parameters.

PROCEDURE get_cost_prices
(p_sp_id IN cost_items.fk_sp_id%TYPE,
p_effective_date IN DATE,
p_list_price OUT cost_items.monetary_value%TYPE,
p_lp_changed_on_this_date OUT BOOLEAN,
p_drop_discount_money OUT cost_items.monetary_value%TYPE,
p_drop_discount_pc OUT cost_items.percentage_value%TYPE,
p_dd_changed_on_this_date OUT BOOLEAN,
p_off_invoice_1_money OUT cost_items.monetary_value%TYPE,
p_off_invoice_1_pc OUT cost_items.percentage_value%TYPE,
p_oi1_changed_on_this_date OUT BOOLEAN,
p_off_invoice_2_money OUT cost_items.monetary_value%TYPE,
p_off_invoice_2_pc OUT cost_items.percentage_value%TYPE,
p_oi2_changed_on_this_date OUT BOOLEAN,
p_promo_off_invoice_money OUT cost_items.monetary_value%TYPE,
p_promo_off_invoice_pc OUT cost_items.percentage_value%TYPE,
p_poi_changed_on_this_date OUT BOOLEAN,
p_settlement_discount_money OUT NUMBER,
p_settlement_discount_pc OUT best_supp_discounts.discount%TYPE,
p_net_price OUT NUMBER,
p_cost_element_1_money OUT cost_items.monetary_value%TYPE,
p_cost_element_1_pc OUT cost_items.percentage_value%TYPE,
p_ce1_changed_on_this_date OUT BOOLEAN,
p_cost_element_2_money OUT cost_items.monetary_value%TYPE,
p_cost_element_2_pc OUT cost_items.percentage_value%TYPE,
p_ce2_changed_on_this_date OUT BOOLEAN,
p_retro_money OUT cost_items.monetary_value%TYPE,
p_retro_pc OUT cost_items.percentage_value%TYPE,
p_r_changed_on_this_date OUT BOOLEAN,
p_promo_retro_money OUT cost_items.monetary_value%TYPE,
p_promo_retro_pc OUT cost_items.percentage_value%TYPE,
p_pr_changed_on_this_date OUT BOOLEAN,
p_net_net_price OUT NUMBER,
p_bill_price OUT cost_items.monetary_value%TYPE,
p_bp_changed_on_this_date OUT BOOLEAN);

Can anyone give a simple step by step guide what to do? Thanks in advance.

 

[Ranman256]

in the query property, set
the' source database' of the stored proc.
and the 'source string connect'

 

[aziz rasul]

Would I be right to make the source database equal to pm1_prices. get_cost_prices? What would the source string connect typically look like for Oracle tables?

 

[aziz rasul]

I want to call an Oracle procedure with two parameters (an ID value and a date), I can't get that to work. Here's what I am trying in the SQL window

COST_ITEMS.GET_INVOICE_MATCH_COST 31590,TO_DATE('01-JAN-17', 'DD-MON-YY')

where COST_ITEMS is the table name, GET_INVOICE_MATCH_COST the procedure. I get invalid SQL statement.

Any ideas anyone?

 

[Minty]

I don't use Oracle but this lengthy thread has some code examples and explanations.
https://social.msdn.microsoft.com/F...tored-procedure-from-msaccess?forum=accessdev

I would recommend creating a pass-through query that works and simply call that - as the link demonstrates.

Read carefully about the connection string advice from Albert Kallal.

 

[aziz rasul]

Thanks, I will have a look.