Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Passing string of IDs to Oracle stored procedure

Posted on 2004-08-04
6
Medium Priority
?
894 Views
Last Modified: 2008-03-17
We created a stored procedure to copy all fields of specified records from one table to another:

CREATE OR REPLACE PROCEDURE "XX"."EXPORT_XXXX" ( sIDS  VARCHAR2) IS
BEGIN
 INSERT INTO "XX"."EXPXXXX"  (SELECT * from "XX"."XXXX" WHERE XX.XXXX.INTID IN  (sIDS));
END;

If we call it with deXX.procEXPORT_XXXX( "1") then all is fine.
If we call it with deXX.procEXPORT_XXXX( "1,2,3") then Oracle indicates an invalid number.
I think the problem is that we cannot pass numbers in strings to Oracle.
Maybe we have to create an array inside the proc and populate it with the numbers extracted from the sIDS string.
Or maybe there is some other calling type we can use such as VARIANT.

Any solutions to the problem welcome.


0
Comment
Question by:CharlesSHill
[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
  • 5
6 Comments
 
LVL 6

Expert Comment

by:PePi
ID: 11716735
why not create the sql string dynamically and pass it to the stored procedure for execution.
0
 

Author Comment

by:CharlesSHill
ID: 11716828
Good Idea, what would my Stored Procedure then look like? Sorry, have never worked with SPs
0
 
LVL 6

Expert Comment

by:PePi
ID: 11717879
i'm not familiar with Oracle SQL syntax. there is a system stored procedure for SQL Server called sp_executesql where you just pass the sql statement as a parameter. Oracle might have a similar system stored procedure that's equivalent to sp_executesql.

in my case, i created my own stored procedure using this system stored procedure.

CREATE PROCEDURE unsp_EXECUTE_SQL
(
    @sql nvarchar(4000)
)
AS
    EXEC sp_executesql @sql
GO

i then call this stored procedure and pass my dynamically created stored procedure.

hope this helps
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 6

Expert Comment

by:PePi
ID: 11717893
btw, is your application written in VB6? just curious
0
 
LVL 6

Expert Comment

by:PePi
ID: 11717961
ooops

i mean i call the stored procedure passing my dynamically created sql statement
0
 
LVL 6

Accepted Solution

by:
PePi earned 500 total points
ID: 11718623
i found the equivalent of sp_executesql for oracle. its EXECUTE IMMEDIATE and its syntax is:

EXECUTE IMMEDIATE SQL_string
   [INTO {define_variable[, define_variable]... | record}]
   [USING [IN | OUT | IN OUT] bind_argument
       [, [IN | OUT | IN OUT] bind_argument]...];

SQL_string
A string expression containing the SQL statement or PL/SQL block

define_variable
A variable that receives a column value returned by a query

record
A record based on a user-defined TYPE or %ROWTYPE that receives an entire row returned by a query

bind_argument
An expression whose value is passed to the SQL statement or PL/SQL block

INTO clause
Use for single-row queries; for each column value returned by the query, you must supply an individual variable or field in a record of compatible type.

USING clause
Allows you to supply bind arguments for the SQL string. This clause is used for both dynamic SQL and PL/SQL, which is why you can specify a parameter mode. This usage is only relevant for PL/SQL, however; the default is IN, which is the only kind of bind argument you would have for SQL statements.


example:

EXECUTE IMMEDIATE 'SELECT * FROM Employee WHERE Emp_ID = (eID)';
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

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