I know how to run normal select query.
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@xxx";
String uname = "";
String passwd = "";
Connection conn = DriverManager.getConnection(url, uname, passwd);
Statement stmt = conn.createStatement();
String sql = "select * from table_name";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println();
}
But how to run query like sql = "describe table_name"
?
CodePudding user response:
describe
is a SQL*Plus command; although it works elsewhere (such as in SQL Developer or TOAD), it is not a "standard" command so I don't think you can use it the way you wanted.
Therefore, as you already know how to run a "normal" query, do it again, but this time by fetching data from user_tab_columns
which contains data you need. For example:
SQL> SELECT column_name, data_type, data_precision, data_length, nullable
2 from user_tab_columns
3 where table_name = 'TEMP';
COLUMN_NAME DATA_TYPE DATA_PRECISION DATA_LENGTH N
--------------- --------------- -------------- ----------- -
ID NUMBER 22 Y
ENAME VARCHAR2 10 Y
JOB VARCHAR2 15 Y
DEPT NUMBER 22 Y
HIREDATE DATE 7 Y
LOC VARCHAR2 10 Y
6 rows selected.
which can be compared to
SQL> describe temp
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
ENAME VARCHAR2(10)
JOB VARCHAR2(15)
DEPT NUMBER
HIREDATE DATE
LOC VARCHAR2(10)
SQL>
As of comments: there's that nice view named dictionary
you can query and find some useful information, i.e. a system view which then lets you find another information. Here's how:
SQL> select * from dictionary
2 where lower(table_name) like '%user%' and lower(comments) like '%comment%';
TABLE_NAME COMMENTS
------------------------- --------------------------------------------------
USER_COL_COMMENTS Comments on columns of user's tables and views
USER_INDEXTYPE_COMMENTS Comments for user-defined indextypes
USER_MVIEW_COMMENTS Comments on materialized views owned by the user
USER_OPERATOR_COMMENTS Comments for user-defined operators
USER_TAB_COMMENTS Comments on the tables and views owned by the user
OK; it is user_tab_comments
and user_col_comments
I need. So let's add some comments to the temp
table:
SQL> comment on table temp is 'Sample table for Stack Overflow';
Comment created.
SQL> comment on column temp.ename is 'Employee''s name';
Comment created.
Result:
SQL> select * from user_tab_comments where table_name = 'TEMP';
TABLE_NAME TABLE_TYPE COMMENTS
------------------------- ----------- --------------------------------------------------
TEMP TABLE Sample table for Stack Overflow
SQL> select * from user_col_comments where table_name = 'TEMP';
TABLE_NAME COLUMN_NAME COMMENTS
------------------------- --------------- --------------------------------------------------
TEMP ID
TEMP ENAME Employee's name
TEMP JOB
TEMP DEPT
TEMP HIREDATE
TEMP LOC
6 rows selected.
SQL>