CODEWITHSUNDEEP
c#sqloracle-database
Asaf Shazar
Asaf Shazar

Reputation: 1065

I want to get command on oracle sql

The things I looking for some help with command on Oracle DB.

Right now I not near that database so I cannot try anything.

I will write down on paper your answers and I will try them and will let you know.

For now some info:

I using OLE connection to database by using c# OLE Connect which command I need to execute to get:

primary key every table:

I already got SQL for primary key but I don't know how to execute it to change the casting things 

            "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: removed the If exist (i will get all tables and foreach table I will add drop table query)

But how it on oracle (for now I will keep it I will let you know tomorrow morning 9-11 hours from now I will update this topic)

How I check if some columns is_identity it mean it auto_increment

and when I found it how to check 

           **"IDENT_SEED ,IDENT_INCR ,IDENT_CURRENT"**

Last thing:

how can I get info from every table, info like that :

column_name, data_type, is_nullable, character_maximum_length

Thank you in advance for helping

Upvotes: 3

Views: 187

Answers (3)

Asaf Shazar
Asaf Shazar

Reputation: 1065

Thanks you guys The project work. The versiob is 11.42 so there no auto incerment And everthing work perfect All column info work Primary keys work

Upvotes: 2

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

Oracle does not have any way to drop table using if exists table clause. As a 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 be notified, then use,

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE mytable';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;

Regarding the table details, query ALL_TAB_COLUMNS.

And, IDENTITY COLUMNSare available with Oracle 12c. Which version are you on?

Query, select * from v$version to know your database version.

Query 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 OP regarding PRIMARY KEY of a table.

In all_constraints, the column constraint_type will have value P for primary keys. So, in your query, put where constraint_type = 'P'

Your query seems to be fine. Just execute it.

Upvotes: 1

Multisync
Multisync

Reputation: 8797

select count(*) 
from all_tables 
where table_name = 'YOUR_TABLE_NAME_IN_UPPERCASE';

returns 0 if table doesn't exist (or your user doesn't have any privileges 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';

gives details about table columns

And there is not such a thing as identity column in Oracle which version is less than 12c. Are you using 12c?

Upvotes: 1

Related Questions