Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

EnterpriseDB: DESCRIBE Command
Oracle Tips by Burleson
 

The describe command works as you would expect for the most part.  The describe against a table is actually better than in Oracle because it provides a list of indexes and triggers.

edb=# desc hr.employees

                   Table "hr.employees"

     Column     |            Type             | Modifiers
----------------+-----------------------------+-----------
 employee_id    | numeric(6,0)                | not null
 first_name     | character varying(20)       |
 last_name      | character varying(25)       | not null
 email          | character varying(25)       | not null
 phone_number   | character varying(20)       |
 hire_date      | timestamp without time zone | not null
 job_id         | character varying(10)       | not null
 salary         | numeric(8,2)                |
 commission_pct | numeric(2,2)                |
 manager_id     | numeric(6,0)                |
 department_id  | numeric(4,0)                |


Indexes:

    "emp_emp_id_pk" PRIMARY KEY, btree (employee_id)
    "emp_email_uk" UNIQUE, btree (email)
    "emp_department_ix" btree (department_id)
    "emp_job_ix" btree (job_id)
    "emp_manager_ix" btree (manager_id)
    "emp_name_ix" btree (last_name, first_name)

Check constraints:

    "emp_salary_min" CHECK (salary > 0::numeric)

Describes on views are also nice (I modified the text for readability):

edb=# desc user_tables;

             View "sys.user_tables"

   Column    |         Type         | Modifiers
-------------+----------------------+-----------
 owner       | text                 |
 schemaname  | text                 |
 table_name  | text                 |
 table_space | text                 |
 status      | character varying(5) |

View definition:

 SELECT all_tables."owner",
        all_tables.schemaname,
        all_tables.table_name,
        all_tables.table_space,
        all_tables.status
   FROM all_tables
  WHERE all_tables."owner" = upper("current_user"()::text);

It falls behind when you try to describe a code object:

edb=# select funname, funrettype, funlang

        from pg_function

        where funname = 'xmlattributes';

    funname    | funrettype | funlang
---------------+------------+---------
 xmlattributes |         25 |   17424

(1 row)

edb=# desc xmlattributes

Did not find any relation named "xmlattributes".

Describe only works for objects that the database server considers a "relation". You can think of a relation as something that can be joined to something else, like a table or a view.

This is not a big deal because as I show, you can get much the same information from alternate sources.  It is kind of annoying when you just want a quick describe of a package though.

Data Type Conversion

A particular problem I find in much of the SQL code that I have to maintain at times is implicit conversions.  I stress to all new programmers, and even to old hands, to always use explicit conversion and do not rely on assumptions.

It is not a lot of work to ensure that when you mean to compare a number to a number or a date to a date that you are in fact doing so.  You can ensure that you are using an explicit conversion by using the appropriate conversion function, such as to_char, to_date, to_number.  When using such a conversion function it is also important to use the appropriate format mask. 

Using appropriate data type conversion, and by appropriate I mean explicit, is an important standard even if you are not planning to port your application.  One reason for this is that it makes the code much easier to maintain.  The second is that it is less error prone and the third is that you make the important decisions (like the data type) and let the database optimizer make the data access choices (like which index to use).

When converting an application from Oracle to EnterpriseDB (or vice versa), it behooves you to use appropriate data type conversion.  The problem becomes more dramatic when you consider the additional data types that EnterpriseDB supports.

In Oracle, a number is the equivalent of an integer.  In EnterpriseDB, a number and an integer are two distinct data types.  For example, if we have function A:

CREATE OR REPLACE PROCEDURE a( p_in_val IN INTEGER )
AS
BEGIN
  NULL;
END;

If we call that with an anonymous block:

DECLARE
  v NUMBER;
BEGIN
  a(v);
END;

We get an error:

ERROR:  EDB-00302: procedure 'A' not found

But if we change the anonymous block variable v from NUMBER to INTEGER:

DECLARE
  v INTEGER;
BEGIN
  a(v);
END;

We get an entirely different result:

EDB-SPL Procedure successfully complete

It is nice when we can change the code so that the error goes away, but that is not always an option.  Sometimes you really mean NUMBER when the procedure wants an INTEGER.  EnterpriseDB provides the CAST function and related cast syntax (::) for such instances.

The anonymous block above could be re-written as:

DECLARE
  v NUMBER;
BEGIN
  a(CAST (v AS INTEGER));
END;

On the other hand, it could be re-written as:.

DECLARE
  v NUMBER;
BEGIN
  a(v::INTEGER);
END;

Both of these blocks produce the same result.  Coming from an Oracle background, I find the CAST syntax much more readable than the :: syntax. 

The only place I have really run into difficulties with type conversion has been with NUMBER and INTEGER.  The reason that this is the only place I have run into issues is because I never rely on implicit type conversion in my code.  I always use TO_CHAR, TO_DATE, etc so I am always aware of the type of data I should be dealing with.




This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter