3 Useful SQL*Plus Tips
I stumbled on the following SQL*Plus tips at a new Oracle blog called Tahiti Views:
@ (“at” sign) vs. @@ (double “at” sign): With @@, all the import commands are processed relative to the directory where the original file sits, not the directory where you run SQL*Plus.
Splitting Up Package Code: You can split a big PL/SQL package script file into many smaller ones using the @ (“at” sign) SQL*Plus command. For example:
create or replace package foo
as
@foo_declarations;
@foo_procedures;
@foo_functions;
end;
Turn a File into a String Literal: Also using the @ command, you can turn the entire content of a file into a string literal. For example:
select
'
@foo.htm;
'
from dual;
You can also use the alternative quoting mechanism in case the file contains single quotes. For example:
select
q'{
@bar;
}'
from dual;
neat!
Filed in Oracle, Tips on 25 Mar 07 | Tags: sqlplus
Reader's Comments
About This Blog
- Subscribe by Email
-
-
-
- The views expressed on this blog are mine alone and do not necessarily reflect the views of my employer.
I’d never seen the Q quoting mechanism, thanks for pointing it out. I’ve written some generators and gotten SO confused quoting quotes. I wrote some helper functions – one, oddly enough, is called Q, and simply puts single quotes around any string. So instead of: ‘title:=”My Title”;’ I write ‘title:=’||q(‘My Title’)||’;’ This looks more complicated in the example, but when you have lots of this stuff, it winds up more readable. I also use REPLACE a lot.
John, you may also want to take a look at REGEXP_REPLACE, it is powerful and can be very helpful.
As I understand it, the idea behind the Q- quoting mechanism is to minimize confusion and maximize readability when dealing with string literals that may have lots of single-quoting in them. The Q- quoting mechanism was only introduced in 10gR2. So, what’s new is important.
Thanks for the tips.