spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories

Getting table information with plsql


You could do it in plain SQL, no need of PL/SQL.

  • ALL_TAB_COLUMNS describes the columns of the tables, views, and clusters accessible to the current user.
  • DBA_TAB_COLUMNS describes the columns of all tables, views, and clusters in the database.
  • USER_TAB_COLUMNS describes the columns of the tables, views, and clusters owned by the current user. This view does not display the OWNER column.

Read *_TAB_COLS

SQL> SELECT table_name, column_name
  2  FROM all_tab_cols
  3  WHERE owner='SCOTT'
  4  /

TABLE_NAME      COLUMN_NAME
--------------- ---------------
TABLE_NAME      DEPTNO
TABLE_NAME      ENAME
TABLE_NAME      EMPNO
SALGRADE        HISAL
SALGRADE        LOSAL
SALGRADE        GRADE
EMP             SYS_STSA11ZDTGW
                $SYV6W40D3EV5X5

EMP             DEPTNO
EMP             COMM
EMP             SAL
EMP             HIREDATE
EMP             MGR
EMP             JOB
EMP             ENAME
EMP             EMPNO
DEPT            LOC
DEPT            DNAME
DEPT            DEPTNO
BONUS           COMM
BONUS           SAL
BONUS           JOB
BONUS           ENAME

22 rows selected.

SQL>

EDIT If you still want to use PL/SQL, then,

SQL> BEGIN
  2    FOR i IN
  3    ( SELECT table_name, column_name FROM
all_tab_cols WHERE owner='SCOTT'
  4    )
  5    LOOP
  6      dbms_output.put_line('TABLE_NAME =
'||i.table_name||' COLUMN_NAME =
'||i.column_name);
  7    END LOOP;
  8  END;
  9  /
TABLE_NAME = TABLE_NAME COLUMN_NAME = DEPTNO
TABLE_NAME = TABLE_NAME COLUMN_NAME = ENAME
TABLE_NAME = TABLE_NAME COLUMN_NAME = EMPNO
TABLE_NAME = SALGRADE COLUMN_NAME = HISAL
TABLE_NAME = SALGRADE COLUMN_NAME = LOSAL
TABLE_NAME = SALGRADE COLUMN_NAME = GRADE
TABLE_NAME = EMP COLUMN_NAME =
SYS_STSA11ZDTGW$SYV6W40D3EV5X5
TABLE_NAME = EMP COLUMN_NAME = DEPTNO
TABLE_NAME = EMP COLUMN_NAME = COMM
TABLE_NAME = EMP COLUMN_NAME = SAL
TABLE_NAME = EMP COLUMN_NAME = HIREDATE
TABLE_NAME = EMP COLUMN_NAME = MGR
TABLE_NAME = EMP COLUMN_NAME = JOB
TABLE_NAME = EMP COLUMN_NAME = ENAME
TABLE_NAME = EMP COLUMN_NAME = EMPNO
TABLE_NAME = DEPT COLUMN_NAME = LOC
TABLE_NAME = DEPT COLUMN_NAME = DNAME
TABLE_NAME = DEPT COLUMN_NAME = DEPTNO
TABLE_NAME = BONUS COLUMN_NAME = COMM
TABLE_NAME = BONUS COLUMN_NAME = SAL
TABLE_NAME = BONUS COLUMN_NAME = JOB
TABLE_NAME = BONUS COLUMN_NAME = ENAME

PL/SQL procedure successfully completed.

SQL>

EDIT 2 If you want to restrict the output only to tables, then,

SELECT a.table_name,
  a.column_name,
  c.object_type
FROM all_tab_cols A,
  all_tables b,
  all_objects c
WHERE A.table_name = b.table_name
AND   b.table_name = c.object_name
AND A.owner        ='SCOTT'
/

Categories : SQL

Related to : Getting table information with plsql
Hive table reading from GZIP contains meta information like file name in the first row
That depends on what version of Hive you are using. For Hive version 13 and above: There is a table property tblproperties ("skip.header.line.count"="1") which you can use while creating the table. So it will skip that no of lines. For Hive Version 12 and below: You need to remove the line/header manually or by using some shell/python script. Hope it helps...!!!

Categories : Hadoop
Postgresql: Trying to Understand the Information Schema Table Constraints Info for a NOT NULL Contstraint
Postgres stores its information about tables, types, columns, constraints, etc. in the pg_catalog schema. The top level is namespace. In your example above, the 2200 refers to the namespace. You can see this by evaluating: select oid,* from pg_catalog.pg_namespace where oid=2200; The second number from your example above (77911989) refers to the oid (object id) of the table. You can see

Categories : SQL
Ask user for information and return that information
You're trying to mix Objective-C and C here. This is somewhat tricky to do, but let me see if I can help point you the right direction. First, change: NSString * name = @""; scanf("%@", &name); to: char name[64]; scanf("%s", &name); and see if that works better. scanf is a C function that works with c types, and NSString is an objective C object which doesn't really work with "sca

Categories : Objective C
How to get information from 2 different tables in PHP
See if this is what you want: SELECT `weekly_stats.*`, SUM(`team.wins`), SUM(`team.losses`) FROM `weekly_stats` INNER JOIN `team` ON `team.name` = `weekly_stats.team` If I misunderstood you, I'm really sorry.

Categories : PHP
Dropdown with 2 information in each row (PHP)
$city_name = $city_row[0]; // or $city_row['city_name'] $city_point = $city_row[1]; // or $city_row['city_point'] See code below: $sql = "SELECT city_name, city_point FROM ".CITIES_TABLE.""; $result = mysql_query($sql); $city_name = array("---"); while ($city_row = $site_db->fetch_array($result)) { $additional_combo_fields['combo_city'] = array($lang['combo_city'],

Categories : PHP
Recently Add
Use REGEXP_REPLACE to replace variable number of characters with same number of constant characters
How to subtract a data with nano-second in a same group
listagg alternative in Oracle
How to join 3 tables to a single table in SQL
Postgresql: Trying to Understand the Information Schema Table Constraints Info for a NOT NULL Contstraint
Data Frame in R use like SQL, possibly using sqldf()
How can I group by a table by the date?
Query slows down in Conditional IF
Getting the daily sales report given the date
Retrieve rows with unique values in a column
case sensitive sql search in vb.net
Oracle multi dimensional query
Update column when datediff is greater then other column in the same table
add value from different tables and insert total value into another table
How to prevent transaction locking in sql server for all the connections?
Check the query efficiency
how can I run this select statement inside a select statement without any problems?
Add amount once on duplicate entries
SQL Server 2012 - Unique Priority Column
Query: employees who do not have the same name
Trigger to not allow a member to rent if unpaid balance exceeds $50
Anyway way to change this Oracle SQL statment to use regs?
If you set a field name as an Alias, can you utilize that name within SQL code?
Increment next month with dayOfTheMonth SQL sybase
Update Table Set From is not working as expected
How do I find one matching strings in two txt files
SQL help needed (oracle application express)
How to Roll Up Weekending Data in SQL
How do you call a variable (that is a list of values) in a conditional statement?
Oracle INSTR backward in Oracle SQL
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.