如何阻止SQL Developer在视图中展开星号(*)通配符？

Hey there! Let me break down why SQL Developer is expanding your view1.* into explicit columns when you save view2, and what you can do about it.

Oracle (and by extension, SQL Developer) doesn't store the * wildcard in a view's definition permanently. When you create a view using view1.*, the database immediately resolves that wildcard to the exact list of columns that exist in view1 at the time of creation. SQL Developer just shows you the actual definition that's stored in the database, not the shorthand you wrote.

This isn't a bug—it's intentional. Here's why:

• Stability: It locks in the columns that view2 uses. If someone later adds or removes a column from view1, view2 won't suddenly change its output, which prevents breaking downstream queries or reports that depend on view2's structure.
• Oracle's Metadata Rules: The database needs to track the exact columns in each view for things like permissions, dependencies, and query optimization. Storing the explicit column list makes this easier.

If you really need view2 to automatically include any new columns added to view1 later, you have a couple options:

Option 1: Use a Stored Procedure to Refresh the View #

You can create a procedure that dynamically rebuilds view2 by pulling the latest column list from view1. Here's how:

CREATE OR REPLACE PROCEDURE refresh_view2 IS
  v_view_def VARCHAR2(4000);
BEGIN
  -- Build the SELECT clause with all columns from view1, plus table2's columns
  SELECT 'CREATE OR REPLACE VIEW view2 AS SELECT ' ||
         LISTAGG(col_name, ', ') WITHIN GROUP (ORDER BY col_id) ||
         ', col22, col23 FROM view1 JOIN table2 ON view1.col11 = table2.col21'
  INTO v_view_def
  FROM (
    SELECT column_name AS col_name, column_id AS col_id
    FROM user_tab_columns
    WHERE table_name = 'VIEW1'
  );

  -- Execute the dynamic SQL to rebuild the view
  EXECUTE IMMEDIATE v_view_def;
END;
/

Whenever you update view1's structure, just run EXEC refresh_view2; to update view2 to include the new columns.

Option 2: Use a Materialized View (Careful!) #

A materialized view stores the actual data instead of just a query definition. You can set it to refresh periodically, but note that it won't update in real-time unless you configure it that way. Here's a basic example:

CREATE MATERIALIZED VIEW view2
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
AS
SELECT view1.*, col22, col23 
FROM view1 
JOIN table2 ON view1.col11 = table2.col21;

Use this only if you don't need real-time data, since refreshing can be resource-heavy.

If view1's structure doesn't change often, it's usually better to just let SQL Developer expand the columns. This keeps view2's structure predictable—you'll always know exactly which columns it returns, and you won't have to worry about unexpected changes breaking things downstream.

Just remember: This isn't a SQL Developer quirk—it's how Oracle handles views under the hood. Any tool that shows you the actual stored view definition will display the expanded columns, not the wildcard.

内容的提问来源于stack exchange，提问作者Prefijo Sustantivo