Solved

PHP and Oracle for an Idiot

Posted on 2014-09-15
3
156 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 109

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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ā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

832 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