I am new to Unix shell scripting. But i need help to slove the below issue.
Issue description:
I want to read table, view names and package names in a file
my plan to find the table name is : search "From" key word find the table or view
To find the packge name : Search "Package body" key word
To Search Function Name: Search "Function"
In a file i have multiple queries like below.
Sample file:
select * from EMP,DEPT
where emp.deptno=dept.deptno
Package Body xxx_Search_PKG
In the above query i want to find the EMP table first display out as below in a file. find DEPT table and put in second line.
Output: Find each table or Function or Package and display out put like below in one file.
If u have Database access, you can simply fetch details using:
select OBJECT_NAME, OBJECT_TYPE from dba_objects where OBJECT_TYPE in ('TABLE', 'VIEW', 'PROCEDURE', 'PACKAGE') order by OBJECT_TYPE;
In the Unix every thing is case sensitive,
In sql query you can write the query as "FROM table_name WHERE" or "from table_name where" or "From table_name Where" and so on formats.
Some times you are using the Multiple Object names in the select query with alias names also.
select a.col1, a.col2, b.col1, d.col5 from TABLE1 a, Table2 b, table4 d
where ....
It is some more difficult to fetch the all the object details in the file.
actually i have a text file(SQL and PL/SQL Code around 20000 Lines). In the file i have Table Names, Package, Procedures and Functions names.
Suppose in a query i have more than one table in From Clause i need to find first table and put in below format and second table and third table and so on.
I want separate all the object names from the file and put in a new file in a specific Format like below