none
Oracle - Check the tables, rows (dataset), how to insert dataset into oracle RRS feed

  • Question

  • Hello,
    I have received the following description from a customer.

    I found this link, but I need it in C#.

    Customer use 11G.
    I installed
    Assembly Oracle.ManagedDataAccess
        C:\Program Files (x86)\Oracle Developer Tools for VS2017\odp.net\managed\common\Oracle.ManagedDataAccess.dll
     


    GET_SERIAL_NUMBERSui

    My first question is, how can I question the values?
    Do I need to know the table names?
    Do I need to know the table/columns names?
    Can I bring the result into a dataset, then look there what names it contains?
    Then I don't need an ExecuteNonQuery?
    using (OracleConnection connection = new OracleConnection(Cfg.DbConnectionstring))
                {
                    OracleCommand cmd = new OracleCommand("La.GETSERIALNUMBERS", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("pInbatch_id",    OracleDbType.Varchar2, "SupplierXY-19-03-19",         ParameterDirection.Input);
    
    cmd.Parameters.Add("pOutunprocessedCount",    OracleDbType.Varchar2, ParameterDirection.Output);
                    cmd.Parameters.Add("pOutError_message",    OracleDbType.Varchar2, ParameterDirection.Output);
    
    cmd.ExecuteNonQuery();
    
                        OracleDecimal d = (OracleDecimal)cmd.Parameters["pOutunprocessedCount"].Value;
      int retValue = d.ToInt32();
    Can someone publish an example for me? Thank you in advance.
    My second question is
    After the process, I have the result table.
    I want to pass the reult table and send the StoredProcedure with it. How can I do that?
    // --------------------------------------------------------------
    My third question, general.
    I do not have an Oracle license.
    Which tool can I use for test? Maybe this one?https://softfamous.com/toad-for-oracle/
    Do you have here information for me for connection string?
    <DATABASE connectionstring="Data Source=192.168.0.123:1521;User ID=demo; Password=test14;"/>

    Best regards, Markus

    Tuesday, March 19, 2019 5:14 PM

Answers

  • Hello,

    Unfortunately I have no simple examples for working with Oracle as 100 percent of my code is for Enterprise level solutions which in short means separation of concerns and while at Microsoft this week with limited time.

    In regards to tools, we use paid versions of Toad. If not open to this look at SQL-Developer which is free but nearly as rich as Toad.

    To use Oracle consider the free edition.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, March 20, 2019 6:35 PM
    Moderator

All replies

  • https://www.oracle.com/technetwork/topics/dotnet/community/index.html

    IMO, you should post to an Oracle forum.

    Tuesday, March 19, 2019 5:50 PM
  • Hello,

    In short, in the first section of your question, if the stored procedure is using a SELECT then don't use ExecuteNonQuery to get results, use ExecuteReader. If you want the data to be returned in a container e.g. DataTable create the DataTable, then for the command object use ExecuteReader e.g.

    someDataTable.Load(cmd.ExecuteReader());

    If you want to iterate the data 

    var reader = cmd.ExecuteReader();

    Assert there are rows e.g. reader.HasRows in a if statement, if there are rows then do while reader.Read for multiple results or reader.read for single result then get field data via reader.Getxxx e.g. reader.GetString etc.

    Do you need to know table and column names? usually unless you are writing some abstract method which is fairly complex.

    In general, get to learn how to work with stored procedures high level then when comfortable learn the specifics of Oracle which is quite different from SQL-Server.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, March 19, 2019 6:30 PM
    Moderator
  • Hello Karen,
    Thanks for Response, was helpful.
    var DR = cmd.ExecuteReader();
    while ( DR.Read() ) 
    {
    //** If I know the columns name
     FclOutput.PrintOut( DR["Name"].ToString() + " (Product #" + DR["ID"].ToString() + ") " + "costs " + DR["Price"].ToString() + " €" );
    // ** If not, I can use the index, right?
         DR[0].ToString() + DR[1].ToString()
    // ** How many columns are in the result
    // ** 
        DR.GetValue(0) + DR.GetValue(1);
    }


    Thanks in advance for more tips.
    Any idea for a test equipment for orcale?Tool
    I will soon get a script from my client to create the data table. However, I have no oracle license.
    I just have to read the data, maybe change it and send it back.
    I am looking for a way to test it in advance, not more.
    Best regards, Markus
    Wednesday, March 20, 2019 5:44 PM
  • Hello,

    Unfortunately I have no simple examples for working with Oracle as 100 percent of my code is for Enterprise level solutions which in short means separation of concerns and while at Microsoft this week with limited time.

    In regards to tools, we use paid versions of Toad. If not open to this look at SQL-Developer which is free but nearly as rich as Toad.

    To use Oracle consider the free edition.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, March 20, 2019 6:35 PM
    Moderator