Reputation: 87
I have a question on SQL I'm using oracle 11g, I want to know if it is possible to do this:
For example I have this SQL QUERY:
SELECT DC.DASHBOARD_UID
FROM DASHBOARD_SAVE DS
LEFT JOIN DASHBOARD_COLOR DC
ON DC.DASHBOARD_UID = DS.DASHBOARD_UID
I want to know if it possible to make a substring of this query to get these informations:
COL_NAME: DC.DASHBOARD_UID
TABLE_NAME: DASHBOARD_COLOR DC
JUNCTURE_NAME: LEFT JOIN DASHBOARD_COLOR DC ON DC.DASHBOARD_UID = DS.DASHBOARD_UID
Thanks
Upvotes: 0
Views: 115
Reputation: 143163
Well, in a simple case like yours, something like the following code might do the job.
However, in realistic situations, things can get much more complicated (e.g. inline views, WHERE
clause, unions, ...) so you'd end up with writing your own parser. If that's what you want, go ahead. I had to do it when I was a student (not with SQL, though) and it was interesting.
Here you go (temp
CTE is used to remove new line characters and multiple spaces):
SQL> with test (col) as
2 (select 'SELECT DC.DASHBOARD_UID, DS.SOME_COL
3 FROM DASHBOARD_SAVE DS
4 LEFT JOIN DASHBOARD_COLOR DC
5 ON DC.DASHBOARD_UID = DS.DASHBOARD_UID'
6 from dual
7 ),
8 temp as
9 (select regexp_replace(UPPER(replace(col, chr(10), '')), '\s{2,}', ' ') col
10 from test
11 )
12 select
13 trim(substr(col, instr(col, 'SELECT') + 7,
14 instr(col, 'FROM') - instr(col, 'SELECT') - 7
15 )) column_list,
16 --
17 trim(regexp_replace(substr(col, instr(col, 'FROM') + 4,
18 instr(col, 'JOIN') - instr(col, 'FROM') - 4
19 ),
20 'LEFT|RIGHT|CROSS', '')) table_name,
21 --
22 regexp_substr(col, '(LEFT|RIGHT|CROSS JOIN) .+') juncture_name
23 From temp;
COLUMN_LIST TABLE_NAME JUNCTURE_NAME
----------------------------- ----------------- -------------------------------------------------------------------
DC.DASHBOARD_UID, DS.SOME_COL DASHBOARD_SAVE DS LEFT JOIN DASHBOARD_COLOR DC ON DC.DASHBOARD_UID = DS.DASHBOARD_UID
SQL>
Upvotes: 1
Reputation: 3970
I guess you are looking for something like
SPOOL filename
. Spooling writes to your respective file whatever gets printed on the sql console after spool command as I see as per your query above you dont want to get the data but design creation as o/p
Upvotes: 1