Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql statement ignored
| pl/sql statement ignored [message #638884] |
Wed, 24 June 2015 09:54  |
 |
raja_rani
Messages: 5
Registered: June 2015
Location: oh
|
Junior Member |
|
|
Create Or Replace
PROCEDURE temp1 AS
BEGIN
Declare
Cursor Weekly_Sales_cur is Select Store, Sku, Sales_Date, Qty,Status From Tem_Weeklysales;
Cursor Ss_Can_Cur Is Select To_Sku, From_Sku From Ss_Can;
Cursor Ss_Usa_Cur Is Select To_Sku, From_Sku From Ss_Usa;
Cursor Skus_cur Is Select Sku From Skus;
--variable declaration
V_Sku_No Varchar2(9);
V_Store_No Varchar2(4);
V_Sales_Date DATE;
v_QTY VARCHAR2(10);
V_Status Varchar2(2);
v_errorcode NUMBER;
V_Errortext Varchar2(200);
Begin
For Sales_Rec In Weekly_Sales_Cur
Loop
V_Sku_No := Sales_Rec.Sku;
V_Store_No := Sales_Rec.Store;
V_Sales_Date :=Sales_Rec.Sales_Date;
V_Qty := Sales_Rec.Qty;
V_Status := Sales_Rec.Status;
Dbms_Output.Put_Line(V_Sku_No||' '||V_Store_No||' '||V_Sales_Date||' '||V_Qty||' '||V_Status);
IF V_Status = 'A' then
IF V_Sku_No = Ss_Can_Cur.From_Sku THEN
INSERT
INTO Tem_Weeklysales VALUES
(
Ss_Can_Cur.To_Sku,
V_Store_No,
V_Sales_Date,
v_QTY
);
ElsIF V_Sku_no = Ss_Can_Cur.To_Sku THEN
INSERT
INTO Tem_Weeklysales VALUES
(
V_Sku_No,
V_Store_No,
V_Sales_Date,
v_QTY
);
ELSIF V_Sku_no = Ss_Can_Cur.Skus_Cur THEN
INSERT
INTO Tem_Weeklysales VALUES
(
V_Sku_No,
V_Store_No,
V_Sales_Date,
V_Qty
);
END IF;
ElsIF V_Sku_no = Ss_Usa_Cur.From_Sku THEN
INSERT
INTO Tem_Weeklysales VALUES
(
Ss_Usa_Cur.To_Sku,
V_Store_No,
V_Sales_Date,
v_QTY
);
ELSIF V_Sku_no = Ss_Usa_Cur.To_Sku THEN
INSERT
INTO Tem_Weeklysales VALUES
(
V_Sku_No,
V_Store_No,
V_Sales_Date,
v_QTY
);
ELSIF V_Sku_no = Ss_Usa_Cur.Skus_Cur THEN
INSERT
INTO Tem_Weeklysales VALUES
(
V_Sku_No,
V_Store_No,
V_Sales_Date,
v_QTY
);
End If;
End Loop;
End;
END temp1;
I am getting errors
Error(32,2): PL/SQL: Statement ignored
Error(34,2): PL/SQL: Statement ignored
Error(34,27): PLS-00225: subprogram or cursor 'SS_CAN_CUR' reference is out of scope
Error(62,30): PLS-00225: subprogram or cursor 'SS_USA_CUR' reference is out of scope
please let me know what I am doing wrong .
Thanks in advance.
Rani
*BlackSwan added {code} tags. Please do so yourself in the future.
http://www.orafaq.com/forum/t/174502/
[Updated on: Wed, 24 June 2015 10:06] by Moderator Report message to a moderator |
|
|
|
| Re: pl/sql statement ignored [message #638885 is a reply to message #638884] |
Wed, 24 June 2015 10:00  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Welcome to the forum.
Please read and follow How to use [code] tags and make your code easier to read?
At the beginning of the procedure you're doing a loop over a cursor which fetches the data into a record variable.
You're then using the record variable to do other things - which is correct.
So why do you think you can refer to cursors as they they are record variables - they're not.
You need to fetch the data from the other cursors into variables and then do checks against the variables.
|
|
|
|
| Re: pl/sql statement ignored [message #638886 is a reply to message #638885] |
Wed, 24 June 2015 11:06 |
|
raja_rani
Messages: 5
Registered: June 2015
Location: oh
|
Junior Member |
|
|
As you said I did the correction to my code thank you for the help, but still I am doing something wrong can you please let me know
create or replace
PROCEDURE temp1 AS
BEGIN
Declare
Cursor Weekly_Sales_cur is Select Store, Sku, Sales_Date, Qty, Status From Tem_Weeklysales;
Cursor Ss_Can_Cur Is Select To_Sku, From_Sku From Ss_Can;
Cursor Ss_Usa_Cur Is Select To_Sku, From_Sku From Ss_Usa;
Cursor Skus_cur Is Select Sku From Skus;
--variable declaration
V_Sku_No Varchar2(9);
V_Store_No Varchar2(4);
V_Sales_Date DATE;
v_QTY VARCHAR2(10);
V_Status Varchar2(2);
v_errorcode NUMBER;
V_Errortext Varchar2(200);
Begin
For Sales_Rec In Weekly_Sales_Cur
Loop
V_Sku_No := Sales_Rec.Sku;
V_Store_No := Sales_Rec.Store;
V_Sales_Date :=Sales_Rec.Sales_Date;
V_Qty := Sales_Rec.Qty;
Dbms_Output.Put_Line(V_Sku_No||' '||V_Store_No||' '||V_Sales_Date||' '||V_Qty||' '||V_Status);
If Sales_Rec.Status = 'A' Then
For Ss_Can_Rec In Ss_Can_Cur
Loop
IF Ss_Can_Rec.From_Sku = Sales_Rec.Sku THEN
INSERT
INTO Tem_Weeklysales VALUES
(
Ss_Can_Cur.To_Sku,
V_Store_No,
V_Sales_Date,
v_QTY
);
EXIT loop;
ELSIF Ss_Can_Cur.To_Sku = Sales_Rec.Sku THEN
INSERT
INTO Tem_Weeklysales VALUES
(
V_Sku_No,
V_Store_No,
V_Sales_Date,
v_QTY
);
Exit loop;
end if;
End Loop;
For Ss_Usa_Rec In Ss_Usa_Cur
Loop
IF Ss_Usa_Rec.From_Sku = Sales_Rec.Sku THEN
INSERT
INTO Tem_Weeklysales VALUES
(
Ss_Can_Cur.To_Sku,
V_Store_No,
V_Sales_Date,
v_QTY
);
EXIT loop;
ELSIF Ss_Usa_Rec.To_Sku = Sales_Rec.Sku THEN
INSERT
INTO Tem_Weeklysales VALUES
(
V_Sku_No,
V_Store_No,
V_Sales_Date,
v_QTY
);
EXIT loop;
End If;
End Loop;
For Skus_Rec In Skus_cur
Loop
if Skus_Rec.Sku = Sales_Rec.Sku THEN
INSERT
INTO Tem_Weeklysales VALUES
(
V_Sku_No,
V_Store_No,
V_Sales_Date,
V_Qty
);
EXIT loop;
End If;
End Loop;
End If;
End Loop;
END;
END temp1;
Error(33,14): PL/SQL: Statement ignored
Error(34,15): PL/SQL: SQL Statement ignored
Error(35,20): PL/SQL: ORA-00947: not enough values
Error(42,17): PL/SQL: Statement ignored
Error(42,22): PLS-00201: identifier 'LOOP' must be declared
Error(43,33): PLS-00225: subprogram or cursor 'SS_CAN_CUR' reference is out of scope
Error(58,15): PL/SQL: SQL Statement ignored
Error(59,20): PL/SQL: ORA-00947: not enough values
Error(66,17): PL/SQL: Statement ignored
Error(66,22): PLS-00201: identifier 'LOOP' must be declared
Error(68,17): PL/SQL: SQL Statement ignored
Error(69,22): PL/SQL: ORA-00947: not enough values
Error(76,19): PL/SQL: Statement ignored
Error(76,24): PLS-00201: identifier 'LOOP' must be declared
Error(83,7): PL/SQL: SQL Statement ignored
Error(84,12): PL/SQL: ORA-00947: not enough values
Error(91,9): PL/SQL: Statement ignored
Error(91,14): PLS-00201: identifier 'LOOP' must be declared
[EDITED by LF: fixed code tags]
[Updated on: Wed, 24 June 2015 13:56] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: pl/sql statement ignored [message #638888 is a reply to message #638887] |
Wed, 24 June 2015 13:23 |
|
raja_rani
Messages: 5
Registered: June 2015
Location: oh
|
Junior Member |
|
|
CREATE OR REPLACE
PROCEDURE temp1
AS
BEGIN
DECLARE
CURSOR Weekly_Sales_cur IS
SELECT Store, Sku, Sales_Date, Qty, Status FROM Tem_Weeklysales;
CURSOR Ss_Can_Cur IS
SELECT To_Sku, From_Sku FROM Ss_Can;
CURSOR Ss_Usa_Cur IS
SELECT To_Sku, From_Sku FROM Ss_Usa;
CURSOR Skus_cur
IS
SELECT Sku FROM Skus;
--variable declaration
V_Sku_No VARCHAR2(9);
V_Store_No VARCHAR2(4);
V_Sales_Date DATE;
V_QTY VARCHAR2(10);
V_Status VARCHAR2(2);
V_errorcode NUMBER;
V_Errortext VARCHAR2(200);
BEGIN
FOR Sales_Rec IN Weekly_Sales_Cur
LOOP
V_Sku_No := Sales_Rec.Sku;
V_Store_No := Sales_Rec.Store;
V_Sales_Date :=Sales_Rec.Sales_Date;
V_Qty := Sales_Rec.Qty;
Dbms_Output.Put_Line(V_Sku_No||' '||V_Store_No||' '||V_Sales_Date||' '||V_Qty||' '||V_Status);
IF Sales_Rec.Status = 'A' THEN
FOR Ss_Can_Rec IN Ss_Can_Cur
LOOP
IF Ss_Can_Rec.From_Sku = Sales_Rec.Sku THEN
INSERT
INTO Tem_sel_sales VALUES
(
Ss_Can_Rec.To_Sku,
V_Store_No,
V_Sales_Date,
v_QTY
);
EXIT;
ELSIF Ss_Can_Cur.To_Sku = Sales_Rec.Sku THEN
INSERT
INTO Tem_sel_sales VALUES
(
V_Sku_No,
V_Store_No,
V_Sales_Date,
v_QTY
);
EXIT;
END IF;
END LOOP;
FOR Ss_Usa_Rec IN Ss_Usa_Cur
LOOP
IF Ss_Usa_Rec.From_Sku = Sales_Rec.Sku THEN
INSERT
INTO Tem_sel_sales VALUES
(
Ss_Usa_Rec.To_Sku,
V_Store_No,
V_Sales_Date,
v_QTY
);
EXIT;
ELSIF Ss_Usa_Rec.To_Sku = Sales_Rec.Sku THEN
INSERT
INTO Tem_sel_sales VALUES
(
V_Sku_No,
V_Store_No,
V_Sales_Date,
v_QTY
);
EXIT;
END IF;
END LOOP;
FOR Skus_Rec IN Skus_cur
LOOP
IF Skus_Rec.Sku = Sales_Rec.Sku THEN
INSERT
INTO Tem_sel_sales VALUES
(
V_Sku_No,
V_Store_No,
V_Sales_Date,
V_Qty
);
EXIT;
END IF;
END LOOP;
END IF;
END LOOP;
END;
END temp1;
Error(34,11): PL/SQL: Statement ignored
Error(44,30): PLS-00225: subprogram or cursor 'SS_CAN_CUR' reference is out of scope
Sorry I am first time user.Thank you I have updated
* BlackSwan corrected {code} tags
* LF did the same
[Updated on: Wed, 24 June 2015 13:57] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 05:44:36 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
