Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Posts
    12

    Unanswered: SQLPLUS command line variables-maxlimit

    Hi all,

    I hv an problem with sqlplus command line variable value passing.

    I do will pass 16 variables from command prompt as %1 .. %16.

    Which will be call an .sql file with arguments as &1..&16.

    From argument &1 till &9 everything works ok.

    FOR &10 the value is getting replaced of &1 and '0' is getting attached to the same and continues till &16.

    old 10: schedule_run(upper('&1'), '&2', '&3', '&4', '&5', '&6', '&7', '&8', '&9', '&10', '&11', '&12', '&13', '&14', '&15', '&16', l_process_status);
    new 10: schedule_run(upper('RUN_ZARC_SIMULATION'), '14-05-2004', '16:29', '14-05-2004', '1', 'DAY', 'FALSE', 'FALSE', 'FALSE', 'RUN_ZARC_SIMULATION0, 'RUN_ZARC_SIMULATION1', 'RUN_ZARC_SIMULATION2', 'RUN_ZARC_SIMULATION3', 'RUN_ZARC_SIMULATION4', 'RUN_ZARC_SIMULATION5', 'RUN_ZARC_SIMULATION6', l_process_status);


    I'm not getting how to overcome this problem. In one of the version of User guide of SQLPLUS, it says, we can use from &1..&41 command line arguments. But not the fix what I required.

    Thanks in advance.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'd suggest you to omit "%" while calling your procedure. For example:
    PHP Code:
    REM This is myexample.sql
     
    SELECT 
    &1 a, &2 b, &3 c, &4 d, &5 e, &6 f, &7 g, &8 h, &9 i, &10 j, &11 k
    FROM dual
    ;
     
    REM End of myexample.sql 
    Call this script using
    PHP Code:
    SQLPLUS myun/mypw @myexample 1 2 3 4 5 6 7 8 9 111 222 
    And the output is
    PHP Code:
    A B C D E F G H I J   K
    - - - - - - - - - --- ---
    1 2 3 4 5 6 7 8 9 111 222 
    Last edited by Littlefoot; 05-17-04 at 04:15.

  3. #3
    Join Date
    May 2003
    Posts
    12

    Got the solution !!!

    Hi all,

    This error is not due to the sql-plus. I was calling the sqlfile in an batch file. And passing command line arguments 1 - 16. I had specified in the batch file saying %1..%16. Where as DOS-batch-file supports only %0-%9 which was not throwing any error or message for using 16 command line variables.

    thx.

  4. #4
    Join Date
    Mar 2009
    Posts
    1

    How can you override the SQLPlus prompt for a value?

    Quote Originally Posted by Littlefoot
    I'd suggest you to omit "%" while calling your procedure. For example:
    PHP Code:
    REM This is myexample.sql
     
    SELECT 
    &1 a, &2 b, &3 c, &4 d, &5 e, &6 f, &7 g, &8 h, &9 i, &10 j, &11 k
    FROM dual
    ;
     
    REM End of myexample.sql 
    Call this script using
    PHP Code:
    SQLPLUS myun/mypw @myexample 1 2 3 4 5 6 7 8 9 111 222 
    And the output is
    PHP Code:
    A B C D E F G H I J   K
    - - - - - - - - - --- ---
    1 2 3 4 5 6 7 8 9 111 222 
    I'm trying to do something simmilar by calling an .sql file with arguments from a batch file.

    e.g.

    CALL user/pwd@db @"E:\scripts\sql\validate_files.sql" 2

    The SQL code is simmilar to:

    set serveroutput on;
    exec DBMS_OUTPUT.ENABLE (1000000);
    exec dbms_output.put_line(&1);

    ...but when it runs, sqlplus does not recognise the argument 2, and will prompt for a value instead.

    Why is this? Can you help?

    Many thanks.

Posting Permissions

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