Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.oracle -> Re: PL/SQL: Dynamically passing/changing value in WHERE clause ???

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

From: Pedro Lopes <pedro.lopes_at_netvisao.pt>
Date: Thu, 29 Apr 2004 00:58:51 +0100
Message-ID: <newscache$tonwwh$kw8$1@newsfront4.netvisao.pt>


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

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 Wed Apr 28 2004 - 18:58:51 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US