Sqlplus read parameters from file

Active 5 years, 3 months ago. Viewed 96k times. Improve this question. Add a comment. Active Oldest Votes. Firstly, you will need to invoke your script like so: sqlplus. Improve this answer. That was it. ChadD see sqlplus spool -command. In addition, not having to change application code also makes it easier to enforce password management policies for these user accounts.

When you configure a client to use the external password store, applications can use the following syntax to connect to databases that use password authentication:. Instead your system looks for database login credentials in the client wallet. You are logged in once you successfully change your password. If your password has expired, the Expired Password screen is automatically displayed when you attempt to log in.

Fill out the fields on the Expired Password screen as you would for the Change Password screen. If you click the Cancel button, you are returned to the Login screen. You can change your Oracle Database account password in the Change Password screen. If you have logged in with DBA privileges, you can change the password of other users.

You access the Change Password screen from the Preferences screen. You must connect to an Oracle Database instance before you can query or modify data in that database. You can connect to the default database and to other databases accessible through your network. To connect to another database over a network, both databases must have Oracle Net configured, and have compatible network drivers. You must enter either a connection identifier or a net service name to connect to a database other than the default.

Your DBA is responsible for creating the databases you use and defining net service names for them in the tnsnames. A net service name definition in the tnsnames. To use a net service name alias , it must have an entry in the tnsnames. An entry in tnsnames. Example The tnsnames. Example Start a command-line session to the sales database using the net service name.

Depending on your configuration, use the full connection identifier syntax like:. It combines a database name with a domain name. It defaults to the SID you entered during database creation. Example Start a command-line session to the sales database using the easy connection identifier. The easy connection identifier can be used wherever you can use a full connection identifier, or a net service name. The easy syntax is less complex, and no tnsnames.

If you are connecting to a remote Oracle database, make sure your Oracle Net software is installed and working properly. The site profile file, glogin. For example, processing glogin. Enter your username and press Return. Enter your password and press Return again. Change parms. Your performance problem sounds like it might possibly be related to bind variable peeking, particularly the 'stuck with a bad plan' description.

The execution plan is determined by the first query run, which might choose suitable indexes etc. The stand-alone straight SQL will have a separate parse and thus possibly different more suitable plan. In the past I've had to gather stats manually to prevent histograms being used, which at least stabilises things.

Well, a parameterized query is going to do better, hands down. If so, this might work for you:. And the results should match the incoming parameters. The catch is that the SQL needs to be in the shell script. Pretty sure that using the filename. Side Note: Why not store the parameters in a place that's already ready made for storing data?

That is, your database? If the parameters need to be changeable, then write a screen or other process that lets the parameters be updated by the end-user, and just link to the table in your process. Side Side Note: In my example, I am not doing anything with regards to binding variables.

Therefore performance is not likely to be great when used in a long-running statement. Then Oracle should be able to optimize the statement nicely. Connect and share knowledge within a single location that is structured and easy to search.

I've been tinkering with sqlplus for awhile now, and I've managed to get sqlplus to read the contents of a file into a substitution variable as such:. This mostly works as my script requires However, I was only using a sample file which was 50 or 60 bytes in size. The actual files I'll end up using will be at least a few kilobytes.

So it was at this point I decided to check the max size of a substitution variable: characters. Is there a way within my sqlplus script to read a file's contents into a bind variable?

Note that the file props. You can then run , edit and list the buffer. A substitution variable is not the right place to load a file into. But of course the file has to be located on the server in this case. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams?