Solved

Passing a parameter to a SQL statement with Oracle Database

Posted on 2008-10-03
3
552 Views
Last Modified: 2013-12-18
Hello,
I am using an Oracle Database.
I am trying to pass a parameter to the SQL statement.

My orginal SQL which gets the records is as follows:
SELECT rug.user_group_id, rug.user_group_desc, rh.code_hotel
FROM rpt_user_groups rug, rpt_user_group_xref rugx, rpt_hotels rh
WHERE rugx.rpt_user_group_parent_id in 1 <-------------------i have to pass a parameter here instead of 1
AND rugx.rpt_user_group_child_id = rug.user_group_id
AND rug.user_group_id = rh.user_group_id(+) ORDER BY rug.user_group_desc,rug.user_group_id

i am trying like below:

Protected Sub RadTreeView1_NodeExpand(ByVal sender As Object, ByVal e As RadTreeNodeEventArgs) Handles RadTreeView1.NodeExpand
Dim oradb As String = "Data Source=(DESCRIPTION=(ADDRESS_LIST=" _
                                 + "(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521)))" _
                                 + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=something)));" _
                                 + "User Id=something;Password=something;"
 Dim conn As New OracleConnection(oradb)
        conn.Open()

Dim sql As New OracleCommand("SELECT rug.user_group_id, rug.user_group_desc, rh.code_hotel FROM rpt_user_groups rug, rpt_user_group_xref rugx, rpt_hotels rh WHERE rugx.rpt_user_group_parent_id in @ParentId AND rugx.rpt_user_group_child_id = rug.user_group_id AND rug.user_group_id = rh.user_group_id(+) ORDER BY rug.user_group_desc,rug.user_group_id", conn) ' VB.NET

        sql.Parameters.Add("@ParentId", e.Node.Value)
        Dim adapter As New OracleDataAdapter(sql)  --- my parameter is not replaced with '1'
Dim data As New DataTable()
adapter.Fill(data)    <----- fails here with message (An exception of type 'Oracle.DataAccess.Client.OracleException' occurred in Oracle.DataAccess.dll but was not handled in user code.Additional information: External component has thrown an exception.)

End Sub


when i execute the query i get a error pop-up:  ORA-00936  missing expression

any help would be greatly appreciated.
anagm



     
0
Comment
Question by:anagm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
Priest04 earned 500 total points
ID: 22638500
I am not very familiar wit Oracle database, but according to this article

http://download.oracle.com/docs/cd/B19306_01/win.102/b14307/OracleParameterClass.htm

parameter sign is not @, but is :1, :2, :3... etc

Dim sql As New OracleCommand("SELECT rug.user_group_id, rug.user_group_desc, rh.code_hotel FROM rpt_user_groups rug, rpt_user_group_xref rugx, rpt_hotels rh WHERE rugx.rpt_user_group_parent_id in :1 AND rugx.rpt_user_group_child_id = rug.user_group_id AND rug.user_group_id = rh.user_group_id(+) ORDER BY rug.user_group_desc,rug.user_group_id", conn) ' VB.NET

also I am not sure if this is the correct Oracle syntax

WHERE rugx.rpt_user_group_parent_id in 1

in SQL server, it would be

WHERE rugx.rpt_user_group_parent_id = 1

try to declare parameter with all attributes. Someting like

OracleParameter prm = new OracleParameter("ParentId",OracleType.VarChar); // change to according type
prm.Direction = ParameterDirection.Input;
prm.Value = "1";
sql.Parameters.Add(prm);

Goran
0
 

Author Comment

by:anagm
ID: 22638584
It worked when i gave ':ParentId' instead of '@ParentId'
Dim sql As New OracleCommand("SELECT rug.user_group_id, rug.user_group_desc, rh.code_hotel FROM rpt_user_groups rug, rpt_user_group_xref rugx, rpt_hotels rh WHERE rugx.rpt_user_group_parent_id in :ParentId AND rugx.rpt_user_group_child_id = rug.user_group_id AND rug.user_group_id = rh.user_group_id(+) ORDER BY rug.user_group_desc,rug.user_group_id", conn)


sql.Parameters.Add(":ParentId", e.Node.Value)
0
 

Author Closing Comment

by:anagm
ID: 31502936
thank you, Goran.
anagm
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question