Menu
I have a table that exists in an Oracle database, but doesn't show on my list of tables in the tool SQL Developer. However, if I go to SQL*Plus, and do a
select table_name from user_tables; I get the table listed. If I type
desc snp_clearinghouse; it shows me the fields. I'd like to get the create statement, because I need to add a field. I can modify the table to add the field, but I still need the create statement to put into our source control. What pl/sql statement is used to get the create statement for a table?
473
Answer: To do this, the Oracle CREATE TABLE syntax is: CREATE TABLE new_table AS (SELECT * FROM old_table WHERE 1=2); For example: CREATE TABLE suppliers AS (SELECT * FROM companies WHERE 1=2);
The plus sign is Oracle syntax for an outer join. There isn't a minus operator for joins. An outer join means return all rows from one table. Also return the rows from the outer joined where there's a match on the join key. If there's no matching row, return null.
From Get table and index DDL the easy way:
set heading off; set echo off; Set pages 999; set long 90000; spool ddl_list.sql select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual; select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual; spool off; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
