Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    Munich, Germany
    Posts
    35

    declaring variables in SQLPLUS script

    Hi all,

    How can I declare a local variable in a SQLPLUS script.

    Here is what I want to do:

    DECLARE @value NUMBER (38);
    SET @value = SELECT MAX(COLUMNNAME) from Some_TABLE;
    CREATE SEQUENCE SOMESEQ START WITH @value + '1' INCREMENT BY 1

    However, these statements do not work in SQLPlus. Can someone help me out with it?

    Thanks,
    Pankaj

  2. #2
    Join Date
    Mar 2004
    Location
    Warsaw / Poland
    Posts
    73
    Hello,

    Code:
    VARIABLE ret_val NUMBER
    :ret_val := 1;
    PRINT ret_val
    BEGIN
      SELECT COUNT(*)
        INTO :ret_val
        FROM SomeTable
    END;
    /
    PRINT ret_val
    BTW are you sure you have read SQL*Plus User's Guide and Reference? Maybe you should try with 6 Using Scripts in SQL*Plus: Using Bind Variables?

    Thanks,
    Grzegorz

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,004
    Would this work?
    PHP Code:
    DECLARE
      
    l_val NUMBER;
      
    l_stat VARCHAR2(1024);
    BEGIN
      SELECT MAX
    (empnoINTO l_val FROM EMP;
      
    l_stat := 
        
    'create sequence seq_val start with ' || 
        
    TO_CHAR(l_val 1) || 
        
    ' increment by 1'
     
      
    EXECUTE IMMEDIATE l_stat;
    END;


  4. #4
    Join Date
    Apr 2004
    Posts
    246
    Simple answer, no pl/sql

    column value new_value value
    SELECT MAX(COLUMNNAME) + 1 value from Some_TABLE;
    CREATE SEQUENCE SOMESEQ START WITH &value INCREMENT BY 1;

  5. #5
    Join Date
    Jan 2004
    Location
    Munich, Germany
    Posts
    35

    thanx!

    Perfect!
    Thanks for answering

    Sincerely,
    Pankaj

Posting Permissions

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