First Procedure - A little Help Please 2

[spence27]

Here is the proedure i am trying to get to run basically by passing a variable in it brings back any data matching it, but it just wont compile

create or replace
PROCEDURE VIEW_PRODUCTS_SOLD(
  Start_Date in date,
  Finish_Date in Date
) AS
BEGIN
    SELECT 
        PRODUCT_ID,
        CUSTOMER_ID,
        MADE_ON
    FROM PURCHASES_WITH_TIMESTAMP 
    WHERE MADE_ON between @Start_Date and @Finish_Date;
    
END VIEW_PRODUCTS_SOLD;

i get the following errors in the compiler log

Project: C:\Program Files\sqldeveloper\sqldeveloper\system\oracle.sqldeveloper.1.1.2.25.79\DefaultWorkspace\Project1.jpr
    PROCEDURE STORE.PROCEDURE1@Local Oracle - STORE
        Error(7,5): PL/SQL: SQL Statement ignored
        Error(12,27): PL/SQL: ORA-00936: missing expression

Any help would be really apreciated

Thanks#Spence

 

[johnherman]

I was not sure whether to post this, since it is not an answer. I notice you are comparing a TIMESTAMP (or so the column is labeled) with at DATE. Perhaps you need to use a TO_TIMESTAMP conversion on the dates, using 00:00:00 as the time for Start Date and 23:59:59 for the end date. Just a thought - I am not sure if Oracle will do the implicit conversion of data types for you.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[Dagon]

You are making the usual beginners' mistake of trying to write PL/SQL as if it is SQL. You cannot simply put a piece of SQL into the procedure and expect it to return results to the screen. It is not designed to work like that.

You must declare you SQL as a cursor and fetch into a set of variables declared at the start of the procedure.

 

[SantaMufasa]

Here is sample code, Spence, that includes Dagon's suggestion, above:

create or replace
PROCEDURE VIEW_PRODUCTS_SOLD(
  Start_Date in date,
  Finish_Date in Date
) AS
BEGIN
    for x in (SELECT PRODUCT_ID, CUSTOMER_ID, MADE_ON
                FROM PURCHASES_WITH_TIMESTAMP
               WHERE MADE_ON between Start_Date and Finish_Date) loop
        dbms_output.put_line(x.product_id||': '||x.customer_id||': '||x.made_on);
    end loop;
END VIEW_PRODUCTS_SOLD;
/

Procedure created.

SQL> set serveroutput on
SQL> exec view_products_sold (to_date('20-AUG-1992','dd-MON-yyyy'),to_date('02-SEP-1992','dd-MON-yyyy'))
30421: 205: 31-AUG-92
41010: 205: 31-AUG-92
50169: 205: 31-AUG-92
50417: 205: 31-AUG-92
50530: 205: 31-AUG-92
41100: 205: 31-AUG-92
40422: 205: 31-AUG-92
20108: 206: 01-SEP-92
20201: 206: 01-SEP-92

PL/SQL procedure successfully completed.
*************************************************************************************

Let us know if this is helpful.

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

 

[spence27]

Thats a lot slicker than i was able to put together, but they both give the same output when using the command line SQL-PLUS, though i think i might re-evaluate ;-) some of mine. I was just wondering why is there no output when using SQLDeveloper I get script succeded but no results? THe only time i see any output is in SQL-PLUS

Thanks#Spence

create or replace
procedure sp_getVolsOfProdsSoldInDates
( Startdate in date,
  Finishdate in date
) as
  CURSOR cv_volsofprodssoldindates IS
    SELECT PRODUCT_ID, CUSTOMER_ID, MADE_ON
    FROM PURCHASES_WITH_TIMESTAMP 
    WHERE MADE_ON between Startdate and Finishdate
    ORDER BY CUSTOMER_ID;
        
  --  declare the variables
  v_product_id  PURCHASES_WITH_TIMESTAMP.PRODUCT_ID%TYPE;
  v_customer_id PURCHASES_WITH_TIMESTAMP.CUSTOMER_ID%TYPE;
  v_made_on     PURCHASES_WITH_TIMESTAMP.MADE_ON%TYPE;
    
begin

  --  open the cursor
  OPEN cv_volsofprodssoldindates;
  
  LOOP
    --  fetch the rows from the cursor
    FETCH cv_volsofprodssoldindates
    INTO v_product_id, v_customer_id, v_made_on;

    -- exit the loop when there are no more rows, as indicated by
    -- the Boolean variable cv_product_cursor%NOTFOUND (= true when
    -- there are no more rows)
    EXIT WHEN cv_volsofprodssoldondate%NOTFOUND;

    -- use DBMS_OUTPUT.PUT_LINE() to display the variables
    DBMS_OUTPUT.PUT_LINE(
      'v_product_id = ' || v_product_id || ', v_customer_id = ' || v_customer_id ||', v_made_on = ' || v_made_on
    );

  END LOOP;
  
  --  close the cursor
  CLOSE cv_volsofprodssoldindates;
  
end sp_getVolsOfProdsSoldInDates;

 

[SantaMufasa]

Spence,

Sorry, I don't use SQL Developer, so I don't know the SQL Developer equivalent of SQL*Plus's "set serveroutput on". Isn't there a clue on SQL Developer's HELP menu?

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

 

[Beilstwh]

The manual for sql developer says to enable DBMS OUTPUT in the
DBMS OUTPUT PANE.

Bill
Oracle DBA/Developer
New York State, USA