you can probably use some js and the transformation scripts to append or prefix the short name to the comments for all of your entities, auto-magically
1->I would like to know if it is possible to connect Oracle SQL Developer to SQLite DB using the apropriate JDBC ( https://github.com/xerial/sqlite-jdbc )
2->I also would like to know how Oracle SQL Developer adds the connection tabs to Connection Window (New / Select Database Connection) , when i add the mysql JDBC to Oracle SQL Developer, reset it and open the Connection Window it will appear a new tab with the name of MySQL.
How Oracle SQL Developer creates that tab ?
Is the mySQL JDBC that tells to the Oracle SQL Developer the content of that tab or the content of that tab is in the Oracle SQL Developer code?
It’s sad … that Oracle SQL Developer is not compatible with SQLite because i like the environment and I am used to it and didn’t want to change.
I would like that in the future Oracle SQL Developer would be compatible with SQLite because that way I wouldn’t need to change my frontend to connect to database.
Thank you very much for your response.
Even if we did, many of the features you love wouldn’t be available… They’re coded specifically for an Oracle Database.
Carlos Rocha
You said “They’re coded specifically for an Oracle Database.” but if I am not wrong Oracle SQL Developer works as a frontend to Microsoft SQL Server, MySQL and Sybase and they aren’t Oracle Database 😛
That said I ask to myself: SQLite is so diferent from the other Databases supported that isn’t compatible to the logic of Oracle SQL Developer?
b/c no one (and by ‘no one’, not enough to spend the time building out support for it) is migrating sqllite databases to oracle database – see this post
Carlos Rocha
Thank you very much for your time, attention and to share these useful information that allowed me to understand better the world of Oracle SQL Developer.
Best regards
Hello, I just downloaded
Oracle SQLDeveloper Command-Line (SQLcl) version: 4.2.0.16.308.0750. When I start sqlcl it states that “New version: 4.2.0.1 available to download”, however, I cannot find this version to download.
Thanks for you blog, it is most interesting.
Sam
Oracle SQLDeveloper Command-Line (SQLcl) version: 4.2.0.16.308.0750 IS the latest version. We’re trying to get the new version messaging in the app working, sorry for the confusion.
I found a bug with the formatter in 4.2
Version 4.2.0.16.260
Build 16.260.1303
Code before SQL Developer formatter is run
DECLARE
v_string VARCHAR2(4000);
v_value varchar2(4000) := ’10’;
BEGIN
v_string := ‘The value ‘
|| ””
|| v_value
|| ””
|| ‘ is in the variable v_value.’;
dbms_output.put_line(v_string);
END;
Run it and the output is:
The value ’10’ is in the variable v_value.
Code after SQL Developer formatter is run, notice that the formatter has removed a single quote from two lines of code
DECLARE
v_string VARCHAR2(4000);
v_value VARCHAR2(4000) := ’10’;
BEGIN
v_string := ‘The value ‘
|| ”’
|| v_value
|| ”’
|| ‘ is in the variable v_value.’;
dbms_output.put_line(v_string);
END;
Run it and the output is:
The value ‘
|| v_value
|| ‘ is in the variable v_value.
This changes the meaning of the concatenation and introduces an insidious bug that might be hard to track down.
SET SERVEROUTPUT ON
DECLARE
v_string VARCHAR2(4000);
v_value VARCHAR2(4000) := '10';
BEGIN
v_string := 'The value '
|| ''''
|| v_value
|| ''''
|| ' is in the variable v_value.';
dbms_output.put_line(v_string);
END;
executes...
The value '10' is in the variable v_value.
PL/SQL procedure successfully completed.
Hi Jeff,
We have a user running Oracle SQL Developer 1.5.5 with a 64 bit version of Excel 2013. He says nothing happens when exporting his query results to Excel.
Is it possible that his ancient version is not compatible with the 64 bit version of Excel? Do you think a simple upgrade will fix?
How do I completely remove anything to do with SQL Developer so that I can re-install? How does the n-jre version find the jdk directory? Should I not be getting a dialog box asking where the jdk folder is? And how does it find all the connections?
Regards
Amin Adatia
And it is Windows 10
Amin Adatia
I install jdk_8u-102-windows-x64.exe
installs in Program Files\Java\\jdk1.8.0_102
I point to that for the no-jre version 4.1.3.20-78
Error => Unable to create JVM at
C:\Program Files\Java\jdk1.8.0_102\jre\bin\server\jvm.dll
Hi Jeff,
would you like help me with look and feel of the Oracle Developer ?
My problem is very very simple.
On the right up corner is a comboBox with known connections.
Unfortunately, in case of high number of connections it is become unusable
because of limitation of displayed records (currently it is 8). Yes, it has a scroll bar, but in a huge list of connections it is not providing an easy survey.
Do you happen to know, if there exists any way, how to resize this combo ?
I would like to see more times records in it then it is right now.
Thank you for answer and tip
Regards
Antonin Pokorny
have followed your guidance and can spool out results to files etc but what im now trying to do is to spool some results which come as a result of several subqueries..
example.
with sub1..,
with sub2..,
with sub3..
select blah from sub1, blah from sub2, blah from sub3..etc
how do i spool the ‘select’ statement into a csv please?
do i put the spool command before the ‘select’ or before the 1st ‘with’?
I’ve tried both but i get a multiline java/ oracle error.
thanks for any help..
Random extensibility thought (and extreme laziness) …
Could the SD Help Center be extended to include other documentation like SQL Reference, etc? I love Mr. Google, but highlighting regexp_like and hitting to see the syntax or usage of the function would be cool and make SD even more accessible to learners.
As an extension, is it possible to roll-my-own content that would be available through SD Help Center?
Thanks Jeff! Enabling this wasn’t clear to me, so I’m sharing…took me a bit of digging to find the option. For the record, it’s here: Open Help Center > click on search magnifying glass (little menu icon next to it!) There are options for searching.
I raised this question on stackoverflow about a week back.
It’s to do with substitution variable behaviour in SQL developer (V4.1.1.19.59 on windows 7 enterprise service pack 1).
Basics are: I want to have 1 parameter which contains a year i.e. YYYY.
Then I want to dynamically use this parameter to make a 2nd parameter YYYY2 which is YYYY – 1.
Now I can do this and make the equation explicit using the select from dual method or the new_value style method but when I actually call it to name a table in a select * from i get the sql developer popup asking me to enter a value even though I’ve already set the value.
One kind responder on stackoverflow has suggested it’s a bug as in his version the popup does not trigger and all is well.
So before i raise it as a bug I was wondering if you’d observed this behaviour before and know about it and how to overcome it?
Haven’t observed that personally..and I would need a code sample to have an opinion. But, if it works in sqlplus and not in SQLDev then it’s a bug. Probably.
Jeff, I find sqlcl to be slower overall than sqlplus. For example logging in via sqlplus is pretty quick but sqlcl takes few seconds. Is this a known limitation or can something be done to make it faster?
is it connect time to start-up time that you’re seeing? for example, does running sql /nolog take the same amount of time as a connect hr/oracle after sqlcl has already started? part of the issue is that SQLcl is a java app, it’s not a compiled native C application, so it’ll probably never start as fast as SQL*Plus. and the first time it’s started might take more than a few seconds, but if you close it and start it again, will and should start much faster
Nazeem
sql /nolog takes a sec or two and connect takes more than that. Yes, closing and reconnecting is better than first time. Why is it slightly better when relogging in?
Is the overall slowness due to drivers (i assume jdbc drivers are being used) or java being slower than C? Today I was doing desc dbms_stats and it took ages for sqlcl to show the output whereas in sqlplus its super quick. This slowness is making me go back to sqlplus though i like features of sqlcl. Any settings that can be made to make it faster?
I tried to connect timesten from sql developer (4.1) but get a problem “..no ttjdbcCS” in java.library.path.
I have that library in my local, and know I need to add its location to java.library.path. But I could not find a way to do it on SQL developer. I tried to set via $PATH, $CLASSPATH, $DYLD_LIBRARY_PATH, and even add it directly on sqldeveloper.conf. But nothing work.
Can you tell me how I can configure the value of java.library.path on SQL developer?
Could you please suggest how to get results using something like this (pipeline + CLOB):
SELECT * FROM table(dbms_stats.diff_table_stats_in_history(‘ME’,’MY_TABLE’,SYSDATE-1,null,0));
To obtain results in SQLPLUS I use
set long 30000
show long
and it works.
But in SQL Developer I get:
“ORA-20010: DBMS_STATS INTERNAL ERROR in report_line : Length of line “SOURCE A : Statistics as of 11-APR-16 08.48.40.000000 AM AMERICA/LOS_ANGELES
” exceeds 80, line length = 82″
i want to add radio button to table on my website back end if it checked yes the row get the color light green if it is not checked or checked no the row color is the table default color the checked data stored in database
SQL Dev 4.1.1.19.59
I am trying to migrate from MS Access into Oracle 12c. Connections only shows SQL Server and Sybase. Where do I get the required drivers for MS Access? In the Third Party stuff I added
Jeff, I just installed a vbox appliance for DB development and SQL Developer does not even have a “Containers” tree in the DBA navigator. Is this a bug? It correctly displays it as a CDB. Lost in Atlanta…
you built your own vm? if so, did you create a 12101 or 12102 db? and you made sure to create it with the multitenant architecture? and if yes to that, are you connected to the Container and not a Pluggable?
Dean Geary
No, it is the pre built from oracle. I was doing the sqldeveloper lab that came with the pre built. I just wanted to get up to speed with the features for 12C
I’m using Sql Developer Version 4.1.1.19 and java version was jdk-8u60-linux-x64.rpm and using in linux, am able to see the list of objects where in am unable to view the procedure scripts. It shows only “create or replace” text alone in the window. please reply ASAP..
I’ve seen this before, but it always went away on an app restart
is this for your stored procs or a foreign schema?
for the best support, open a SR with MOS
virendra singh
actually I have create pluggable database from dfb file from another windows system to linux system and not create a new user for that . and also on that system sql developer was response properly with stored procedure on old database but on my linux system after createting pdb from dfb file. when i goes for editing stored procedure It shows only “create or replace” text alone in the window. please reply ASAP..
stop with the ASAP stuff, this is a blog, not a hotline
this is the query we run to populate that window
WITH src AS (SELECT ROWNUM,LINE,TEXT,origin_con_id FROM SYS.Dba_SOURCE
WHERE TYPE = :TYPE AND OWNER = :OWNER AND NAME = :NAME)
SELECT text FROM src, (SELECT max(origin_con_id) max_orig FROM src)
WHERE origin_con_id = max_orig
ORDER BY LINE
you can see what’s going on under the covers when you open View > Log > Statements
and like i said, are you logged in as the code owner or as someone else?
virendra singh
am sending statement log where owner is relyc that is correct
and m connected with relyc . I think that was not a problem of permission .. please reply..
120 MultiPlay 88 SELECT data_type, argument_name name
FROM all_arguments a, all_objects o
WHERE a.object_id=o.object_id
AND o.object_name=? and o.owner=? and a.package_name is NULL
order by position 1=”PROC_ERRORLOGS”, 2=”RELYC”
119 MultiPlay 1 SELECT /*OracleDictionaryQueries.ALL_ORACLE_SINGLE_OBJECT_QUERY*/
O.OBJECT_NAME, O.OBJECT_TYPE, O.OBJECT_ID, O.LAST_DDL_TIME
FROM SYS.DBA_OBJECTS O
WHERE O.OWNER = ?
AND O.OBJECT_NAME = ?
AND O.OBJECT_TYPE = ?
1=”RELYC”, 2=”PROC_ERRORLOGS”, 3=”PROCEDURE”
118 MultiPlay 26
WITH src AS (SELECT ROWNUM,LINE,TEXT,origin_con_id FROM SYS.Dba_SOURCE
WHERE TYPE = :TYPE AND OWNER = :OWNER AND NAME = :NAME)
SELECT text FROM src, (SELECT max(origin_con_id) max_orig FROM src)
WHERE origin_con_id = max_orig
ORDER BY LINE
“OWNER”=”RELYC”, “TYPE”=”PROCEDURE”, “NAME”=”PROC_ERRORLOGS”
117 MultiPlay 2 SELECT LINE,POSITION,TEXT,ATTRIBUTE FROM USER_ERRORS WHERE TYPE=? AND NAME=? 1=”PROCEDURE”, 2=null
116 MultiPlay 31
WITH src AS (SELECT ROWNUM,LINE,TEXT,origin_con_id FROM SYS.Dba_SOURCE
WHERE TYPE = :TYPE AND OWNER = :OWNER AND NAME = :NAME)
SELECT text FROM src, (SELECT max(origin_con_id) max_orig FROM src)
WHERE origin_con_id = max_orig
ORDER BY LINE
“OWNER”=”RELYC”, “TYPE”=”PROCEDURE”, “NAME”=”PROC_ERRORLOGS”
I’m using Sql Developer Version 3.2.09, am able to see the list of objects where in am unable to view the objects scripts. It shows only “create or replace” text alone in the window.
do you see queries running in the View > Log > Statements panel?
eric
java should be 8, because I downloaded 4.1.1 with jdk 8. oracle version is 11204 enterprise with diagnostic and tuning package. yes, I see queries running in the view > log statement panel. queries like select fixed_sg_size……
I am a contractor working on a large government site. Most of the other developers use a different tool. But in an effort to save money, the government has decided to limit its purchases of this well known but expensive tool. So I am using Sql Developer. I have been unable to get the debugger to work because of a permissions error that the DBA hasn’t solved.
So I have been using lots of DBMS_OUTPUT.PUT_LINE statements to try to trace the execution of the program. The problem is that after the execution of a SQL statement (select, insert etc.), the program loses its way and jumps out of the current procedure and control returns to the calling procedure.
>> So I am using Sql Developer. I have been unable to get the debugger to work because of a permissions error that the DBA hasn’t solved.
Permissions or Networking? I can help with that perhaps…should be the exact same privs required to use the debugger in the ‘well known but expensive tool’
>>The problem is that after the execution of a SQL statement (select, insert etc.), the program loses its way and jumps out of the current procedure and control returns to the calling procedure.
Hi
Some Data Modeler questions:
1: When Syncronizing Data Model With Dictionary I would like to to that without Storage and Schema name.
I have de-selected every option under
Data Modeler –> DDL/Storage and unchecked Storage options under Data Modeler –>DDL. Still I get diff on Storage Clause for PK and FK Contraints
2: I have a model where the default all-Objects are missing (don’t know how I have disabled it). I only see my Subviews, but would like to enable this all-Objects again if possible?
We’re not dropping zeros – Excel is. Make sure your target cells are formatted as Text first. Paste the same data in Notepad to see what’s actually on the clipboard.
141 Comments
In a logical model in Oracle Datamodeler I would like to show both the Name and the Short name of an entity. Is this possible?
On the diagram, or in a report?
In a diagram.
you could add the short name as an entity comment, and then configure the ERD to display comments
The field “comments” does not work. But filling the field “Comments in RDBMS” and checking “View Details –> Comments” did work out fine.
Thanks.
you can probably use some js and the transformation scripts to append or prefix the short name to the comments for all of your entities, auto-magically
1->I would like to know if it is possible to connect Oracle SQL Developer to SQLite DB using the apropriate JDBC ( https://github.com/xerial/sqlite-jdbc )
2->I also would like to know how Oracle SQL Developer adds the connection tabs to Connection Window (New / Select Database Connection) , when i add the mysql JDBC to Oracle SQL Developer, reset it and open the Connection Window it will appear a new tab with the name of MySQL.
How Oracle SQL Developer creates that tab ?
Is the mySQL JDBC that tells to the Oracle SQL Developer the content of that tab or the content of that tab is in the Oracle SQL Developer code?
No, sorry.
It’s sad … that Oracle SQL Developer is not compatible with SQLite because i like the environment and I am used to it and didn’t want to change.
I would like that in the future Oracle SQL Developer would be compatible with SQLite because that way I wouldn’t need to change my frontend to connect to database.
Thank you very much for your response.
Even if we did, many of the features you love wouldn’t be available… They’re coded specifically for an Oracle Database.
You said “They’re coded specifically for an Oracle Database.” but if I am not wrong Oracle SQL Developer works as a frontend to Microsoft SQL Server, MySQL and Sybase and they aren’t Oracle Database 😛
That said I ask to myself: SQLite is so diferent from the other Databases supported that isn’t compatible to the logic of Oracle SQL Developer?
b/c no one (and by ‘no one’, not enough to spend the time building out support for it) is migrating sqllite databases to oracle database – see this post
Thank you very much for your time, attention and to share these useful information that allowed me to understand better the world of Oracle SQL Developer.
Best regards
you’re very welcome, Carlos! – thanks for the feedback
Hello, I just downloaded
Oracle SQLDeveloper Command-Line (SQLcl) version: 4.2.0.16.308.0750. When I start sqlcl it states that “New version: 4.2.0.1 available to download”, however, I cannot find this version to download.
Thanks for you blog, it is most interesting.
Sam
Oracle SQLDeveloper Command-Line (SQLcl) version: 4.2.0.16.308.0750 IS the latest version. We’re trying to get the new version messaging in the app working, sorry for the confusion.
I found a bug with the formatter in 4.2
Version 4.2.0.16.260
Build 16.260.1303
Code before SQL Developer formatter is run
DECLARE
v_string VARCHAR2(4000);
v_value varchar2(4000) := ’10’;
BEGIN
v_string := ‘The value ‘
|| ””
|| v_value
|| ””
|| ‘ is in the variable v_value.’;
dbms_output.put_line(v_string);
END;
Run it and the output is:
The value ’10’ is in the variable v_value.
Code after SQL Developer formatter is run, notice that the formatter has removed a single quote from two lines of code
DECLARE
v_string VARCHAR2(4000);
v_value VARCHAR2(4000) := ’10’;
BEGIN
v_string := ‘The value ‘
|| ”’
|| v_value
|| ”’
|| ‘ is in the variable v_value.’;
dbms_output.put_line(v_string);
END;
Run it and the output is:
The value ‘
|| v_value
|| ‘ is in the variable v_value.
This changes the meaning of the concatenation and introduces an insidious bug that might be hard to track down.
latest dev build, appears to be good
post format
SET SERVEROUTPUT ON DECLARE v_string VARCHAR2(4000); v_value VARCHAR2(4000) := '10'; BEGIN v_string := 'The value ' || '''' || v_value || '''' || ' is in the variable v_value.'; dbms_output.put_line(v_string); END; executes... The value '10' is in the variable v_value. PL/SQL procedure successfully completed.Hi Jeff,
We have a user running Oracle SQL Developer 1.5.5 with a 64 bit version of Excel 2013. He says nothing happens when exporting his query results to Excel.
Is it possible that his ancient version is not compatible with the 64 bit version of Excel? Do you think a simple upgrade will fix?
Thanks in advance.
Definitely upgrade to v4.1.3
Hi Jeff
How do I completely remove anything to do with SQL Developer so that I can re-install? How does the n-jre version find the jdk directory? Should I not be getting a dialog box asking where the jdk folder is? And how does it find all the connections?
Regards
And it is Windows 10
I install jdk_8u-102-windows-x64.exe
installs in Program Files\Java\\jdk1.8.0_102
I point to that for the no-jre version 4.1.3.20-78
Error => Unable to create JVM at
C:\Program Files\Java\jdk1.8.0_102\jre\bin\server\jvm.dll
you need to nuke the AppData folders for SQL Developer and sqldeveloper under the roaming profiles directories
Hi Jeff,
would you like help me with look and feel of the Oracle Developer ?
My problem is very very simple.
On the right up corner is a comboBox with known connections.
Unfortunately, in case of high number of connections it is become unusable
because of limitation of displayed records (currently it is 8). Yes, it has a scroll bar, but in a huge list of connections it is not providing an easy survey.
Do you happen to know, if there exists any way, how to resize this combo ?
I would like to see more times records in it then it is right now.
Thank you for answer and tip
Regards
Antonin Pokorny
hello,
have followed your guidance and can spool out results to files etc but what im now trying to do is to spool some results which come as a result of several subqueries..
example.
with sub1..,
with sub2..,
with sub3..
select blah from sub1, blah from sub2, blah from sub3..etc
how do i spool the ‘select’ statement into a csv please?
do i put the spool command before the ‘select’ or before the 1st ‘with’?
I’ve tried both but i get a multiline java/ oracle error.
thanks for any help..
Andy
give me an actual example, and i’ll give it a whirl
Random extensibility thought (and extreme laziness) …
Could the SD Help Center be extended to include other documentation like SQL Reference, etc? I love Mr. Google, but highlighting regexp_like and hitting to see the syntax or usage of the function would be cool and make SD even more accessible to learners.
As an extension, is it possible to roll-my-own content that would be available through SD Help Center?
That’s supposed to be already there a la
But I can’t get it to bring up the docs link for REGEXP_() .. gonna file a bug.
You can also ask the sqldev help search to go across OTN and the Database Docs too
checked with Dev, it’s a bug 🙂
Thanks Jeff! Enabling this wasn’t clear to me, so I’m sharing…took me a bit of digging to find the option. For the record, it’s here: Open Help Center > click on search magnifying glass (little menu icon next to it!) There are options for searching.
sheepishly greatful!
Hi Jeff, at Timekiller’s suggestion,
I raised this question on stackoverflow about a week back.
It’s to do with substitution variable behaviour in SQL developer (V4.1.1.19.59 on windows 7 enterprise service pack 1).
Basics are: I want to have 1 parameter which contains a year i.e. YYYY.
Then I want to dynamically use this parameter to make a 2nd parameter YYYY2 which is YYYY – 1.
Now I can do this and make the equation explicit using the select from dual method or the new_value style method but when I actually call it to name a table in a select * from i get the sql developer popup asking me to enter a value even though I’ve already set the value.
One kind responder on stackoverflow has suggested it’s a bug as in his version the popup does not trigger and all is well.
So before i raise it as a bug I was wondering if you’d observed this behaviour before and know about it and how to overcome it?
http://stackoverflow.com/questions/37427468/sql-developer-substitution-variable-dynamic-creation-based-on-equation/37440575?noredirect=1#comment62569295_37440575
Regards,
Clancy.
Haven’t observed that personally..and I would need a code sample to have an opinion. But, if it works in sqlplus and not in SQLDev then it’s a bug. Probably.
Here’s the code sample…
define YYYY = 2014;
column dual_x new_value YYYY2;
select &&YYYY – 1 dual_x from dual;
select * from cb_nsl_&&YYYY2 ;
This last line triggers the popup.
Jeff, I find sqlcl to be slower overall than sqlplus. For example logging in via sqlplus is pretty quick but sqlcl takes few seconds. Is this a known limitation or can something be done to make it faster?
is it connect time to start-up time that you’re seeing? for example, does running sql /nolog take the same amount of time as a connect hr/oracle after sqlcl has already started? part of the issue is that SQLcl is a java app, it’s not a compiled native C application, so it’ll probably never start as fast as SQL*Plus. and the first time it’s started might take more than a few seconds, but if you close it and start it again, will and should start much faster
sql /nolog takes a sec or two and connect takes more than that. Yes, closing and reconnecting is better than first time. Why is it slightly better when relogging in?
Is the overall slowness due to drivers (i assume jdbc drivers are being used) or java being slower than C? Today I was doing desc dbms_stats and it took ages for sqlcl to show the output whereas in sqlplus its super quick. This slowness is making me go back to sqlplus though i like features of sqlcl. Any settings that can be made to make it faster?
Hi, Jeff,
I tried to connect timesten from sql developer (4.1) but get a problem “..no ttjdbcCS” in java.library.path.
I have that library in my local, and know I need to add its location to java.library.path. But I could not find a way to do it on SQL developer. I tried to set via $PATH, $CLASSPATH, $DYLD_LIBRARY_PATH, and even add it directly on sqldeveloper.conf. But nothing work.
Can you tell me how I can configure the value of java.library.path on SQL developer?
Thank you very much for the nice help.
Jian
Hi Jeff
Could you please suggest how to get results using something like this (pipeline + CLOB):
SELECT * FROM table(dbms_stats.diff_table_stats_in_history(‘ME’,’MY_TABLE’,SYSDATE-1,null,0));
To obtain results in SQLPLUS I use
set long 30000
show long
and it works.
But in SQL Developer I get:
“ORA-20010: DBMS_STATS INTERNAL ERROR in report_line : Length of line “SOURCE A : Statistics as of 11-APR-16 08.48.40.000000 AM AMERICA/LOS_ANGELES
” exceeds 80, line length = 82″
Any ideas?
Thank you.
Roman
i want to add radio button to table on my website back end if it checked yes the row get the color light green if it is not checked or checked no the row color is the table default color the checked data stored in database
ok
4 years ago I imported our data models from Designer 2000 and opened it up in Data Modeler. I saved the out, zipped it and saved it in Perforce.
Now I am trying to open it but really don’t know how to do.
Shall I use import or open?
Which file, .dmdz?
SQL Dev 4.1.1.19.59
I am trying to migrate from MS Access into Oracle 12c. Connections only shows SQL Server and Sybase. Where do I get the required drivers for MS Access? In the Third Party stuff I added
file:/C:/Users/Amin/AppData/Roaming/SQL Developer/system4.1.1.19.59/o.sqldeveloper.migration.msaccess.12.2.0.19.59/
Access is no longer supported. You need an older version of sqldev and java 6 or 7.
Java 8 no longer has a odbc bridge which makes access connections impossible.
That is just too bad as I have a lot of MS Access “databases” to migrate. I would prefer they use Oracle but likely will go with MS SQL Server.
I was able to open the MS Access file and then export e3ach table via an ODBC Connection.
Just get an older copy of sqldev.
Hey Jeff, is this something that will be addressed in future releases or is it now a policy?
Jeff, I just installed a vbox appliance for DB development and SQL Developer does not even have a “Containers” tree in the DBA navigator. Is this a bug? It correctly displays it as a CDB. Lost in Atlanta…
you built your own vm? if so, did you create a 12101 or 12102 db? and you made sure to create it with the multitenant architecture? and if yes to that, are you connected to the Container and not a Pluggable?
No, it is the pre built from oracle. I was doing the sqldeveloper lab that came with the pre built. I just wanted to get up to speed with the features for 12C
ok, so show me your connection string to the CDB
Thanks Jeff, here is my connect string:
sys@//localhost:1521/orcl
that’s your problem, the container sid is cdb1
orcl is the pluggable service
P.S., the NLS_RDBMS_VERSION parameter shows 12.1.0.2.0
I’m using Sql Developer Version 4.1.1.19 and java version was jdk-8u60-linux-x64.rpm and using in linux, am able to see the list of objects where in am unable to view the procedure scripts. It shows only “create or replace” text alone in the window. please reply ASAP..
I’ve seen this before, but it always went away on an app restart
is this for your stored procs or a foreign schema?
for the best support, open a SR with MOS
actually I have create pluggable database from dfb file from another windows system to linux system and not create a new user for that . and also on that system sql developer was response properly with stored procedure on old database but on my linux system after createting pdb from dfb file. when i goes for editing stored procedure It shows only “create or replace” text alone in the window. please reply ASAP..
stop with the ASAP stuff, this is a blog, not a hotline
this is the query we run to populate that window
WITH src AS (SELECT ROWNUM,LINE,TEXT,origin_con_id FROM SYS.Dba_SOURCE
WHERE TYPE = :TYPE AND OWNER = :OWNER AND NAME = :NAME)
SELECT text FROM src, (SELECT max(origin_con_id) max_orig FROM src)
WHERE origin_con_id = max_orig
ORDER BY LINE
you can see what’s going on under the covers when you open View > Log > Statements
and like i said, are you logged in as the code owner or as someone else?
am sending statement log where owner is relyc that is correct
and m connected with relyc . I think that was not a problem of permission .. please reply..
120 MultiPlay 88 SELECT data_type, argument_name name
FROM all_arguments a, all_objects o
WHERE a.object_id=o.object_id
AND o.object_name=? and o.owner=? and a.package_name is NULL
order by position 1=”PROC_ERRORLOGS”, 2=”RELYC”
119 MultiPlay 1 SELECT /*OracleDictionaryQueries.ALL_ORACLE_SINGLE_OBJECT_QUERY*/
O.OBJECT_NAME, O.OBJECT_TYPE, O.OBJECT_ID, O.LAST_DDL_TIME
FROM SYS.DBA_OBJECTS O
WHERE O.OWNER = ?
AND O.OBJECT_NAME = ?
AND O.OBJECT_TYPE = ?
1=”RELYC”, 2=”PROC_ERRORLOGS”, 3=”PROCEDURE”
118 MultiPlay 26
WITH src AS (SELECT ROWNUM,LINE,TEXT,origin_con_id FROM SYS.Dba_SOURCE
WHERE TYPE = :TYPE AND OWNER = :OWNER AND NAME = :NAME)
SELECT text FROM src, (SELECT max(origin_con_id) max_orig FROM src)
WHERE origin_con_id = max_orig
ORDER BY LINE
“OWNER”=”RELYC”, “TYPE”=”PROCEDURE”, “NAME”=”PROC_ERRORLOGS”
117 MultiPlay 2 SELECT LINE,POSITION,TEXT,ATTRIBUTE FROM USER_ERRORS WHERE TYPE=? AND NAME=? 1=”PROCEDURE”, 2=null
116 MultiPlay 31
WITH src AS (SELECT ROWNUM,LINE,TEXT,origin_con_id FROM SYS.Dba_SOURCE
WHERE TYPE = :TYPE AND OWNER = :OWNER AND NAME = :NAME)
SELECT text FROM src, (SELECT max(origin_con_id) max_orig FROM src)
WHERE origin_con_id = max_orig
ORDER BY LINE
“OWNER”=”RELYC”, “TYPE”=”PROCEDURE”, “NAME”=”PROC_ERRORLOGS”
Hi,
I’m using Sql Developer Version 3.2.09, am able to see the list of objects where in am unable to view the objects scripts. It shows only “create or replace” text alone in the window.
Could you pleae help in fixing this issue?
First thing to try, upgrade.
Since your version, we have released:
I am excited about this sql developer 4.1.1 db instance viewer, but when I tried to open it, it remains blank. I connected with sysdba privilege.
can you look into this? and how can I log a bug ?
what version of Oracle?
what version of Java?
do you see queries running in the View > Log > Statements panel?
java should be 8, because I downloaded 4.1.1 with jdk 8. oracle version is 11204 enterprise with diagnostic and tuning package. yes, I see queries running in the view > log statement panel. queries like select fixed_sg_size……
and if you run them?
Also, check this:
Help > About
Properties tab
Type in ‘fx’ in the search panel, do javafx.runtime.version and javafx.version show up?
Also, are we going to have this conversation here or on the forums – pick one 🙂
When I add comments as First Line(Top most) in my Stored Procs, they are being removed on reopening, cant we have comments as the First line?
they have to go after the CREATE or REPLACE…if you go look in ALL_SOURCE, you’ll see we’re not removing anything
How does one get the Current Statement on a session when if run “so fast” that you don’t have time to do copy/paste to notepad ??
I am a contractor working on a large government site. Most of the other developers use a different tool. But in an effort to save money, the government has decided to limit its purchases of this well known but expensive tool. So I am using Sql Developer. I have been unable to get the debugger to work because of a permissions error that the DBA hasn’t solved.
So I have been using lots of DBMS_OUTPUT.PUT_LINE statements to try to trace the execution of the program. The problem is that after the execution of a SQL statement (select, insert etc.), the program loses its way and jumps out of the current procedure and control returns to the calling procedure.
>> So I am using Sql Developer. I have been unable to get the debugger to work because of a permissions error that the DBA hasn’t solved.
Permissions or Networking? I can help with that perhaps…should be the exact same privs required to use the debugger in the ‘well known but expensive tool’
>>The problem is that after the execution of a SQL statement (select, insert etc.), the program loses its way and jumps out of the current procedure and control returns to the calling procedure.
I’m confused – are you in the debugger or not?
Hi
Some Data Modeler questions:
1: When Syncronizing Data Model With Dictionary I would like to to that without Storage and Schema name.
I have de-selected every option under
Data Modeler –> DDL/Storage and unchecked Storage options under Data Modeler –>DDL. Still I get diff on Storage Clause for PK and FK Contraints
2: I have a model where the default all-Objects are missing (don’t know how I have disabled it). I only see my Subviews, but would like to enable this all-Objects again if possible?
how do I get SQL developer to stop dropping our zero’s when pasting into Excel?
We’re not dropping zeros – Excel is. Make sure your target cells are formatted as Text first. Paste the same data in Notepad to see what’s actually on the clipboard.