Results 1 to 8 of 8

Thread: ACCEPT command

  1. #1
    Join Date
    Aug 2003
    Posts
    5

    Unanswered: ACCEPT command

    Trying to exe this statement in a sql file but can't

    ACCEPT dept PROMPT 'Provide the department name: '
    SELECT *
    FROM dept
    where dname = UPPER('&dept');
    /

    Tells me ORA-00900: invalid SQL statement

    When I run ACCEPT dept PROMPT 'Provide the department name: '
    alone, it works.

    However, when I include the rest of the statements, it doesn't work.

    Any idea, what's wrong?

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Are you trying to run all the statements together ???

    Have you tried saving all statements to a file and executing the file...

    sql> @c:\....\filename

    Gregg

  3. #3
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: ACCEPT command

    Originally posted by c3uc3
    Trying to exe this statement in a sql file but can't

    ACCEPT dept PROMPT 'Provide the department name: '
    SELECT *
    FROM dept
    where dname = UPPER('&dept');
    /

    Tells me ORA-00900: invalid SQL statement

    When I run ACCEPT dept PROMPT 'Provide the department name: '
    alone, it works.

    However, when I include the rest of the statements, it doesn't work.

    Any idea, what's wrong?
    Wouldn't it be:
    "ACCEPT &dept PROMPT 'Provide the department name: "
    instead?
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Use double quotes on your description ...

    ACCEPT dept PROMPT "Provide the department name: "
    SELECT *
    FROM dept
    where dname = UPPER('&dept');
    /

    Gregg

  5. #5
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Cool Question

    Hi
    Thanx for posting that question. That really adds something to my knowledge base after such a long time.
    I am persuing for OCA by next month end.
    Thanx and Regards
    Aruneesh

  6. #6
    Join Date
    Jun 2011
    Posts
    4

    need help

    Quote Originally Posted by gbrabham View Post
    Use double quotes on your description ...

    ACCEPT dept PROMPT "Provide the department name: "
    SELECT *
    FROM dept
    where dname = UPPER('&dept');
    /

    Gregg
    Hi,
    I have used a similar syntax as below:
    SQL> select * from person;

    LAST FIRST
    -------------------- --------------------
    baba saigal
    raju krishnam
    SQL> accept lastname prompt'please enter last name'
    please enter last nameraju
    SQL> select * from person where last = &lastname;
    SP2-0552: Bind variable "LASTNAME" not declared.
    But i recieve an error as bindvariable not declared.
    Please help me in this regard
    Thanks,
    Krishnam

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> desc emp
     Name					   Null?    Type
     ----------------------------------------- -------- ----------------------------
     EMPNO					   NOT NULL NUMBER(4)
     ENAME						    VARCHAR2(10)
     JOB						    VARCHAR2(9)
     MGR						    NUMBER(4)
     HIREDATE					    DATE
     SAL						    NUMBER(7,2)
     COMM						    NUMBER(7,2)
     DEPTNO 					    NUMBER(2)
     INSERT_DATE					    DATE
     DOB						    DATE
    
    SQL> help var
    
     VARIABLE
     --------
    
     Declares a bind variable that can be referenced in PL/SQL, or
     lists the current display characteristics for a single variable
     or all variables.
    
    VAR[IABLE] [variable [type]]
    
     where type represents one of the following:
    
         NUMBER         CHAR          CHAR (n [CHAR|BYTE])
         NCHAR          NCHAR (n)     VARCHAR2 (n [CHAR|BYTE])
         NVARCHAR2 (n)  CLOB          NCLOB
         REFCURSOR      BINARY_FLOAT  BINARY_DOUBLE
    
    
    SQL> var lastname varchar2
    SQL> accept lastname
    foobar
    
    SQL> select count(*) from emp where ename = '&lastname';
    old   1: select count(*) from emp where ename = '&lastname'
    new   1: select count(*) from emp where ename = 'foobar'
    
      COUNT(*)
    ----------
    	 0
    
    SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    @yelra05: you "forgot" to enclose the variable into single quotes. See this example and compare it to your attempt:
    Code:
    SQL> accept ename prompt 'Enter employee name: '
    Enter employee name: KING
    SQL>
    SQL> select empno, job, sal
      2  from emp
      3  where ename = '&ename';         --> note single quotes here
    
         EMPNO JOB              SAL
    ---------- --------- ----------
          7839 PRESIDENT       5800
    
    SQL>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •