1

I have an Oracle database server running on a machine. Clients performs operations through a frontend application build from a different company, which generates corresponding SQL queries for these operations.

The frontend application generates SQL queries that we cannot modify. What I would like to know if there is any way to rewrite the SQL query upon its arrival. More specifically, we would like to be able to change tablespace names, default attribute values and most importantly compression parameters. For example, change this query:

CREATE TABLE EXAMPLE_TABLE (
    ID INTEGER NOT NULL,  
    AMOUNT FLOAT(126) DEFAULT 0.0, 
    TAG VARCHAR2(50) DEFAULT ' '
)
TABLESPACE EXAMPLE_TABLESPACE NOCOMPRESS

to:

CREATE TABLE EXAMPLE_TABLE (
    ID INTEGER NOT NULL,  
    AMOUNT FLOAT(126) DEFAULT 2.0, 
    TAG VARCHAR2(50) DEFAULT ' '
)
TABLESPACE EXAMPLE_TABLESPACE_TWO COMPRESS FOR OLTP

Note that the rewrites are not limited to create table statements, but can be applied to any SQL queries.

Any ideas about how to do this?

Improve this question
8
  • 1
    Actually CREATE TABLE ... is not a SQL query. If you would talk about real queries (i.e. SELECT or any DML) such rewrite would be no problem. However, in your case it will end up in the frontend application must not invoke any SQL command Commented Aug 6, 2015 at 12:01
  • One possible solution is synonyms. Alas, they don't work for table spaces or create table. I don't think there is a database way to do what you want. Commented Aug 6, 2015 at 12:04
  • How do you know that someone is attempting to run this code? You possibly can do what you want post execution, and alter existing database objects. Commented Aug 6, 2015 at 12:07
  • @Bulat: You are absolutely right, we know of this posibility. However, unfortunately, we want to do this every time a frontend client submits a query or statement because of many constraints that we have -- for example we want tables and indices to be created in a compressed state immediately, rather that first creating them uncompressed and then compressing then with the alter table command. Commented Aug 6, 2015 at 12:58
  • @Wernfried: I do not unfortunately get what you meant with the last part of your answer: "However, in your case it will end up in the frontend application must not invoke any SQL command". Can you please explain more? Commented Aug 6, 2015 at 12:59

3 Answers 3

Reset to default
2

You might enjoy this Oracle Database 12c feature, the SQL Translation Framework. Designed for taking T-SQL to SQL when migrating applications from Sybase or SQL Server to Oracle, it can also be used to help with hard-coded vendor SQL you need to optimize/fix.

Oracle Docs

Blog Example

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Great answer, too bad my server has 11g and not 12g :(. But this is a great feature and I will most definitely take a closer look.
0

If I didn't understand correctly, you want to do two things.

  1. Change tablespace for this table
  2. Change metadata from this table

You can do both easily.

  1. alter table [table_name] move [new_tablespace]; -- it must exists ( check tablespace quota in this tb for this user )

  2. alter table [table_anem] MODIFY(AMOUNT DEFAULT 0.2);

Improve this answer

Comments

-1

There is no way to modify a query upon its arrival. I think it would be a better idea to create tablespaces and everything else, according to your standards and requirements, before running the application.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.