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!

Help with PLSQL error

Discussion in 'SQL PL/SQL' started by jeffray, Aug 19, 2012.

  1. jeffray

    jeffray Guest

    Hi i am new to PLSQL and i tried writing a PLSQL code and executing it.

    CODE:
    -------

    create or replace procedure "GETCITY1"
    (a IN NUMBER,
    b OUT VARCHAR2)
    is
    begin
    Select city_name into b from city where city_id=a;
    end;
    /

    This is how i called the procedure .

    call getcity1(2,@b);
    select @b ;

    But i get the error "missing expression". Let me know what the issue could be.
     
  2. Bharat

    Bharat Community Moderator Forum Guru

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

    Please try to use the following lines to execute the procedure.

    :C is nothing but a bind variable. And print is going to print the output which is returned from the procedure.
     
  3. Bharat

    Bharat Community Moderator Forum Guru

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

    Have you got working of your procedure?
     
  4. durgareddygade

    durgareddygade Active Member

    Messages:
    38
    Likes Received:
    3
    Trophy Points:
    110
    Location:
    Guntur,Andhra Pradesh
    Hi first you need to create one variable to store the outparameter value.
    technically it is a bind variable.

    Go to sql* plus

    HTML:
    var x;
    so x variable is declared known as bind variable.
    after that call the procedure as bharat told

    HTML:
    exec getcity1(7788,:x);
    Regards,
    Durga
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    A bit more than just 'var x;' needs to be declared; a working example is shown below:

    Code (SQL):
    SQL> CREATE TABLE city(
      2          city_id NUMBER,
      3          city_name varchar2(40)
      4  );
     
    TABLE created.
     
    SQL>
    SQL> INSERT ALL
      2  INTO city
      3  VALUES (1, 'Boston')
      4  INTO city
      5  VALUES (2, 'San Francisco')
      6  INTO city
      7  VALUES (3, 'San Rafael')
      8  INTO city
      9  VALUES (4, 'San Diego')
     10  INTO city
     11  VALUES (5, 'San Simeon')
     12  INTO city
     13  VALUES (6, 'Kansas City')
     14  INTO city
     15  VALUES (7, 'St Louis')
     16  INTO city
     17  VALUES (8, 'Atlanta')
     18  INTO city
     19  VALUES (9, 'Seattle')
     20  INTO city
     21  VALUES (10, 'New York')
     22  SELECT * FROM dual;
     
    10 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> CREATE OR REPLACE PROCEDURE "GETCITY1"
      2  (a IN NUMBER,
      3          b OUT VARCHAR2)
      4  IS
      5  BEGIN
      6          SELECT city_name INTO b FROM city WHERE city_id=a;
      7  END;
      8  /
     
    PROCEDURE created.
     
    SQL>
    SQL>
    SQL> variable x varchar2(40)
    SQL>
    SQL> EXEC getcity1(7, :x)
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> print x
     
    X
    -----------------------------------------------------------------------
    St Louis
     
    SQL>
    Notice the variable also needs a data type and size (since it's a varchar2); simply declaring 'var x;' will result in an error:

    Code (SQL):
    SQL> var x;
    SP2-0552: Bind variable "x" NOT declared.
    SQL>
    It helps tremendously to test code before it's posted; I've said this more than once and still we see such examples that will produce errors. Please test your code BEFORE you offer it up to others.