0

I'm searching for a long time for some way to iterate through PL/SQL row, but I did not get any appropriate results.

For example, if I have a PL/SQL row that looks like this row(first_name,last_name) and I want to print the first name and the last name using PL/SQL without knowledge about the row i.e. I don't know how the row looks like so I need some kind of code that looks like this

FOR column IN My_Row  --some way to iterate through the row.
LOOP
    PRINT column...   --do my stuff, in this example
END LOOP;
John Doyle
  • 7,475
  • 5
  • 33
  • 40
Asaf Maimon
  • 71
  • 3
  • 9
  • What do you mean by "PL/SQL row"? ROWTYPE? A (comma-separated) string? And why do you not know that structure of the row? How do you get this row? – Szilard Barany Feb 15 '12 at 07:42
  • Yes' it's a ROWTYPE, and the reason that I don't know is because I want to iterate through all the database, it means that all kinds of ROWTYPE that exists in my data base will be reached to that code. By the way, the main idea is to find all the tables within the database that contains a given value in some cell. thanks. – Asaf Maimon Feb 15 '12 at 08:23

1 Answers1

1

If you know the name of the table the row is from then you can get the names of the columns from ALL_TAB_COLUMNS. You can build a dynamic SQL around that. See this Ask Tom article regarding something like this: Referencing %rowtype variables without using column names.

Based upon your comment you'll be going through all your tables so you can use ALL_TABLES to get all their names. Though remember that ALL_TABLES is all tables your login has access to while DBA_TABLES is every table in the database and USER_TABLES is those that your user owns.

John Doyle
  • 7,475
  • 5
  • 33
  • 40
  • Some Exception ALL_TAB_COLUMNS contains tables- column pair, but not all db exists within the table... only a few tables exists in ALL_TAB_COLUMNS . is there another place that I can take these values from? – Asaf Maimon Feb 19 '12 at 08:48
  • @Asaf Maimon - `ALL_TAB_COLUMNS` shows columns for tables you have permissions on, corresponding to `ALL_TABLES`. Similarly `DBA_TAB_COLUMNS` contains columns for all tables in the database, but you may not have permission to see that, or to select from some of the tables it shows. – Alex Poole Feb 19 '12 at 10:57
  • ahhhh, I got you, look I have some problem, may be you will find some solution, i'm trying to iterate through all the data base in order to get all the tables that contains some value. but if the table ALL_TAB_COLUMNS contains only tables that I have a permissions to them, how can I search the value whitin tables that I don't have a permission to? I can select values from these tables, so i sure that there is a way to check if they contains some value... thanks – Asaf Maimon Feb 19 '12 at 12:56
  • @Asaf Maimon - see [this question](http://stackoverflow.com/questions/208493). But you can't search data you can't see. You'll need to run your query as a user that can see all tables; or separately as each of the owners of tables (using `USER_TABLES`); or get a DBA to run it for you. – Alex Poole Feb 19 '12 at 19:14
  • there something that I can't figure out, if I have a permission to see data from some table, why can't I see the columns of the table? – Asaf Maimon Feb 21 '12 at 07:58