spool to unc path with username

[gtjr92]

I need to spool a query to a unc path on a different domain than my oracle db is on.
Normally if i was going to spool to a folder i would not need to have username since it would be on the same domain.
where can i add domain\user name and password to spool?
normally i would do this
spool \\123.456.789\foldername\filename.txt;


Thanks

 

[SantaMufasa]

GT,

Following is a technique that I find very useful to generate, "on-the-fly", replacement values for scripts that I run from SQL*Plus.

Since you have not posted the sources for the IP, folder name, and filename entries that you use for your example, above, I'll "stub" those entries with a SELECT of SQL*Plus variables (":var") from the DUAL table, but you can replace my stubbed SELECT with the real SELECT that you would like to use, cause similar, successful results for your situation. (You can substitute my "var" and "exec" code for whatever you need to fit your scenario. the "col <label>..." statement format and the "SELECT..." methodology that assigns a value to the <label> is vital for the following to work:

Section 1 -- Proof of Concept script:

var IP char(15)
var folder char(50)
var fn char(50)
exec :IP := '127.0.0.1'
exec :folder := 'sqldba'
exec :fn := 'spool_test.txt'
col myprtfile new_value myprtfile
select '\\'||trim(:IP)||'\'||trim(:folder)||'\'||trim(:fn) myprtfile from dual;
spool &myprtfile
prompt This is test output to "&myprtfile".
spool off
get spool_test.txt

Section 2 -- Sample invocation (and results) of above script:

SQL> @tt_306
\\127.0.0.1\sqldba\spool_test.txt

1 row selected.

This is test output to "\\127.0.0.1\sqldba\spool_test.txt".
  1* This is test output to "\\127.0.0.1\sqldba\spool_test.txt".
SQL>

Let us know if this techneque works to your advantage.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[gtjr92]

Maybe I am overthinking what you are saying, but I don't see anywhere in there that passes my Windows username or password to the server.

 

[LKBrwnDBA]

Have you tried something like:

spool Myid/passwd@\\123.456.789\foldername\filename.txt;




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

...I don't see anywhere in there that passes my Windows username or password to the server.

Where, in your original example, do we see your Windows username or password references/uses in the generated output format that your request? You show me where you want those items to appear, and I can show you how to specify them in the script.

I can certainly query Oracle for your login domain/username, but just as certainly Windows is not easily going to "give up" passwords in an automated fashion. Are you prepared to manually enter an appropriate password in this situation?

Sorry for all the questions...I'm just not yet clear on what you want/need from a formatting perspective.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[gtjr92]

normally i would do this
spool \\123.456.789\foldername\filename.txt
where can i add domain\user name and password to spool?

As you can see in my initial post i was asking how i can do that. Hence why my example didn't include it. I know how to do what you suggested, but that is not relevant to my question.
I will try what LKBrwnDBA suggested above. Do you have another suggestion?

 

[gtjr92]

To be clear the reason i need to add the domain\username\ password is because the folder i am spooling to is on a server that resides on another domain. so I need to use another account to log into to that server.

 

[SantaMufasa]

Sorry for my misunderstanding your need, GT. (I, of course, thought that you were looking for how to compose and specify a spool-file name that has, as its components, those parts.) Unfortunately, I am not familiar with UNC's syntax demands for adding to the equation the windows username/domain+password.

Let us know your findings subsequent to trying LK's suggestion.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[gtjr92]

Have you tried something like:

spool Myid/passwd@\\123.456.789\foldername\filename.txt;

This did not work any other ideas out there?
Thanks

 

[stefanhei]

Did you specify the domain when trying LK's solution?
Another idea would be to mapping a network drive. Modify your script like this:

host net use Z: \\<server>\<folder> <passwd> /user:<domain>\<username> /persistent:no
spool Z:\filename.txt
...
host net use Z: /delete

Or map it using explorer?

Stefan

 

[gtjr92]

Sweet that did it. I didn't use the "host" just net use by itself.
I had tried net use before but I had the password in the wrong place.! Is there anyway to encrypt my password?
Maybe by making an exe file? I am not too familier with that.
Thanks

 

[stefanhei]

Sorry, my windows-knowledge is limited...
"host" is a sqlplus command, which allows you to use os-commands in your sqlplus-command-script. I don't know of any easy way to turn sqlplus-scripts into executeables.

If mapping may be "static" you may use explorer.exe:
tools->map network drives
Here you are given the option to connect using a different username (below the option to reconnect after logon).

Stefan

 

[gtjr92]

If mapping may be "static" you may use explorer.exe:
tools->map network drives

No since noone will be logged in when the script runs using the GUI to map is no good. So the net use command works best

Thanks