Jump to content
  • Advertisement
Sign in to follow this  
TriSwords

PL/SQL Question

This topic is 4075 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

I have a oracle machine I am using and have writting something that gets a list of each table in a schema, then starts with the first table, gets a list of columns, goes to the first column of that table and is suppose to get the data in it, then go to the second column, ect till the table is done then move on to the next table and start all over. So far I can get a list of tables and a list of columns in each table, BUT I cannot figure out a SELECT Statement that A) allows me to use varaibles instead of names IE curr_table = the current tables name curr_column = current columns name select curr_column from curr_table OR B) I have tried Select (Select column_name from all_tab_cols where table_name=curr_table_name and column_name= curr_col_name) from (Select table_name from dba_tables where table_name=curr_table_name) My problem with this is that the tables I am calling are in a schema. My usual call statements are select * from schema.table1 or along those lines If my first large select statement is working then it is coming out to be select column1 from table1 and I cannot figure out how to add a schema. infront of the table1. So it would say Select column1 from schema.table1 Any help?

Share this post


Link to post
Share on other sites
Advertisement
Bad internet, it takes me about 5 minutes to load a page where I am. I tried to post 5 times with no luck (timed out) so I just kept trying until it worked (4 more times) Even though I got 3 of the 4 saying it timed out, 3 of them went through. But it was taking too long to post -> time out -> check for time out -> new post -> wait all over again. Then I had to take about 10 minutes per post to delete the others due to the bad load time.

Share this post


Link to post
Share on other sites
A) Although I am much more familiar with SQL Server than with Oracle, the syntax must also exist for Oracle. In SQL Server I would do the following:

EXECUTE('SELECT ' + curr_column + ' FROM ' + curr_tale)

The EXECUTE function would plug-in the values and execute the statement as a literal.

Share this post


Link to post
Share on other sites
What Adlay29 said. The keyword is dynamic sql. When you have got variables, which concern the structure your data is stored in and not the data itself, you need to build dynamic sql statements.

It is not really complicated. You construct your SQL statement into a string (varchar2) and execute the string.

If you expect the result to be just a single row, you could use the "execute immediate <statement variable>" construct. If you expect to get more than one row, you could declare a cursor variable and use "open <cursor variable> for <statement variable>". Then loop through the results.

If you did not use dynamic SQL before, you should IMO read the relevant chapter in the pl/sql documentation and adapt the examples to your needs.

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!