Oracle and MySQL Data Access (edit)

POCO Class Generator - @manhng

• DBeaver
• MySQL + MySqlConnector
• Oracle + Oracle.ManagedDataAccess (version 18.3.0)
• Dapper (version 1.50.2) + Dapper.Oracle
• StringBuilder -> Generate Code
• Entity Framework 6 with Oracle: Oracle.ManagedDataAccess.EntityFramework (version 18.3.0)

Oracle Comments for Table

How to get comments for table & column from oracle DB from its metadata? - Stack Overflow

SELECT table_name,
  column_name,
  comments
FROM dba_col_comments
WHERE owner ='orcl'
AND table_name='User';

Oracle Comments for Column

How to get comments for table & column from oracle DB from its metadata? - Stack Overflow

SELECT *
FROM user_col_comments;

SELECT *
FROM user_tab_comments;

List table columns in Oracle database

List table columns in Oracle database - Oracle Data Dictionary Queries (dataedo.com)

select col.column_name 
 from sys.all_tab_columns col 
 inner join sys.all_tables t on col.owner = t.owner and col.table_name = t.table_name 
 where col.owner = 'NORTHWIND' AND col.table_name = 'CUSTOMER' 
 order by col.owner, col.table_name, col.column_id;

select col.column_id, col.owner as schema_name, col.table_name, col.column_name, col.data_type, col.data_length, col.data_precision, col.data_scale, col.nullable from sys.all_tab_columns col inner join sys.all_tables t on col.owner = t.owner and col.table_name = t.table_name -- excluding some Oracle maintained schemas where col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS', 'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC') order by col.owner, col.table_name, col.column_id;

Other

select col.column_name 
 from sys.all_tab_columns col 
 inner join sys.all_tables t on col.owner = t.owner and col.table_name = t.table_name 
 where col.owner = 'NORTHWIND' AND col.table_name = 'CUSTOMER' 
 order by col.owner, col.table_name, col.column_id;

select col.column_id, col.owner as schema_name, col.table_name, col.column_name, col.data_type, col.data_length, col.data_precision, col.data_scale, col.nullable from sys.dba_tab_columns col inner join sys.dba_tables t on col.owner = t.owner and col.table_name = t.table_name -- excluding some Oracle maintained schemas where col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS', 'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC') order by col.owner, col.table_name, col.column_id;

Oracle

Oracle Parameterized Queries for the .NET Developer - CodeProject

Table Definitions

First point is that there are obvious differences on how equivalent data types are defined in the table definition. Using these two tables, I will expand on the pitfalls when writing code for database operations.

The first pitfalls are in the connections strings. For SQL Server, connecting to the database is a relatively simple affair. Oracle connections are very different. For one, Oracle connections can be done in two different ways, with the tnsnames.ora file or without. The tsanames.ora file contains the server IP address, the server port, and the Oracle service to connect to.

Lấy tất cả dữ liệu của một bảng

Trying to load an entire table in c# using Oracle.ManagedDataAccess - Stack Overflow

List table columns in MySQL database

List table columns in MySQL database - MySQL Data Dictionary Queries (dataedo.com)

php - Get table column names in MySQL? - Stack Overflow

You can use DESCRIBE:

DESCRIBE my_table;

Or in newer versions you can use INFORMATION_SCHEMA:

SELECT COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table';

Or you can use SHOW COLUMNS:

SHOW COLUMNS FROM my_table;

Or to get column names with comma in a line:

SELECT group_concat(COLUMN_NAME)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table';

Best way

php - MySQL query to get column names? - Stack Overflow

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='yourdatabasename' 
    AND `TABLE_NAME`='yourtablename';