|
Please , can any one confirm that a table type or collection type parameter can be passed to stored procedure in oracle using ADO.NET ?
I , browsed several forum and site but without success
Thanks
love2code
|
|
|
|
|
You need to use, ODP.Net , Data Provider for Oracle supplied by Oracle. Assuming you have that here is the example from Oracle
/********* Example Start *************/
using System;
using System.Data;
using System.Text;
using System.Reflection;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace ODPSample
{
class AssocArray
{
static void Main(string[] args)
{
Console.WriteLine("Demo: PL/SQL Associative Array");
// Connect
string connectStr = "User Id=scott;Password=tiger;Data Source=oracle";
// Setup the Tables for sample
Setup(connectStr);
OracleConnection connection = new OracleConnection(connectStr);
OracleCommand cmd = new OracleCommand("begin MyPack.TestVarchar2(:1, :2, :3);end;",
connection);
OracleParameter param1 = cmd.Parameters.Add("param1", OracleDbType.Varchar2);
OracleParameter param2 = cmd.Parameters.Add("param2", OracleDbType.Varchar2);
OracleParameter param3 = cmd.Parameters.Add("param3", OracleDbType.Varchar2);
// Setup the direction
param1.Direction = ParameterDirection.Input;
param2.Direction = ParameterDirection.InputOutput;
param3.Direction = ParameterDirection.Output;
// Specify that we are binding PL/SQL Associative Array
param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param3.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param1.Value = new string[3]{"Input1",
"Input2",
"Input3"};
param2.Value = new string[3]{"Inout1",
"Inout2",
"Inout3"};
param3.Value = null;
// Specify the maximum number of elements in the PL/SQL Associative
// Array
param1.Size = 3;
param2.Size = 3;
param3.Size = 3;
// Setup the ArrayBind Size for param1
param1.ArrayBindSize = new int[3]{13,14,13};
// Setup the ArrayBind Status for param1
param1.ArrayBindStatus = new OracleParameterStatus[3]{
OracleParameterStatus.Success,
OracleParameterStatus.Success,
OracleParameterStatus.Success};
// Setup the ArrayBind Size for param2
param2.ArrayBindSize = new int[3]{20,20,20};
// Setup the ArrayBind Size for param3
param3.ArrayBindSize = new int[3]{20,20,20};
try
{
connection.Open();
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
Console.WriteLine("Results:");
Display(cmd.Parameters);
}
public static void Setup(string connectStr)
{
OracleConnection connection = new OracleConnection(connectStr);
OracleCommand command = new OracleCommand("", connection);
try
{
connection.Open();
command.CommandText = "drop table T1";
try
{
command.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
command.CommandText = "CREATE TABLE T1(COL1 number, COL2 varchar2(20))";
command.ExecuteNonQuery();
StringBuilder hdr = new StringBuilder();
hdr.Append("CREATE or replace PACKAGE MYPACK AS ");
hdr.Append("TYPE AssocArrayVarchar2_t is table of VARCHAR(20) index by BINARY_INTEGER;");
hdr.Append(" PROCEDURE TestVarchar2(");
hdr.Append(" Param1 IN AssocArrayVarchar2_t,");
hdr.Append(" Param2 IN OUT AssocArrayVarchar2_t,");
hdr.Append(" Param3 OUT AssocArrayVarchar2_t);");
hdr.Append(" END MYPACK;");
command.CommandText = hdr.ToString();
command.ExecuteNonQuery();
StringBuilder body = new StringBuilder();
body.Append("CREATE or REPLACE package body MYPACK as ");
body.Append(" PROCEDURE TestVarchar2(");
body.Append(" Param1 IN AssocArrayVarchar2_t,");
body.Append(" Param2 IN OUT AssocArrayVarchar2_t,");
body.Append(" Param3 OUT AssocArrayVarchar2_t)");
body.Append(" IS");
body.Append(" i integer;");
body.Append(" BEGIN");
body.Append(" -- copy a few elements from Param2 to Param1\n");
body.Append(" Param3(1) := Param2(1);");
body.Append(" Param3(2) := NULL;");
body.Append(" Param3(3) := Param2(3);");
body.Append(" -- copy all elements from Param1 to Param2\n");
body.Append(" Param2(1) := Param1(1);");
body.Append(" Param2(2) := Param1(2);");
body.Append(" Param2(3) := Param1(3);");
body.Append(" -- insert some values to db\n");
body.Append(" FOR i IN 1..3 LOOP");
body.Append(" insert into T1 values(i,Param2(i));");
body.Append(" END LOOP;");
body.Append(" END TestVarchar2;");
body.Append("END MYPACK;");
command.CommandText = body.ToString();
command.ExecuteNonQuery();
command.CommandText="Commit";
command.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(command.CommandText + ":" + e.Message);
}
}
public static void Display(OracleParameterCollection collection)
{
foreach(OracleParameter p in collection)
{
Console.Write(p.ParameterName + ": ");
for(int i=0;i<3;i++)
{
if (p.Value is OracleString[])
Console.Write((p.Value as OracleString[])[i]);
else
Console.Write((p.Value as string[])[i]);
Console.Write(" ");
}
Console.WriteLine();
}
}
}
}
/********* Example End *************/
Regards
|
|
|
|
|
I found a really cool function at http://www.devx.com/tips/Tip/20009.
You pass in in a string with what it's deliminated by and it returns a temp table variable of all of the data split into rows..
i.e
select * from fn_SplitByID('oranges|apples|pears|lemons','|')
1 Oranges
2 Apples
3 Pears
4 Lemons
All really nice and pretty. The trouble is I have a table products (around 2000 rows). Each has a product_code (varchar(255) primary key), stock level (int), with a description field that contains deliminated text 'blue|green|pink' (varchar(255)), etc.
I want to extract and split the data into a whole new table:
product_id product_code stock_level description
1 ABC NULL BLUE
2 ABC NULL GREEN
3 ABC NULL PINK
4 DEFG NULL SILVER
5 DEFG NULL GOLD
...
Yeah I am not worried about the stock_level, just need a null value into the new table, as the stock would have to be recounted.
I just keep getting stuck on how to do this. Maybe I've been looking at too many different ways and the answer is straight in front of me. If you have any suggestions I would be grateful.
Many thanks
Kev
|
|
|
|
|
hi all,
In our Application ,we have to use 175 SQL Functions.So there are many dependancies between each other.
That mean ,If we creating a function,we have to use several function to create it.
So I need to create a function relation ship between each other.(like relatinship between Tables)
Please kindly tell me,Is there a way to get dependancies and relation in functions?
thanks in advance.
|
|
|
|
|
You could use sp_depends[^]
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
hi everyone,
I want to update a row in a table in SQL with values from another row in the same table,
i wrote this:
UPDATE Income_Codes IC
SET IC.DateLeave = IC1.DateLeave
FROM Income_Codes IC1
WHERE IC.Cm = 420 AND IC1.Cm = 911
but it didn't work.
I tried this:
update Income_Codes
set DateLeave = (select DateLeave from Income_Codes where Cm = 911)
where Cm = 420
it worked, but if i have to update 20 fields, i have to write 20 selects, any other solution?
Thanks
|
|
|
|
|
delete the first row, and insert second row just changing the primary key
love2code
|
|
|
|
|
If you want both rows, then use the following
UPDATE table
SET (col1, col2, col3,..., col20) = (SELECT col1, col2, col3,..., col20
FROM table
WHERE <your_where_clause1>)
WHERE <your_where_clause_2>;
|
|
|
|
|
I tried it, but it gives an error,
any other solutions.
Thanks anyway.
|
|
|
|
|
Hi,
Anybody knows how to know if an ADO connection has been disconnected even before you execute anything? I have an application which has a global connection object and when it was disconnected from the server for whatever reason, it raises an error when you execute something even if the connection has been restored. Is there a way which I could know if the connection is still valid before I even execute anything? Thanks
SDE
|
|
|
|
|
alex1205 wrote: know if an ADO connection has been disconnected even before you execute anything?
You can check the connection state.
State Property (ADO)[^]
DEBUGGING : Removing the needles from the haystack.
|
|
|
|
|
Hi,
Thanks for the response. I've tried that and it doesn't work. I also tried looking at the error count but it doesn't report any. 
SDE
|
|
|
|
|
alex1205 wrote: Thanks for the response. I've tried that and it doesn't work.
Oh, sorry about that. I take it the Connection State is showing the connection is still active?
alex1205 wrote: it raises an error when you execute something even if the connection has been restored.
In the original post you mention the next time you execute a query it throws an error. What is the error message it throws when this happens?
DEBUGGING : Removing the needles from the haystack.
|
|
|
|
|
Hi all,
I have a question on sql writing
for a table schema as below
Table :
(studentID, studentName, courseID, classID, Age)
What is the correct sql of " select studentID from table where classID = "101" and group by courseID and order by age " ? (ie. Group by courseID, and within the same courseID, the studentID are ordered by age)
Thanks
|
|
|
|
|
SELECT studentID
FROM table
WHRERE classID = "101"
GROUP BY courseID
ORDER BY age
By default, ORDER BY will go in ascending order, if you want descending order use ORDER BY age DESC
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
i've used ASP.NET with Microsoft Access Database before.
Now i would to learn how use (create database, create table, create references, add values, update, delete...) SQL databases with ASP.NET (c#).
Can you indicate me a good web tutorial?
|
|
|
|
|
SQL Server BOL [Books On-Line] (i.e. the built in online-help that comes with SQL server and it available on MSDN) have some excellent examples at the bottom of the pages on each of these T-SQL statements. All you have to do in C# is remember that you can run any T-SQL you like through the SqlCommand object.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
We recently had a fire on campus. So we had to move all our data to new servers. The servers are supposed to be identical. I built a large DTS package to move some data into a database. I also made it a scheduled job, and a vb app that calls the scheduled job so our end users can run it by themselves. It was working fine in the previous servers. Now, if I run the DTS package, it works fine. If I try to run the Scheduled Job through Enterprise Manager or through the VB app it fails. Here is the error I get:
Executed as user: server\user. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE) Error string: ActiveX Scripting encountered a Run Time Error during the execution of the script. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4500 Error Detail Records: Error: -2147220482 (800403FE); Provider Error: 0 (0) Error string: ActiveX Scripting encountered a Run Time Error during the execution of the script. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4500 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
I am not sure why it will work if the DTS package is started by me, but not through the scheduled job. I have been working on this for days and I really don't know what else to try, any help would be GREATLY appreciated.
|
|
|
|
|
I have a DTS package, it works fine if I execute it in Enterprise manager. I set up a scheduled job for it, but if I try to start that job it fails. So it works if I run the DTS package, but not if I try to run the job that calls the package. I read the error message and it says it is trying to execute the DTS package as a local user instead of a domain user. In the DTS I have some files moving to another server, so that is what is causing the failure. How can I get the scheduled job to run as a different user?
|
|
|
|
|
I just made a BIG, STUPID mistake. I deleted all the .resx and .aspx.vb files on my machine through Dreamwaver Test Server view! It will kill me to re-write all the code  .
I have recently re-built the project into .DLL file before deleting those files. Is there anybody know how to decompile .DLL file to get source code back????
I also find there is a tempporary folder "_vti_cnf" under my wwwroot/project folder. In this folder, there are .aspx.vb and .RESX files. However, .aspx.vb file only contains the information like :
vti_encoding:SR|utf8-nl
vti_timelastmodified:TR| 17 Oct 2005 16:58:30 -0000
vti_extenderversion:SR| 4.0.2.8912
Could anybody help me out???
Thank you in advance!!!!!
|
|
|
|
|
See http://www.aisto.com/roeder/dotnet/[^]and get Reflector.NET
It won't get your aspx pages back, but it will produce something that looks sort of similar to your original source code.
Also, it might be a good time to consider the benefits of backing up your work from time to time. You may also want to consider using a source control system even if you are working alone - It is a very good discipline and at anytime you can go back and see previous versions.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
-- modified at 4:14 Thursday 10th November, 2005
|
|
|
|
|
hi colin,
as i ear you talking about source control system, i'd like to ask you.
i've installed TortoiseCVS on my computer, at home (i work alone on my projects). for now, the server and the source files source controled are located on the same computer. but i don't really find out how to :
1. configurate the server,
2. inserting files to be source controled
since this is quite in my hand, i'll then try to use a deported server on another computer. i think the two points i was ask previously remain the same, with the exception that the server will probably not be configured the same way.
do you have any idea of how TortoiseCVS work, how this can be reached, or at least, any tutorial links to point me the right way ?
thanks in advance,
TOXCCT >>> GEII power
[toxcct][VisualCalc]
-- modified at 6:34 Thursday 10th November, 2005
|
|
|
|
|
Sorry, I don't know about TortoiseCVS
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
toxcct wrote: i've installed TortoiseCVS on my computer, at home
I experimented with CVS and wasn’t impressed with it (even TortoiseCVS). Subversion SVN on the other hand has some pretty decent documentation, and supports binary files. Basically it’s a better version of CVS, written by the same people who wrote CVS.
Subversion can be found at subversion.tigris.org[^]
The Subversion book can be found Version Control with Subversion[^]
TortoiseSVN (which much like Tortoise CVS can act as both client and server) can be found here. http://tortoisesvn.tigris.org/[^]
[edit]
I don’t know about Tortoise CVS, but Tortoise SVN counter part comes with a decent HTML help file that shows how to configure it for both client and server role. I had thought of writing an article about subversion, but the more I looked the more information I found (in plain site).
[/edit]
Hope that helps Toxcct, if you have any problems I would be glad to try and help.
DEBUGGING : Removing the needles from the haystack.
-- modified at 6:59 Monday 14th November, 2005
|
|
|
|
|
Thank you very much for your help!
Luckly, I have all .aspx files on the remote server. Now I am using Reflector to decompile the project .DLL file to get readable source code to reconstruct my source code.
It's such a pain! But I have hope now...
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
