CODEWITHSUNDEEP
oracle-databaseplsql
Nyyen8
Nyyen8

Reputation: 33

CONCAT two strings into one text variable PL/SQL SQL Developer

Using SQL Developer I'm attempting to CONCAT the firstname and lastname column values into lv_password text variable in PL/SQL, but failing so far. Code and error report is below. I've tried to format it in a variety of ways and it all seems to me to be withing guidelines, but I'm obviously doing something incorrectly.

CREATE OR REPLACE
PROCEDURE member_ck
    (lv_cust_id IN bb_shopper.username%TYPE,
    lv_password IN OUT VARCHAR,
    lv_cookie OUT bb_shopper.cookie%TYPE,
    p_check OUT VARCHAR2)
AS
BEGIN
    SELECT firstname || ' ' || lastname, cookie
    INTO lv_password, lv_cookie
    FROM bb_shopper
    WHERE UPPER(lv_cust_id) = UPPER(username)
    AND lv_password = password;
    DBMS_OUTPUT.PUT_LINE('Name: ' || lv_password || '    Cookie #: ' || lv_cookie);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    p_check := 'INVALID';    
    DBMS_OUTPUT.PUT_LINE('Incorrect login information');
END member_ck;

/

DECLARE 
    lv_cust_id bb_shopper.username%TYPE := 'rat55';
    lv_password bb_shopper.password%TYPE := 'kile';
    lv_cookie bb_shopper.cookie%TYPE;
    p_check VARCHAR(7); 
BEGIN
    member_ck(lv_cust_id, lv_password, lv_cookie, p_check);
END;

Error report

Error report -
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "ORA215.MEMBER_CK", line 8
ORA-06512: at line 7
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

Upvotes: 1

Views: 3429

Answers (3)

Bohemian
Bohemian

Reputation: 425033

Change

lv_password IN OUT VARCHAR -- max length dynamically set to length of input

To

lv_password IN OUT CLOB -- any length

Upvotes: 0

Nyyen8
Nyyen8

Reputation: 33

The issue was that I was implicitly setting my local variables, specifically the lv_password variable to the length of the input string in the anonymous code block below as pointed out by Littlefoot. The solution was to define variables in the anonymous code block of a much larger length so that they could receive a longer string than they input into the procedure. Corrected code is below.

CREATE OR REPLACE
PROCEDURE member_ck
    (lv_cust_id IN VARCHAR2,
    lv_password IN OUT VARCHAR2,
    lv_cookie OUT NUMBER,
    p_check OUT VARCHAR2)
AS
BEGIN
    SELECT (firstname|| ' ' || lastname), cookie
    INTO lv_password, lv_cookie
    FROM bb_shopper
    WHERE LOWER(lv_cust_id) = LOWER(username)
    AND lv_password = password;
    DBMS_OUTPUT.PUT_LINE('Name: ' || lv_password || '    Cookie #: ' || lv_cookie);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    p_check := 'INVALID';    
    DBMS_OUTPUT.PUT_LINE('Incorrect login information');
END member_ck;

/

DECLARE 
    lv_cust_id_1 VARCHAR2(255) := 'rat55';
    lv_cust_id_2 VARCHAR2(255) := 'Rat55';
    lv_cust_id_3 VARCHAR2(255) := 'rat';
    lv_password_1 VARCHAR2(255) := 'kile';
    lv_password_2 VARCHAR2(255) := 'steel';
    lv_cookie bb_shopper.cookie%TYPE;
    p_check VARCHAR2(7); 
BEGIN
    member_ck(lv_cust_id_1, lv_password_1, lv_cookie, p_check);
    lv_password_1 := 'kile';
    member_ck(lv_cust_id_2, lv_password_1, lv_cookie, p_check);
    lv_password_1 := 'kile';
    member_ck(lv_cust_id_3, lv_password_1, lv_cookie, p_check);
    member_ck('Kids2', lv_password_2, lv_cookie, p_check);
END;

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142713

  • lv_password is the 2nd parameter
  • you passed 'kile' as its value
  • as it is declared as IN OUT parameter whose datatype is VARCHAR2 (use VARCHAR2, not VARCHAR), its length is implicitly set to length('kile') = 4
  • when you tried to put concatenated value of the firstname and lastname into it, it is just too small to accept such a "long" value

If you test it as e.g.

SELECT substr(firstname || ' ' || lastname, 1, 5)  --> note 5

it will also fail. But,

SELECT substr(firstname || ' ' || lastname, 1, 4)  --> note 4

will succeed.

What to do? Don't use it as IN OUT parameter; have two of them - one IN, one OUT.

Upvotes: 1

Related Questions