oracle 11g proc for accepting collection/Array of int throw c# oledb

Question

0.00/5 (No votes)

See more:

I need to update oracle 11g db tables like
G_X_Table => groupid(fk),itemid(fk)
Group_Table => groupid(PK),groupName
Item_Table => itemid(PK),itemName

I need to write a stored procedure while updating(insert,delete).I have to pass one groupId and ItemId collection to procedure where insert G_X_Table with groupid and collection,those are not there in item collection those need to delete. Please help me to write procedure.

I need to call this Procedure through C# with oledb connection please anybody help me. I am very new with oracle..connection through c#.

my idea: delete form G_X_Table where groupid=input groupid and insert G_X_Table with new values. But I am confused what the type i need to accept item collection in procedure and what oledb type i need to pass as input parameter.

Thanks in advance..

Posted 30-Nov-13 9:29am

s#@!k

Updated 30-Nov-13 11:17am

Nelek

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

NWPU_Tbeck · Answer 1 · 2013-12-01T14:33:00

Solution 1

for collection, my solution is loop insert/delete in C#.Currently, i haven't found any way to pass a array or collection from C# to oracle, if you found, welcome to share with me.

Posted 1-Dec-13 14:33pm

NWPU_Tbeck

Comments

s#@!k 3-Dec-13 7:29am

Thank you so much for your response. But if I need to update 100 records it is going to hit db 100 times form C# code ha.So I concatenated all int types separated by "|" and made it as string passing input parameter as oledbtype.varchar. I declared varchar2 in procedure as in parameter and in procedure updated by splitting as individual items. Please update any upcoming issues with this solution.

thatraja · Answer 2 · 2013-12-03T22:05:00

For Oracle using OLEDB, check this
How To Access an Oracle Database by Using the OLE DB .NET Data Provider and Visual C# .NET[^]
To send list to stored procedure, check this Tip/Trick(Though it's for SQL server but you could create same for Oracle too, try Google)
Using comma separated value parameter strings in SQL IN clauses[^]