Application: Field name of all data tables contained in the query database for all data in a table with a known field name
How to: Specify the field name, User database table, which can execute the following query
The--oracle build query includes the field names that correspond to the fields specified in all data table record statements
Declare
Mycolumnname VARCHAR (255): = ' userid ';--Define the field name variable to query, change to the field name you want to query before executing
Myownername VARCHAR (255): = ' system ';--Define the database username variables to query, and change to the database you want to query before executing username
MyString NVARCHAR2 (4000): = "; --Define the string variables to output
Cursor MyCursor is--defines a cursor
SELECT * from Dba_tab_columns where lower (column_name) =mycolumnname and Lower (owner) =myownername;
Myrecord Mycursor%rowtype; --Define Cursor record type
Counter int: = 0;
Begin
Open mycursor; --Open cursor
If Mycursor%isopen then-infer open success
Loop-Loop Get recordset
Fetch mycursor into Myrecord; --Get the record in the cursor
If Mycursor%found then--the found property of the cursor infers if there is a record
Mystring:= ' select * from ' | | myrecord.table_name| | '; ';
Dbms_output.put_line (mystring);
Else
Exit
End If;
End Loop;
Else
Dbms_output.put_line (' Cursor not open ');
End If;
Close MyCursor;
End
The execution result is similar to the following statement:
SELECT * from Def$_propagator;
SELECT * from Repcat$_repcatlog;
SELECT * from Repcat$_repgroup_privs;
SELECT * from Sqlplus_product_profile;
SELECT * from Product_privs;
Copyright notice: This article Bo Master original article. Blogs, without consent, may not be reproduced.
The Oracle Build query includes the field names specified in all data table record statements