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

User_6SHB1

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.

EdStevens

Hi everyone, I am new to the oracle community, and I have a problem with an oracle database on version 8i.

FWIW, 8i has been out of support since before the beginning of the millennium.

What I want to do is concatenate all the tables in a database

Exactly what do you mean by 'concatenate all the tables'. I ask, because on the face of it, that makes no sense at all.

(database name is SYS)

I seriously doubt the oracle database is named SYS. In the Oralce world, SYS is the owner/schema of the data dictionary and the all-powerful user. Sounds like you are coming from the SQL Server world, where the term 'database' has a very different conceptual meaning than it does in oracle. A 'database' in SQL Server is roughly analagous to a 'schema' in oracle. In oracle, a 'schema' is a namespace containing all of the objects (tables, indexes, procedures, etc) that belong to a single user/owner. A 'database' contains' multiple 'schemas'.

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

"Single row subquery"? I don't see any subquery. . Ergo, you are not showing us the entire picture.

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

Now you are just shooting in the dark without actually paying attention to the documentation for the commands you are trying.

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

Perhaps if you stepped back a bit. Explain what you mean by 'concatenate all the tables'. Explain your desired end result.

Leave a Comment

EdStevens

Duplicate post - https://community.oracle.com/tech/developers/discussion/4479195/how-to-concatenate-all-the-tables-of-an-oracle-version-8i-database#latest

User_6SHB1

https://community.oracle.com/tech/developers/discussion/comment/16780657#Comment_16780657

when i say i want to 'concatenate all tables' i mean concatenate table names from oracle database.

the result I want to have, is to have all the names of all the tables in the database.

if you have another question, don't hesitate to let me know

EdStevens

when i say i want to 'concatenate all tables' i mean concatenate table names from oracle database.

the result I want to have, is to have all the names of all the tables in the database.

So is there some particular format you are looking for? What's wrong with simply

select owner, table_name from dba_tables order by 1,2

What do you intend to do with the output? I've been managing oracle databases since 1995 or so (and will be retiring in a few weeks) and don't recall ever needing what you seem to be asking for. I strongly suspect this is another x-y problem.

User_6SHB1

https://community.oracle.com/tech/developers/discussion/comment/16780757#Comment_16780757

I see, well, then there is no problem if I tell you, what I do is the following, business owners contact me to test the security of their web pages or servers, and I have a case that they gave me, It's about finding a vulnerability on this page "kameddata.com" is a website about medicine, and they paid me to help them improve security by finding vulnerabilities of any kind and reporting it to the web developers of the page, and I found a vulnerability of SQL injection, I have a lot of experience in this vulnerability (it is in the top 1 in OWASP) but the database is in a version that I had never seen, the 8i, so I resorted to this oracle community, which by the way I like quite.

User_6SHB1

https://community.oracle.com/tech/developers/discussion/comment/16780757#Comment_16780757

User_6SHB1

https://community.oracle.com/tech/developers/discussion/comment/16780757#Comment_16780757

what I want is to print (when I say print, I mean to do the same thing you saw in the screenshoot that I sent you before, in short, make the data I want to get seen on the same page), in short, I just want to print the names of the database tables, that's what I want to do.

But the problem is that I have tried in many ways, using several different queries, I tried to see the oracle documentation in version 8i but I could not find the solution, so I turned to this community, to see if someone could help me.

by the way, the query you sent me is very simple and straightforward, I like it, and I've also tried it, but it still doesn't work for me, then I'll send you a screenshot of what appears when I execute the query you sent me

User_6SHB1

https://community.oracle.com/tech/developers/discussion/comment/16780757#Comment_16780757

I need a way to give a limit to the number of values ​​or characters that I want to appear,In order to have a proof of concept (POC), if you want to help me, or if you are interested or curious about this case, you can contact me by telegram or discord, personally I would like you to help me, since it would help me to have the support of such a person experienced in oracle databases as you are.