StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>One approach is to use some PL/SQL to read the data in the table object into PL/SQL associative arrays, one per column, and then read these using ODP.NET:</p> <pre class="lang-c# prettyprint-override"><code> private const string PlSqlBlock = @" DECLARE l_egyen_tab pck_egyen.egyentab; BEGIN pck_egyen.list(l_egyen_tab); FOR i IN 1..l_egyen_tab.COUNT LOOP :ids(i) := l_egyen_tab(i).id; :tajszams(i) := l_egyen_tab(i).tajszam; :nevs(i) := l_egyen_tab(i).nev; END LOOP; END;"; public static void ListEgyenTable(OracleConnection con) { using (var cmd = new OracleCommand(PlSqlBlock, con)) { OracleParameter idParam = cmd.Parameters.Add("ids", OracleDbType.Decimal); OracleParameter tajszamParam = cmd.Parameters.Add("tajszams", OracleDbType.Varchar2); OracleParameter nevParam = cmd.Parameters.Add("nevs", OracleDbType.Varchar2); int arraySize = 1000; int[] varcharArrayBindSize = Enumerable.Repeat(4000, arraySize).ToArray(); foreach (OracleParameter param in cmd.Parameters) { param.Direction = ParameterDirection.Output; param.CollectionType = OracleCollectionType.PLSQLAssociativeArray; param.Size = arraySize; if (param.OracleDbType == OracleDbType.Varchar2) { param.ArrayBindSize = varcharArrayBindSize; } } cmd.ExecuteNonQuery(); if (idParam.Value is OracleDecimal[] && tajszamParam.Value is OracleString[] && nevParam.Value is OracleString[]) { List<decimal> ids = (idParam.Value as OracleDecimal[]).Select(dec => dec.Value).ToList(); List<string> tajszams = (tajszamParam.Value as OracleString[]).Select(str => str.Value).ToList(); List<string> nevs = (nevParam.Value as OracleString[]).Select(str => str.Value).ToList(); for (int i = 0; i < ids.Count; ++i) { Console.WriteLine("Got id {0}, tajszam {1}, nev {2}", ids[i], tajszams[i], nevs[i]); } } else { Console.WriteLine("Sorry, returned data not as expected :("); } } } </code></pre> <p>The only complication here is <code>arraySize</code>. This value needs to be at least the number of rows that will be returned from your stored procedure, and if it is too small you will get an ORA-06513 'PL/SQL: index for PL/SQL table out of range for host language array' error.</p> <p>I created your table and package, and inserted into the table the following test data:</p> <pre class="lang-none prettyprint-override"><code>SQL> select * from egyen; ID TAJSZAM NEV ---------- --------- ---------- 1 abc defg 2 def mnop 3 ghi qrstu 4 jkl vwxyz </code></pre> <p>When I ran the C# code above, I got the following output:</p> <pre> Got id 1, tajszam abc, nev defg Got id 2, tajszam def, nev mnop Got id 3, tajszam ghi, nev qrstu Got id 4, tajszam jkl, nev vwxyz </pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload