Monday, January 14, 2013

missing keyword in sql insert script containing MERGE statement...

When we deliver a project we also deliver the sql scripts which are necessaryto run our project. Once I had faced a weird error in the scripts delivered.
What we do in our project is we save the sql queries that are used in different places in the code in DB instead of having them inside the code. It is because when you need to update a query you don't need to rebuild your application. You just need to update a column in the DB and thats it. Your new query would reflect in your code. I will elaborate the rpocess in some other post later.
The important here is that the error we got during execution of the scripts. We have a table which has all the queries required for the application. in this table we have unique names for each queries and values in the other column. So to populate this table the insert script would look like :
insert into table_name(query_name,query_value) values ('name_for_the_query','actual_sql_query');

In the previous post i ave explained the MERGE statement. It is largely used in our application. So this merge query also was needed to be inserted in the DB. So it looked like:
insert into query_table(name,value) values ('merge query','merge into xxx using dual on (x=?)
when not matched then insert (x,x,x)
--One blank newline was here
when matched then update.....');
This caused the error when it was run as a script with other queries were also executed with this as a whole insertion into the table.
When the above script was run locally in my DB server using oracle sqldeveloper it did not throw any error.
But somehow in the client's DB it was throwing an error.
Then i redelivered the script removing this blank line and it worked.. :)
Hope you won't have any trouble in future with this type of error.
I don't remember the exact error code or description but if i get to know it i will update the exact error code.
Lastly my advice is always when you try to insert a query as a string in insert or update script in sql do not enter any blank line for your reading ease. ;)

No comments:

Post a Comment