OraFAQ Forum: SQL & PL/SQL » use lexical parameter in stored procedure

Home » SQL & PL/SQL » SQL & PL/SQL » use lexical parameter in stored procedure (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

use lexical parameter in stored procedure [message #555350]

Wed, 23 May 2012 06:11

annu-agi
Messages: 239
Registered: July 2005
Location: Karachi

Senior Member

dear experts
can i use laxical paramter within my procedure

    select '' employee_no, ptdv.fiscal_year, ptdv.trans_month, ptdv.location_code, ptdv.acc_code,ptdv.acc_desc,sum(ptdv.EARNING)-sum(ptdv.DEDUCTION) debit,0 credit,1 tag
    from pr_trans_dtl_v ptdv
    where ptdv.trans_month=nvl(mtrans_month, ptdv.trans_month)
    and ptdv.location_code=nvl(mloc_code, ptdv.location_code)
    and ptdv.fiscal_year=mfiscal_year    
    and ptdv.ACC_TYPE='A'
    and ptdv.trans_type_row  in ('R','A')
    and ptdv.acc_code NOT     IN ( 525050,525100,525101,520500,525300,525320,560800,525360,525310,525350,130150, 802044)
    group by ptdv.fiscal_year, ptdv.trans_month, ptdv.location_code,ptdv.acc_code,ptdv.acc_desc

see the highlighted row
can i use
and ptdv.trans_type_row in &vtype
instead of
and ptdv.trans_type_row in ('R','A')

where the vtype is varchar2(100) and
vtype:='(''R'',''A'')';

regards

anwer

[EDITED by LF: fixed topic title typo; was "laxical"]

[Updated on: Mon, 04 June 2012 03:24] by Moderator

Report message to a moderator

Re: use laxical parameter in stored procedure [message #555353 is a reply to message #555350]

Wed, 23 May 2012 06:25

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Lexical (not laxical) parameters are an oracle reports thing. To do the equivalent in a stored procedure you need to use dynamic sql - read up on execute immediate.
However, if all you want to do is change the contents of an IN list you can use this approach instead: varying in list

Report message to a moderator

Previous Topic:	getting PLS-00201 error.
Next Topic:	pls help in the question (2 Merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Aug 22 07:49:23 CDT 2025