msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

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:


Reader's Comments

  1. |

    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.

  2. |

    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.

  3. |

    Thanks for the tips.