Re: PL/SQL: Dynamically passing/changing value in WHERE clause ???

Yes there is a way :)

from the PL/SQL Users Guide....

The following example computes the total wages paid to employees in a specified department.

DECLARE
   CURSOR c1 (name VARCHAR2, max_wage NUMBER) IS      SELECT * FROM employees WHERE last_name = name and salary < max_wage; BEGIN
   FOR person IN c1('Austin', 30000)
   LOOP

• process data record dbms_output.put_line('Name = ' || person.last_name || ', salary = ' || person.salary); END LOOP; END; /

hope it helps,
pedro

george lewycky wrote:

> Is there someway I can make a parameter or variable of some sort
> to pass into my PL/SQL cursor shown below to change the value of the
> date without having to rebuild the package each time??
>
> Can I alter this value dynamically ??
>
> and trx_date > '31/MAR/04'
>
>
> any ideas would be appreciate
>
> thanks in advance
>
> George Lewycky
> =========================================
>
> CURSOR c_invh IS
> SELECT CUSTOMER_TRX_ID,
> BILL_TO_CUSTOMER_NUMBER,
> substr(BILL_TO_CUSTOMER_NAME,1,45) customer_name,
> DEFAULT_BILL_ATTN,
> BILL_TO_ADDRESS1,
> BILL_TO_ADDRESS2,
> BILL_TO_CITY,
> BILL_TO_STATE,
> BILL_TO_POSTAL_CODE,
> BILL_TO_COUNTRY,
> BILL_TO_COUNTRY_NAME,
> TRX_NUMBER,
> TRX_DATE,
> TERM_NAME,
> CLASS_NAME,
> TOTAL_AMOUNT,
> substr(PURCHASE_ORDER_NUMBER,1,5) jobno,
> BATCH_SOURCE_NAME,
> PRINTING_ORIGINAL_DATE,
> PRINTING_LAST_PRINTED,
> PRINTING_OPTION,
> PRINTING_PENDING,
> PRINTING_COUNT
> FROM AR_INVOICE_HEADER_V INV
> where class_name = 'Invoice'
> and trx_date > '31/MAR/04' <--------------
> and BILL_TO_CUSTOMER_NUMBER not in (1404, 1480)
> order by trx_number;
Received on Thu Apr 29 2004 - 01:58:51 CEST