The Oracle Open Server Plug-in ============================== Registration ------------ The Oracle Open Server plug-in requires a plug-in specific registration key for use with a registered Open Server. System requirements ------------------- The Oracle plug-in requires the client environment for Oracle 8.1.6 or later to be installed on the system on which the Open server will be run. Unix requirements: The Oracle plug-in needs to load a number of Oracle libraries. To be able to do this you must include the location of the Oracle libs directory in the Unix environment variable 'LD_LIBRARY_PATH'. setenv LD_LIBRARY_PATH "${LD_LIBRARY_PATH}:$ORACLE_HOME/lib" Limitations ----------- The Open Server supports data access only, it doesn't directly support data definition. You can work around this by using the command 'dbms query("execute", "")' to send the statements directly to the Oracle server with out processing. The functions "describe databases", "describe tables", and "describe columns" haven't been implemented. Special Settings ---------------- There is an environment setting in the open server's environment file, number 1503, to set the default connection options that the open server will use with Oracle. Set this to the following string: "(ADDRESS=(PROTOCOL=TCP)(Host=)(Port=< port-number>))" where "" is the host name or IP address of the machine on which oracle is running and "" is the TCP port on which it is published. By default the oracle publishes on port 1521. If the Open Server and the Oracle server are running on the same machine then you can set "" to "localhost". It is recommended that the Open Server and the Oracle server run on the same machine to limit network traffic and improve performance. Oracle Specific considerations When a client opens a session with an Open Server running the Oracle Open Server plug-in, the session is initialized but no connection is made to the Oracle data server. A connection to the Oracle data server is made when a database is opened. When opening a database the database name will be interpreted as an "ORACLE_SID" (Oracle System Identifier) and used in the connection string to connect to the Oracle data server. If your application was written for PrimeBase and not Oracle you will probably need to make some changes. Here are some of the things to watch for: 1) Reserved words. A lot of names that you can use in PrimeBase are not allowed in Oracle such as having columns called "Number" or "order". 2 Oracle doesn't allow you to create defaults on serial counters. This can be worked around by using triggers. 3 Oracle doesn't have case insensitive columns. To do a case insensitive search you must force everything to uppercase before doing the comparison. You can talk to your Oracle expert to find the best way to handle this. 4) By default in Oracle a column of type NUMBER with no scale or precision is a float. But the Oracle plug-in converts Oracle data of type NUMBER with no scale or precision to an integer. If this is not done then when the user executes "select count(*) from foo" the Oracle plug-in sees a result set returned with one column of type NUMBER with no scale or precision and your select count(*) would produce a result set with one column of type float, which is probably not what you would expect. If you want NUMBERs with no scale or precision to be treated as floats then this behavior can be turned on and off with the commands. "SET NUMBER_IS_FLOAT()" and "SET NUMBER_IS_INT()". 5) The % operator (as MOD) is not supported by Oracle. PrimeBase, however, supports the MOD() function which is also supported in Oracle. 6) Data concatenation on the server. You cannot use the '+' operator in SQL statements to concatenate strings in Oracle. Instead you must use the CONCAT() function which is supported by both Oracle and PrimeBase. For example the following will work in PrimeBase but not Oracle: "select c1 from foo where c1 = c2 + c3;" where the following statement will work in both PrimeBase and Oracle: "select c1 from foo where c1 = CONCAT(c2,c3);" 7) Oracle doesn't support LOCATE() function. Use 'LIKE' instead. 8) Oracle doesn't have a Boolean data type. By default the Oracle plug-in will return the Oracle type 'NUMBER(1)' which is a 1 digit number as a Boolean. This behavior can be controlled with the custom commands "SET NUMBER1_IS_BOOL()" and "SET NUMBER1_IS_NUMBER()" as described below. 9) Oracle does not support '*' as the wildcard character. However, PrimeBase supports '%' which is also supported by Oracle. 10) When more than 1 table is specified in a select Oracle requires that you explicitly 10) Oracle is very particular when it comes to SQL. If more than one table is used in a select/update/insert/delete statement, each column or * (all columns) should be defined with the table, column method. Otherwise, you will get a "Column Ambiguously Defined" error message. 11) Oracle doesn't like unary Boolean tests in 'where' clauses. The following will result in an error: "select * from foo where c1;" Replace the statement with the following: "select * from foo where c1 = $TRUE;" 12) "select MAX(c1) from foo'" will always return a row on Oracle even if the table is empty. In this case $NULL is the value returned. NOTE: When defining the Oracle scheme use CLOB and BLOB types and not "Long" and "Long Raw". Custom plug-in commands ----------------------- The Oracle Plug-in supports the following custom commands that you can use to work around some of the differences between PrimeBase syntax and Oracle syntax. DBMS QUERY("execute", ""); Executes the string "" in the Oracle server. The string is not parsed by the PrimeBase Virtual Machine so it can contain Oracle specific syntax. The command returns no data. Example: DBMS QUERY("execute", "CREATE PUBLIC SYNONYM Employees for common.Employees"); --- DBMS QUERY("select", "") [INTO [FOR EXTRACT]]; Executes the string "" in the Oracle server. The string is not parsed by the PrimeBase Virtual Machine so it can contain Oracle specific syntax. The command returns cursor as if a normal select had been executed. Example: DBMS QUERY("execute", "select * from foo where UPPER(foo.name) = 'PRIMEBASE'"); This example shows one way to work around the lack of case insensitive columns in Oracle. --- DBMS QUERY("update", "") ; Executes the string "" in the Oracle server. The string is not parsed by the PrimeBase Virtual Machine so it can contain Oracle specific syntax. The command sets the $rowseffected flag. Example: DBMS QUERY("execute", "update foo set quality = "VeryGood" where UPPER(foo.name) = 'PRIMEBASE'"); --- SET NUMBER_IS_FLOAT(); Oracle data type NUMBER with no scale or precision will be treated as a float. --- SET NUMBER_IS_INT(); Oracle data type NUMBER with no scale or precision will be treated as an int. --- SET NUMBER1_IS_BOOL(); Oracle data type NUMBER with precision 1 (a 1 digit number) will be treated as a Bool. The following conversion will take place: Oracle value: PrimeBase Value ----------------------------- 0 : $FALSE 1 : $TRUE >1 : $MAYBE This is the default setting. --- SET NUMBER1_IS_NUMBER(); Oracle data type NUMBER with precision 1 (a 1 digit number) will be treated as an integer. --- SET SCHEMA(""); By default the schema name is assumed to be the same as the user's login name when not specified in a query. You can use this command to set the default schema name to something other than the user's login name. For example if you logged in as 'bob' and you executed the following select: "select * from foo" it would be the equivalent of executing: "select * from bob.foo". If you execute the command "SET SCHEMA('Sally');" then "select * from foo" would default to "select * from Sally.foo". DEBUG SETTINGS -------------- The following can be used to turn on/off tracing on the open server. When tracing is turned on the statements sent to the open server to be executed will be displayed on the Open Server's console. set tracing_on() set tracing_off()