Forum Stats

  • 3,783,352 Users
  • 2,254,762 Discussions
  • 7,880,372 Comments

Discussions

Using sysdate as a parameter when calling procedure

660148
660148 Member Posts: 1
edited Sep 17, 2008 8:15AM in ODP.NET
Is it possible to use sysdate as a parameter when calling a procedure in an OracleCommand?

As in:

Dim cnX As OracleConnection
cnX = New OracleConnection(connectionString)
Dim cmX As OracleCommand
cmX = New OracleCommand("{call dev.myproc(000, 'P1', (select to_char(sysdate-1, 'mm/dd/yyyy hh24mi') from dual), ?)}", cnX)

Thanks &
Cheers.

Answers

  • 24208
    24208 Member Posts: 1,295
    Hi,

    What's up with the braces and the questions marks -- not the sort of thing one would normally see in ODP.NET code.

    Anyway, are you asking if you have a PL/SQL procedure that takes an Oracle Date as a parameter, can you pass "sysdate" as the value for that parameter from an ODP.NET client application?

    Something like this:
    SQL> create or replace procedure date_test (p_in in date, p_out out varchar2) as
      2  begin
      3    p_out := to_char(p_in);
      4  end;
      5  /
    
    Procedure created.
    The question would be how to call it since you can't really specify the literal "sysdate" as the value for an OracleParameter in the .NET code declared as OracleDbType.Date; however, if you use an anonymous block, you could pass in sysdate (or a variable thereof) for that parameter and still use OracleParameter objects in the .NET code for the remaining parameters.

    Using the above PL/SQL procedure, here's a quick and dirty C# sample:
    using System;
    using System.IO;
    using System.Threading;
    using System.Diagnostics;
    using System.Data;
    using System.Text;
    using Oracle.DataAccess.Client;
    using Oracle.DataAccess.Types;
    
    namespace Miscellaneous {
      class Program {
        static unsafe void Main(string[] args) {
          string constr = "User Id=orademo; " +
                          "Password=oracle; " +
                          "Data Source=orademo; " +
                          "Enlist=false;" +
                          "Pooling=false";
    
          OracleConnection con = new OracleConnection(constr);
          con.Open();
    
          OracleCommand cmd = con.CreateCommand();
          cmd.CommandText = "begin date_test(sysdate - 1, :1); end;";
    
          OracleParameter p_out = new OracleParameter("1", OracleDbType.Varchar2, null, ParameterDirection.Output);
          p_out.Size = 32;
    
          cmd.Parameters.Add(p_out);
    
          cmd.ExecuteNonQuery();
    
          Console.WriteLine("p_out: {0}\n", p_out.Value.ToString());
    
          p_out.Dispose();
          cmd.Dispose();
          con.Dispose();
          
          Console.Write("ENTER to continue...");
          Console.ReadLine();
        }
      }
    }
    This produces the following output on my system:
    p_out: 16-SEP-2008 08:14:10

    ENTER to continue...
    Maybe that is of some help,

    Mark
This discussion has been closed.