connect/password question [message #9736] |
Tue, 02 December 2003 14:16 |
Jae Bosou
Messages: 7 Registered: December 2003
|
Junior Member |
|
|
I have a SQL script that contains a hard coded user/password connection. example:
------------------------------------
connect user/password;
drop view myview;
commit;
create view myview
blah.. blah.. blah...
commit;
spool myview.lis
select .... blah..blah..
spool off;
exit;
--------------------------------------------
The problem with this SQL, it requires hard coded user and password every time I run (not secure).
How do I avoid this and create a simple PL/SQL function or procedure? I am a novice PL/SQL. If you can give a step-by-step example or an example link on the net, I really appreciate it.
|
|
|
Re: connect/password question [message #9738 is a reply to message #9736] |
Tue, 02 December 2003 14:44 |
sverch
Messages: 582 Registered: December 2000
|
Senior Member |
|
|
I don't know why you use a script to connect, but
when you drop something including view you do not have to commit because Oracle commits on all DDL commands and Drop is one of them.
Insted of CREATE VIEW use CREATE OR REPLACE VIEW - this way you do not have to DROP your view.
Again, you do not have to COMMIT after CREATE.
And the main thing is - why use a script to create view?
You can CREATE OR REPLACE VIEW once and this view will be automatically refreshed every time when source data changes and you SELECT from the view.
|
|
|
Re: connect/password question [message #9745 is a reply to message #9738] |
Tue, 02 December 2003 15:29 |
Jae Bosou
Messages: 7 Registered: December 2003
|
Junior Member |
|
|
....forget about this creating views script and commit stuff... it's only an example.
What I want to know is how do I create a function/procedure without entering HARD-CODED USERNAME/PASSWORD. In another word ... changing from plain SQLplus script to PL/SQL written in function/prodecure.
|
|
|
|
Re: connect/password question [message #9749 is a reply to message #9745] |
Wed, 03 December 2003 00:14 |
|
Barbara Boehmer
Messages: 9102 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You cannot connect from within a pl/sql procedure, since you have to be connected to execute the procedure and connect is a SQL*Plus command, so it cannot be used within a procedure. I have listed some alternatives below. I can't tell you which is best without more specifics about what you want to do.
If you are already connected, but want to select from another schema, you can accomplish this by granting the necessary privileges and using schema.tablename.
If you want to prompt for a password without displaying the password, you can use ACCEPT and PROMPT and HIDE to prompt for and accept a hidden password without showing it anywhere.
If you want to use your script as is, but you just need to prevent anyone from reading the password within it, you can use WRAP to encrypt the SQL script to prevent anyone from reading the entire script, including username and password.
|
|
|