Home » Other » Training & Certification » PLS-00363: expression 'xxx' cannot be used as an assignment target (merged)
PLS-00363: expression 'xxx' cannot be used as an assignment target (merged) [message #313499] Sun, 13 April 2008 04:01 Go to next message
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 #313500 is a reply to message #313499] Sun, 13 April 2008 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your second parameter p_password_txt is IN OUT and you gave a constant, how could a constant been asssigned?

Homework must be posted in Homework forum.

Regards
Michel
Re: PLS-00363: expression 'xxx' cannot be used as an assignment target [message #313504 is a reply to message #313500] Sun, 13 April 2008 04:25 Go to previous messageGo to next message
horax
Messages: 34
Registered: March 2008
Member
Thanks for moving this for me...I honestly didn't even know there was a homework section of this site.

So the assignment wants an IN OUT variable to hold the user's password (assigned to them) and returns the name of the person.

How could I do that if I don't assign the password properly?
Re: PLS-00363: expression 'xxx' cannot be used as an assignment target [message #313505 is a reply to message #313504] Sun, 13 April 2008 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How could I do that if I don't assign the password properly?

I don't understand the question.
For IN OUT parameter, you have to give a variable not a constant or an expression, even if it is not assigned in the procedure.

Regards
Michel
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 Go to previous messageGo to next message
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!
Re: PLS-00363: expression 'xxx' cannot be used as an assignment target [message #313514 is a reply to message #313507] Sun, 13 April 2008 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, use a variable.

Regards
Michel
Help me declare this variable please [message #313570 is a reply to message #313499] Sun, 13 April 2008 17:20 Go to previous messageGo to next message
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 #313572 is a reply to message #313570] Sun, 13 April 2008 17:26 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SQL*Plus is not equal to PL/SQL.
What is valid in one context, is not necessarily valid in the other context.

Why are you doing context switches between the two?
Re: Help me declare this variable please [message #313573 is a reply to message #313572] Sun, 13 April 2008 17:30 Go to previous messageGo to next message
horax
Messages: 34
Registered: March 2008
Member
Where do I do that at? Maybe that's my issue?
Re: Help me declare this variable please [message #313574 is a reply to message #313570] Sun, 13 April 2008 17:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided table DDL & sample data DML;
as directed in URL above.

What happens if solve the problem only using PL/SQL?
Re: Help me declare this variable please [message #313575 is a reply to message #313574] Sun, 13 April 2008 17:45 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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

Re: Help me declare this variable please [message #313608 is a reply to message #313575] Sun, 13 April 2008 23:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You need to either properly declare a SQL*Plus variable:

variable p_cookie varchar2

or properly declare a PL/SQL variable:

declare
p_cookie bb_shopper.cookie%type;
begin
...
end;
/

You cannot mix and match the syntaxes.

I did not check the rest of the code for errors.


Re: Help me declare this variable please [message #313637 is a reply to message #313570] Mon, 14 April 2008 00:43 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't need to start a new topic, you should continue on the previous one.

Regards
Michel
Previous Topic: Oracle Books
Next Topic: Schema Compilation
Goto Forum:
  


Current Time: Thu Mar 28 21:22:46 CDT 2024