[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 637
  • Last Modified:

Oracle data update string with single quotes

I am getting an [ORA-00911:invalid character] error on the following string?  This column's data contains single quotes as part of the value.

UPDATE cmi_sales_filters_tab2 SET FILTER = 'BILLED<>1 AND INVTO_IDENTITY<>''CMOZ'' AND INVTO_IDENTITY<>''CMCB'' AND INVTO_IDENTITY<>''CMTOW'' AND INVTO_IDENTITY<>''CMGAR'' and company = ''05''' WHERE ID = 'PL Exclduing intra co sales'; 

Open in new window


What am I missing?
0
tnowacoski
Asked:
tnowacoski
  • 4
  • 2
2 Solutions
 
sdstuberCommented:
the string itself you are updating is fine.

is the error coming when you try to use that string as part of dynamic sql?
0
 
slightwv (䄆 Netminder) Commented:
Does the error message give you an approximate column for he error?

What you posted seems correct.
0
 
tnowacoskiAuthor Commented:
This is to update a 2 column table: ID (varchar2[50]) and  FILTER (varchar2[255]).  I am trying to update the FILTER column with a string that has single quotes mixed in throughout the string.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
tnowacoskiAuthor Commented:
That is all that I have on the error.  This update is being submitted through a PHP/OCI8 web page.
0
 
slightwv (䄆 Netminder) Commented:
There is nothing wrong with your update statement.

See the code below.  The row updates just fine.

>>submitted through a PHP/OCI8 web page

If is likely a PHP error then. Do you need to escape quotes in PHP?  I'm not a PHP person.
drop table tab1 purge;
create table tab1(col1 varchar2(1000));
insert into tab1 values(null);

UPDATE tab1 SET col1 = 'BILLED<>1 AND INVTO_IDENTITY<>''CMOZ'' AND INVTO_IDENTITY<>''CMCB'' AND INVTO_IDENTITY<>''CMTOW'' AND INVTO_IDENTITY<>''CMGAR'' and company = ''05''';

Open in new window

0
 
tnowacoskiAuthor Commented:
Below is the PHP/OCI8 page that is submitting the update.  I have changed the connection string information to "protect the innocent".

<!DOCTYPE html>
<html lang=en>
<header>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="main.css" />
<title>Sales Filter Record Update</title>
</header>
<body>
<A href="http://localhost/Update2.php">Back</A><BR>
<A href="http://localhost/index.html">Home</A><BR><br>
<?
$objConnect = oci_connect("username","password","database");
$strSQL = "UPDATE cmi_sales_filters_tab2 SET ";
$strSQL .="FILTER = '".str_replace("'", "''", $_POST["txtFilter"])."' ";
$strSQL .="WHERE ID = '".$_GET["ID"]."';";
echo 'Updating record with: <br>';
echo "<small>$strSQL </small><br>";
$objParse = oci_parse($objConnect, $strSQL);
$objExecute = oci_execute($objParse, OCI_DEFAULT);
if($objExecute)
{
oci_commit($objConnect); //*** Commit Transaction ***//
echo "Save completed.";
}
else
{
oci_rollback($objConnect); //*** RollBack Transaction ***//
$e = oci_error($objParse);
echo "Error Save [".$e['message']."]";
}
oci_close($objConnect);
?>

</body>
</html>

Open in new window

0
 
tnowacoskiAuthor Commented:
The solution was to omit the ";" at the end of the update line.......syntax!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now