PL-SQL delete value from variable

Question

In a PL SQL procedure I've a variable(declare as NUMBER) as follows,

Store_Values

I'm loading some data into this variable by select query.So if this data matches to the value(some value which will be compared against variable in if statement),then how to delete matched value from variable. below is the procedure:

CREATE OR REPLACE PROCEDURE UPDATE_SIG_NAME_MAPS(SignatureID NUMBER, RuleCateory VARCHAR2, SignatureMessage VARCHAR2, IsDelete NUMBER, IsNew NUMBER) AS
NumberOfValues      NUMBER ;
BEGIN
select VALUE BULK COLLECT into NumberOfValues from list_details where LIST_CONFIG_ID in(fetching from other table's);
if NumberOfValues = SignatureID then
**delete from sometable with where clause**
end if;
insert variable remaining values to the table using for loop
END ;
/

Reset the value of the varible to NULL once comaprision is over — XING
– XING, Commented May 30, 2017 at 11:59
What is the type of this variable? Please post a relevant part of code to better describe your need — Aleksej
– Aleksej, Commented May 30, 2017 at 12:08
No,i can't do that because i have to insert remaining values of variable into a table @XING — mayur
– mayur, Commented May 30, 2017 at 12:10
@mayur. Not so clear what are you looking or trying to achieve. Please elaborate on what is your actual requirement and that is the expected output — XING
– XING, Commented May 30, 2017 at 12:17
This code can not work. You are fetching into a scalar variable with a BULK COLLECT, which is used to fetch a list of values. — Aleksej
– Aleksej, Commented May 30, 2017 at 12:25

XING · Accepted Answer · 2017-05-30 12:33:26Z

As @Aleksej said, you are trying to fetch into a scalar variable using bulk collect so you need to declare your varible as below:

You can follow the instructions and modify your code accordingly.

CREATE OR REPLACE PROCEDURE UPDATE_SIG_NAME_MAPS (
   SignatureID         NUMBER,
   RuleCateory         VARCHAR2,
   SignatureMessage    VARCHAR2,
   IsDelete            NUMBER,
   IsNew               NUMBER)
AS
   Type NumberOfValue  is table of  NUMBER;
   NumberOfValues NumberOfValue;
BEGIN
   SELECT VALUE
     BULK COLLECT INTO NumberOfValues
     FROM list_details
    WHERE LIST_CONFIG_ID IN (fetching from other table's);

    For rec in 1..NumberOfValues.count 
    loop 
        if NumberOfValues(rec) = SignatureID  then
        **delete from sometable with where clause**

         insert variable remaining values to the table 
    end loop;

END ;

Collectives™ on Stack Overflow

PL-SQL delete value from variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related