7 Replies Latest reply: Dec 27, 2013 7:37 AM by malky RSS

    oracle variables for a select result

    malky

      Hi there, i have 4 very long queries (i only put 2 as sample) that returns 1 value per condition, so i wanted to save the result in a variable to be able to use it within other long queries, so i have the following:

       

      DECLARE
         vara NUMBER;
         varb NUMBER;     
      BEGIN
          SELECT firstvalue INTO vara FROM table1 where conditiona = (select conditiona from table2 where something = a) and rownum = 1;
          SELECT secondvalue INTO varb FROM table1 where conditionb = (select conditionb from table2 where something = b) and rownum = 1;
      END;
      select * from dual where value = :vara;
      select * from dual where value = :varb;
      

       

      but when i print vara and varb i get empty. I also tried moving it inside the begin end but i also get errors and my select * has many column so it seems cumbersome to declare each.

       

      What's the best way to do this? all i want is store a single value return by each of the queries and use it as condition in other subqueries.

       

      Thanks in advance,

        • 1. Re: oracle variables for a select result
          sb92075

          rows in a table are like balls in a basket.

          which ball in a basket is ball #1?

           

          you whole approach is flawed.

           

           

           

          How do I ask a question on the forums?

          https://forums.oracle.com/message/9362002#9362002

          • 2. Re: oracle variables for a select result
            malky

            Well of course it's flawed otherwise i wouldn't be here asking, would I?. If you're refering to the rownum, it is just to show that i'm retrieving 1 value, it doesn't matter what the actual value is, it's just the value i need as a condition, since i've read that if you have many you have to iterate and that's not my case, it's only one.

            • 3. Re: oracle variables for a select result
              JustinCave

              vara and varb are declared as local PL/SQL variables.  They cease to exist once the anonymous PL/SQL block completes.  Your two SELECT statements from DUAL come after the PL/SQL block completes, so neither variable will exist at that point.  Of course, the queries against DUAL are referring to a VALUE column that does not exist.  And it could only return the DUMMY column from DUAL which will only ever have a value of X.  That doesn't seem sensible.

               

              Are you intending to use the VARA and VARB values inside the PL/SQL block?  Or outside the block?

               

              Justin

              • 4. Re: oracle variables for a select result
                malky

                When i move them inside i get the error message that the INTO keyword is missing from my queries so i put them outside. I need to insert a new record based on that value i received, i.e.:

                 

                insert into tablec (cola, colb,colc,cold) values ('something','something', vara, 'something');

                insert into tablec (cola, colb,colc,cold) values ('something','something', varb, 'something');

                • 5. Re: oracle variables for a select result
                  sb92075

                  malky wrote:

                   

                  When i move them inside i get the error message that the INTO keyword is missing from my queries so i put them outside. I need to insert a new record based on that value i received, i.e.:

                   

                  insert into tablec (cola, colb,colc,cold) values ('something','something', vara, 'something');

                  insert into tablec (cola, colb,colc,cold) values ('something','something', varb, 'something');

                  malky wrote:

                   

                  When i move them inside i get the error message that the INTO keyword is missing from my queries so i put them outside. I need to insert a new record based on that value i received, i.e.:

                   

                  insert into tablec (cola, colb,colc,cold) values ('something','something', vara, 'something');

                  insert into tablec (cola, colb,colc,cold) values ('something','something', varb, 'something');

                  SQL & PL/SQL are entirely different & separate languages.

                  You can not arbitrarily mix and match constructs between the two languages.

                   

                  INSERT INTO TABLEC (COLA, COLB) SELECT 'SOMETHING', VARC FROM TABLEA WHERE ROWNUM = 1;

                  • 6. Re: oracle variables for a select result
                    Stew Ashton

                    In your example, when you say "vara" you are declaring and using a local PL/SQL variable.

                     

                    When you say ":vara" (with the colon in front), you are using a "host variable" that lets a client program (such as SQL*Plus) exchange values with the database.

                     

                    These are two different things. There is no relationship between them.

                     

                    To make your example work, declare some host variables:

                     

                    variable vara number;  -- this is a command of the client program to set up a host variable
                    variable varb number;       
                    --
                    begin  
                        select 1 into :vara from dual;  -- here the database puts a value into the host variable
                        select 2 into :varb from dual;  
                    end;  
                    /
                    --
                    print :vara;  -- this is a command of the client program to display the value of the host variable
                    print :varb;  
                    

                     

                    anonymous block completed

                    VARA

                    -

                    1

                     

                    VARB

                    -

                    2


                    Notice in the "variable vara number;" line, there is no colon. If there were, the client program would try to substitute the value of :vara, which is not what you want.

                    • 7. Re: oracle variables for a select result
                      malky

                      Thanks Ashton, that was very clear and you solved my problem.