procedure and parameter question. Not recognizing parameter

[daddypost]

I have the following the snippet of procedure the is using an existing table as a parameter, but every time I run it I get an error saying the "table doesn't exist" I guess it is not recognizing the parameter correctly. PLEASE HELP:


create or replace procedure nc_new_customer_analysis (v_new_customers string)

as




BEGIN





execute immediate 'truncate table nc_internet_sales';
execute immediate 'truncate table nc_broadcast_sales';
execute immediate 'truncate table nc_all_sales';


--***********************************************************************************************
--Breakdown of INTERNET ONLY New Customer Sales
--***********************************************************************************************
--CREATE TABLE nc_internet_sales AS (
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 :1 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"
GROUP BY nc.min_id' using v_new_customers;
commit;

 

[SantaMufasa]

Daddy,

At the time that you attempt to "CREATE or REPLACE" a procedure, all external objects (i.e., TABLEs, SYNONYMS, SEQUENCES, et cetera) to which your code refers must exist (even if your procedure executes EXECUTE IMMEDIATE commands that create the objects).

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

 

[carp]

If you truly have access to the table, then try fully qualifying (i.e., owner.table_name) the table name. If you try to truncate a synonym, you will typically get an error like this.

 

[daddypost]

The problem is in the insert. It isn't recognizing the parameter table? If I replace the :1 nc with nc_monthly_new_customers, the procedure runs fine. can you alias a parameter the way I did? of do I need to say "using v_new_customers nc?

 

[carp]

Try this:

create or replace 
procedure nc_new_customer_analysis (v_new_customers string)
as
BEGIN
execute immediate 'truncate table nc_internet_sales';
execute immediate 'truncate table nc_broadcast_sales';
execute immediate 'truncate table nc_all_sales';
--***********************************************************************************************
--Breakdown of INTERNET ONLY New Customer Sales
--***********************************************************************************************
--CREATE TABLE nc_internet_sales AS (
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  '||v_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"
GROUP BY nc.min_id';
commit;
END nc_new_customer_analysis;

Let us know if that works for you. If not, please let us know what/where the error is.

 

[daddypost]

Well, I no longer get that the table doesn't exist, but now I get that "X" is an invalid identifier???????? Even when I take the variable table name out, and replace it with a specific existing table, I get the invalid identifier?? If I just take the insert statement and paste it to a new window, take the single quotes infront of select and after nc_min_id out, then replace the double quotes with single inside the where clause, it will work fine. But as soon as i try to run it through sql plus, it errors out. The procedure is written JUST AS ABOVE. Thank you all for your help. Hopefully this is an easy fix.

 

[SantaMufasa]

Daddy,

In straight SQL code in Oracle, you must use single quotes to surround literals. (In your code, above, the double quotes appear to be surrounding literals, which should, therefore, appear inside of single quotes.)

If you are testing the above code in SQL*Plus, and if you continue to experience errors, please copy and paste into a reply, the actual screen content that contains both your code and the the error messages that appear as a result of the code.

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

 

[daddypost]

When I try to compile the procedure with single quotes around INET,O, and X the error I recieve is :

PLS-00103 Encountered the symbol "INET" when expecting one of the following *&=-+;</> at in is mod remainder not rem ......

I can't compile the proc with single quotes INSIDE the single quotes for the execute immediate. If I use double quotes I can compile, but when I run "execute nc_new_customer_analysis" in PL/SQL that's when I get :

SQL> execute nc_new_customer_analysis
BEGIN nc_new_customer_analysis; END;

*
ERROR at line 1:
ORA-00904: "X": invalid identifier
ORA-06512: at "MICCOM1.NC_NEW_CUSTOMER_ANALYSIS", line 23
ORA-06512: at line 1


Thanks again. I'm stump

 

[SantaMufasa]

Okay, the problem is that your beginning quote ("execute immediate 'insert...") terminates with one of the single quotes that begins one of your literals.

Therefore, to begin an Oracle SQL literal (which must begin with a single quote, but which mus not terminate the beginning single quote for the "execute immediate", you must use two successive single quotes to create a solitary single quote within a literal in Oracle. Therefore, your code should read something like:

execute immediate 'insert into...
...
o.order_type = ''O'' AND
      o.ostatus <> ''X''...

Notice that the characters surrounding both the O and the X are two successive single quotes, ('') not double quotes (").

Let us know if this helps.


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