Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Passing Parameter Values to SQL Query

645793Jul 6 2010 — edited Jul 9 2010
Hi,
I have a weird problem with SQL parameter passing. I will be grateful if someone can help me out.
Database is Oracle 11G. If I run:
select 1 from msf952
where dstrct_code = upper('CTH')
and parent_acct = upper('18ITDPT')

26 rows will be returned.

However if I run:

VAR StartAccount Varchar2(100);
exec :StartAccount :='18ITDPT';
VAR District VARCHAR2(100);
exec :District:='CTH';

select 1 from msf952
where dstrct_code = :District
and parent_acct = :StartAccount

I have 0 row returned.

I've tested
VAR StartAccount Varchar2(100);
exec :StartAccount :='18ITDPT';
VAR District VARCHAR2(100);
exec :District:='CTH';

select :StartAccount, :District from dual;
It worked so it means values have been assigned to my parameters and can be referenced by query script.

So can anyone tell me why my query can't return proper data when I put parameters in the where clause?
Thanks a lot.




select 1 from msf952
where dstrct_code = upper('CTH')
and parent_acct = upper('18ITDPT')
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 6 2010
Added on Jul 6 2010
11 comments
9,862 views