Error ORA-00907 Right parenthesis missing

Question

Hello i have a long SQL Query for Oracle DB. My Problem is i'm searching for arround 2 hours to get the error fixed. On all Queries i have the same error with parenthesis.

Here is my class where i get the error inside the command of OracleDB Query. I didnt find the error. The Query shown below is working on Delphi without problems. The only thing i change are the quotation marks at the beginning and at the end of each line which a normally single quoted.

using System;
using System.Windows.Forms;
using Oracle.ManagedDataAccess.Client;

namespace RawBinderLabel
{
    public partial class Rawbinder_Manually : Form
    {
        private database_conn db_conn = new database_conn();
        private OracleConnection OraConn = new OracleConnection();

        public Rawbinder_Manually()
        {
            InitializeComponent();
            get_rawbinder_data();
        }

        public void get_rawbinder_data()
        {
            string rcs = db_conn.connection();

            using (OracleConnection OraConn = new OracleConnection(rcs))
            {
                using (OracleCommand OraCmd = OraConn.CreateCommand())
                {
                    try
                    {
                        OraConn.Open();
                        OraCmd.BindByName = true;

                        //SQL Command to retrieve manual binder that ar OK
                        OraCmd.CommandText = "SELECT l.a_layer_pos, " +
     " l.a_serial, " +
     " P_ROHBINDER_ETIKETTEN.GET_BARCODE (mb.a_serial,''M'') Barcode, " +
     " mb.a_splitted," +
     " mb.a_dlam, " +
     " l.a_order_id, " +
     " l.a_section_id, " +
     " P_ROHBINDER_ETIKETTEN.GET_MANUALPOSITIONS (mb.a_serial) Positionen, " +
     " P_ROHBINDER_ETIKETTEN.GET_MANUALABBUND (mb.a_serial) abbund, " +
     " P_ROHBINDER_ETIKETTEN.GET_MANUALQUALITY (mb.a_prodquality_id) qualitaet, " +
     " to_char(lt.LIEFERTERMIN, ''dd.mm.yyyy'') liefertermin, " +
     " ''1'' a_beam_pos, " +
     " P_ROHBINDER_ETIKETTEN.GET_MAXMANUALLENGTH (l.a_serial) max_a_length, " +
     " substr(c.a_cust_name,1,40) kunde, " +
     " l.a_dimter_start_date, " +
     " l.a_dimter_end_date, " +
     " p.a_prod_date, " +
     " p.a_destheight, " +
     " p.a_description, " +
     " dl.a_layer_serial, " +
     " dl.a_width, " +
     " dl.a_lamellaheight, " +
     " dl.A_RAWWIDTH, " +
     " dl.A_RAWLAMELLAHEIGHT, " +
     " dl.A_GLUE_DESCRIPTION, " +
     " dl.A_COLOR_DESCRIPTION, " +
     " dl.a_pressproc_description, " +
     " dl.a_pressproc_description ||' - ' || p.a_description || '' - ('' ||p.a_destlength || '')'' pressbett, " +
     " mb.a_serial beam_serial," +
     " mb.a_prodquality_id," +
 " FROM t_manual_layer l," +
    "t_manual_pressproc p, " +
    "t_dimter_layer dl, " +
    "t_manualbeam mb, " +
    "v_liefertermin lt, " +
    "t_order o, " +
    "t_customer c " +
" WHERE to_char(p.a_prod_date,''DD.MM.YYYY'') = ''' :rawbinder_date '''" +
  " and p.a_pressproc_id = l.a_pressproc_id " +
  " AND l.A_SERIAL = dl.A_SERIAL " +
  " AND mb.A_LAYER_SERIAL = l.a_serial " +
  " AND lt.PROJEKTNR = l.a_order_id " +
  " AND lt.GPID = l.a_section_id " +
  " AND o.a_order_id = l.a_order_id " +
  " and c.a_customer_id = o.a_customer_id " +
" order by l.A_SERIAL, l.a_layer_pos";

                        //Assign Parameters to Date selected in Overview Form
                        OracleParameter rawbinder_date = new OracleParameter("rawbinder_date", RawBinder_Overview.rawbinder_date);
                        OraCmd.Parameters.Add(rawbinder_date);

                        //Execute the command and display it using DataReader
                        OracleDataReader OraDataRead = OraCmd.ExecuteReader();
                        while (OraDataRead.Read())
                        {
                            Console.WriteLine("Manually Implemented" + OraDataRead.GetString(0));
                        }
                    }
                    catch (OracleException ex)
                    {
                        switch (ex.Number)
                        {
                            case 1:
                                MessageBox.Show("Fehler beim Einfügen der Daten");
                                break;

                            case 12560:
                                MessageBox.Show("Die Datenbank ist nicht erreichbar.");
                                break;

                            default:
                                MessageBox.Show("Datenbankfehler: " + ex.Message.ToString());
                                break;
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message.ToString());
                    }
                    finally
                    {
                        OraConn.Dispose();
                    }
                }
            }
        }
    }
}

Does someone has an Idea how to fix that Problem?

Answer 1

I think there are multiple issues:

• 2 times single quotes are not required everywhere
• comma after " mb.a_prodquality_id," + is not required as it is the last expression of SELECT clause
• as mentioned in the comment by @crowcoder, single quotes around parameters are not required. Means around this: :rawbinder_date

Try to solve all this problems and check if it executes properly.