quick question about passing parameters to multiple procedures

[daddypost]

OK. I've got an initial procedure that can be passeed an optional date parameter. I need those parameters to be able to be passed to a procedure that is called within the first procedure. Then I need those to be passed again to a third procedure which is called from the second. Make since?

So I have this sqlplus statement:
execute nc_begin-new_customer_process('01 jan 2007','01 feb 2007)

So that query looks like this:
create or replace procedure nc_begin-new_customer_process (begin_date date,end_date date)

is

BEGIN
blah blah blah...(right after all this is done I need to call a second procedure called nc_new_customer_alaysis
using the begin_date, and end_date passed above. I tried using

execute immediate 'nc_new_customer_analysis ('||start_date||','||end_date||')';

It compiles, but it doesn't work. What am I doing wrong?
The ('||start_date||','||end_date||')' parameter will be used in a query that looks like this.


execute immediate 'insert into nc_internet_sales
SELECT /*+ ORDERED USE_NL(O L) INDEX(L) */
nc.min_id,
COUNT(DISTINCT o.order_no) orders_inet,
COUNT(DISTINCT DECODE(NVL(o.order_ref, 0 ),0, o.order_no,o.order_ref)) carts_inet,
MIN(o.order_date) first_order_inet,
MAX(o.order_date) last_order_inet,
SUM(l.uprice*(l.qty-NVL(l.qty_returned, 0))) gross_sales_inet,
SUM((CASE
WHEN l.litem_id = 1
THEN o.shipping
ELSE 0
END)) shipping_inet,
SUM(l.qty)
qty_ordered_inet,
SUM(NVL(l.qty_returned,0)) qty_returned_inet
FROM nc_monthly_new_customers nc,
acntv.orders o,
acntv.litem l
WHERE nc.cust_id = o.cust_id AND
o.order_no = l.order_no AND
miccom1.selling_channel(o.batch, o.ctype_id) = ''INET'' AND
o.order_type = ''O'' AND
o.ostatus <> ''X'' and
nc.order_date between to_date ('||start_date||',''mm/dd/yyyy HH12:MI:SS AM'') and to_date ('||end_date||',''mm/dd/yyyy HH12:MI:SS AM'');

GROUP BY nc.min_id';
commit;



I know this has to be confusing but I'm sure there is a simple way to do what I want to have happen. If you can decipher all this, I would appreciate any help you can give.
Thank you.

 

[LKBrwnDBA]

DO NOT CONCATENATE QUOTES:

('||start_date||','||end_date||')

JUST DO:

(start_date,end_date)





----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

And no need to use execuet immediate:

nc_new_customer_analysis (start_date,end_date);

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[daddypost]

Here is my first query. When I run it I get an error saying end date invalid identifier:

create or replace procedure nc_get_new_customers(start_date date DEFAULT trunc(sysdate-1),
end_date date DEFAULT trunc(sysdate))

IS


BEGIN


--***********************************************************************************************
--This will get every cust_id that had an order placed and not canceled in the designated month
--***********************************************************************************************
--CREATE TABLE nc_daily_orders AS (
execute immediate 'insert into nc_daily_orders
SELECT DISTINCT o.cust_id
FROM acntv.orders o
WHERE o.order_date BETWEEN start_date and end_date and
ostatus <> ''X'' AND
order_type = ''O''';
commit;

end;

 

[SantaMufasa]

Daddypost,

I believe that you may be getting tangled up in your knickers on this one. I'm sure we can help simplify things if you just take a moment to post to us a clear, functional (non-technical) description of what you are trying to do.

The "execute immediate" is unnecessary in this context, which, if you remove that clause, should simplify the issues that I notice you are having with quotes.

So, please just begin with a non-technical restatement of your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[daddypost]

okay.I have an initial procedure that will be started automatically as a scheduled job around 2:00am. It will need run automatically for the day prior. Within the initial procedure it will call a second procedure. and finally the second procedure will call a third procedure. I need to have the ability to run the procedure manually at any given time for any date range needed. So I need to be able to pass the initial parameters I input in the execute statment of SQLPLUS to the second procedure then have that query pass it to the third procedure.

My initial procedure starts with


create or replace procedure begin_new_customers(start_date date DEFAULT trunc(sysdate-1),
end_date date DEFAULT trunc(sysdate))

This calls a procedure like this
nc_get_new_customers(start_date, end_date)

that procedure looks like this
create or replace procedure nc_get_new_customers(start_date date DEFAULT trunc(sysdate-1),
end_date date DEFAULT trunc(sysdate))

and calls a third procedure
nc_new_customer_analysis(start_date,end_date)


I think the execute immediate is throwing me. I'm not sure when to use it and when you don't need to. The only reason I put it in there was because it seemed to work. And I'm also passing a table variable to the 3rd query and using the execute immediate was the advice that was given to me. Sorry if I am confusing. I have 500 lines of code and I'm trying to just summarize as best I can.
Iactually got the entire thing to compile and to run, but when I went back to check what had been inserted into the tables, everything was blank.
Figure I'm missing something with the date paramters.
Thank you for all your help.

 

[SantaMufasa]

Daddy,

You do not need EXECUTE IMMEDIATE for any SQL DML command. Specifically, SELECT, INSERT, UPDATE, DELETE, COMMIT, and ROLLBACK do not require EXECUTE IMMEDIATE execution. For other SQL statements, you can compose a statement that can run as the operand of an EXECUTE IMMEDIATE.

To isolate your specific issues, I recommend executing pieces of code to confirm valid behaviour. Then, once the components work indepentently, you can re-combine the components, a piece at a time, confirming the validity of the re-combined components.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]