Dynamic SQL Results to Export Dataset

Toad for Oracle
1

I suspect this info is out there somewhere, but I’m afraid I’m just not finding it. What I am trying to do is the following:

  1. Run a script using dynamic SQL to generate another SQL script

  2. I then run a DOS script to do a little cleanup on the generated script

  3. Execute the generated script. But here I want the output to be exported to an Excel file. I’m not seeing how to pass the generated script into the export dataset task. Or is there another method I should be using?

Can anyone point me in the right direction?

Thanks,

Steve

2

Hey Steve,

I don’t think there is a way to dynamically load a file into export dataset as the query to be run, but I do have another idea. Can you make a view out of it, then just have fixed text "select * from " in the export dataset action?

-John

3

Could you possibly create a function that returns the dynamic SQL string that you then retrieve into a text file to manipulate? I like John's view idea (we do that in a number of our scripts), but it sounds like you can't get the results of the dynamic sql to be exactly what you want. . .

What kind of cleanup is it that you need to run using your DOS script?

On Wed, Dec 21, 2016 at 8:32 AM, John Dorlon bounce-jdorlon@toadworld.com wrote:

RE: Dynamic SQL Results to Export Dataset

Reply by John Dorlon
Hey Steve,

I don't think there is a way to dynamically load a file into export dataset as the query to be run, but I do have another idea. Can you make a view out of it, then just have fixed text "select * from " in the export dataset action?

-John

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

--
Phyllis Helton

Data Magician

Security Gestapo
Digital Products & Strategies, Cru | Data Sciences & Analytics
Office :phone: 407-515-4452

phyllis.helton@cru.org

4

The first script I run contains a UNION ALL to a number of databases across database links. So my cleanup script just replaces the last line (UNION ALL) with a semi-colon. Perhaps you found another way around that issue?

Thanks,

Steve

5

If you're writing pl/sql or even sql to create it, just append that to the end of your string.

When I'm doing something like this, I would do something like put the union all at the beginning of the loop code when I'm not on the first record so I don't get an extra one at the end. Or, if you know you are getting an extra union all at the end of the string, you could trim it by 9 characters to trim it off. Then you add the semi-colon after the loop.

If you need more help, feel free to post your code and I can look at it.

On Thu, Dec 22, 2016 at 9:59 AM, sgivens bounce-sgivens@toadworld.com wrote:

RE: Dynamic SQL Results to Export Dataset

Reply by sgivens
The first script I run contains a UNION ALL to a number of databases across database links. So my cleanup script just replaces the last line (UNION ALL) with a semi-colon. Perhaps you found another way around that issue?

Thanks,

Steve

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

--
Phyllis Helton

Data Magician

Security Gestapo
Digital Products & Strategies, Cru | Data Sciences & Analytics
Office :phone: 407-515-4452

phyllis.helton@cru.org

6

Thanks Phyllis. Your response basically confirms what I was thinking - that I would need to convert my SQL script to PL/SQL. Here’s what I am currently running:

SELECT ‘Select database, node “Server”, mkid “Master Key ID” from whoami@’
|| HOST
|| ‘,vw_x$kcbtek@’
|| HOST
|| ‘,v$encryption_wallet@’
|| HOST
|| CHR (10)
|| ’ where ts# = 0 and wrl_type = ‘‘HSM’’ and status = ‘‘OPEN’’’
|| CHR (10)
|| ‘UNION ALL’
FROM user_db_links;

7

BTW, there are a couple of views in that code that I built myself - in case you were wondering why you weren’t seeing those objects in your database (whoami, ,vw_x$kcbtek). But you get the idea…

8

My usual trick for this type of query to create SQL is to use the ROW_NUMBER analytic in a CASE statement to prevent the extra trailing(or leading) text;

SELECT ‘Select database, node “Server”, mkid “Master Key ID” from whoami@’
|| HOST
|| ‘,vw_x$kcbtek@’
|| HOST
|| ‘,v$encryption_wallet@’
|| HOST
|| CHR (10)
|| ’ where ts# = 0 and wrl_type = ‘‘HSM’’ and status = ‘‘OPEN’’’
|| CHR (10)
|| CASE
WHEN row_number() OVER (ORDER BY db_link DESC) != 1 THEN ‘UNION ALL’
END
FROM user_db_links
ORDER BY db_link;

9

I do something similar, but using rownum so I don't have to use the analytic functions. :slight_smile:

SELECT CASE

WHEN ROWNUM > 1

THEN

'Union all' || CHR(10)

END

|| 'Select database, node "Server", mkid "Master Key ID" from whoami@'

|| HOST

|| ',vw_x$kcbtek@'

|| HOST

|| ',v$encryption_wallet@'

|| HOST

|| CHR(10)

|| ' where ts# = 0 and wrl_type = ''HSM'' and status = ''OPEN'''

|| CHR(10)

FROM user_db_links;

On Thu, Dec 22, 2016 at 10:57 AM, davidm_1335 bounce-davidm_1335@toadworld.com wrote:

RE: Dynamic SQL Results to Export Dataset

Reply by davidm_1335
My usual trick for this type of query to create SQL is to use the ROW_NUMBER analytic in a CASE statement to prevent the extra trailing(or leading) text;

SELECT 'Select database, node "Server", mkid "Master Key ID" from whoami@'
|| HOST
|| ',vw_x$kcbtek@'
|| HOST
|| ',v$encryption_wallet@'
|| HOST
|| CHR (10)
|| ' where ts# = 0 and wrl_type = ''HSM'' and status = ''OPEN'''
|| CHR (10)
|| CASE
WHEN row_number() OVER (ORDER BY db_link DESC) != 1 THEN 'UNION ALL'
END
FROM user_db_links
ORDER BY db_link;

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

--
Phyllis Helton

Data Magician

Security Gestapo
Digital Products & Strategies, Cru | Data Sciences & Analytics
Office :phone: 407-515-4452

phyllis.helton@cru.org

10

Thanks all! I now have this working using some of all your suggestions.

Much appreciated,

Steve