By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,197 Members | 974 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,197 IT Pros & Developers. It's quick & easy.

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

P: n/a
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;
Jul 19 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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;

Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.