Home » SQL & PL/SQL » SQL & PL/SQL » SP : expression can't be used as assignment target (Oracle 10g, windows server 2003)
SP : expression can't be used as assignment target [message #350547] Thu, 25 September 2008 06:29 Go to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

Hi i am trying to create a simple procedure but getting following error.

  1  CREATE OR REPLACE PROCEDURE PURGE_DATA
  2  (
  3             CutOffDate      in      timestamp := NULL,      -- In Parameter
  4     StartDate               timestamp,
  5     Now                     timestamp
  6  )
  7  AS
  8  BEGIN
  9             StartDate := Now;
 10* END PURGE_DATA;


error :

SQL> show error;
Errors for PROCEDURE PURGE_DATA:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/5      PL/SQL: Statement ignored
9/5      PLS-00363: expression 'STARTDATE' cannot be used as an assignment
         target
Re: SP : expression can't be used as assignment target [message #350548 is a reply to message #350547] Thu, 25 September 2008 06:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
By default, any parameter listed in a parameter declaration is anIN parameter.

You cannot assign a new value to an IN parameter.

Re: SP : expression can't be used as assignment target [message #350549 is a reply to message #350547] Thu, 25 September 2008 06:42 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

ok but then what should be the parameter type for StartDate and Now variable.

and i want to assign a value to CutOffDate if user does not pass any value as an in parameter.

in that case the value of CutOffDate will be (sysdate - 6 months)
Re: SP : expression can't be used as assignment target [message #350553 is a reply to message #350549] Thu, 25 September 2008 06:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think you need to read the Pl/Sql Fundamentals.

Assuming (and you are far from clear in your requirements) that you want a procedure with a single In parameter, called Cutoffdate that defaults to sysdate-6 months, your code should look like this:
  CREATE OR REPLACE PROCEDURE PURGE_DATA(
             CutOffDate in  timestamp DEFAULT systimestamp - interval '6' month) AS
    StartDate               timestamp,
    Now                     timestamp
  BEGIN
    StartDate := Now;
  END PURGE_DATA;
Re: SP : expression can't be used as assignment target [message #350554 is a reply to message #350549] Thu, 25 September 2008 06:49 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OUT, in this example.

Maybe you should read PL/SQL User's Guide and Reference

Regards
Michel
Previous Topic: What is this technique called????
Next Topic: table UPDATE with ANALYTIC function
Goto Forum:
  


Current Time: Sat Dec 03 16:22:44 CST 2016

Total time taken to generate the page: 0.07704 seconds