Home » SQL & PL/SQL » SQL & PL/SQL » PROBEL WITH RPOCEDURE
PROBEL WITH RPOCEDURE [message #206565] Thu, 30 November 2006 09:47 Go to next message
amar.reddy
Messages: 2
Registered: November 2006
Location: hyderabad
Junior Member

This is a procedure for what the weekly_items_sold should be displayed in a month.

The procedure is compiling but not executing.

Brif information about data base tables

SELL_THRU_STG ( TRANSACTION TABLE)
STILA_PRODUCTS(MASTER TABLE)
THE CONDITION IS
SELL_THRU_STG.SKU=STILA_PRODUCTS.PRODUCT_CODE
OK BUT I AM GETTING RUN TIME ERROR FOR THIS PROCEDURE
PLS ANY ONE CAN FIND

CREATE OR REPLACE PROCEDURE WEEKLY_UNITS_SOLD_PROCEDURE IS
/*
-- Author : Amar Nath Reddy
-- Component Id : SAMPLE_WEEK_PROC
-- Description :
-- Parameters :
-- Name :
-- Type :
-- Datatype :
-- Required :
-- Format :
-- Purpose :
--
-- Name :
-- Type :
-- Datatype :
-- Required :
-- Format :
-- Purpose :
--
-- Name :
-- Type :
-- Datatype :
-- Required :
-- Format :
-- Purpose :
--
--
*/

V_PRODUCT_CODE VARCHAR2(77);
V_UNITS_SOLD NUMBER(8);
V_WEEK NUMBER(2);
LV_COUNT NUMBER(2);
CURSOR WEEK_SOLD IS

SELECT
SP.PRODUCT_CODE,
SUM(STS.SHIPPED_QTY) AS UNITS_SOLD ,
TRUNC((STS.SHIPPED_DATE-NEXT_DAY(TRUNC(STS.SHIPPED_DATE,'MM')-7,'MONDAY'))/7,0)+1 AS WEEK
FROM
SELL_THRU_STG STS,
STILA_PRODUCTS SP
WHERE
STS.SKU=SP.PRODUCT_CODE
AND
TO_CHAR(STS.SHIPPED_DATE,'MMYYYY')='022006'
GROUP BY
SP.PRODUCT_CODE,
TRUNC((STS.SHIPPED_DATE-NEXT_DAY(TRUNC(STS.SHIPPED_DATE,'MM')-7,'MONDAY'))/7,0)+1;

BEGIN
LV_COUNT:=0;
OPEN WEEK_SOLD;
FETCH WEEK_SOLD INTO
V_PRODUCT_CODE,
V_UNITS_SOLD,
V_WEEK;
while WEEK_UNITS_SOLD%found loop
FETCH WEEK_UNITS_SOLD INTO
V_PRODUCT_CODE,
V_UNITS_SOLD,
V_WEEK;
lv_line_count:=lv_line_count + 1;
END LOOP;
IF lv_line_count = 0 THEN
DBMS_OUTPUT.PUT_LINE('No Data Found.');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Error in PROCEDURE');
raise_application_error(-20001,'Error ');
END;



Re: PROBEL WITH RPOCEDURE [message #206566 is a reply to message #206565] Thu, 30 November 2006 10:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm afraid I don't believe that code compiles.
The variable lv_line_count is not declared anywhere.

Other than that, how would you ever tell if it did anything.
It just steps through a cursor continuously overwriting the same local variables, and would do a DBMS_OUTPUT if there were no lines (assuming lv_line_count were defined and initialised to 0 at some point in the code.)

Why don't you try again.
Re: PROBEL WITH RPOCEDURE [message #206567 is a reply to message #206565] Thu, 30 November 2006 10:02 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
The error message might be a nice place to start don't you think ? Oh wait, I don't see any error message posted
Re: PROBEL WITH RPOCEDURE [message #206588 is a reply to message #206565] Thu, 30 November 2006 11:19 Go to previous message
amar.reddy
Messages: 2
Registered: November 2006
Location: hyderabad
Junior Member

SORRY

IT IS NOT LV_LINE_COUNT IT IS JUST LV_COUNT, I MADE A SMALL MISTAKE.

IT IS WORKING NOW.

ok.



Previous Topic: Auto Number or unique identifier
Next Topic: Create sequence and trigger with nocache
Goto Forum:
  


Current Time: Wed Dec 07 08:45:18 CST 2016

Total time taken to generate the page: 0.08841 seconds