Utilizing Oracle.ManagedDataAccess.Client for Oracle Package/Stored Procedure in tableview #2875

GitHubDroid · 2023-10-02T17:08:43Z

GitHubDroid
Oct 2, 2023

I am trying to utilize the Oracle.ManagedDataAccess.Client to execute Oracle Stored Procedures in an Oracle Package and view the results in a table view or datagridview if possible after a button click.

I am able to use the following to produce results in a table view:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Terminal.Gui;
using System.Diagnostics;
using Oracle.ManagedDataAccess.Client;
using Application = Terminal.Gui.Application;
using Window = Terminal.Gui.Window;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.Window;
using System.Windows.Forms;
using System.Drawing.Text;
using MessageBox = Terminal.Gui.MessageBox;

namespace COY_Console
{

    public partial class ConsoleDB : Window
    {
        
        public ConsoleDB()
        {
            InitializeComponent();

            var scheme = new ColorScheme()

            {
                Normal = Application.Driver.MakeAttribute(Color.Green, Color.Black),
                Focus = Application.Driver.MakeAttribute(Color.Green, Color.Black),
                HotNormal = Application.Driver.MakeAttribute(Color.Green, Color.Black),
                HotFocus = Application.Driver.MakeAttribute(Color.Green, Color.Black),
                Disabled = Application.Driver.MakeAttribute(Color.Green, Color.Black),

            };

            this.btnGetPAFS.Clicked += () =>
            {

                try
                {
                    bool SaveStat = false;
                    {

 DataTable dt = new DataTable();
                     
 using (var connection = new Oracle.ManagedDataAccess.Client.OracleConnection("user id = user; password = password; data source=serverdb:1521/server.test.com"))
                        {
                            
                            connection.Open();
Oracle.ManagedDataAccess.Client.OracleCommand cmd = new Oracle.ManagedDataAccess.Client.OracleCommand("SELECT * FROM P2K_AM_USERS", connection);

 var da = new Oracle.ManagedDataAccess.Client.OracleDataAdapter(cmd);

                            da.Fill(dt);


                        }

                        TableView tableView = new TableView()
                        {
                            X = 0,
                            Y = 0,
                            Width = 200,
                            Height = 150,
                        };


                        tableView.Table = dt;


                        Add(tableView);
                        tableView.ColorScheme = scheme;

                    
                        SaveStat = true;
                    }

                    if (SaveStat == true)



                        MessageBox.Query("Report Successfull", "Report_Test", "OK");
                }

                catch

                {
                    MessageBox.ErrorQuery("Report Failed!", "Error Launching Report Test", "OK");

                }

                
           };

            this.ColorScheme = scheme;
       
        }

        }

        }

This produces the following results:
(I have inserted overlays to hide data that is irrelevant)

However when I attempt to use an Oracle Stored Procedure from a Package which utilizes date parameters which always needs to capture the latest saturday to saturday, and a cursor. So far I am not able to get this to work. I get my messageBox.ErrorQuery and the following exception is being thrown:

Exception thrown: 'System.ArgumentException' in Oracle.ManagedDataAccess.dll

The code used for trying to execute the Oracle Store Procedure is the following:


using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Terminal.Gui;
using System.Diagnostics;
using Oracle.ManagedDataAccess.Client;
using Application = Terminal.Gui.Application;
using Window = Terminal.Gui.Window;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.Window;
using System.Windows.Forms;
using System.Drawing.Text;
using MessageBox = Terminal.Gui.MessageBox;

namespace COY_Console
{

    public partial class ConsoleDB : Window
    {
        
        public ConsoleDB()
        {
            InitializeComponent();

            var scheme = new ColorScheme()

            {
                Normal = Application.Driver.MakeAttribute(Color.Green, Color.Black),
                Focus = Application.Driver.MakeAttribute(Color.Green, Color.Black),
                HotNormal = Application.Driver.MakeAttribute(Color.Green, Color.Black),
                HotFocus = Application.Driver.MakeAttribute(Color.Green, Color.Black),
                Disabled = Application.Driver.MakeAttribute(Color.Green, Color.Black),

            };
            

            this.btnGetPAFS.Clicked += () =>
            {

                try
                {
                    bool SaveStat = false;
                    {
                        var Date2 = new Terminal.Gui.TextField();
                        var Date1 = new Terminal.Gui.TextField();

                        Date2.Visible = false;
                        Date1.Visible = false; 

                        // Date range always Saturday to Saturday

                        DateTime now = System.DateTime.Now;

                        DateTime result2 = now.AddDays(-((now.DayOfWeek - System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat.FirstDayOfWeek + 8) % 7)).Date;

                        Date2.Text = result2.ToShortDateString();

                        DateTime result1 = result2.AddDays(-7).Date;

                        Date1.Text = result1.ToShortDateString();
                      
                        DataTable dt = new DataTable();
                    
                        using (var connection = new Oracle.ManagedDataAccess.Client.OracleConnection("user id = user; password = password; data source=TestDB:1521/Server.test.com"))
                        {
                          
                            connection.Open();
                            var cmd = new Oracle.ManagedDataAccess.Client.OracleCommand("P2K.PAF_REPORTER.RPT_COY_WEEKLY_PA", connection);
                           
                           cmd.CommandType = CommandType.StoredProcedure;

                            // Oracle Parameters

                            OracleParameter p1 = new OracleParameter();
                            p1.Value = Date1.Text;

                            OracleParameter p2 = new OracleParameter();
                            p2.Value = Date2.Text;

                            OracleParameter rcursor = new OracleParameter("rcursor", OracleDbType.RefCursor, ParameterDirection.Output);

                            // Add Oracle Parameters

                            cmd.Parameters.Add(p1);
                            cmd.Parameters.Add(p2);
                            cmd.Parameters.Add(rcursor);  

                            // Excecute cmd Stored Procedure

                            cmd.ExecuteNonQuery();

                            var da = new Oracle.ManagedDataAccess.Client.OracleDataAdapter(cmd);

                            da.Fill(dt);

                        }

                        TableView tableView = new TableView()
                        {
                            X = 0,
                            Y = 0,
                            Width = 200,
                            Height = 150,
                        };


                        tableView.Table = dt;                             

                        Add(tableView);
                        tableView.ColorScheme = scheme;

                      
                        SaveStat = true;
                    }

                    if (SaveStat == true)



                        MessageBox.Query("Report Successfull", "REPORT COY_WEEKLY_PA_RPT", "OK");
                }

                catch

                {
                    MessageBox.ErrorQuery("Report Failed!", "Error Launching COY_WEEKLY_PA_RPT", "OK");

                }

                
           };

            this.ColorScheme = scheme;                    
         
        }

        }

        }

Also I am including the respective designer file (ConsoleDB.Designer.cs) as well :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace COY_Console
{
    using System;
    using System.Data;
    using Terminal.Gui;
    using Oracle.ManagedDataAccess.Client;

    public partial class ConsoleDB : Terminal.Gui.Window
    {


        private Terminal.Gui.ColorScheme greenOnBlack;

        private Terminal.Gui.ColorScheme blueOnBlack;


        private Terminal.Gui.Label lblSystem;

        private Terminal.Gui.Label lblSubsys;

        private Terminal.Gui.Label lblDisplay;


        private Terminal.Gui.Label lblProg_prc;

        //private Terminal.Gui.Label lblMenu;

        private Terminal.Gui.Label lblLib;

        private Terminal.Gui.TextField Date2;
        private Terminal.Gui.TextField Date1;
        private Terminal.Gui.Button btnGetPAFS;

        private Terminal.Gui.Label lblCopyright;

        private Terminal.Gui.LineView lineView;

        private Terminal.Gui.TableView tableView;
        private DataTable dt;
       
        private void InitializeComponent()  {
            this.Date2 = new Terminal.Gui.TextField();
            //this.Date2.Visible = false;
            this.Date1 = new Terminal.Gui.TextField();
            //this.Date1.Visible = false;
            //this.tableView = new Terminal.Gui.TableView();
            //this.dt = new DataTable();
            this.Add(Date1);
            this.Add(Date2);
          
            this.lineView = new Terminal.Gui.LineView();
            this.lblCopyright = new Terminal.Gui.Label();
            this.btnGetPAFS = new Terminal.Gui.Button();

            this.lblLib = new Terminal.Gui.Label();
            //this.lblMenu = new Terminal.Gui.Label();
            this.lblProg_prc = new Terminal.Gui.Label();

            this.lblDisplay = new Terminal.Gui.Label();
            this.lblSubsys = new Terminal.Gui.Label();
            this.lblSystem = new Terminal.Gui.Label();

            this.greenOnBlack = new Terminal.Gui.ColorScheme();
            this.greenOnBlack.Normal = new Terminal.Gui.Attribute(Terminal.Gui.Color.Green, Terminal.Gui.Color.Black);
            this.greenOnBlack.HotNormal = new Terminal.Gui.Attribute(Terminal.Gui.Color.BrightGreen, Terminal.Gui.Color.Black);
            this.greenOnBlack.Focus = new Terminal.Gui.Attribute(Terminal.Gui.Color.Green, Terminal.Gui.Color.Magenta);
            this.greenOnBlack.HotFocus = new Terminal.Gui.Attribute(Terminal.Gui.Color.BrightGreen, Terminal.Gui.Color.Magenta);
            this.greenOnBlack.Disabled = new Terminal.Gui.Attribute(Terminal.Gui.Color.Gray, Terminal.Gui.Color.Black);
            this.blueOnBlack = new Terminal.Gui.ColorScheme();
            this.blueOnBlack.Normal = new Terminal.Gui.Attribute(Terminal.Gui.Color.BrightBlue, Terminal.Gui.Color.Black);
            this.blueOnBlack.HotNormal = new Terminal.Gui.Attribute(Terminal.Gui.Color.Cyan, Terminal.Gui.Color.Black);
            this.blueOnBlack.Focus = new Terminal.Gui.Attribute(Terminal.Gui.Color.BrightBlue, Terminal.Gui.Color.BrightYellow);
            this.blueOnBlack.HotFocus = new Terminal.Gui.Attribute(Terminal.Gui.Color.Cyan, Terminal.Gui.Color.BrightYellow);
            this.blueOnBlack.Disabled = new Terminal.Gui.Attribute(Terminal.Gui.Color.Gray, Terminal.Gui.Color.Black);
            this.Width = Dim.Fill(0);
            this.Height = Dim.Fill(0);
            this.X = 0;
            this.Y = 0;
            this.Visible = true;
            this.ColorScheme = this.greenOnBlack;
            this.Modal = false;
            this.IsMdiContainer = false;
            this.Border.BorderStyle = Terminal.Gui.BorderStyle.Single;
            this.Border.Effect3D = false;
            this.Border.Effect3DBrush = null;
            this.Border.DrawMarginFrame = true;
            this.TextAlignment = Terminal.Gui.TextAlignment.Left;
            this.Title = "";

            this.lblSystem.Width = 6;
            this.lblSystem.Height = 1;
            this.lblSystem.X = 129;
            this.lblSystem.Y = 9;
            this.lblSystem.Visible = true;
            this.lblSystem.ColorScheme = this.greenOnBlack;
            this.lblSystem.Data = "lblSystem";
            this.lblSystem.Text = "System  .  .  .  .  .  :      COYTERM/400";
            this.lblSystem.TextAlignment = Terminal.Gui.TextAlignment.Left;
            this.Add(this.lblSystem);
            this.lblSubsys.Width = 4;
            this.lblSubsys.Height = 1;
            this.lblSubsys.X = 129;
            this.lblSubsys.Y = 10;
            this.lblSubsys.Visible = true;
            this.lblSubsys.ColorScheme = this.greenOnBlack;
            this.lblSubsys.Data = "lblSubsys";
            this.lblSubsys.Text = "Subsystem  .  .  .  .  :      COYSUBTERM";
            this.lblSubsys.TextAlignment = Terminal.Gui.TextAlignment.Left;
            this.Add(this.lblSubsys);
            this.lblDisplay.Width = 4;
            this.lblDisplay.Height = 1;
            this.lblDisplay.X = 129;
            this.lblDisplay.Y = 11;
            this.lblDisplay.Visible = true;
            this.lblDisplay.ColorScheme = this.greenOnBlack;
            this.lblDisplay.Data = "lblDisplay";
            this.lblDisplay.Text = "Display .  .  .  .  .  :      COYDSPDEV000R";
            this.lblDisplay.TextAlignment = Terminal.Gui.TextAlignment.Left;
            this.Add(this.lblDisplay);

            this.lblProg_prc.Width = 4;
            this.lblProg_prc.Height = 2;
            this.lblProg_prc.X = 85;
            this.lblProg_prc.Y = 21;
            this.lblProg_prc.Visible = true;
            this.lblProg_prc.ColorScheme = this.greenOnBlack;
            this.lblProg_prc.Data = "lblProg_prc";
            this.lblProg_prc.Text = "Program/Procedure .  .  .  .  .  .  .  .  .  .    __________________";
            this.lblProg_prc.TextAlignment = Terminal.Gui.TextAlignment.Centered;
            this.Add(this.lblProg_prc);
            //this.lblMenu.Width = 4;
            // this.lblMenu.Height = 2;
            //this.lblMenu.X = 85;
            // this.lblMenu.Y = 23;
            // this.lblMenu.Visible = true;
            // this.lblMenu.ColorScheme = this.greenOnBlack;
            // this.lblMenu.Data = "lblMenu";
            // this.lblMenu.Text = "Menu  .  .  .  .  .  .  .  .  .  .  .  .  .  .    __________________";
            //this.lblMenu.TextAlignment = Terminal.Gui.TextAlignment.Centered;
            // this.Add(this.lblMenu);
            this.lblLib.Width = 4;
            this.lblLib.Height = 2;
            this.lblLib.X = 85;
            this.lblLib.Y = 25;
            this.lblLib.Visible = true;
            this.lblLib.ColorScheme = this.greenOnBlack;
            this.lblLib.Data = "lblLib";
            this.lblLib.Text = "Current Library   .  .  .  .  .  .  .  .  .  .    __________________";
            this.lblLib.TextAlignment = Terminal.Gui.TextAlignment.Centered;
            this.Add(this.lblLib);

            this.btnGetPAFS.Width = 18;
            this.btnGetPAFS.Height = 1;
            this.btnGetPAFS.X = 135;
            this.btnGetPAFS.Y = 32;
            this.btnGetPAFS.Visible = true;
            this.btnGetPAFS.Data = "btnGetPAFS";
            this.btnGetPAFS.Text = "Pull Latest COY PAFs";
            this.btnGetPAFS.TextAlignment = Terminal.Gui.TextAlignment.Justified;
            this.btnGetPAFS.IsDefault = false;
            this.Add(this.btnGetPAFS);
            this.lineView.Width = 240;
            this.lineView.Height = 1;
            this.lineView.X = -1;
            this.lineView.Y = 60;
            this.lineView.Visible = true;
            this.lineView.ColorScheme = this.blueOnBlack;
            this.lineView.Data = "lineView";
            this.lineView.TextAlignment = Terminal.Gui.TextAlignment.Left;
            this.lineView.LineRune = '─';
            this.lineView.Orientation = Terminal.Gui.Graphs.Orientation.Horizontal;
            this.Add(this.lineView);
          
        }
      
    }
}

Any insight or help would be greatly appreciated.

GitHubDroid · 2023-10-05T13:34:05Z

GitHubDroid
Oct 5, 2023
Author

It seems that when I introduce parameters I get this issue. I am able to use the same in a winform application with datagridview with no problem. Any Ideas ?

0 replies

GitHubDroid · 2023-10-05T15:20:11Z

GitHubDroid
Oct 5, 2023
Author

I figured it out. I needed to specify the Oracle Data Type property ("OracleDbType.Varchar2") for the p1 and p2 parameters.

Old Code that did not work:

                    var p1 = new OracleParameter();
                    p1.Value = this.Date1.Text;

                    var p2 = new OracleParameter();
                    p2.Value = Date2.Text;

Fixed Working Code:

                    var p1 = new OracleParameter("p1", OracleDbType.Varchar2);
                    p1.Value = this.Date1.Text;

                    var p2 = new OracleParameter("p2", OracleDbType.Varchar2);
                    p2.Value = Date2.Text;

0 replies

GitHubDroid · 2023-10-05T15:23:41Z

GitHubDroid
Oct 5, 2023
Author

Fixed and working solution for calling Oracle Stored Procedure from Button Click :


this.btn.Clicked += () =>
            {
                DataTable dt = new DataTable();
                using (OracleConnection connection = new OracleConnection("user id = username; password = pw; data source=testdb:1521/testdb.data.com"))
                {
                    connection.Open();

                    OracleCommand cmd = new Oracle.ManagedDataAccess.Client.OracleCommand("PACKAGE.STORED_PROCEDURE", connection);
                   
                    cmd.CommandType = CommandType.StoredProcedure;
                    // Oracle Parameters

                    var p1 = new OracleParameter("p1", OracleDbType.Varchar2);
                    p1.Value = this.Date1.Text;

                    var p2 = new OracleParameter("p2", OracleDbType.Varchar2);
                    p2.Value = Date2.Text;

                    var rcursor = new OracleParameter("rcursor", OracleDbType.RefCursor, ParameterDirection.Output);

                    // Add Oracle Parameters

                    cmd.Parameters.Add(p1);
                    cmd.Parameters.Add(p2);
                    cmd.Parameters.Add(rcursor);  

                    // Excecute cmd Stored Procedure

                    cmd.ExecuteNonQuery();
                    OracleDataAdapter adapter = new Oracle.ManagedDataAccess.Client.OracleDataAdapter(cmd);

                    adapter.Fill(dt);

                    DataTable results = dt;
                 
                    if (results != null && results.Rows.Count > 0)  // Check datatable is null or not
                    {

                        foreach (DataRow dr in dt.Rows)
                        {
                            StringWriter sw = new StringWriter();
                            foreach (DataColumn col in dt.Columns)
                            {
                                
                                sw.Write((dr[(col)].ToString() + ", "));
                                
                            }

                            var output = sw.ToString();

                            if (output.Length > 2) 
                            { 
                            
                                output = output.Substring(0, output.Length - 2);
                            }
                            
                            Debug.WriteLine(output);

                        
                        }

1 reply

tig Oct 6, 2023
Maintainer

Glad you figured it out.

Neat to see apps like this.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Utilizing Oracle.ManagedDataAccess.Client for Oracle Package/Stored Procedure in tableview #2875

Uh oh!

{{title}}

Uh oh!

Replies: 3 comments 1 reply

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{editor}}'s edit

{{editor}}'s edit

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{editor}}'s edit

{{editor}}'s edit

Uh oh!

Uh oh!

{{title}}

Uh oh!

Select a reply

Uh oh!

Utilizing Oracle.ManagedDataAccess.Client for Oracle Package/Stored Procedure in tableview #2875

Uh oh!

GitHubDroid Oct 2, 2023

Replies: 3 comments · 1 reply

Uh oh!

GitHubDroid Oct 5, 2023 Author

Uh oh!

Uh oh!

GitHubDroid Oct 5, 2023 Author

Uh oh!

Uh oh!

GitHubDroid Oct 5, 2023 Author

Uh oh!

tig Oct 6, 2023 Maintainer

GitHubDroid
Oct 2, 2023

Replies: 3 comments 1 reply

GitHubDroid
Oct 5, 2023
Author

GitHubDroid
Oct 5, 2023
Author

GitHubDroid
Oct 5, 2023
Author

tig Oct 6, 2023
Maintainer