Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2006
    Posts
    11

    Unanswered: Using bind variables in dynamic sql in oracle reports

    /* sql_cmd := 'Update xxxar_collectreg_rpt_temp r1 ' ||
    ' Set collection_source_trx_count = 1 ' ||
    ' Where r1.rowid IN ' ||
    ' (Select min(r2.rowid) ' ||
    ' From xxxar_collectreg_rpt_temp r2, ' ||
    ' ra_customer_trx ct, ' ||
    ' ar_payment_schedules ps' ||
    ' Where r2.request_id = ' || _conc_request_id ||
    ' and r2.applied_customer_trx_id = ct.customer_trx_id ' ||
    ' and ct.customer_trx_id = ps.customer_trx_id ' ||
    ' and r2.delinquent_indicator = ''Y''' ||
    ' and ps.class <> ''DM''' ||
    ' Group by nvl

    (ct.related_customer_trx_id,ct.customer_trx_id),r2 .collection_source)';
    */
    --This has been modified as below adding the Hint and bind variable using.

    sql_cmd := 'Update xxxar_collectreg_rpt_temp r1 ' ||
    ' Set collection_source_trx_count = 1 ' ||
    ' Where r1.rowid IN ' ||
    ' (Select /*+ index (ps AR_PAYMENT_SCHEDULES_N2) */ min

    (r2.rowid) ' ||
    ' From xxxar_collectreg_rpt_temp r2, ' ||
    ' ra_customer_trx ct, ' ||
    ' ar_payment_schedules ps' ||
    ' Where r2.request_id = :1 ' || using p_conc_request_id ||
    ' and r2.applied_customer_trx_id = ct.customer_trx_id ' ||
    ' and ct.customer_trx_id = ps.customer_trx_id ' ||
    ' and r2.delinquent_indicator = ''Y''' ||
    ' and ps.class <> ''DM''' ||
    ' Group by nvl

    (ct.related_customer_trx_id,ct.customer_trx_id),r2 .collection_source)';

    srw.do_sql(sql_cmd);
    srw.message('3',sql_cmd);

    But the bind variable using is giving an error:
    Encountered the symbol "P_CONC_REQUEST_ID when expecting one of the following:
    .(*@%&=-+;</> in mod not rem an exponent (**).

    Can anyone help me about the correct usage of the bind variable using in dynamic sql

    in Oracle Reports.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Code:
    ' Where r2.request_id = :1 ' || using p_conc_request_id ||
    So this is the syntax? Didn't know that ...

    What happens if you rewrite it as
    Code:
    ' Where r2.request_id = ' || p_conc_request_id ||
    (optionally, prefix "p_conc_request_id" with a colon; I'm not sure whether it is necessary (it might be if "p_conc_request_id" is a report parameter, not if it is a variable in this PL/SQL block).

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by Littlefoot
    Code:
    ' Where r2.request_id = :1 ' || using p_conc_request_id ||
    So this is the syntax? Didn't know that ...

    What happens if you rewrite it as
    Code:
    ' Where r2.request_id = ' || p_conc_request_id ||
    (optionally, prefix "p_conc_request_id" with a colon; I'm not sure whether it is necessary (it might be if "p_conc_request_id" is a report parameter, not if it is a variable in this PL/SQL block).
    I wouldn't use the second type. Too much chance for sql injection.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jan 2006
    Posts
    11

    Where r2.request_id = ' || : p_conc_request_id ||

    Where r2.request_id = ' || : p_conc_request_id ||
    This is already existing in the previous code( User Parameter), but due to performance issues we had to change it.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I am willing to bet dollars to donuts that using bind variables won't give you what you want.
    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.

  6. #6
    Join Date
    Jan 2006
    Posts
    11
    Quote Originally Posted by anacedent
    I am willing to bet dollars to donuts that using bind variables won't give you what you want.

    Can you please be a little specific!
    Because of the concatenations used in the dynamic SQL the bind variable was not serving its purpose, so instead of that i am planning to use "using" bind variable for performance.


    http://www.akadia.com/services/ora_bind_variables.html

Posting Permissions

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