[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Passing a parameter to a SQL statement with Oracle Database

Posted on 2008-10-03
3
Medium Priority
?
580 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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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.  …
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.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

649 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