Oracle Illustrated

Feb 21 2010   8:35AM GMT

Migrating from 9i to 11g – SQL*PLUS Enhancements

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Support to BLOB

Prior to Oracle 11g it is not possible to see the column of datatype BLOB.

Example –
create table test_blob (a blob);

insert into test_blob values (‘65454545454′);

1 row created

SQL> select * from test_blob;

A
———————————————————–
065454545454

Append, Create and Replace – spool file

Prior to Oracle 10g it is possible only to create a spool file – cannot append or replace. This is particularly helpful when we spool the data inside shell scripts and use the same spool file to append the data.

Example –
spool F:\app\Luxananda\oradata\lux\spool_file1 create

select * from dual;

spool off

open spool_file1

SQL>
SQL> select * from dual;

D

X

SQL> spool off

spool F:\app\Luxananda\oradata\lux\spool_file1 append

select 1 from dual;

spool off

open spool_file1

SQL>
SQL> select * from dual;

D

X

SQL> spool off
SQL>
SQL> select 1 from dual;

1
———-
1

SQL> spool off

spool F:\app\Luxananda\oradata\lux\spool_file1 replace

select ‘a’ from dual;

spool off

open spool_file1

SQL> select ‘a’ from dual;



a

SQL> spool off

Glogin, Login and Predefined Variables

GLOGIN.sql and LOGIN.SQL will be run after each successful attempt connection to SQL*PLUS. This is quite useful as the current sessions information is set using login.sql.

show recyclebin table name

We discussed about RECYCLEBIN in the earlier section and saw the SHOW RECYCLEBIN command – this displays the contents in the table.

It is also possible to display the contents in the recyclebin for a specific table.

Quick example –

create table a (a1 number);

create table b (a1 number);

drop table a;

drop table b

show recyclebin

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————- —————————— ———— ——————-

A BIN$9rSstjwnQO6P74YgQV+1vQ==$0 TABLE 2010-02-20:11:21:27

B BIN$Yu7KhbxlSAOkWS4u0QxPuQ==$0 TABLE 2010-02-20:11:21:31

show recyclebin a

SQL> show recyclebin a
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————- —————————— ———— ——————-
A BIN$9rSstjwnQO6P74YgQV+1vQ==$0 TABLE 2010-02-20:11:21:27

DESC command

Now the DESC command compiles invalid object before displaying the output – if it cannot be re-compiled then, ORA-24372 is returned.

Quick example –

create table a (a number)

CREATE OR REPLACE PROCEDURE test_desc AS
b1 number;
BEGIN
SELECT a into b1 from a where rownum = 1;
END test_desc;
/

drop table a

SQL> select object_name, status from all_objects where object_name = ‘TEST_DESC';

OBJECT_NAME STATUS
—————————— ——-
TEST_DESC INVALID

create table a (a number)

SQL> select object_name, status from all_objects where object_name = ‘TEST_DESC';

OBJECT_NAME STATUS
—————————— ——-
TEST_DESC INVALID

Now lets describle TEST_DESC procedure –

SQL> desc test_desc
PROCEDURE test_desc

Now, lets query again !!

SQL> select object_name, status from all_objects where object_name = ‘TEST_DESC';

OBJECT_NAME STATUS
—————————— ——-
TEST_DESC VALID

Whitespace

Now we can have whitespace in sql*plus.
Example –
Spool “spool file” create

Set serveroutput

This command is used to control write to DBMS_OUTPUT.PUT_LINE. Now, from Oracle 11g the length of the text is increased from 255 to 32,767 bytes. The default is set to UNLIMITED.

dbms_output.put_line in Function

Prior to Oracle 10g if we have DBMS_OUTPUT.PUT_LINE inside a function it would not return any value. From Oracle 10g we can have this inside a function call and the SELECT statement displays the text – extremely powerful utility for debugging I bet !

Lets take a look at a quick example –

CREATE OR REPLACE FUNCTION samp_debug (in_number NUMBER) RETURN NUMBER
AS
out_number NUMBER;
BEGIN
out_number := dummy_seq.nextval * in_number;
dbms_output.put_line(‘The value of in_number is ‘ || in_number || ‘ sequence is ‘ || dummy_seq.currval || ‘ out number is ‘ || out_number);
return out_number;
END samp_debug;
/

Test :

SQL> set serveroutput on
SQL> SELECT samp_debug(5) from dual;

SAMP_DEBUG(5)
————-
337810

The value of in_number is 5 sequence is 67562 out number is 337810

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: