Oracle statement

Hi all,
what do i need to do to use in Oracle query statement the parameter? In SQL
server, I use @.ParameterName but I can not use it like that in Oracle.
In SQL Server: select * from tableA where columnID = @.ParameterName
This works great in SQL
I can not use the same thing in oracle. Please help. ThanksTry Using just the question mark "?". That's what I have been doing. It
seems that the oracle client cannot handle named parameters.
"VNN" <VNN@.hotmail.com> wrote in message
news:%23ZvRckZDFHA.1280@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> what do i need to do to use in Oracle query statement the parameter? In
> SQL server, I use @.ParameterName but I can not use it like that in Oracle.
> In SQL Server: select * from tableA where columnID = @.ParameterName
> This works great in SQL
> I can not use the same thing in oracle. Please help. Thanks
>|||I use the question mark "?" but I got another error message:
ORA-01036: illegal variable name/number.
I put the where columnName='?' and run the query, it pops up a dialog to
type in the value (dropdown list has null and blank), this is the same
dialog when I use SQL with @.parameterName, I entered the string value and
hit OK, then I got the error dialog box.
Please advice.
"Gregory C. Quinn, Jr." <gquinn@.triad.rr.com> wrote in message
news:X12Od.68699$fE4.11961448@.twister.southeast.rr.com...
> Try Using just the question mark "?". That's what I have been doing. It
> seems that the oracle client cannot handle named parameters.
> "VNN" <VNN@.hotmail.com> wrote in message
> news:%23ZvRckZDFHA.1280@.TK2MSFTNGP10.phx.gbl...
> > Hi all,
> >
> > what do i need to do to use in Oracle query statement the parameter? In
> > SQL server, I use @.ParameterName but I can not use it like that in
Oracle.
> >
> > In SQL Server: select * from tableA where columnID = @.ParameterName
> >
> > This works great in SQL
> >
> > I can not use the same thing in oracle. Please help. Thanks
> >
>|||I think Oracle uses a : but I am not sure. I remember it is not a @. and it
is not a ?. Try
select * from tableA where columnID = :ParameterName
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"VNN" <VNN@.hotmail.com> wrote in message
news:u%23jQXLiDFHA.936@.TK2MSFTNGP12.phx.gbl...
> I use the question mark "?" but I got another error message:
> ORA-01036: illegal variable name/number.
> I put the where columnName='?' and run the query, it pops up a dialog to
> type in the value (dropdown list has null and blank), this is the same
> dialog when I use SQL with @.parameterName, I entered the string value and
> hit OK, then I got the error dialog box.
> Please advice.
> "Gregory C. Quinn, Jr." <gquinn@.triad.rr.com> wrote in message
> news:X12Od.68699$fE4.11961448@.twister.southeast.rr.com...
> > Try Using just the question mark "?". That's what I have been doing.
It
> > seems that the oracle client cannot handle named parameters.
> >
> > "VNN" <VNN@.hotmail.com> wrote in message
> > news:%23ZvRckZDFHA.1280@.TK2MSFTNGP10.phx.gbl...
> > > Hi all,
> > >
> > > what do i need to do to use in Oracle query statement the parameter?
In
> > > SQL server, I use @.ParameterName but I can not use it like that in
> Oracle.
> > >
> > > In SQL Server: select * from tableA where columnID = @.ParameterName
> > >
> > > This works great in SQL
> > >
> > > I can not use the same thing in oracle. Please help. Thanks
> > >
> >
> >
>|||This depends on what you are doing. Oracle is a bit special. If you use the
graphical query designer it is using OLEDB if you are using the generic it
is using the managed provider. At runtime it uses the managed provider. My
suggestion is to use the generic query designer. Then do this:
select * from sometable where somefield = :ParameterName
With the managed provider that is how a named parameter works.
Here is some additional info from a MS dude:
>>>>>>>>>>>>
Robert Bruckner [MSFT] Mar 15 2004, 8:03 pm show options
Newsgroups: microsoft.public.sqlserver.reportingsvcs
From: "Robert Bruckner [MSFT]" <rob...@.online.microsoft.com> - Find
messages by this author
Date: Mon, 15 Mar 2004 20:00:54 -0800
Local: Mon, Mar 15 2004 8:00 pm
Subject: Re: Dynamic Oracle Query with Parameters Example (not using
Packages/Procs)
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse
A good KB article is given at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
The Visual Data Tools (VDT) query designer (2 panes) actually uses OLE DB in
the preview pane. The text-based generic query designer (GQD; 4 panes) uses
the .NET provider for Oracle. Whenever you get strange results in VDT, you
should try it with GQD.
Note: the syntax for parameters in oracle queries depends on the provider
you are using:
* Managed Oracle provider (named parameters):
select from table where ename = :parameter
* OLE DB for Oracle (unnamed parameters):
select from table where ename = ?
The parameter binding between query parameters (accessible on the dataset
dialog) and report parameters (accessible through the menu - report - report
parameters) will be done automatically by the query designer.
Robert M. Bruckner
Microsoft SQL Server Reporting Services
>>>>>>>>>>>>>>>>>>
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"VNN" <VNN@.hotmail.com> wrote in message
news:u%23jQXLiDFHA.936@.TK2MSFTNGP12.phx.gbl...
> I use the question mark "?" but I got another error message:
> ORA-01036: illegal variable name/number.
> I put the where columnName='?' and run the query, it pops up a dialog to
> type in the value (dropdown list has null and blank), this is the same
> dialog when I use SQL with @.parameterName, I entered the string value and
> hit OK, then I got the error dialog box.
> Please advice.
> "Gregory C. Quinn, Jr." <gquinn@.triad.rr.com> wrote in message
> news:X12Od.68699$fE4.11961448@.twister.southeast.rr.com...
> > Try Using just the question mark "?". That's what I have been doing.
It
> > seems that the oracle client cannot handle named parameters.
> >
> > "VNN" <VNN@.hotmail.com> wrote in message
> > news:%23ZvRckZDFHA.1280@.TK2MSFTNGP10.phx.gbl...
> > > Hi all,
> > >
> > > what do i need to do to use in Oracle query statement the parameter?
In
> > > SQL server, I use @.ParameterName but I can not use it like that in
> Oracle.
> > >
> > > In SQL Server: select * from tableA where columnID = @.ParameterName
> > >
> > > This works great in SQL
> > >
> > > I can not use the same thing in oracle. Please help. Thanks
> > >
> >
> >
>|||That's it. It works great. Thanks very much.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:u7kH6diDFHA.3908@.TK2MSFTNGP15.phx.gbl...
> This depends on what you are doing. Oracle is a bit special. If you use
> the
> graphical query designer it is using OLEDB if you are using the generic it
> is using the managed provider. At runtime it uses the managed provider. My
> suggestion is to use the generic query designer. Then do this:
> select * from sometable where somefield = :ParameterName
> With the managed provider that is how a named parameter works.
> Here is some additional info from a MS dude:
>>>>>>>>>>>>
> Robert Bruckner [MSFT] Mar 15 2004, 8:03 pm show options
> Newsgroups: microsoft.public.sqlserver.reportingsvcs
> From: "Robert Bruckner [MSFT]" <rob...@.online.microsoft.com> - Find
> messages by this author
> Date: Mon, 15 Mar 2004 20:00:54 -0800
> Local: Mon, Mar 15 2004 8:00 pm
> Subject: Re: Dynamic Oracle Query with Parameters Example (not using
> Packages/Procs)
> Reply to Author | Forward | Print | Individual Message | Show
> original
> | Report Abuse
> A good KB article is given at:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
>
> The Visual Data Tools (VDT) query designer (2 panes) actually uses OLE DB
> in
> the preview pane. The text-based generic query designer (GQD; 4 panes)
> uses
> the .NET provider for Oracle. Whenever you get strange results in VDT, you
> should try it with GQD.
>
> Note: the syntax for parameters in oracle queries depends on the provider
> you are using:
> * Managed Oracle provider (named parameters):
> select from table where ename = :parameter
> * OLE DB for Oracle (unnamed parameters):
> select from table where ename = ?
>
> The parameter binding between query parameters (accessible on the dataset
> dialog) and report parameters (accessible through the menu - report -
> report
> parameters) will be done automatically by the query designer.
>
> --
> Robert M. Bruckner
> Microsoft SQL Server Reporting Services
>>>>>>>>>>>>>>>>>>
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
>
>
> "VNN" <VNN@.hotmail.com> wrote in message
> news:u%23jQXLiDFHA.936@.TK2MSFTNGP12.phx.gbl...
>> I use the question mark "?" but I got another error message:
>> ORA-01036: illegal variable name/number.
>> I put the where columnName='?' and run the query, it pops up a dialog to
>> type in the value (dropdown list has null and blank), this is the same
>> dialog when I use SQL with @.parameterName, I entered the string value and
>> hit OK, then I got the error dialog box.
>> Please advice.
>> "Gregory C. Quinn, Jr." <gquinn@.triad.rr.com> wrote in message
>> news:X12Od.68699$fE4.11961448@.twister.southeast.rr.com...
>> > Try Using just the question mark "?". That's what I have been doing.
> It
>> > seems that the oracle client cannot handle named parameters.
>> >
>> > "VNN" <VNN@.hotmail.com> wrote in message
>> > news:%23ZvRckZDFHA.1280@.TK2MSFTNGP10.phx.gbl...
>> > > Hi all,
>> > >
>> > > what do i need to do to use in Oracle query statement the parameter?
> In
>> > > SQL server, I use @.ParameterName but I can not use it like that in
>> Oracle.
>> > >
>> > > In SQL Server: select * from tableA where columnID = @.ParameterName
>> > >
>> > > This works great in SQL
>> > >
>> > > I can not use the same thing in oracle. Please help. Thanks
>> > >
>> >
>> >
>>
>