Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to concatenate all the tables of an oracle version 8i database

User_6SHB1Jan 7 2021

Hi everyone, I am new to the oracle community, and I have a problem with an oracle database on version 8i. What I want to do is concatenate all the tables in a database (database name is SYS) and I put the following query:
select table_name from all_tables where rownum < 3

And when I did, I skip the following error:
SQL execution error, ORA-01427: single-row subquery returns more than one row

Try putting wm_concat ():
select wm_concat (table_name) from SYS.all_tables where rownum < 3

but I get the following error:
SQL execution error, ORA-00904: invalid column name

Also try concat ():
select concat (table_name) from SYS.all_tables where rownum < 3

but I get this error:
SQL execution error, ORA-00909: invalid number of arguments

And finally, try LISTAGG:
SELECT LISTAGG (table_name || CHR (58) || CHR (58) || column_name || CHR (60) || CHR (108) || CHR (105) || CHR (62)) within group (ORDER BY table_name ) FROM SYS.all_tab_columns WHERE rownum < 2

But I get this error too:
SQL execution error, ORA-00923: FROM keyword not found where expected

I don't know what to do to be able to concatenate all the tables in the SYS database.

If someone knows how please help me and tell me how to solve this problem, I would be very grateful.

Comments

Gary Graham-Oracle

You do not say which version of SQL Developer is in use, which JDK, which OS, whether Thin or OCI/Thick JDBC driver, etc., etc.  However, assuming recent versions not subject to old, known bugs, you may just need to read this blog post...

http://www.thatjeffsmith.com/archive/2013/03/why-cant-i-see-my-tables-in-oracle-sql-developer/

2933934

Thank you, Gary.

I updated the post with the version info. I did not explicitly install this JDBC driver, so whatever the OSD came with?  Unless it is using whatever is installed on my machine, in which case would be the thin driver.

I did check out that post, and I appreciate you sharing it. I did click the 'include synonyms' filter, but behavior remained unchanged.

I would prefer to use OSD, and that is what our dev team was expecting to use, but we just can't see the information.  We are using an ID that does not 'own' those tables, per se.  But, why would DBVisualizer show these tables, when OSD does not?

1 - 2

Post Details

Added on Jan 7 2021
8 comments
465 views