What Happens if I REST Enable a table with no Primary Key in ORDS?

thatjeffsmith ORDS 1 Comment

When you AUTO-REST enable table in ORDS, we publish a full API for you: We auto find your Primary Key Column and use that to address particular rows (items) in your table (collection.) But..what happens if you don’t have a primary key? CREATE TABLE no_keys (letter CHAR(1), letters VARCHAR2(25), numbers INTEGER);   INSERT INTO no_keys VALUES (’a’, ‘abc’, 1); INSERT …

AUTO vs ‘Bespoke’ REST Services

thatjeffsmith ORDS, SQL Developer 3 Comments

The Automatic REST features in ORDS are very handy for getting started quickly with providing REST APIs to your data and stored procedures. Give me the data for an employee. Easy. REST enable the schema. REST enable the table. DO a GET on /ords/schema/table/id. But…I don’t want a ‘null’ in my JSON collection for value pair “commission_pct.” What’s an Oracle …

Generating Table DDL via SQLcl, with and without Storage Clauses

thatjeffsmith SQL Developer 8 Comments

We’re getting much closer to having Oracle SQL Developer version 4.1 ready for release – which includes SQLcl, our new command-line interface for Oracle Database. Just a quick reminder in case you’re too lazy to click to that other post: We’ve taken our existing Java based SQL*Plus script engine from SQL Developer, and built a new command line interface that …

Adding Common Columns to Tables via Templates and Transformation Scripts

thatjeffsmith SQL Developer 10 Comments

In Oracle SQL Developer Data Modeler, you can create a table in your relational design that stores columns that you want to be added to all of your other tables. Call it, ‘table_template.’ Now call the transformation script. Tools > Design Rules & Transformations > Transformations. Before After These are cool, but they don’t belong in my Logical model! So …

30 SQL Developer Posts in 30 Days, Day 25: Importing Partitioned Tables to a Data Model

thatjeffsmith SQL Developer 0 Comments

We get lots of questions around how to model specific partitioning schemes in Oracle SQL Developer Data Modeler, and we do support all of the different ways to part and subpart your data – but today I want to show you: Where to see the partitions in the model How to bring in everything BUT the partitions Where are my …

30 SQL Developer Tips in 30 Days, Day 19: Quick and Clean Table Copies

thatjeffsmith SQL Developer 1 Comment

Quick and clean, what’s that? Consider the following: CREATE TABLE new_table AS SELECT * FROM old_table WHERE 1=2; I call that ‘quick and dirty,’ and I imagine you have used this technique many, many times. But, I wish it would have grabbed column comments, indexes, maybe even the partitions. Partitioning a table after the fact is pretty much a no-go. …

30 SQL Developer Tips in 30 Days, Day 10: Compare Stuff Side By Side

thatjeffsmith SQL Developer 2 Comments

We try to make comparing things easy to do. When you’re troubleshooting, you frequently need to know why A isn’t quite like B. So let’s look at a few scenarios. Execution Plans or Autotrace Runs Run a plan or autotrace. Pin it. Run another. Right-click on one, and compare with the other. Voila. Looking at Objects, Side By Side So …