declaration and scope of variable in procedure

[Shay1975]

Hi,
I am not very familiar with PL/SQL and packages so can someone tell me what is the problem with below?...the error it generates is something along the lines of "Ecnountere d the symbol "SELECT" when expecting one of the following...

Thanks in advance

PROCEDURE SET_USERPARAMET ER(USERID IN INTEGER, APPLICATIONID IN INTEGER,
PARAMNAME IN VARCHAR2, PARAMVALUE IN VARCHAR2) IS
BEGIN
DECLARE l_count NUMBER;

SELECT COUNT(*) INTO l_count
FROM LOGIN_USER_PARA METER
WHERE USER_ID = USERID
AND APPLICATION_ID = APPLICATIONID
AND PARAM_NAME = PARAMNAME;


IF l_count > 0 THEN
UPDATE LOGIN_USER_PARA METER
SET PARAM_VALUE = PARAMVALUE
WHERE USER_ID = USERID
AND APPLICATION_ID = APPLICATIONID
AND PARAM_NAME = PARAMNAME;
ELSE
INSERT INTO LOGIN_USER_PARA METER (USER_ID, APPLICATION_ID, PARAM_NAME, PARAM_VALUE)
VALUES (USERID, APPLICATIONID, PARAMNAME, PARAMVALUE);
END IF;
END;

[chandu031]

Hi,
I am not very familiar with PL/SQL and packages so can someone tell me what is the problem with below?...the error it generates is something along the lines of "Ecnountere d the symbol "SELECT" when expecting one of the following...

Thanks in advance

PROCEDURE SET_USERPARAMET ER(USERID IN INTEGER, APPLICATIONID IN INTEGER,
PARAMNAME IN VARCHAR2, PARAMVALUE IN VARCHAR2) IS
BEGIN
DECLARE l_count NUMBER;

SELECT COUNT(*) INTO l_count
FROM LOGIN_USER_PARA METER
WHERE USER_ID = USERID
AND APPLICATION_ID = APPLICATIONID
AND PARAM_NAME = PARAMNAME;


IF l_count > 0 THEN
UPDATE LOGIN_USER_PARA METER
SET PARAM_VALUE = PARAMVALUE
WHERE USER_ID = USERID
AND APPLICATION_ID = APPLICATIONID
AND PARAM_NAME = PARAMNAME;
ELSE
INSERT INTO LOGIN_USER_PARA METER (USER_ID, APPLICATION_ID, PARAM_NAME, PARAM_VALUE)
VALUES (USERID, APPLICATIONID, PARAMNAME, PARAMVALUE);
END IF;
END;

Hi,

The only problem with your code is that, you have declared the variable l_count inside the body of the procedure , when it has to be declared outside the body. That is declare the variable l_count before BEGIN.

Hope this helps...

[Shay1975]

Hi,

The only problem with your code is that, you have declared the variable l_count inside the body of the procedure , when it has to be declared outside the body. That is declare the variable l_count before BEGIN.

Hope this helps...

I moved the variable from the body of the package to the package spec (interface part) and it seems to be fine.

But does this mean the variable has global scrope? What if I only wanted to have the scope of the variable as the sproc? How would you delcare a "local" variable?

Thanks

[debasisdas]

Hi
To create a global variable it must bedeclared within the package specification.
and to create a local variable u can declare in the body (implementation ) part.

[vijayg14]

Hi,

The only problem with your code is that, you have declared the variable l_count inside the body of the procedure , when it has to be declared outside the body. That is declare the variable l_count before BEGIN.

Hope this helps...

Dear Shay,
After DECLARE, always use BEGIN for stating any code.
Ex:
DECLARE
variable datatype;
BEGIN
.......
......
END;

This will solve your problem

[amitpatel66]

Use this:

[code=oracle]

PROCEDURE SET_USERPARAMET ER(USERID IN INTEGER, APPLICATIONID IN INTEGER,
PARAMNAME IN VARCHAR2, PARAMVALUE IN VARCHAR2) IS
BEGIN
DECLARE l_count NUMBER;
BEGIN
SELECT COUNT(*) INTO l_count
FROM LOGIN_USER_PARA METER
WHERE USER_ID = USERID
AND APPLICATION_ID = APPLICATIONID
AND PARAM_NAME = PARAMNAME;

IF l_count > 0 THEN
UPDATE LOGIN_USER_PARA METER
SET PARAM_VALUE = PARAMVALUE
WHERE USER_ID = USERID
AND APPLICATION_ID = APPLICATIONID
AND PARAM_NAME = PARAMNAME;
ELSE
INSERT INTO LOGIN_USER_PARA METER (USER_ID, APPLICATION_ID, PARAM_NAME, PARAM_VALUE)
VALUES (USERID, APPLICATIONID, PARAMNAME, PARAMVALUE);
END IF;
END;
END;

[/code]

[leo7979]

amitpatel66 -> Muito bom. Respondeu de forma simples e completa. Obrigado!