Problem with Oracle

Hi,
I have a problem with PHP and Oracle 10 Database.
I read sql script from file and execute it.
Files are something like this:

insert into pts (pid, txt) values (1,'502a');
insert into pts (pid, txt) values (2,'502b');
......

I receive a message: "ORA-00911: invalid character"
When I remove manually the ";" from the end of every row and execute
separate every command
everything is ok.
I have and script files fo creating procedures/functions and there in not a
problem with ";".

Where is the problem ? Why can not be used ";" as command separator from PHP
?

Thanks in advance!
Rosen

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Problem with Oracle

Rosen wrote:
> Hi,
> I have a problem with PHP and Oracle 10 Database.
> I read sql script from file and execute it.
> Files are something like this:
>
> insert into pts (pid, txt) values (1,'502a');
> insert into pts (pid, txt) values (2,'502b');
> .....
>
> I receive a message: "ORA-00911: invalid character"
> When I remove manually the ";" from the end of every row and execute
> separate every command
> everything is ok.
> I have and script files fo creating procedures/functions and there in not a
> problem with ";".

Then you're not showing us the exact content of the files or the code
you're using to make this insert into your database.

What's the exact code you're using to go through the file and insert the
data?

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Problem with Oracle

"Chris" wrote in message
news:453D71EF.6090101@gmail.com...
> Rosen wrote:
>> Hi,
>> I have a problem with PHP and Oracle 10 Database.
>> I read sql script from file and execute it.
>> Files are something like this:
>>
>> insert into pts (pid, txt) values (1,'502a');
>> insert into pts (pid, txt) values (2,'502b');
>> .....
>>
>> I receive a message: "ORA-00911: invalid character"
>> When I remove manually the ";" from the end of every row and execute
>> separate every command
>> everything is ok.
>> I have and script files fo creating procedures/functions and there in not
>> a problem with ";".
>
> Then you're not showing us the exact content of the files or the code
> you're using to make this insert into your database.
>
> What's the exact code you're using to go through the file and insert the
> data?
>


Do you mean PHP code ?



> --
> Postgresql & php tutorials
> http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Problem with Oracle

--=_alternative 00479FFD85257211_=
Content-Type: text/plain;
charset=us-ascii
Content-Transfer-Encoding: 7bit

In Oracle you would write: insert into pts (pid, txt) values (1,'502a');

But in PHP you are going to do the following:

$conn = oci_connect('scott','tiger','my_db');
$sql = "insert into pts (pid, txt) values (1,'502a')";
$cursor = oci_parse($conn, $sql);
if (! $cursor)
{ $err = oci_error($conn);
print htmlentities($e['message']);
exit;
}
$results = oci_execute($cursor);
oci_commit($conn);
oci_close($conn);

The difference is that in PHP it appends a command seperator during the
parsing. A simple thing is to remove the trailing comma from the parsed
SQL or remove it from the file.

Roy A. Jones




"Rosen"
23-Oct-2006 19:21

To
php-db@lists.php.net
cc

Subject
[PHP-DB] Problem with Oracle






Hi,
I have a problem with PHP and Oracle 10 Database.
I read sql script from file and execute it.
Files are something like this:

insert into pts (pid, txt) values (1,'502a');
insert into pts (pid, txt) values (2,'502b');
......

I receive a message: "ORA-00911: invalid character"
When I remove manually the ";" from the end of every row and execute
separate every command
everything is ok.
I have and script files fo creating procedures/functions and there in not
a
problem with ";".

Where is the problem ? Why can not be used ";" as command separator from
PHP
?

Thanks in advance!
Rosen

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




--=_alternative 00479FFD85257211_=--

Re: Problem with Oracle

roy.a.jones@gsk.com wrote:
> In Oracle you would write: insert into pts (pid, txt) values (1,'502a');
>
> But in PHP you are going to do the following:
>
> $conn = oci_connect('scott','tiger','my_db');
> $sql = "insert into pts (pid, txt) values (1,'502a')";
> $cursor = oci_parse($conn, $sql);
> if (! $cursor)
> { $err = oci_error($conn);
> print htmlentities($e['message']);
> exit;
> }
> $results = oci_execute($cursor);
> oci_commit($conn);
> oci_close($conn);


The statements:

$results = oci_execute($cursor);
oci_commit($conn);

do two commits. Oci_execute() commits by default. The message
to the database to perform the commit is "piggybacked" in the
execute call. However the subsequent (unecessary in this case)
oci_commit() call requires an explicit round trip to the DB.

If you were doing multiple inserts you might do something like:

$s = oci_parse($c, 'insert into ptab (pdata) values (:bv)');
oci_bind_by_name($s, ':bv', $v, 20, SQLT_CHR);
foreach ($a as $v) {
$r = oci_execute($s, OCI_DEFAULT);
}
oci_commit($c);

Or explore calling a PL/SQL block and do a bulk FORALL insert.
I'll blog about bulk FORALL in a few days.

Chris

--
Christopher Jones, Oracle
Email: Christopher.Jones@oracle.com Tel: +1 650 506 8630
Blog: http://blogs.oracle.com/opal/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php