Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL Dev 4.1.3.20 Script Output Formatting defaults on Mac OS X

Daniel J Van Der MeulenMay 2 2016 — edited May 2 2016

I'm having an issue with SQL Dev 4.1.3.20. In SQL Dev 3.1 I did not run into this issue but 3.1 no longer works after updating java so I downloaded the latest version. 3.1 worked right out the gate, 4.1 is a bear. 3.2 doesn't work either.

The issue I'm running into is, unless I specify SET LINESIZE 32767; SET PAGESIZE 50000; when I first open a worksheet then the script output is off and a total mess. I can't get SQL Dev to default to these line and page size settings.

For example, Before I run the set line size and page size commands:

Screen Shot 2016-05-02 at 10.23.32 AM.png

SHOW ALL

appinfo is OFF and set to "SQL Developer"

arraysize default

autocommit OFF

autoprint OFF

autotrace OFF

BTitle OFF

colsep " "

concat "." (hex 5c)

copycommit 0

define "&"

echo OFF

escape OFF

FEEDBACK ON for 6 or more rows

heading ON

headsep "|" (hex 7c)

linesize 80

long 80

longchunksize 80

null null

numformat ""

numwidth 10

pagesize 14

PAUSE is OFF

release 1102000400

scan ON

serveroutput OFF

space " "

spool OFF

sqlcode 0

sqlprompt "SQL> "

suffix "sql"

termout ON

timing OFF

trimout OFF

trimspool OFF

TTitle OFF

USER is "*******"

verify ON

wrap : lines will be wrapped

After I run the line size and page size commands:

Screen Shot 2016-05-02 at 10.25.05 AM.png

My SHOW ALL after changing the line and page sizes

appinfo is OFF and set to "SQL Developer"

arraysize default

autocommit OFF

autoprint OFF

autotrace OFF

BTitle OFF

colsep " "

concat "." (hex 5c)

copycommit 0

define "&"

echo OFF

escape OFF

FEEDBACK ON for 6 or more rows

heading ON

headsep "|" (hex 7c)

linesize 32767

long 80

longchunksize 80

null null

numformat ""

numwidth 10

pagesize 50000

PAUSE is OFF

release 1102000400

scan ON

serveroutput OFF

space " "

spool OFF

sqlcode 0

sqlprompt "SQL> "

suffix "sql"

termout ON

timing OFF

trimout OFF

trimspool OFF

TTitle OFF

USER is "*******"

verify ON

wrap : lines will be wrapped

I have looked everywhere in preferences and cannot find where to set a high level default for all DBs to default to the larger page and line size. Will I be forced to always run a formatting command when I want to run some SQL statements? Is there a setting I'm over looking? I've been through every one of the menu options and can't find a line size or page size anywhere.

I tried turning off wrapping without the line size and page size changes and received !ROWSTRUNCATED! error messages and the information was truncated so that doesn't fix my issue plus I still have to enter a command before I run my script.

Does anyone know where to default these settings at in the preferences?

Thanks in advance for any assistance or advice you may provide. If need be I could always file an Oracle support ticket but thought I'd try the community first to see if anyone had the answer.

Comments

Alex Keh-Oracle

Did you add the required Fluent API (i.e. HasMaxLength(x > 2000)) so that ODP.NET EF Core can map a byte[] to a BLOB?

user606112

My fluent API looks like this:

        protected override void OnModelCreating(ModelBuilder modelBuilder)

        {

            //Default - Schema: HR

            modelBuilder.HasDefaultSchema("HR");

            // Customer-Configuration

            modelBuilder.Entity<Customer>().Property(c => c.CompanyName).HasColumnType("varchar2");

            modelBuilder.Entity<Customer>().Property(c => c.CompanyName).HasMaxLength(40);

            modelBuilder.Entity<Customer>().Property(c => c.Ludsys).IsConcurrencyToken();

            // Order-Configuration

            modelBuilder.Entity<Order>().Property(o => o.Ludsys).IsConcurrencyToken();

            // OrderDetail-Configuration

            modelBuilder.Entity<OrderDetail>().Property(od => od.Ludsys).IsConcurrencyToken();

            // Product-Configuration

            modelBuilder.Entity<Product>().Property(p => p.ProductName).HasColumnType("varchar2");

            modelBuilder.Entity<Product>().Property(p => p.ProductName).HasMaxLength(40);

            modelBuilder.Entity<Product>().Property(p => p.Ludsys).IsConcurrencyToken();

            // Picture-Configuration

            modelBuilder.Entity<Picture>().Property(p => p.Bild).HasColumnType("blob");

            modelBuilder.Entity<Product>().Property(p => p.ProductName).HasMaxLength(6000000); // 6MB

            modelBuilder.Entity<Picture>().Property(p => p.Ludsys).IsConcurrencyToken();

            // Beziehungen der DbSets untereinander

            modelBuilder.Entity<Customer>().HasMany(c => c.Orders).WithOne(o => o.Customer);

            modelBuilder.Entity<Order>().HasMany(o => o.OrderDetails).WithOne(od => od.Order);

            modelBuilder.Entity<OrderDetail>().HasMany(od => od.Pictures).WithOne(p => p.OrderDetail);

            modelBuilder.Entity<Product>().HasMany(p => p.OrderDetails).WithOne(od => od.Product);

        } // OnModelCreating

Alex Keh-Oracle

Have you tried adding the required HasMaxLength(x > 2000) Fluent API to the following line:

       modelBuilder.Entity<Picture>().Property(p => p.Bild).HasColumnType("blob");

This Fluent API is required as per the documentation.

user606112

I tried 3 variants:

only: modelBuilder.Entity<Picture>().Property(p => p.Bild).HasColumnType("blob");

           

only: modelBuilder.Entity<Product>().Property(p => p.ProductName).HasMaxLength(6000000); // 6MB

both: modelBuilder.Entity<Picture>().Property(p => p.Bild).HasColumnType("blob");

         modelBuilder.Entity<Product>().Property(p => p.ProductName).HasMaxLength(6000000); // 6MB

I get always: ORA-01460

Alex Keh-Oracle
Answer

Have you tried the HasMaxLength on the Bild property, not the ProductName?

modelBuilder.Entity<Picture>().Property(p => p.Bild).HasMaxLength(6000000);

It should work with the HasMaxLength, but without the HasColumnType.

Marked as Answer by user606112 · Sep 27 2020
user606112

Hallo Alex,

thank you very much for your help. Only the following property is necessary.

modelBuilder.Entity<Picture>().Property(p => p.Bild).HasMaxLength(6000000);

I did much copy and paste to resolve the problem, so that I didn't see the wrong columnname "productname".

Now all is working pretty .

With kind regards! Günter Hoormann

Alex Keh-Oracle

Hi Gunter,

Glad to hear it works for you. Which DB version are you using?

My team hadn't seen this error before when we tested similar scenarios.

user606112

Hallo Alex,

I use DB-Version as below to create my Test-DB:

BANNER

-------------------------------------------------------------------

Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

BANNER_FULL

-------------------------------------------------------------------

Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

Version 18.4.0.0.0

BANNER_LEGACY

-------------------------------------------------------------------

Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

I created the DB by application in VisualStudio 2019 using the following nuget-packages:

Util1.JPG

DB looks like this:

Util3.JPG

Util4.JPG

I tested EF Core 3.1 using packages:

Util2.JPG

Actual Fluent-API:

    protected override void OnModelCreating(ModelBuilder modelBuilder)

    {

        //Default - Schema: HR

        modelBuilder.HasDefaultSchema("HR");

        // Customer-Configuration

        modelBuilder.Entity\<Customer>().Property(c => c.CompanyName).HasMaxLength(40);

        modelBuilder.Entity\<Customer>().Property(c => c.Ludsys).IsConcurrencyToken();

        // Order-Configuration

        modelBuilder.Entity\<Order>().Property(o => o.Ludsys).IsConcurrencyToken();

        // OrderDetail-Configuration

        modelBuilder.Entity\<OrderDetail>().Property(od => od.Ludsys).IsConcurrencyToken();

        // Product-Configuration

        modelBuilder.Entity\<Product>().Property(p => p.ProductName).HasMaxLength(40);

        modelBuilder.Entity\<Product>().Property(p => p.Ludsys).IsConcurrencyToken();

        //Picture - Configuration

        modelBuilder.Entity\<Picture>().Property(p => p.Bild).HasMaxLength(6000000); // 6MB

        modelBuilder.Entity\<Picture>().Property(p => p.Ludsys).IsConcurrencyToken();

        // Beziehungen der DbSets untereinander

        modelBuilder.Entity\<Customer>().HasMany(c => c.Orders).WithOne(o => o.Customer);

        modelBuilder.Entity\<Order>().HasMany(o => o.OrderDetails).WithOne(od => od.Order);

        modelBuilder.Entity\<OrderDetail>().HasMany(od => od.Pictures).WithOne(p => p.OrderDetail);

        modelBuilder.Entity\<Product>().HasMany(p => p.OrderDetails).WithOne(od => od.Product);

    } // OnModelCreating

For ludsys column I created some trigger like this:

-- Customers

create or replace trigger trg_Customers_Ludsys

before insert or update on "Customers"

for each row

begin

:new."Ludsys" := sysdate;

end trg_Customers_Ludsys;

/

I hope this will help you.

Günter

1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 30 2016
Added on May 2 2016
3 comments
934 views