sqlplus vs sqldeveloper

When I run a DDL script in SQLDeveloper, it just runs perfect. Same script in SQL throws plenty of errors like:

ORA-00955: name is already used by an existing object

Incidentally, SQLPlus runs me completely crazy because it never tells you in a single line which TABLE is actually giving trouble, so it becomes really difficult to grep and report for errors...

Anyway, it turns out that if you run this in SQLPlus:

  CREATE TABLE "BUILD_POINTS" 
   ( "ENV" VARCHAR2(2 BYTE), 
 "SUCCESS" NUMBER, 
 "POINTS" NUMBER
   ) ;
/

it interprets the final / as a "repeat last command", which obviously fails because the table was already created.

The problem is that I generate this DDL with a SQLDeveloper export, so I have little control on how this is being generated. I cannot unconditionally remove all / , because in the "create package" statement they are absolutely necessary. I should write a parsing script to conditionally remove the / when they belong to a "create table".

Being able to edit tables in SQLDeveloper and then export them is too convenient....I don't want to resort to having to code SQL manually...

One workaround could be inserting a table comment for each table, so that the / would be inserted after the "COMMENT ON TABLE" statement and not after the "CREATE TABLE" statement. Repeating a "COMMENT ON TABLE" doesn't generate any error.

See also this and better still this

An excellent solution is to upgrade to SQLDeveloper 4, which doesn't generate the ";\" sequence.

Check also the (quoting) checkbox called "Terminator" which when selected uses semicolons to terminate each statement (unquoting). It's in Tools/Database/Utilities/Export.

SQLDeveloper: exporting/importing of CLOB data (XML)

Ok, I know that SQLDeveloper should not be the tool for this job, but it's so convenient, readily available and flexible.


One can export to a SQL INSERT file, but the CLOB is not exported.
One can use TEXT export to a TSV file, but if the XML contains quotes, you are screwed.
One can use XML export, it's perfect because each field is wrapped in a CDATA, but when importing it fails with a "there are no readers registered for the xml data type" (what??? then why do you offer me a XML export if you can't import???)

I find the XML format very convenient, so I think I will write a Python script to import it to a DB.

Here I read that the XML reader is in the pipe for Oracle to develop.... the issue is that I will probably retire sooner that that.

SQLDeveloper 4.0 preview: unable to run it

I have installed Java 7 and added 2 extra lines to sqldeveloper.bat:

cd C:\pierre\sqldeveloper-4\sqldeveloper\sqldeveloper\bin
sqldeveloper.bat

set JAVA_HOME=C:\pierre\Java\jdk1.7.0_45

set PATH=C:\pierre\Java\jdk1.7.0_45\bin

java -Xmx640M -Xms128M -Xverify:none -Doracle.ide.util.AddinPolicyUtils.OVERRIDE_FLAG=false -Dsun.java2d.
ddoffscreen=false -Dwindows.shell.font.languages= -XX:MaxPermSize=128M -Dide.AssertTracingDisabled=true -Doracle.ide.util.AddinPolicyUtils.OVERRIDE_FLAG=true -Djava.util.logging.config.file=logging.conf -Dsqldev.debug=false -Dide.conf="./sqldeveloper.conf" -Dide.startingcwd="." -classpath ../../ide/lib/ide-boot.jar oracle.ide.boot.Launcher

ERROR: You're trying to run the product with the legacy launcher oracle.ide.boot.Launcher . Check your .conf file and be sure to include:
        AddJavaLibFile  ../../ide/lib/fcpboot.jar
        SetMainClass    oracle.ide.osgi.boot.NbLauncher

I have added the 2 extra lines

AddJavaLibFile  ../../ide/lib/fcpboot.jar
SetMainClass    oracle.ide.osgi.boot.NbLauncher

to sqldeveloper.conf, but I still get the same error.
Giving up.
So sad.

sqldeveloper export date with time

I am using SQLDeveloper 3.2

See https://forums.oracle.com/forums/thread.jspa?threadID=1023785



Tools/preferences/ date format="yyyy/mm/dd/ hh24:mi:ss"
this will generate dates as to_date('2013/05/07/ 12:03:43','yyyy/mm/dd/ hh24:mi:ss')

SQLDeveloper and the connections.xml file

I am trying to generate the connections.xml file used by SQLDeveloper to contain all connection info. It doesn't seem too difficult, the only trick is to encrypt the password.

<?xml version = '1.0' encoding = 'UTF-8'?>
<References xmlns="http://xmlns.oracle.com/adf/jndi">
   <Reference name="hqblln422.acme.com:1551_TST_OSB_ts1_pippo_gm" className="oracle.jdeveloper.db.adapter.DatabaseProvider" xmlns="">
      <Factory className="oracle.jdeveloper.db.adapter.DatabaseProviderFactory"/>
      <RefAddresses>
         <StringRefAddr addrType="OracleConnectionType">
            <Contents>ADVANCED</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="oraDriverType">
            <Contents>thin</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="user">
            <Contents>ts1_pippo_gm</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="role">
            <Contents/>
         </StringRefAddr>
         <StringRefAddr addrType="NoPasswordConnection">
            <Contents>TRUE</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="subtype">
            <Contents>oraJDBC</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="KERBEROS_AUTHENTICATION">
            <Contents>false</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="OS_AUTHENTICATION">
            <Contents>false</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="RaptorConnectionType">
            <Contents>Oracle</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="ConnName">
            <Contents>hqblln422.acme.com:1551_TST_OSB_ts1_pippo_gm</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="customUrl">
            <Contents>jdbc:oracle:thin:@//hqblln422.acme.com:1551/TST_OSB</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="password">
            <Contents>053F1E4CDCA2D3C78B79D97CC5A0ED93CA39ADBDA8ED2CB2D3</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="SavePassword">
            <Contents>true</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="driver">
            <Contents>oracle.jdbc.OracleDriver</Contents>
         </StringRefAddr>
      </RefAddresses>
   </Reference>
</References>

Here a post telling you how to encrypt:

http://stackoverflow.com/questions/1032721/does-anybody-know-what-encrypting-technique-is-jdeveloper-sql-developer-using-to

and here how to decrypt:

http://jvzoggel.wordpress.com/tag/sqldeveloper/

You need these libraries:

/opt/oracle/fmw11_1_1_5/oracle_common/modules/oracle.adf.model_11.1.1/db-ca.jar

/opt/oracle/fmw11_1_1_5/oracle_common/modules/oracle.ldap_11.1.1/ojmisc.jar

and the code is

import oracle.jdevimpl.db.adapter.DatabaseProviderHelper;


cleartextPassword = DatabaseProviderHelper.comingIn(encryptedPassword)


encryptedPassword = DatabaseProviderHelper.goingOut(cleartextPassword)

oracle sql developer keep connection alive

https://sites.google.com/site/keepaliveext/


this man is a genious.