Solved

PHP and Oracle for an Idiot

Posted on 2014-09-15
3
158 Views
Last Modified: 2014-09-20
I've worked with MySQL and T-SQL, but I'm brand new to Oracle.

While I'm working through some books and tutorials, I wanted to put it out here on Experts Exchange and ask for some "high altitude" perspective.

I'm looking at sites like this: http://docs.oracle.com/cd/E12151_01/doc.150/e12155/oracle_mysql_compared.htm and I'm seeing differences in terms of features, but how do things differ in terms of:

- connection to the database
- queries / code that retrieves info from the database

Not trying to minimize the obvious amount of study and work that will be needed to make this work, but I was hoping for some "bullets" to guide me in my quest.

Thoughts?
0
Comment
Question by:brucegust
3 Comments
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 150 total points
ID: 40324232
Probably the most important thing you can know is contained in the prominent red warning box on this page:
http://www.php.net/manual/en/ref.pdo-oci.php

After that, it's just like any other of the PHP database extensions.  There are examples with the function documentation.
http://www.php.net/manual/en/book.oci8.php
0
 
LVL 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 150 total points
ID: 40324310
Using the OCI8 extension http://php.net/manual/en/book.oci8.php , I was able to easily make the connections and queries to the 'free' developers version of Oracle.  However... setting up the users and databases privileges was Quite different and took some time to figure out.  That part is not like MySQL.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 200 total points
ID: 40324541
Dave's point about security is a really good one.

Regarding "queries / code that retrieves info from the database" here are some things I can think of:

Case sensitivity. By default Oracle is case sensitive.

"FROM DUAL"
  you can't just do "select 1" you MUST use a from clause and there's a special (oddly named) pseudo table "dual"
  "select 1 from dual"

NULL handling
   IFNULL(MySQL,'')
   ISNULL(MSSQL,'')
   NVL(Oracle,'')
   
   or, just use COALESCE(anydb,'')

string functions, these differ
   e.g. CONCAT() only handles 2 parameters,
    and unlike TSQL you can concatenate without cast to varchar first, and you use double pipes for concatenation

     select 'concatenation' || ' differs in more ways than ' || 1 from dual

    LISTAGG() is similar to GROUP_CONCAT()

date/time handling e.g.
   select SYSDATE from dual
   select TRUNC(SYSDATE) from dual -- equivalent to CURDATE()
   to_date('2014-04-21','YYYY-MM-DD')
   to_char(date_field_here,'YYYY-MM-DD')

   &  note that the timestamp data type is more complex to use than the date data type
   
useful function references:
     http://docs.oracle.com/cd/E21901_01/doc/timesten.1122/e21642/function.htm#TTSQL446
     http://www.techonthenet.com/oracle/functions/index.php

PL/SQL
   The procedural language differs in many ways.

DDL
   There are some syntax differences but they aren't extensive.
   BUT you will have to get used to UPPERCASE table and field names in the "dictionary" e.g.

   select * from user_tab_columns where table_name = 'YOUR_TABLE'
 

It isn't that hard & Oracle is a damn fine product.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
This article discusses how to implement server side field validation and display customized error messages to the client.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question