Click here to Skip to main content
15,886,963 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have multiple stores and i need to create a new Stored Procedure in every store
i have created 11 files in a folder and executing them one by one the 9 and 10 is the stored procedure..every thing is working fine but Stored Procedure is creating with errors

error

SQL
PROCEDURE 01951.PROCESS_SCANNED_BARCODE_GDN
On line:  1
PLS-00103: Encountered the symbol "" when expecting one of the following:

   ( ; is with authid as cluster compress order using compiled
   wrapped external deterministic parallel_enable pipelined


and when i am running the same in Toad without a single change it's compiling without any error

here is my code
C#
foreach (DataRow row in ds.Tables[0].Rows)
      {
          try
          {
              user_id = row["STORE"].ToString();
              //goto skip;
              password = Convert.ToInt32(row["STORE"].ToString()) + 7;
              ftp_ip = row["FTP_IP"].ToString().Trim();
              constr = "Data Source=" + ftp_ip + "/orcl;User ID=" + user_id + ";Password=" + password + "";
              store_con = new OracleConnection(constr);
              store_con.Open();
              for (int i = 1; i < 12; i++)
              {
                  using (StreamReader reader = new StreamReader("C:\\DTS_Package\\HHT\\" + i.ToString() + ".txt"))
                  {
                      try
                      {
                          str = reader.ReadToEnd();
                          store_cmd = new OracleCommand(str, store_con);
                          store_cmd.ExecuteScalar();
                      }
                      catch (Exception ex)
                      {

                      }

                  }
              }
              str = "INSERT INTO STORES_NOT_CONNECTED VALUES ('" + user_id + "','" + ftp_ip + "','Updated',SYSDATE,'HHT')";
              cmd = new OracleCommand(str, con);
              cmd.ExecuteNonQuery();
              store_con.Close();
          }
          catch (Exception ex)
          {
              str = "INSERT INTO STORES_NOT_CONNECTED VALUES ('" + user_id + "','" + ftp_ip + "','" + ex.Message + "',SYSDATE,'HHT')";
              cmd = new OracleCommand(str, con);
              cmd.ExecuteNonQuery();
          }

      }
Posted
Updated 12-May-15 0:35am
v4

1 solution

Instead of concatenating your SQL-statements like you do here, you should use Sql-Parameters (or here "OracleParameter"). It will not only resolve the issue you're having here (because of quotes interfering with each other) but also make your SQL-statements safe against SQL-injection-attacks and make your code more readable and therefore more maintainable.

Please see here:
https://msdn.microsoft.com/en-us/library/system.data.oracleclient.oraclecommand.parameters%28v=vs.110%29.aspx[^]
Gotcha #1161: Using Named Parameters with Oracle ODP.NET[^]
 
Share this answer
 
Comments
Basmeh Awad 6-May-15 7:02am    
Thanks for your reply
Still i didn't understand how i will use this. if i am using a insert statement then i can use oracle Parameters but for creating a stored procedure how can i use it. procedure is already written in my text file i just need to execute it
Sascha Lefèvre 6-May-15 19:39pm    
I apologize, I haven't paid enough attention when reading your code. You are correct, your problem isn't related to using or not using Sql-Parameters.

I'm not sure if this will be the solution, but using ExecuteScalar() is unusual (for this purpose). Please change it to ExecuteNonQuery() and see if that makes a difference. If it still doesn't work, please post the stored procedure/file content.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900