Stored Procedures

[azizrasul]

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.

 

[xenou]

Access doesn't really have stored procedures. You would just write parameterized queries.

 

[azizrasul]

Would you be able to expand on that?

 

[xenou]

One example is a query written like this:
select * from table where ProductID = [Forms]![Form1]![txtID]

 

[azizrasul]

OK I understand what you are saying. However if 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.

 

[xenou]

Not sure. I've never used Oracle. At first glance, I'm not sure why you'd have a table name here. Stored Procedures aren't part of tables - they are objects in their own right. You call the stored procedure with it's own name. What is this proc supposed to do?

 

[azizrasul]

It returns some costings.

 

[xenou]

Is it supposed to return records, like a query? or a single value, more like a function?

Note this url says to use the Execute keyword with oracle procs:
http://plsql-tutorial.com/plsql-procedures.htm

that's how sql server would basically work as well. But it's very different if they procedure returns records (like a query) or if it returns out parameters (more like a function using byref variables).

 

[james_lankford]

and you have to do a pass-through query

open your query in design view

then in the Access menu it should say "Query Tools"
then find a menu item that says pass-through (it will have a little globe next to it
then you have to supply a connection string to the oracle database

then you should be able to call Oracle procedures

 

[azizrasul]

Thank you, will try that.