Forum Stats

  • 3,852,174 Users
  • 2,264,072 Discussions
  • 7,904,981 Comments

Discussions

Calling a Procedure and Variables with a : vs. a ?

530085
530085 Member Posts: 168
Sorry, this is a long one, but I'm trying to provide as much background as possible.



The below code (from a procedure called view_ibba_for_maint) is a snippet of code from a much larger SQL package. The package is called from a web browser.


htp.tableOpen('CELLSPACING=1 CELLPADDING=1 width="50%"
bordercolor="#669966"', calign=>'center');
htp.tableRowOpen;
htp.tableData(fontx||htf.anchor(owa_util.get_owa_service_path||
*'nfdw_budadmn.del_ibba_warn:2'||'fyear='||fyear||'&'||*
*'budorg='||budorg||'&'||'appr_unit='||appr_unit||'&'||*
*'obje_ocat='||obje_ocat||'&'||*
*'bud_auth_amount='||bud_auth_amount||'&'||*
*'appr_name='||appr_name||'&'||*
*'fy_id='||fycode||'&'||*
*'borg_id='||borg_id||'&'||*
*'appr_id='||appr_id||'&'||*
*'obje_id='||obje_id||*
*'"onMouseOver=" window.status=''Click to Remove IBBA Budget Line.'';*
return true','Remove Budget'),calign=>'CENTER');

htp.tableData(fontx||htf.anchor(owa_util.get_owa_service_path||
*'nfdw_budadmn.mod_ibba_inp'||*
*'"onMouseOver=" window.status=''Click to Return to Query Form.'';*
return true','Return To Query Form'),calign=>'CENTER');
htp.tableRowClose;
htp.tableClose;




This portion of code calls a procedure called del_ibba_warn (in the nfdw_budadmn package). Both procedures are in the nfdw_budadmn package. view_ibba_for_maint calls del_ibba_warn a few times in the procedure. Anyway, when we were on Oracle 8i, the package and procedures worked fine. However, when we upgraded to 10g, it fails. (see the following web_browser link and error that is generated)


Internet Explorer Address Bar: http://orasrv2/fdotdwat/nfdw_budadmn.del_ibba_warn:2fyear=2008&budorg=B065&appr_unit=466026&obje_ocat=8130&bud_auth_amount=0&appr_name=INFORMATION%20SERVICES&fy_id=6497&borg_id=65775&appr_id=263566&obje_id=23968

Error:
Bad Request
Your browser sent a request that this server could not understand.
mod_plsql: /fdotdwat/nfdw_budadmn.del_ibba_warn:2fyear=2008&budorg=B065&appr_unit=466026&obje_ocat=8130&bud_auth_amount=0&appr_name=INFORMATION SERVICES&fy_id=6497&borg_id=65775&appr_id=263566&obje_id=23968 HTTP-400 Name "nfdw_budadmn.del_ibba_warn:2fyear=2008&budorg=B065&appr_unit=466026&obje_ocat=8130&bud_auth_amount=0&appr_name=INFORMATION SERVICES&fy_id=6497&borg_id=65775&appr_id=263566&obje_id=23968" is longer than 30 chars





We believe it is a problem with the way del_ibba_warn is called. In the original code it is called like this: 'nfdw_budadmn.del_ibba_warn:2'. So I try to replace the :2 with a ? (like this: 'nfdw_budadmn.del_ibba_warn?' (also tried nfdw_budadmn.del_ibba_warn?2'), but it still fails. The program actually replaces the new ? that I put into the code with a :1 in the internet explorer address bar when I run it.. (Not sure why a :1 and not a :2.) But, if I go and substitute the :1 with a ? in the address bar that is generated after I attempt to call the procedure, it works. I change the address bar to this:

http://orasrv2/fdotdwat/nfdw_budadmn.del_ibba_warn?fyear=2008&budorg=B065&appr_unit=466026&obje_ocat=8130&bud_auth_amount= 0&appr_name=INFORMATION%20SERVICES&fy_id=6497&borg_id=65775&appr_id=263566&obje_id=23968


Hit enter, and the procedure goes forward like it should. So, the overall question is why is the IE address bar forcing the :2 or :1 to show up in the address bar? Why won't it take my ? I put in? Why was it necessary for the original programer to label the del_ibba_warn calls with a : or a :2 at all? (Again, del_ibba_warn is called a few times in the procedure). And, it used to work when we were on 8i.

We have been trying to figure this out (on and off) for some time now. We feel like we're right there for figuring this out, but can't quite get it. Any ideas? Please let me know if I can provide any additional info. Thanks!

Answers

  • Asif M. Naqvi
    Asif M. Naqvi Member Posts: 785 Silver Badge
    You have syntax problems. the standard method to use it is as follows:

    htp.listItem( htf.anchor(owa_util.get_owa_service_path||
    'PACKAGE.PROCEDURE?'||
    'PARAMETER1='||VALUE1||
    '&PARAMETER2='||VALUE2);

    Try the following in your procedure instead of the snippet you showed.

    htp.tableOpen('CELLSPACING=1 CELLPADDING=1 width="50%" bordercolor="#669966"', calign=>'center');
    htp.tableRowOpen;
    htp.tableData(fontx||htf.anchor(owa_util.get_owa_service_path||'nfdw_budadmn.del_ibba_warn?fyear='||fyear||
    '&budorg='||budorg||'&appr_unit='||appr_unit||'&obje_ocat='||obje_ocat||
    '&bud_auth_amount='||bud_auth_amount||'&appr_name='||appr_name||
    '&fy_id='||fycode||'&borg_id='||borg_id||'&appr_id='||appr_id||'&obje_id='||obje_id||
    '"onMouseOver=" window.status=''Click to Remove IBBA Budget Line.'';return true','Remove Budget'),calign=>'CENTER');

    Please report the result or errors.
  • 530085
    530085 Member Posts: 168
    edited Nov 14, 2008 4:33PM
    Darn, didn't work. I still get the same error.......

    The address bar:
    http://orasrv2/fdotdwat/nfdw_budadmn.del_ibba_warn:2fyear=2008&budorg=B065&appr_unit=466026&obje_ocat=8130&bud_auth_amount=0&appr_name=INFORMATION%20SERVICES&fy_id=6497&borg_id=65775&appr_id=263566&obje_id=23968


    The Error:
    Bad Request
    Your browser sent a request that this server could not understand.
    mod_plsql: /fdotdwat/nfdw_budadmn.del_ibba_warn:2fyear=2008&budorg=B065&appr_unit=466026&obje_ocat=8130&bud_auth_amount=0&appr_name=INFORMATION SERVICES&fy_id=6497&borg_id=65775&appr_id=263566&obje_id=23968 HTTP-400 Name "nfdw_budadmn.del_ibba_warn:2fyear=2008&budorg=B065&appr_unit=466026&obje_ocat=8130&bud_auth_amount=0&appr_name=INFORMATION SERVICES&fy_id=6497&borg_id=65775&appr_id=263566&obje_id=23968" is longer than 30 chars



    Now, I pasted the changes you suggested and the package did compile. But notice how it forces the ":2" into the address bar and the error message? That, I just don't get. Would it help if I posted the two procedures involved? I'll avoid posting the whole package (it's way big).

    Here are the two procedures involved.

    view_ibba_for_maint (before I made your suggested changes) & del_ibba_warn




    procedure view_ibba_for_maint(
    fyear IN VARCHAR2,
    budorg IN VARCHAR2,
    appr_unit IN VARCHAR2,
    obje_ocat IN VARCHAR2,
    password IN VARCHAR2)
    IS

    CURSOR c1 (c1_fyr_id IN NUMBER,
    c1_borg_id IN NUMBER,
    c1_appr_id IN NUMBER,
    c1_obje_id IN NUMBER,
    c1_fmonth IN NUMBER) IS
    select appr_budget_account_code, appr_budget_account_category, appr_name,
    orga_organization, obje_object_code, ibba_budget_authorized_amount
    from internal_budget_balances, appropriations, organizations, objects
    where ibba_fiscal_year_code = c1_fyr_id
    and ibba_fiscal_month_code = c1_fmonth
    and ibba_organization_id = c1_borg_id
    and orga_organization_id = ibba_organization_id
    and ibba_appropriation_id = c1_appr_id
    and appr_appropriation_id = ibba_appropriation_id
    and ibba_object_id = c1_obje_id
    and obje_object_id = ibba_object_id
    order by orga_organization,
    appr_budget_account_code||appr_budget_account_category,
    obje_object_code;

    CURSOR c2 (c2_fyr_id IN NUMBER,
    c2_borg_id IN NUMBER,
    c2_appr_id IN NUMBER,
    c2_ocat IN VARCHAR2,
    c2_fmonth IN NUMBER) IS
    select appr_budget_account_code, appr_budget_account_category, appr_name,
    orga_organization, ibba_budget_authorized_amount,
    ibba_object_category
    from internal_budget_balances, appropriations, organizations
    where ibba_fiscal_year_code = c2_fyr_id
    and ibba_fiscal_month_code = c2_fmonth
    and ibba_organization_id = c2_borg_id
    and orga_organization_id = ibba_organization_id
    and ibba_appropriation_id = c2_appr_id
    and appr_appropriation_id = ibba_appropriation_id
    and ibba_object_category = c2_ocat
    order by orga_organization,
    appr_budget_account_code||appr_budget_account_category,
    ibba_object_category;

    p_app VARCHAR2(40) :='nfdw_budadmn.mod_ibba_inp';
    c1_rec c1%ROWTYPE;
    c2_rec c2%ROWTYPE;
    dh_aelg_rec application_error_log%ROWTYPE;
    its_an_obje VARCHAR2(1) := 'F';
    valid_combo number := 0;
    obje_id objects.obje_object_id%TYPE;
    obje_name objects.obje_name%TYPE;
    borg_id organizations.orga_organization_id%TYPE;
    appr_id appropriations.appr_appropriation_id%TYPE;
    appr_name appropriations.appr_name%TYPE;
    report_date VARCHAR2(35) := to_char(sysdate, 'Month DD, YYYY');
    bud_auth_amount internal_budget_balances.ibba_budget_authorized_amount%TYPE;
    password_upper VARCHAR2(10);
    v_budadmn_password VARCHAR2(10);
    fmcode time.time_time_code%TYPE;
    fycode time.time_time_code%TYPE;
    fonth VARCHAR2(100) :=
    '<font face="Verdana, Arial, Helvetica, sans-serif" size=2>';
    fontx VARCHAR2(100) :=
    '<font face="Verdana, Arial, Helvetica, sans-serif" size=3>';
    NULL_EXCEPTION EXCEPTION;
    INVALID_DOC EXCEPTION;
    PASSWORD_FAIL EXCEPTION;

    BEGIN

    SELECT distinct budadmn_password
    INTO v_budadmn_password
    FROM budadmn_password;

    v_budadmn_password := upper(v_budadmn_password);
    v_budadmn_password := rtrim(v_budadmn_password);
    password_upper := upper(password);
    password_upper := rtrim(password_upper);

    IF password_upper = v_budadmn_password
    THEN
    NULL;

    ELSE
    RAISE PASSWORD_FAIL;
    END IF;

    dh_aelg_rec.app_marker := 'GFYD';
    nfdw_budadmn.get_fiscal_data('NONE', fyear, fmcode, fycode, dh_aelg_rec);

    IF dh_aelg_rec.app_error_mesg = 'FAIL'
    THEN
    RAISE NULL_EXCEPTION;
    END IF;

    -- get appr id
    dh_aelg_rec.app_marker := 'APPR';
    nbdm_web.g_appr_id(substr(appr_unit,1,4), substr(appr_unit,5,2), fyear,
    appr_id, dh_aelg_rec);

    IF dh_aelg_rec.app_error_mesg = 'FAIL'
    THEN
    RAISE NULL_EXCEPTION;
    END IF;

    -- get budget org id
    dh_aelg_rec.app_marker := 'ORGA';
    nbdm_web.g_orga_id(budorg, fyear, borg_id, dh_aelg_rec);

    IF dh_aelg_rec.app_error_mesg = 'FAIL'
    THEN
    RAISE NULL_EXCEPTION;
    END IF;

    -- determine id search is by obje or ocat.
    IF length(obje_ocat) > 2
    THEN
    its_an_obje := 'T';
    dh_aelg_rec.app_marker := 'OBJE';
    nbdm_web.g_obje_id(obje_ocat, fyear, obje_id, obje_name,
    substr(appr_unit,1,4), dh_aelg_rec);

    IF dh_aelg_rec.app_error_mesg = 'FAIL'
    THEN
    RAISE NULL_EXCEPTION;
    END IF;
    END IF;

    IF its_an_obje = 'T'
    THEN
    dh_aelg_rec.app_marker := 'CHK1';
    SELECT COUNT(1)
    INTO valid_combo
    FROM internal_budget_balances
    WHERE ibba_fiscal_year_code = fycode
    AND ibba_organization_id = borg_id
    AND ibba_appropriation_id = appr_id
    AND ibba_object_id = obje_id;

    ELSE
    dh_aelg_rec.app_marker := 'CHK2';
    SELECT COUNT(1)
    INTO valid_combo
    FROM internal_budget_balances
    WHERE ibba_fiscal_year_code = fycode
    AND ibba_organization_id = borg_id
    AND ibba_appropriation_id = appr_id
    AND ibba_object_category = obje_ocat;
    END IF;

    IF valid_combo < 1
    THEN
    RAISE INVALID_DOC;
    END IF;

    IF its_an_obje = 'T'
    THEN
    -- Get max(ibba_fiscal_month_code)
    dh_aelg_rec.app_marker := 'FMC1';
    select max(ibba_fiscal_month_code)
    into fmcode
    from internal_budget_balances
    where ibba_fiscal_year_code = fycode
    and ibba_organization_id = borg_id
    and ibba_appropriation_id = appr_id
    and ibba_object_id = obje_id;

    nfdw_budadmn.nfdw_budadmn_rep_head(
    'Budget Maintenance',
    'BMAINT02',
    fyear,
    'N',
    'B',
    'N',
    'N');

    htp.tableOpen('border cellspacing=1 cellpadding=1');
    htp.p('<tr bgcolor=lightblue>');
    htp.tableHeader(fonth||(htf.bold('Orgn')), cattributes=>'WIDTH="2%"',
    calign=>'center');
    htp.tableHeader(fonth||(htf.bold('Appr Unit')),cnowrap=>'nowrap',
    cattributes=>'WIDTH="4%"',calign=>'center');
    htp.tableHeader(fonth||(htf.bold('Appr Name')),cnowrap=>'nowrap',
    cattributes=>'WIDTH="4%"',calign=>'center');
    htp.tableHeader(fonth||(htf.bold('Obje/Cat')),cattributes=>'WIDTH="2%"',
    calign=>'center');
    htp.tableHeader(fonth||(htf.bold('Auth Budget Amount')),
    cattributes=>'WIDTH="2%"', calign=>'center');
    htp.tableRowClose;

    OPEN c1(fycode, borg_id, appr_id, obje_id, fmcode);

    LOOP
    FETCH c1 INTO c1_rec;
    EXIT WHEN c1%NOTFOUND;

    BEGIN

    -- Create HTP
    htp.tableRowOpen;
    htp.tableData(fonth||nvl(c1_rec.orga_organization,'-'),
    cnowrap=>'nowrap', calign=>'center');
    htp.tableData(fonth||nvl(c1_rec.appr_budget_account_code||
    c1_rec.appr_budget_account_category,'-'),
    cnowrap=>'nowrap', calign=>'center');
    htp.tableData(fonth||nvl(c1_rec.appr_name,'-'),
    cnowrap=>'nowrap', calign=>'center');
    appr_name := c1_rec.appr_name;
    htp.tableData(fonth||nvl(obje_ocat,'-'),
    cnowrap=>'nowrap', calign=>'center');

    htp.tableData(fonth||to_char(c1_rec.ibba_budget_authorized_amount,
    '9,999,999,999.99'), cnowrap=>'nowrap', calign=>'right');
    bud_auth_amount := c1_rec.ibba_budget_authorized_amount;
    htp.tableRowClose;
    htp.tableClose;

    END;

    END LOOP;
    CLOSE c1;

    ELSE
    -- Get max(ibba_fiscal_month_code)
    dh_aelg_rec.app_marker := 'FMC2';
    select max(ibba_fiscal_month_code)
    into fmcode
    from internal_budget_balances
    where ibba_fiscal_year_code = fycode
    and ibba_organization_id = borg_id
    and ibba_appropriation_id = appr_id
    and ibba_object_category = obje_ocat;

    nfdw_budadmn.nfdw_budadmn_rep_head(
    'Budget Maintenance',
    'BMAINT02',
    fyear,
    'N',
    'B',
    'N',
    'N');

    htp.tableOpen('border cellspacing=1 cellpadding=1');
    htp.p('<tr bgcolor=lightblue>');
    htp.tableHeader(fonth||(htf.bold('Orgn')), cattributes=>'WIDTH="2%"',
    calign=>'center');
    htp.tableHeader(fonth||(htf.bold('Appr Unit')),cnowrap=>'nowrap',
    cattributes=>'WIDTH="4%"',calign=>'center');
    htp.tableHeader(fonth||(htf.bold('Appr Name')),cnowrap=>'nowrap',
    cattributes=>'WIDTH="4%"',calign=>'center');
    htp.tableHeader(fonth||(htf.bold('Obje/Cat')),cattributes=>'WIDTH="2%"',
    calign=>'center');
    htp.tableHeader(fonth||(htf.bold('Auth Budget Amount')),
    cattributes=>'WIDTH="2%"', calign=>'center');
    htp.tableRowClose;

    OPEN c2(fycode, borg_id, appr_id, obje_ocat, fmcode);

    LOOP
    FETCH c2 INTO c2_rec;
    EXIT WHEN c2%NOTFOUND;

    BEGIN

    -- Create HTP
    htp.tableRowOpen;
    htp.tableData(fonth||nvl(c2_rec.orga_organization,'-'),
    cnowrap=>'nowrap', calign=>'center');
    htp.tableData(fonth||nvl(c2_rec.appr_budget_account_code||
    c2_rec.appr_budget_account_category,'-'),
    cnowrap=>'nowrap', calign=>'center');
    htp.tableData(fonth||nvl(c2_rec.appr_name,'-'),
    cnowrap=>'nowrap', calign=>'center');
    appr_name := c2_rec.appr_name;
    htp.tableData(fonth||nvl(c2_rec.ibba_object_category,'-'),
    cnowrap=>'nowrap', calign=>'center');

    htp.tableData(fonth||to_char(c2_rec.ibba_budget_authorized_amount,
    '9,999,999,999.99'), cnowrap=>'nowrap', calign=>'right');
    bud_auth_amount := c2_rec.ibba_budget_authorized_amount;
    htp.tableRowClose;
    htp.tableClose;

    END;

    END LOOP;
    CLOSE c2;

    END IF;

    -- Links
    htp.br;
    htp.br;
    htp.br;
    htp.line;
    htp.tableOpen('CELLSPACING=1 CELLPADDING=1 width="50%"
    bordercolor="#669966"', calign=>'center');
    htp.tableRowOpen;
    htp.tableData(fontx||htf.anchor(owa_util.get_owa_service_path||
    'nfdw_budadmn.del_ibba_warn:2'||'fyear='||fyear||'&'||
    'budorg='||budorg||'&'||'appr_unit='||appr_unit||'&'||
    'obje_ocat='||obje_ocat||'&'||
    'bud_auth_amount='||bud_auth_amount||'&'||
    'appr_name='||appr_name||'&'||
    'fy_id='||fycode||'&'||
    'borg_id='||borg_id||'&'||
    'appr_id='||appr_id||'&'||
    'obje_id='||obje_id||
    '"onMouseOver=" window.status=''Click to Remove IBBA Budget Line.'';
    return true','Remove Budget'),calign=>'CENTER');

    htp.tableData(fontx||htf.anchor(owa_util.get_owa_service_path||
    'nfdw_budadmn.mod_ibba_inp'||
    '"onMouseOver=" window.status=''Click to Return to Query Form.'';
    return true','Return To Query Form'),calign=>'CENTER');
    htp.tableRowClose;
    htp.tableClose;

    EXCEPTION

    WHEN NULL_EXCEPTION THEN
    dh_aelg_rec.app_error_mesg := 'FAIL';
    dh_aelg_rec.comments :=
    'Parameters are: fyear='||fyear||
    ', budorg='||budorg||
    ', appr_unit='||appr_unit||
    ', obje_ocat='||obje_ocat||
    ', password='||password;
    dh_aelg_rec.app_hierarchy := ' nfdw_budadmn.view_ibba_for_maint:';
    util_web.util_return_page
    ('An application error has occurred.',
    'Please contact the I.S. Helpdesk.',
    p_app);

    WHEN INVALID_DOC THEN
    util_web.util_return_page
    ('Requested Data Is Not Available.',
    'Check Request and Try Again.',
    p_app);

    WHEN PASSWORD_FAIL THEN
    util_web.util_return_page
    ('Invalid Password.',
    'Please Reenter.',
    p_app);

    WHEN OTHERS THEN
    dh_aelg_rec.app_error_mesg := 'FAIL';
    dh_aelg_rec.ora_error_number := SQLCODE;
    dh_aelg_rec.ora_error_mesg := substr(SQLERRM, 1, 100);
    dh_aelg_rec.comments :=
    'Parameters are: fyear='||fyear||
    ', budorg='||budorg||
    ', appr_unit='||appr_unit||
    ', obje_ocat='||obje_ocat||
    ', password='||password;
    dh_aelg_rec.app_hierarchy := ' nfdw_budadmn.view_ibba_for_maint:';
    util_web.util_log_app_error(dh_aelg_rec);
    util_web.util_return_page
    ('An application error has occurred.',
    'Please contact the I.S. Helpdesk.',
    p_app);

    END view_ibba_for_maint;






    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .
    .










    procedure del_ibba_warn(
    fyear IN VARCHAR2,
    budorg IN VARCHAR2,
    appr_unit IN VARCHAR2,
    obje_ocat IN VARCHAR2,
    bud_auth_amount IN NUMBER,
    appr_name IN VARCHAR2,
    fy_id IN NUMBER,
    borg_id IN NUMBER,
    appr_id IN NUMBER,
    obje_id IN NUMBER)
    IS

    p_app VARCHAR2(40) :='nfdw_budadmn.mod_ibba_inp';
    exp_ck number := 0;
    dh_aelg_rec application_error_log%ROWTYPE;
    fontx VARCHAR2(100) :=
    '<font face="Verdana, Arial, Helvetica, sans-serif" size=3>';
    fonth VARCHAR2(100) :=
    '<font face="Verdana, Arial, Helvetica, sans-serif" size=2>';

    BEGIN

    -- Check if budget line has expenditures in the NOEM.
    IF length(obje_ocat) > 2
    THEN
    dh_aelg_rec.app_marker := 'SEL1';
    SELECT count(1)
    INTO exp_ck
    FROM nbdm_object_expense_fmon
    WHERE fiscal_year = fyear
    AND fiscal_month = '12'
    AND budget_org = budorg
    AND budget_account = substr(appr_unit,1,4)
    AND budget_category = substr(appr_unit,5,2)
    AND object = obje_ocat
    AND fytd_expended_amount > 0;

    ELSE
    dh_aelg_rec.app_marker := 'SEL2';
    SELECT count(1)
    INTO exp_ck
    FROM nbdm_object_expense_fmon
    WHERE fiscal_year = fyear
    AND fiscal_month = '12'
    AND budget_org = budorg
    AND budget_account = substr(appr_unit,1,4)
    AND budget_category = substr(appr_unit,5,2)
    AND object_category = obje_ocat
    AND fytd_expended_amount > 0;
    END IF;

    htp.p('<BR>');
    htp.p('<BR>');
    htp.tableOpen('BORDER=0 BGCOLOR=WHITE', calign=>'center');
    htp.tableRowOpen;
    htp.tableData(fontx||htf.bold('WARNING!! WARNING!! WARNING'));
    htp.tableRowClose;
    htp.tableClose;
    htp.p('<BR>');

    IF exp_ck > 0
    THEN
    htp.tableOpen('BORDER=0 BGCOLOR=WHITE', calign=>'center');
    htp.tableRowOpen;
    htp.tableData(fontx||htf.bold('THE CHOSEN BUDGET HAS VALID EXPENDITURES '));
    htp.tableData(fontx||htf.bold('AND CANNOT BE REMOVED'));
    htp.tableRowClose;
    htp.tableClose;

    ELSE
    htp.tableOpen('BORDER=0 BGCOLOR=WHITE', calign=>'center');
    htp.tableRowOpen;
    htp.tableData(fontx||htf.bold('YOU ARE ABOUT TO PERMANANTLY REMOVE '));
    htp.tableData(fontx||htf.bold('THE FOLLOWING'));
    htp.tableRowClose;
    htp.tableClose;

    htp.tableOpen('BORDER=0 BGCOLOR=WHITE', calign=>'center');
    htp.tableRowOpen;
    htp.tableData(fontx||htf.bold('AUTHORIZED BUDGET FROM THE NDOT'));
    htp.tableData(fontx||htf.bold('FINANCIAL DATA WAREHOUSE'));
    htp.tableRowClose;
    htp.tableClose;
    htp.p('<BR>');
    END IF;

    htp.tableOpen('border cellspacing=1 cellpadding=1');
    htp.p('<tr bgcolor=lightblue>');
    htp.tableHeader(fonth||(htf.bold('Orgn')), cattributes=>'WIDTH="2%"',
    calign=>'center');
    htp.tableHeader(fonth||(htf.bold('Appr Unit')),cnowrap=>'nowrap',
    cattributes=>'WIDTH="4%"',calign=>'center');
    htp.tableHeader(fonth||(htf.bold('Appr Name')),cnowrap=>'nowrap',
    cattributes=>'WIDTH="4%"',calign=>'center');
    htp.tableHeader(fonth||(htf.bold('Obje/Cat')),cattributes=>'WIDTH="2%"',
    calign=>'center');
    htp.tableHeader(fonth||(htf.bold('Auth Budget Amount')),
    cattributes=>'WIDTH="2%"', calign=>'center');
    htp.tableRowClose;

    htp.tableRowOpen;
    htp.tableData(fonth||nvl(budorg,'-'),
    cnowrap=>'nowrap', calign=>'center');
    htp.tableData(fonth||nvl(appr_unit,'-'),
    cnowrap=>'nowrap', calign=>'center');
    htp.tableData(fonth||nvl(appr_name,'-'),
    cnowrap=>'nowrap', calign=>'center');

    htp.tableData(fonth||nvl(obje_ocat,'-'),
    cnowrap=>'nowrap', calign=>'center');

    htp.tableData(fonth||to_char(bud_auth_amount,
    '9,999,999,999.99'), cnowrap=>'nowrap', calign=>'right');

    htp.tableRowClose;
    htp.tableClose;

    htp.br;
    htp.br;
    htp.br;
    htp.line;

    IF exp_ck > 0
    THEN
    htp.tableOpen('CELLSPACING=1 CELLPADDING=1 width="50%"
    bordercolor="#669966"', calign=>'center');
    htp.tableRowOpen;
    htp.tableData(fontx||htf.anchor(owa_util.get_owa_service_path||
    'nfdw_budadmn.mod_ibba_inp'||
    '"onMouseOver=" window.status=''Click to Return to Query Form.'';
    return true','Return To Query Form'),calign=>'CENTER');
    htp.tableRowClose;

    ELSE
    htp.tableOpen('CELLSPACING=1 CELLPADDING=1 width="50%"
    bordercolor="#669966"', calign=>'center');
    htp.tableRowOpen;
    htp.tableData(fontx||htf.anchor(owa_util.get_owa_service_path||
    'nfdw_budadmn.del_ibba_line:1'||
    'fy_id='||fy_id||'&'||
    'borg_id='||borg_id||'&'||
    'appr_id='||appr_id||'&'||
    'obje_id='||obje_id||'&'||
    'obje_ocat='||obje_ocat||'&'||
    'fyear='||fyear||'&'||
    'budorg='||budorg||'&'||
    'appr_unit='||appr_unit||
    '"onMouseOver=" window.status=''Click to Remove IBBA Budget Line.'';
    return true','Remove Budget'),calign=>'CENTER');

    htp.tableData(fontx||htf.anchor(owa_util.get_owa_service_path||
    'nfdw_budadmn.mod_ibba_inp'||
    '"onMouseOver=" window.status=''Click to Return to Query Form.'';
    return true','Return To Query Form'),calign=>'CENTER');
    htp.tableRowClose;
    END IF;

    htp.tableClose;

    EXCEPTION

    WHEN OTHERS THEN
    dh_aelg_rec.app_error_mesg := 'FAIL';
    dh_aelg_rec.ora_error_number := SQLCODE;
    dh_aelg_rec.ora_error_mesg := substr(SQLERRM, 1, 100);
    dh_aelg_rec.app_hierarchy := ' nfdw_budadmn.del_ibba_warn:';
    dh_aelg_rec.comments :=
    'Parameters are: fyear='||fyear||
    ', budorg='||budorg||
    ', appr_unit='||appr_unit||
    ', obje_ocat='||obje_ocat||
    ', bud_auth_amount='||bud_auth_amount||
    ', appr_name='||appr_name||
    ', fy_id='||fy_id||
    ', borg_id='||borg_id||
    ', appr_id='||appr_id||
    ', obje_id='||obje_id;
    util_web.util_log_app_error(dh_aelg_rec);
    util_web.util_return_page
    ('An application error has occurred.',
    'Please contact the I.S. Helpdesk.',
    p_app);

    END del_ibba_warn;
  • Asif M. Naqvi
    Asif M. Naqvi Member Posts: 785 Silver Badge
    edited Nov 14, 2008 5:19PM
    I think your errors are pretty expressive and are dropping some good hints to think about. Let's move up from your errors.
    Your errors report that

    Bad Request
    Your browser sent a request that this server could not understand.
    mod_plsql: /fdotdwat/nfdw_budadmn.del_ibba_warn:2fyear=2008&budorg=B065&appr_unit=466026&obje_ocat=8130&bud_auth_amount=0&appr_name=INFORMATION SERVICES&fy_id=6497&borg_id=65775&appr_id=263566&obje_id=23968
    HTTP-400 Name *"nfdw_budadmn.del_ibba_warn:2fyear=2008&budorg=B065&appr_unit=466026&obje_ocat=8130&bud_auth_amount=0&appr_name=INFORMATION SERVICES&fy_id=6497&borg_id=65775&appr_id=263566&obje_id=23968"* is longer than 30 chars

    If you look at the HTTP-400 error, your '*?*' was replaced by '*:2*'. That resulted into a long string as given above as the name of the procedure. This is longer than the limit of permissible procedure names.

    Why do you get this question mark replaced by ':2'?+

    a) Look at the string which it mentions as the name and where it ends up.
    ...&appr_id=263566&obje_id=23968
    this shows that the code beyond it was not clear to the processor and was considered outside the string value assigned at the end.

    It is here that I feel the syntax error is occurring. which is making it such a messed-up string.

    b) another problem: if it were portal, there is a reported issue with the utility function you are using and which is given in the following note.
    Subject: owa_util.get_owa_service_path getting different results on various versions of Portal
    Doc ID: Note:378170.1
    if it is a different product, even then there may be a similar issue with the function. the replacement of ? into :2 could be a result of a similar problem.

    - What you may try now?+
    1. test this utilitiy function that what does it produce in a simple call. try these calls:
    htf.anchor(owa_util.get_owa_service_path||'nfdw_budadmn.del_ibba_warn?fyear='||fyear||
    '&budorg='||budorg),calign=>'CENTER');

    htf.anchor(owa_util.get_owa_service_path||'/nfdw_budadmn.del_ibba_warn?fyear='||fyear||
    '&budorg='||budorg),calign=>'CENTER');

    2. Then in your original procedure, try using the following this time. see result and report.

    htp.tableData(fontx||
    htf.anchor(
    curl=owa_util.get_owa_service_path||'nfdw_budadmn.del_ibba_warn?fyear='||fyear||
    '&budorg='||budorg||'&appr_unit='||appr_unit||'&obje_ocat='||obje_ocat||
    '&bud_auth_amount='||bud_auth_amount||'&appr_name='||appr_name||
    '&fy_id='||fycode||'&borg_id='||borg_id||'&appr_id='||appr_id||'&obje_id='||obje_id,
    ctext=>'Remove Budget'),calign=>'CENTER');

    3. also explore can you use the following method of smuggling html code in plsql?
    htp.p('
    <table CELLSPACING="1" CELLPADDING="1" width="50%" bordercolor="#669966" valign="center">
    <tr width="80%">
    <td align="LEFT" width="50%"> First Column</td>
    <td align="LEFT" width="50%"> TestLink '||owa_util.get_owa_service_path||'nfdw_budadmn.del_ibba_warn?'||'fyear='||fyear||'&'||'budorg='||budorg'</td>
    </tr>
    </table>
    ');

    4. what is orasrv2? a hostname? does it need to have a domain name after it or not. Confirm.

    hope that helps!

    AMN
  • 530085
    530085 Member Posts: 168
    Thanks for looking into our problem. Much appreciated.


    I looked at the Metalink note you posted (Doc ID: Note:378170.1). That sounds very similar. It mentions upgrading the portal from 3.0.9.8.0 to 3.0.9.8.5. I checked and our portal version is as follows:
    OracleAS Metadata Repository Used By Portal
    Database Version 10.1.0.4.2
    Repository Version 10.1.2.0.2
    So, I'm not sure if we're beyond their suggested fix.



    I tried implementing your suggestion #2, but I get an error when compiling.

    Line # = 3003 Column # = 1 Error Text = PL/SQL: Statement ignored
    Line # = 3005 Column # = 1 Error Text = PLS-00201: identifier 'CURL' must be declared


    As far as what is orasrv2, it is a hostname and it does have a domain after it. But, all of our other procedures and packages from that server work fine.



    Not sure where you are located, but I'm thinking you might be leaving work or something for the day/weekend. I won't be back in the office myself until Tuesday, but I'd like to follow-up on this some more with you. In the meantime, I'm going to go over some more what you already mentioned and see if I can get anywhere. As I just mentioned, I had a problem compiling. If you have any more thoughts, I'd like to hear them, but I probably won't be able to reply until Tuesday. And thanks again for putting in so much effort :)
  • Asif M. Naqvi
    Asif M. Naqvi Member Posts: 785 Silver Badge
    I am still positive your problem is syntactical in nature.
    Please read my previous reply with care. particularly the highlighted items and potential causes of errors.

    1. About my last suggestion, I am sorry, I had a typo in the suggested piece. CURL is one of the parameters of htf.anchor. So we should have CURL=> instead of CURL= just like what we did for ctext. the corrected one should be like this and should not yield that compilation error. see its description here.
    [htp and htf Packages |http://download.oracle.com/docs/cd/B14099_19/web.1012/b15896/pshtp.htm]

    htp.tableData(fontx||
    htf.anchor(
    curl=>owa_util.get_owa_service_path||'nfdw_budadmn.del_ibba_warn?fyear='||fyear||
    '&budorg='||budorg||'&appr_unit='||appr_unit||'&obje_ocat='||obje_ocat||
    '&bud_auth_amount='||bud_auth_amount||'&appr_name='||appr_name||
    '&fy_id='||fycode||'&borg_id='||borg_id||'&appr_id='||appr_id||'&obje_id='||obje_id,
    ctext=>'Remove Budget'),calign=>'CENTER');

    However, did you also try my suggestions 1 and 2?
    2. the main intent was to see what do you get when you call the owa_util.get_owa_service_path function.
    3. what do you get for a simplified call of htf.anchor. what you are using is a simple call only upto the second last parameter. your last parameter is trying to smuggle object attributes like into it which is bringing complicated syntax.
    4. did you also explore if you can use the following method of smuggling html code in plsql?

    htp.p('
    <table CELLSPACING="1" CELLPADDING="1" width="50%" bordercolor="#669966" valign="center">
    <tr width="80%">
    <td align="LEFT" width="50%"> First Column</td>
    <td align="LEFT" width="50%"> TestLink '||owa_util.get_owa_service_path||'nfdw_budadmn.del_ibba_warn?fyear='||fyear||'&budorg='||budorg||'</td>
    </tr>
    </table>
    ');

    report errors or results.

    AMN
  • 530085
    530085 Member Posts: 168
    edited Nov 21, 2008 5:50PM
    Hello again AMN,

    So sorry for just now getting back to you. I just saw today that you replied.



    After that I applied your change with the typo fixed for the "curl=>". It compiled now, but still came back with the same error:
    Bad Request
    Your browser sent a request that this server could not understand.
    mod_plsql: /fdotdwat/nfdw_budadmn.del_ibba_warn:1fyear=2008&budorg=B065&appr_unit=466026&obje_ocat=8130&bud_auth_amount=0&appr_name=INFORMATION SERVICES&fy_id=6497&borg_id=65775&appr_id=263566&obje_id=23968 HTTP-400 Name "nfdw_budadmn.del_ibba_warn:1fyear=2008&budorg=B065&appr_unit=466026&obje_ocat=8130&bud_auth_amount=0&appr_name=INFORMATION SERVICES&fy_id=6497&borg_id=65775&appr_id=263566&obje_id=23968" is longer than 30 chars





    I also tried your 1st suggestion where you told me to use the simplified htf.anchor call. I had to add a small bit of code to get it to compile.....
    htf.anchor(owa_util.get_owa_service_path||'nfdw_budadmn.del_ibba_warn?fyear='||fyear||
    *'&budorg='||budorg, ctext=>'Remove Budget'),calign=>'CENTER');*

    Without the "ctext=>'Remove Budget'" I was getting PLS-00306: wrong number or types of arguments in call to 'ANCHOR'.

    I ran that simplified call, and got this error:
    Bad Request
    Your browser sent a request that this server could not understand.
    mod_plsql: /fdotdwat/nfdw_budadmn.del_ibba_warn:1fyear=2008&budorg=B065 HTTP-400 Name "nfdw_budadmn.del_ibba_warn:1fyear=2008&budorg=B065" is longer than 30 chars

    Virtually the same error as before, just with less variables. And still forcing in that darn ':1'. One thing on the suggestion you gave for this part, it sorta looks like you pasted the same code twice. The only difference in the second set of code in your 1st suggestion has a '/'. Was that intentional?
    Like so:
    *1. test this utilitiy function that what does it produce in a simple call. try these calls:*
    htf.anchor(owa_util.get_owa_service_path||'nfdw_budadmn.del_ibba_warn?fyear='||fyear||
    *'&budorg='||budorg),calign=>'CENTER');*

    htf.anchor(owa_util.get_owa_service_path||'/nfdw_budadmn.del_ibba_warn?fyear='||fyear||
    *'&budorg='||budorg),calign=>'CENTER');*











    As for the smuggling html code one. I tried that a few days ago without success. I couldn't get it to compile. I did that a few days ago, so I don't have the excact compile messages. Can't remember what the compile errors were right now, I'll try again. But, I wanted to show you my results with what I've tried so far in case it takes me some time to get to the point where the html smuggling compiles.



    Almost forgot. Our main DBA is looking into if there is a patch or something we can apply to our portal version. Even though our portal version is so far ahead of the ones mentioned in that Metalink article, their scenario and ours just sounds too similar to ignore.

    Edited by: user527082 on Nov 21, 2008 2:47 PM
This discussion has been closed.