| PLS-00363: expression 'xxx' cannot be used as an assignment target (merged) [message #313499] |
Sun, 13 April 2008 04:01  |
horax
Messages: 34
Registered: March 2008
|
Member |
|
|
First of all, I"m in my first PL/SQL class, and I'm supposed to be writing a code taht utilizes an IN OUT parameter to return an authenticated user by inputting their username and password.
Here is my code:
CREATE OR REPLACE PROCEDURE member_ck_sp
(p_username IN bb_shopper.username%type,
p_password_txt IN OUT varchar2,
p_check_txt OUT varchar2)
IS
lv_membername_txt VARCHAR2(30);
lv_firstname bb_shopper.firstname%type;
lv_lastname bb_shopper.lastname%type;
lv_cookie bb_shopper.cookie%type;
BEGIN
IF p_username='Crackj' AND p_password_txt='flyby'
THEN lv_firstname:='John ';
lv_lastname:='Carter';
lv_cookie:=1;
p_check_txt:='User Authenticated';
ELSIF p_username='MaryS' AND p_password_txt='pupper'
THEN lv_firstname:='Margaret ';
lv_lastname:='Somner';
lv_cookie:=1;
p_check_txt:='User Authenticated';
ELSIF p_username='rat55' AND p_password_txt='kile'
THEN lv_firstname:='Kenny ';
lv_lastname:='Ratman';
lv_cookie:=0;
p_check_txt:='User Authenticated';
ELSIF p_username='kids2' AND p_password_txt='steel'
THEN lv_firstname:='Camryn ';
lv_lastname:='Sonnie';
lv_cookie:=1;
p_check_txt:='User Authenticated';
ELSIF p_username='fdwell' AND p_password_txt='tweak'
THEN lv_firstname:='Scott ';
lv_lastname:='Savid';
lv_cookie:=1;
p_check_txt:='User Authenticated';
ELSIF p_username='gma1' AND p_password_txt='goofy'
THEN lv_firstname:='Monica ';
lv_lastname:='Cast';
lv_cookie:=1;
p_check_txt:='User Authenticated';
ELSIF p_username=' ' AND p_password_txt=' '
THEN lv_firstname:=NULL;
lv_lastname:=NULL;
lv_cookie:=0;
p_check_txt:='User Authenticated';
ELSE p_check_txt:='Invalid';
END IF;
P_PASSWORD_txt:=CONCAT(lv_firstname, lv_lastname);
DBMS_OUTPUT.PUT_LINE(P_PASSWORD_txt);
DBMS_OUTPUT.PUT_LINE(lv_cookie);
DBMS_OUTPUT.PUT_LINE(p_check_txt);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('User Does Not Exist');
END;
/
I then declare the variables:
SQL> variable p_password_txt VARCHAR2(10);
SQL> VARIABLE p_check_txt varchar2(10);
And I get the error:
SQL> execute member_ck_sp('kids2','steel',:p_check_txt);
BEGIN member_ck_sp('kids2','steel',:p_check_txt); END;
*
ERROR at line 1:
ORA-06550: line 1, column 28:
PLS-00363: expression 'steel' cannot be used as an assignment target
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
P_CHECK_TXT
--------------------------------
Invalid
EDIT: I forgot to add the detail fo the table the procedure gets the data types from.
SQL> desc bb_shopper;
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------
IDSHOPPER NOT NULL NUMBER(4)
FIRSTNAME VARCHAR2(15)
LASTNAME VARCHAR2(20)
ADDRESS VARCHAR2(40)
CITY VARCHAR2(20)
STATE CHAR(2)
ZIPCODE VARCHAR2(15)
PHONE VARCHAR2(10)
FAX VARCHAR2(10)
EMAIL VARCHAR2(25)
USERNAME VARCHAR2(8)
PASSWORD VARCHAR2(8)
COOKIE NUMBER(4)
DTENTERED DATE
PROVINCE VARCHAR2(15)
COUNTRY VARCHAR2(15)
PROMO CHAR(1)
Any ideas on why this is giving me this error?
Thanks.
[Updated on: Sun, 13 April 2008 04:07] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: PLS-00363: expression 'xxx' cannot be used as an assignment target [message #313507 is a reply to message #313505] |
Sun, 13 April 2008 04:32  |
horax
Messages: 34
Registered: March 2008
|
Member |
|
|
Let me try to explain the question so we can understnad this thing:
This is a program for the users to input their username and password for authentication purposes. I need to use an IN OUT parameter that accepts the password of the user (which is constant) and returns the full nmae of hte person that matches that password.
This is the part that's driving me crazy. Every user has one distinct password, but in order to utilize it as an IN OUT, it would need to NOT be a constant!
|
|
|
|
|
|
| Help me declare this variable please [message #313570 is a reply to message #313499] |
Sun, 13 April 2008 17:20 |
horax
Messages: 34
Registered: March 2008
|
Member |
|
|
Here is my code that I am having issues with. For the life of me, I can't seem to figure thsi basic step out.
CREATE OR REPLACE PROCEDURE member_ck_sp
(p_username IN bb_shopper.username%type,
p_password IN OUT varchar2,
p_cookie OUT bb_shopper.cookie%type,
p_check OUT varchar2)
IS
lv_shopperid bb_shopper.idshopper%type;
lv_firstname bb_shopper.firstname%type;
lv_lastname bb_shopper.lastname%type;
LV_USERNAME bb_shopper.username%type;
LV_PASSWORD bb_shopper.password%type;
BEGIN
SELECT firstname, lastname, idshopper, cookie, username, password
INTO lv_firstname, lv_lastname, lv_shopperid, p_cookie,lv_username, lv_password
FROM bb_shopper
WHERE p_username=username AND p_password=password;
IF p_username=lv_username AND p_password=lv_password THEN
p_password:=concat(lv_firstname, lv_lastname);
p_check:='User Verified';
ELSE p_check:='Invalid User';
END IF;
DBMS_OUTPUT.PUT_LINE(p_password);
DBMS_OUTPUT.PUT_LINE(p_cookie);
DBMS_OUTPUT.PUT_LINE(p_check);
END;
/
When executed with the following:
variable p_password varchar2;
variable p_cookie bb_shopper.cookie%type;
variable p_check varchar2;
execute member_ck_sp('rat55','kile',:p_cookie,:p_check);
I get the error stating that the bind variable "p_cookie" has not been defined.
P_cookie is to be returned from the procedure, and it comes from a table named bb_shopper, column named cookie, and has a set value.
Can you help me with this, oh wizened Oracle genii?
|
|
|
|
|
|
|
|
|
|
| Re: Help me declare this variable please [message #313575 is a reply to message #313574] |
Sun, 13 April 2008 17:45 |
horax
Messages: 34
Registered: March 2008
|
Member |
|
|
Oh sorry.
Here's the bb_shopper table:
SQL> desc bb_shopper;
Name Null? Type
----------------------------------------------------------------------------------- -------- ----------------
IDSHOPPER NOT NULL NUMBER(4)
FIRSTNAME VARCHAR2(15)
LASTNAME VARCHAR2(20)
ADDRESS VARCHAR2(40)
CITY VARCHAR2(20)
STATE CHAR(2)
ZIPCODE VARCHAR2(15)
PHONE VARCHAR2(10)
FAX VARCHAR2(10)
EMAIL VARCHAR2(25)
USERNAME VARCHAR2(8)
PASSWORD VARCHAR2(8)
COOKIE NUMBER(4)
DTENTERED DATE
PROVINCE VARCHAR2(15)
COUNTRY VARCHAR2(15)
PROMO CHAR(1)
And hte description fo the procedure:
SQL> desc member_ck_sp
PROCEDURE member_ck_sp
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_USERNAME VARCHAR2(8) IN
P_PASSWORD VARCHAR2 IN/OUT
P_COOKIE NUMBER(4) OUT
P_CHECK VARCHAR2 OUT
And the contents of bb_shopper (where I'm pulling the data from):
SQL> select * from bb_shopper;
IDSHOPPER FIRSTNAME LASTNAME ADDRESS CITY ST ZIPCODE PHONE FAX EMAIL USERNAME PASSWORD COOKIE DTENTERED PROVINCE COUNTRY P
---------- --------------- -------------------- ---------------------------------------- -------------------- -- --------------- ---------- ---------- ------------------------- -------- -------- ---------- --------- --------------- --------------- -
21 John Carter 21 Front St. Raleigh NC 54822 9014317701 Crackjack@aol.com Crackj flyby 1 13-JAN-07 USA
22 Margaret Somner 287 Walnut Drive Cheasapeake VA 23321 7574216559 MargS@infi.net MaryS pupper 1 03-FEB-07 USA
23 Kenny Ratman 1 Fun Lane South Park NC 54674 9015680902 ratboy@msn.net rat55 kile 0 26-JAN-07 USA
24 Camryn Sonnie 40162 Talamore South Riding VA 20152 7035556868 kids2@xis.net kids2 steel 1 19-MAR-07 USA
25 Scott Savid 11 Pine Grove Hickory VA 22954 7578221010 scott1@odu.edu fdwell tweak 1 19-FEB-07 USA
26 Monica Cast 112 W. 4th Greensburg VA 27754 7573217384 gma@earth.net gma1 goofy 1 09-FEB-07 USA
27 Pete Parker 1 Queens New York NY 67233 1013217384 spider@web.net 0 14-FEB-07 USA
7 rows selected.
SQL>
Sorry in advance for the sidescroll...
|
|
|
|
| Re: Help me declare this variable please [message #313576 is a reply to message #313570] |
Sun, 13 April 2008 17:52 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
http://www.orafaq.com/forum/t/88153/0/
This URL clear states the following:
Provide DDL (CREATE TABLE, etc.) as appropriate instead of listing the table structure. It'll help people setting up a test set (yes, some people really do that)
Provide INSERT statements for sample data instead of pasting in or mocking up the results of a SELECT statement.
It is obvious you don't know what either DDL or DML is.
Since you can NOT follow straight forward directions, You're On Your Own (YOYO)!
[Updated on: Sun, 13 April 2008 17:53] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
