I want to get a command in oracle sql
What I'm looking for will help the Oracle DB team.
I'm not near this database right now, so I can't try anything.
I will write your answers on paper and I will try them and let you know.
For now, some information:
I am using OLE database connection using C # OLE Connect, what command do I need to execute to get:
primary key for each table:
I already have SQL for the primary key, but I don't know how to execute it to change the casting.
"SELECT a.owner, a.TABLE_NAME, b.column_name
FROM all_constraints a, all_cons_columns b
WHERE a.constraint_type='P'
AND a.constraint_name=b.constraint_name
AND a.TABLE_NAME = 'CASTING'"
Edit: deleted If exists (I will get all tables and foreach table, I will add drop table query)
But how is it on oracle (while I save it, I will let you know tomorrow morning 9-11 o'clock after that, I will update this thread)
How do I check if columns is_identity
have auto_increment value
and when i found how to check
**"IDENT_SEED ,IDENT_INCR ,IDENT_CURRENT"**
Last thing:
how can I get information from each table, information like this:
column_name, data_type, is_nullable, character_maximum_length
Thanks in advance for your help
source to share
select count(*)
from all_tables
where table_name = 'YOUR_TABLE_NAME_IN_UPPERCASE';
returns 0 if the table does not exist (or your user does not have any rights on this table)
select column_name, data_type, nullable, data_length, data_scale, data_precision
from all_tab_columns where table_name = 'YOUR_TABLE_NAME_IN_UPPERCASE';
contains information about table columns
And in Oracle there is no such thing as an identity column whose version is less than 12c. Are you using 12c?
source to share
Oracle has no way to drop a table using a clause if exists table
. As best practice, just drop the table and ignore the error using
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE mytable';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
If you want to receive notifications, use
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE mytable';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
For table details, please ask ALL_TAB_COLUMNS
.
And IDENTITY COLUMNS
available from Oracle 12c
. What version are you working on?
Query select * from v$version
to find out the version of your database.
Request all_tab_identity_cols
for IDENTITY COLUMNS
details.
SELECT table_name,
column_name,
generation_type,
identity_options
FROM all_tab_identity_cols
WHERE owner = 'TEST'
ORDER BY 1, 2;
EDIT As requested by the OP regarding the PRIMARY KEY
table.
The all_constraints
column constraint_type
will have a value P
for primary keys. So, in your query putwhere constraint_type = 'P'
Your request seems fine. Just follow it.
source to share