Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2007
    Posts
    1

    Unhappy Unanswered: maximum parameter length problem

    Hi,

    I am running a sql script where it is taking user input through some substitution variables. To run this I am getting a error, which is "The maximum parameter length for SqlPlus is 239 characters. Parameter #6 exceeds the maximum.".
    I don't understand that how I can solve the problem. Please help me !!!!!!



    here is the code...

    WHENEVER SQLERROR EXIT 1;

    set serveroutput on size 99999;

    define extension = '.html';

    -- inputs
    define report_dir = '&1';
    define report_name = '&2';

    define period_from_in = '&3';
    define period_to_in ='&4';
    --define resource_pool_in ='&5';
    define group_id_in = '&6';
    define skill_in ='&7';
    define work_category_in = '&8';
    define status_code_in = '&9';


    variable x_status number;
    variable x_status_message varchar2(1000);
    variable report_id number;
    variable line_num number;

    variable user_id number;

    declare

    resource_pool_in varchar2(4000);


    begin

    resource_pool_in := '&5';



    SELECT nvl(max(created_by),0)
    INTO :user_id
    FROM KNTA_REPORT_SUBMISSIONS
    WHERE report_submission_id = to_number(substr('&report_name',5,20));

    UAL_Analyze_Resource_Pool_RPT
    (P_PERIOD_FROM => '&period_from_in',
    P_PERIOD_TO => '&period_to_in',
    P_RSC_POOL_ID => resource_pool_in,
    P_GROUP_ID => '&group_id_in',
    P_SKILL_ID => '&skill_in',
    P_WORK_CATEGORY => '&work_category_in',
    P_STATUS_CODE => '&status_code_in',
    P_REPORT_SUB_ID => to_number(substr('&report_name',5,20)),
    P_REPORT_ID => :report_id,
    P_LINE_NUM => :line_num,
    STATUS => :x_status,
    STATUS_MESSAGE => :x_status_message
    );

    end;
    /

    set lines 200;
    set heading off;
    set feedback off;
    set term off;
    set pagesize 0;
    set trimspool on;

    spool &report_dir&report_name&extension;

    select line_text
    from knta_report_output
    where report_id = :report_id
    order by line_num;

    spool off;

    rollback;

    select to_date('error')
    from sys.dual
    where :x_status != 0;

    exit;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >for SqlPlus is 239 characters. Parameter #6 exceeds the maximum.".
    >I don't understand that how I can solve the problem. Please help me !!!!!!
    Make sure Parameter#6 & all others are less than 239 or use a client other than SQL*Plus
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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