CODEWITHSUNDEEP
oracle-databasesqlplustoad
Norie Cris Sagun
Norie Cris Sagun

Reputation: 58

Why does the result in TOAD and SQLPlus differ?

I have this query.

select
   dbms_metadata.get_ddl('USER', username) || '/' usercreate
from
   dba_users where username = 'NSAGUN';

In TOAD, I get this text. (using SAVE AS TAB DELIMITED)

USERCREATE

   CREATE USER "NSAGUN" IDENTIFIED BY VALUES '1EE5F58CB716B194'
      DEFAULT TABLESPACE "PIN01"
      TEMPORARY TABLESPACE "PINTEMP"
 /

But in SQLPlus I only get this:

USERCREATE
--------------------------------------------------------------------------------

   CREATE USER "NSAGUN" IDENTIFIED BY VALUES '1EE5F58CB716B194'
      DEFAULT T

Why is that? And how can I make the output in SQLPlus the same as in TOAD?

Upvotes: 2

Views: 816

Answers (2)

Marco Baldelli
Marco Baldelli

Reputation: 3728

Try using these settings in SQL*Plus before executing the query:

set long 1000000
set longchunk 1000000
set linesize 200

The dbms_metadata.get_ddl function returns a CLOB value and by default SQL*Plus sets the LONG variable to 80 bytes.

Upvotes: 3

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

SQL> set long 1000000
SQL> set pagesize 0
SQL> SELECT
  2     dbms_metadata.get_ddl('USER', 'LALIT') || '/' usercreate
  3  from
  4     dba_users where username = 'LALIT'
  5  /

   CREATE USER "LALIT" IDENTIFIED BY VALUES 'S:F10EA8C6778ACE16430E4714FE8C41CFB
2C9E5BC73ADDC503E134EA91AF9;H:076ADC10B6F6540DEEB030DF6C97A752;C6F71E6F6BA0F4BD'

      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"/


SQL>

LONG {80|n}

Set the maximum width (in chars) for displaying and copying LONG values.

SET PAGES[IZE] {14 | n}

Sets the number of rows on each page of output in iSQL*Plus, and the number of lines on each page of output in command-line and Windows GUI. You can set PAGESIZE to zero to suppress all headings, page breaks, titles, the initial blank line, and other formatting information.

Upvotes: 1

Related Questions