3 Replies Latest reply: Jan 18, 2013 12:47 AM by XeM RSS

    how to select variable value in pl-sql

    983732
      Hi am a new bee for pl-sql basically am ms sql guy , am having the below query .



      declare
      user_name nvarchar2(1000) := ravi;
      select user_name ;

      if i execute below query i will get below error. please let me know what is the issue and solution for this.

      Error starting at line 18 in command:
      declare
      user_name nvarchar2(1000) := ravindra;
      select user_name ;
      Error report:
      ORA-06550: line 3, column 1:
      PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

      begin function package pragma procedure subtype type use
      <an identifier> <a double-quoted delimited-identifier> form
      current cursor
      06550. 00000 - "line %s, column %s:\n%s"
      *Cause:    Usually a PL/SQL compilation error.
      *Action:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
        • 1. Re: how to select variable value in pl-sql
          Sachinmrt
          Is it your full code???
          declare
          user_name nvarchar2(1000) := ravi;
          select user_name ;
          i don't thing so..

          Read the pl/sql books for knowledge basis.

          Right code is :-
          DECLARE
          V_USER_NAME VARCHAR2(1000) := 'RAVI';
          BEGIN
          SELECT V_USER_NAME INTO V_USER_NAME FROM DUAL;
          DBMS_OUTPUT.PUT_LINE(V_USER_NAME);
          END;
          • 2. Re: how to select variable value in pl-sql
            HamidHelal
            Hi, koteravindra

            Welcome to the Oracle Forums. Please take a few minutes to review the following:

            <ul>
            <li>Oracle Forums FAQ
            <li>Before posting on this forum please read
            <li>10 Commandments for the OTN Forums Member
            <li>How to ask questions the smart way
            </ul>

            Following these simple guidelines will ensure you have a positive experience in any forum; not just this one!

            Hi am a new bee for pl-sql basically am ms sql guy , am having the below query .



            declare
            user_name nvarchar2(1000) := ravi;
            select user_name ;

            if i execute below query i will get below error. please let me know what is the issue and solution for this.

            Error starting at line 18 in command:
            declare
            user_name nvarchar2(1000) := ravindra;
            select user_name ;
            You cannot write syntax in this way. You can use variable directly in your code or in select statement.
            for example
            declare
               numOfrecords number := 10;
               v_name varchar2(40);
            begin
               select name into v_name
              from customers2008 
            where rownum < numOfrecords;
            end;
            Always put your code in between
             tag.
            
            Hope this helps
            
            
            +*If someone's response is helpful or correct, please mark it accordingly.*+                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
            • 3. Re: how to select variable value in pl-sql
              XeM
              there is two portions of plsql code procedures

              declare
              var_1 datatype;
              var_2 datatype;
              var_n datatype;

              begin

              OTHER STATEMENTS goes here...
              .......

              end;

              in your case
              first declare variable of the type of value you want to return in it and note that varchar2(1000) is not correct i am not sure but think varchar2(400) is the max allocation.
              then in begin portion
              write your query: like

              select tbl_column into var_1 from your_tbl;

              and then end the procedure. Make sure that your query returns single value. For more than one value you will have to write explicit cursor.

              Hope it will help you.