Call a stored procedure with date parameter from C#

Question

I want to create and run a stored procedure with an IN parameter, which is a date. Once the date passed into the procedure I want to run a simple select statement to transfer all data into a destination table.

Assuming that source table and destination tables are exactly same, I'm using:

CREATE OR REPLACE procedure Test ( start_date in data ) as

Begin

   insert into Destination_table (column 1 , column2, date_Column)
   Select column1, column 2, Date_column 
     from Source_table 
    Where date_column = Start_date;
Commit;
End Test;

Edited ...

CREATE OR REPLACE PROCEDURE Procedure_TEST ( s_date IN date ) AS
BEGIN

   INSERT INTO Ps_dest_table ( Name, Salary, STATEMENTDATE )
   SELECT Name, Salary, STATEMENTDATE 
     FROM ps_Source_table 
    WHERE (statementdate = s_date)                              

END procedure_TEST;

Is this the correct way of doing this? or am I missing something? How can I call this from C# code?

Yes, this is correct, but remove the commit. Transaction control should be managed by the caller. See here for exampel for C# http://stackoverflow.com/questions/3940587/calling-oracle-stored-procedure-from-c-net — OldProgrammer, Jun 26 '13 at 17:58
I tried this but I get source does not have a runnable target.Also from the program when I run this proc nothing happens. example destination table has no data. — Aaron, Jun 26 '13 at 18:02
Well, then please update your question with the most correct details. You get this error, " source does not have a runnable target" from C#? — OldProgrammer, Jun 26 '13 at 18:09
I get this error in SQL Developer itself When I run the procedure as script. Edited the Question with the real Procedure — Aaron, Jun 26 '13 at 18:12
You are missing a semi-colon at the end of the insert: WHERE (statementdate = s_date); — OldProgrammer, Jun 26 '13 at 18:25
My final comment - As I asked, please compile the procedure from sqlplus and see if that give an error to exclude any behavior of SQLdeveloper. — OldProgrammer, Jun 26 '13 at 18:51
@ OldProgrammer Can you tell me the syntax of executing this procedure on SQL plus ? also, if you can make a dummy proc that is functional. So , I can compare where the mistake is in my procedure. — Aaron, Jun 27 '13 at 12:50

score 0 · Answer 1 · answered Jun 26 '13 at 18:08

This looks correct. You have some inconsistencies in your spacing in your example, though.

insert into Destination_table (column 1 , column2, date_Column)

should be:

insert into Destination_table (column1 , column2, date_Column)

and

Select column1, column 2, Date_column from Source_table

should be:

Select column1, column2, Date_column from Source_table

what is your actual query?

score 0 · Answer 2 · edited Jun 26 '13 at 18:51

0

It seems the guys answered your first question about the procedure.

To call it from .Net/C# you can use Oracle Data Provider .Net (ODP.Net) its a free API from Oracle, you can find it here.

edited Jun 26 '13 at 18:51

Ben

51,770
36
127
149

answered Jun 26 '13 at 18:37

Jafar Kofahi

763
6
22

score 0 · Answer 3 · answered Jul 02 '13 at 13:38

Solved! The Problem was not with how I had the stored procedure written. but there is a bug in the SQLDeveloper itself. when I created the stored procedure and compiled it. It complied but did not run. I was getting "Source does not have a runnable target." I simply closed the application and restarted it and the procedure executed without any problems. So, anyone who has a same kind of problem. Just, restarting the application can solve your problem pending that your procedure compiles.

Call a stored procedure with date parameter from C#

3 Answers3