How to deal with the semi-colon in select from dual;

Question

Ive been tasked to write a simple php+oracle web reprting tool and I happen to have chanced a pretty complex query that perfectly works in SQL Developer:

SELECT 'LASTNAME, FIRSTNAME, STATUS' from dual;

with user as

(
...
..
..
..

(very long query)

and then it gives me this:

PHP Warning:  oci_execute(): ORA-00911: invalid character

based on my initial investigation, it looks like PHP complains about the ';' after first line (where it says select from ... dual ;)

On the PHP side, it looks like this:

$stid = oci_parse($conn, $query);
oci_execute($stid);

where $query is the very long query:

$query = "SELECT 'LASTNAME, FIRSTNAME, STATUS' from dual;

with user as

(
...
..
..
..

I want to know if there is a way to rewrite the entire query without using the 'dual' part?

Please learn about escaping `'` http://stackoverflow.com/questions/6269188/how-to-escape-only-single-quotes — SQLMason, Feb 11 '14 at 15:50
@DanAndrews - I updated my question that should have been $query = "SELECT .. — Robert L., Feb 11 '14 at 15:53
`dual;` is your table name??? or you attempt to run two queries? — Maheswaran Ravisankar, Feb 11 '14 at 15:58

score 0 · Answer 1 · answered Feb 11 '14 at 16:44

0

Dual table is a dummy table with an single row, single column, only data is X.

You can create you own dual table, it is just a dummy table. may be you want to repeat the result twice and create a dual2 table with two row in it. the content doesn't matter.

answered Feb 11 '14 at 16:44

tom87416

542
4
9

Apparently, the first line that says `select... from dual ; ` was what's breaking it. Further research (internally & via Google) led me to the conclusion that I may not need that line after all, and could just rewrite the header myself. Helpful link which made me discover/learn about the WITH clause : http://www.oracle-base.com/articles/misc/with-clause.php – Robert L. Feb 11 '14 at 20:14

How to deal with the semi-colon in select from dual;

1 Answers1