Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    39

    Unanswered: Sqlplus : DEFINE Variable

    can we store an output of a query in the
    local variable in sqlplus.
    for example
    we can do .... SQL> DEFINE variable='some text'

    I want to store out put of query:
    SQL>select max(value) from table1;
    into the "variable"

    Is that possible

    Thanks in advance
    ydj

  2. #2
    Join Date
    Feb 2004
    Location
    Hong Kong
    Posts
    15

    Re: Sqlplus : DEFINE Variable

    Originally posted by ydj
    can we store an output of a query in the
    local variable in sqlplus.
    for example
    we can do .... SQL> DEFINE variable='some text'

    I want to store out put of query:
    SQL>select max(value) from table1;
    into the "variable"

    Is that possible

    Thanks in advance
    ydj
    YOU CAN USE PROCEDURE IN PL/SQL TO DO SO...

  3. #3
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    or use spooling

    spool C:\myTemp.sql
    select 'Define someVar = '|| max(value) from table1
    spool off
    @myTemp

    After running the myTemp spool file you have your variable set... nice, isnt it?

    Regards

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Try this ... If you are trying to get a value and use it later ....

    --- Define variable ---
    column OPER_PKEY new_value OPER_PRIMARY_KEY noprint

    --- Load the variable ---
    select constraint_name OPER_PKEY
    from user_constraints
    where table_name = 'OPERATION'
    and constraint_type = 'P';

    -- Use the above variable --

    SELECT /*+ INDEX (OPERATION &OPER_PRIMARY_KEY) */

    HTH
    Gregg

  5. #5
    Join Date
    Oct 2014
    Posts
    7
    This post is*very*informative.*Thank you!

Posting Permissions

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