1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Variable question

Discussion in 'SQL PL/SQL' started by eulogix, Jul 3, 2012.

  1. eulogix

    eulogix Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Hey guys I'm trying to build a variable query but im getting Error 0RA-00933: SQL command not properly ended

    this is what I'm tryna put in
    Code (SQL):
    SELECT * FROM subject_area WHERE subject_name = &subject);
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Try removing ')' symbol in the query:
    like
    Code (SQL):
    SELECT * FROM subject_area WHERE subject_name = &subject;
     
    eulogix likes this.
  3. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi eulogix,

    You have missed one open brace at the end of SQL or you are not supposed to write braces.

    Code (Text):

    SELECT * FROM subject_area WHERE subject_name = ('&subject');

    SELECT * FROM subject_area WHERE subject_name = '&subject';
     
    You need to provide single quote for the parameter if it was character or string, if it was number you are not supposed to provide single quotes. After looking into that subject_name it seems to be like string, so if it is then you need to provide single quote for parameter as explained in above query.
     
    eulogix likes this.
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    And that still may return no results as the case of the text string may not match the case of the data:

    Code (SQL):
    SQL> accept job prompt 'Enter job description: '
    Enter job description: clerk
    SQL>
    SQL> SELECT *
      2  FROM emp
      3  WHERE job = '&job';
    OLD   3: WHERE job = '&job'
    NEW   3: WHERE job = 'clerk'
     
    no ROWS selected
     
    SQL>
    SQL> SELECT *
      2  FROM emp
      3  WHERE job = UPPER('&job');
    OLD   3: WHERE job = UPPER('&job')
    NEW   3: WHERE job = UPPER('clerk')
     
         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17-DEC-80        800                    20
          7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
          7900 JAMES      CLERK           7698 03-DEC-81        950                    30
          7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
    SQL>
     
    Bharat and eulogix like this.