Oracle – PL/SQL Stored Procedure create tables

oracleplsqlstored-procedures

I've been tasked with improving old PL/SQL and Oracle SQL legacy code. In all there are around 7000 lines of code! One aspect of the existing code that really surprises me is the previous coder needlessly created hundreds of lines of code by not writing any procedures or functions – instead the coder essentially repeats the same code throughout.

For example, in the existing code there are literally 40 or more repetitions of the following SQL:

CREATE TABLE tmp_clients
AS
    SELECT * FROM live.clients;

CREATE TABLE tmp_customers
AS
    SELECT * FROM live.customers;

CREATE TABLE tmp_suppliers
AS
    SELECT * FROM live.suppliers WHERE type_id = 1;

and many, many more.....

I'm very new to writing in PL/SQL, though I have recently purchased the excellent book "Oracle PL/SQL programming" by Steven Feuerstein. However, as far as I can tell, I should be able to write a callable procedure such as:

procedure create_temp_table (new_table_nme in varchar(60)
  source_table in varchar(60))
IS
    s_query varchar2(100);
BEGIN
    s_query := 'CREATE TABLE ' + new_table_nme + 'AS SELECT * FROM ' + source_table;
   execute immediate s_query;

EXCEPTION
    WHEN OTHERS THEN
       IF SQLCODE = -955 THEN
           NULL;
       ELSE
           RAISE;
       END IF;
END;

I would then simply call the procedure as follows:

create_temp_table('tmp.clients', 'live.clients');
create_temp_table('tmp.customers', 'live.customers');
  1. Is my proposed approach reasonable given the problem as stated?
  2. Are the datatypes in the procedure call reasonable, ie should varchar2(60) be used, or is it possible to force the 'source_table' parameter to be a table name in the schema? What happens if the table name is more than 60 characters?
  3. I want to be able to pass a third non-required parameter in cases where the data has to be restricted in a trivial way, ie to deal with cases "WHERE type_id = 1". How do I modify the procedure to include a parameter that is only used occasionally and how would I modify the rest of the code. I would probably add some sort of IF/ELSE statement to check whether the third parameter was not NULL and then construct the s_query accordingly.
  4. How would I check that the table has actually been created successfully?

  5. I want to trap for two other exceptions, namely

    • The new table (eg 'tmp.clients') already exists; and
    • The source table doesn't exist.

    Does the EXCEPTION as written handle these cases?

  6. More generally, from where can I obtain the SQL error codes and their meanings?

Any suggested improvements to the code would be gratefully received.

Best Answer

You could get rid of a lot of code (gradually!) by using GLOBAL temporary tables. Execute immediate is not a bad practice but if there are other options then they should be used. Global temp tables are common where you want to extract and transform data but once processed you don't need it anymore until the next load. Each user can only see the data they insert and no redo logs are generated. You can index the data for faster querying if required.

Something like this

-- Create table

create global temporary table GT_CLIENTS
(
  id                    NUMBER(10) not null,
  Client_id             NUMBER(10) not null,
  modified_by_id        NUMBER(10),
  transaction_id        NUMBER(10),
  local_transaction_id  VARCHAR2(30) not null,
  last_modified_date_tz TIMESTAMP(6) WITH TIME ZONE not null
)
on commit preserve rows;

I recommend the on commit preserve rows option so that you can debug your procedure and see what went into the table.

Usage would be

INSERT INTO GT_CLIENTS
SELECT * FROM live.clients;
Related Topic