Nearly 7,000,000 Oracle professionals use SQL Developer on a regular basis. Have a question about Oracle SQL Developer? Searched this blog and couldn’t find the answer? Ask away!
If your question is about Oracle Database, SQL, PL/SQL, etc – go Ask Tom!
Feel free to ask anything you want, but I’ll feel free to send you to Support or our Forums if it goes sideways.
Note: This page has turned out way more successful(?) than I would have ever imagined. Please keep these things in mind when asking questions.
- I am NOT support. Don’t expect me to log bugs for you, or give you official timelines on bug fixes, enhancements, or product releases.
- I AM NOT SUPPORT. Don’t open an SR with My Oracle Support AND leave a question here. Pick one and go with it, and when in doubt, go to My Oracle Support.
- I try to answer questions as quickly as possible. If you don’t get an answer, ask me for an update. I may have just forgotten or overlooked your request.
Go!
7,739 Comments
Hi Jeff,
Does SQL Developer have anything similar to QUICK SQL ?
The Data Modeler for folks that want to get serious about designing a schema.
We have support for Quick SQL on our to-do list.
Hi Jeff, is there a way to define the author or the Id in the changeset when I use the sqlcl command lb genobject table?
Thanx and best regards
René
Not today, no…but we’re adding flags to allow you to define things like this.
Hi Jeff,
I created tha next table.
CREATE TABLE “JSON_BITACORA_EXAMPLE”
( “ID” NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
“NOMBRE” VARCHAR2(100),
“APELLIDO” VARCHAR2(100),
“TELEFONO” VARCHAR2(20),
“BITACORA” CLOB DEFAULT ‘[]’,
CONSTRAINT “PK_JSON_BITACORA_EXAMPLE” PRIMARY KEY (“ID”)
USING INDEX ENABLE,
CONSTRAINT “CHK_JSON_BIT” CHECK (bitacora is json) ENABLE
)
/
now when I use
POST: [HOST:PORT]/ords/siniestros/json_bitacora_example/
Request:{“id”:null,”nombre”:”DANIEL”,”apellido”:”ALEJANDRO”,”telefono”:”9512164719″,”bitacora”:”{\”data\”:[{\”cdbitacora\”:\”\”,\”cdusuario\”:\”ICEEMPL13\”,\”dscambio\”:\”{\\\”id\\\”:\\\”\\\”,\\\”nombre\\\”:\\\”DANIEL\\\”,\\\”apellido\\\”:\\\”ALEJANDRO\\\”,\\\”telefono\\\”:\\\”9512164719\\\”,\\\”bitacora\\\”:\\\”\\\”}\”,\”fefecha\”:\”2020-04-25T01:40:22.307Z\”,\”dstipo\”:\”1\”,\”dsaccion\”:\”CREATE\”,\”dscomentario\”:\”COMENTARIO ORIGINAL\”}]}”}
The ords engine is returning :
The request was rejected because it violates a data integrity constraint: ORA-01400: cannot insert NULL into (“SINIESTROS”.”JSON_BITACORA_EXAMPLE”.”ID”) ORA-06512: at line 4
Can you tell me what i´m doing wrong.
Regards,
Jhonatan Pacheco
Change your ID field to be BY DEFAULT ON NULL
I have installed ORDS 19.4 and Tomcat 9.0.31. I use PL/SQL Gateway. I have tried to config attributs in connector Tomcat, config server.xml to config enabling large file and post data. But they don’t work, I obtain allways the same error when try with large file uploads or large data in form post
500 Internal Server Error
2020-04-22T14:19:57.081Z | 7pvIao-S13Az9uIPFD0omw
Trying upoad large files:
InternalServerException [statusCode=500, reasons=[An unexpected error with the following message occurred: Stream ended unexpectedly]]
at oracle.dbtools.http.errors.ServletResponseExceptionMapper.mapError(ServletResponseExceptionMapper.java:89)
…..
Caused by: org.apache.commons.fileupload.MultipartStream$MalformedStreamException: Stream ended unexpectedly
at org.apache.commons.fileupload.MultipartStream$ItemInputStream.makeAvailable(MultipartStream.java:1033)
at org.apache.commons.fileupload.MultipartStream$ItemInputStream.close(MultipartStream.java:971)
at org.apache.commons.fileupload.MultipartStream$ItemInputStream.close(MultipartStream.java:950)
at org.apache.commons.fileupload.FileUploadBase$FileItemIteratorImpl$FileItemStreamImpl.close(FileUploadBase.java:873)
at org.apache.commons.fileupload.FileUploadBase$FileItemIteratorImpl.findNextItem(FileUploadBase.java:1031)
at org.apache.commons.fileupload.FileUploadBase$FileItemIteratorImpl.hasNext(FileUploadBase.java:1121)
AND with large POST DATA arrays:
InternalServerException [statusCode=500, reasons=[]]
at oracle.dbtools.apex.ModApexContext.handleError(ModApexContext.java:288)
Caused by: java.sql.SQLException: ORA-06550: línea 2, columna 2:
PLS-00306: número o tipos de argumentos erróneos al llamar a ‘ACTUALITZA’
ORA-06550: línea 2, columna 2:
PL/SQL: Statement ignored
My tomcat configuration server.xml is:
PLEASE HELP!!!!! I have done many test, anything works! I NEED IT WORKS!!! :_(
I suposed is somenthing about timeout, with POST LARGE DATA it depends on the connection speed sometimes it works, depending also which navigator I try.
I’m not a tomcat person, sorry. Maybe try a different version of tomcat, or what happens with ORDS when it runs standalone?
I upgrade the version of Tomcat to 9.0.34 and the problem was solved!
Thank you for your time!!!!
Hi Jeff,
I’m trying to install ORDS via SQL Developer for Demo purposes (ORDS version 19.4.0.352.1226 and SQL Developer version 19.2.1.247). The installation seemed to complete successfully but I get a 404 error whenever I try to test it:
“The request could not be mapped to any database. Check the request URL is correct, and that URL to database mappings have been correctly configured”
I ran java -jar ords,war validate and got the following error:
Connecting to database user: ORDS_PUBLIC_USER url: jdbc:oracle:thin:@//act-xsts-
12-dev.xsts-be.local:1523/ERDV64
2020-04-22T16:47:45.204Z WARNING ORA-06550: line 7, column 32:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 7, column 5:
PL/SQL: SQL Statement ignored
java.sql.SQLException: ORA-06550: line 7, column 32:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 7, column 5:
PL/SQL: SQL Statement ignored
Having checked the database there are no objects belonging to ORDS_PUBLIC_USER.
I do not have APEX installed.
I should add, I attempted a “Repair” via SQL Developer specifiying database user “ORDS_PUBLIC_USER” but got:
“Cannot repair the ORDS schema. The ORDS schema must exist and the schema version must be the same as the product version.
Product version is 19.4.0.r3521226”
Any assistance would be most appreciated.
Thank you,
Sophie.
I’ve realised now that I need to run the ords.war validate with SYS credentials, so am waiting for the powers that be to provide those and will try again. Thanks.
How were you testing it?
“I’m trying to install ORDS via SQL Developer for Demo purposes (ORDS version 19.4.0.352.1226 and SQL Developer version 19.2.1.247). The installation seemed to complete successfully but I get a 404 error whenever I try to test it:”
What URL were you hitting?
If you didn’t rest enable a schema AND publish a rest service, you would get a 404
Hi – thanks for the reply.
I am testing with URL: http://localhost:8089/ords/starcoffee/dept
I am running ORDS in standalone on port 8089 (because there was a clash on port 8080) and have REST enabled my schema XXORDS_TESTSCHEMA aliased “starcoffee” in which there is a module “ORG” defined as follows:
ORDS.ENABLE_SCHEMA(
p_enabled => TRUE,
p_schema => ‘XXORDS_TESTSCHEMA’,
p_url_mapping_type => ‘BASE_PATH’,
p_url_mapping_pattern => ‘starcoffee’,
p_auto_rest_auth => FALSE);
ORDS.DEFINE_MODULE(
p_module_name => ‘org’,
p_base_path => ‘/org/’,
p_items_per_page => 25,
p_status => ‘PUBLISHED’,
p_comments => NULL);
ORDS.DEFINE_TEMPLATE(
p_module_name => ‘org’,
p_pattern => ‘dept’,
p_priority => 0,
p_etag_type => ‘HASH’,
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => ‘org’,
p_pattern => ‘dept’,
p_method => ‘GET’,
p_source_type => ‘json/collection’,
p_items_per_page => 25,
p_mimes_allowed => ”,
p_comments => NULL,
p_source =>
‘SELECT * FROM emp WHERE empno = :empno OR :empno IS NULL’
);
ORDS.DEFINE_TEMPLATE(
p_module_name => ‘org’,
p_pattern => ‘desc’,
p_priority => 0,
p_etag_type => ‘HASH’,
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_TEMPLATE(
p_module_name => ‘org’,
p_pattern => ’emp’,
p_priority => 0,
p_etag_type => ‘HASH’,
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => ‘org’,
p_pattern => ’emp’,
p_method => ‘GET’,
p_source_type => ‘json/collection’,
p_items_per_page => 25,
p_mimes_allowed => ”,
p_comments => NULL,
p_source =>
‘SELECT * FROM emp WHERE empno = :empno OR :empno IS NULL’
);
Thank you,
Sophie.
Correction: http://localhost:8089/ords/starcoffee/org/dept
Since running the ords.war validate I am just getting 404 and not:
“The request could not be mapped to any database. Check the request URL is correct, and that URL to database mappings have been correctly configured”
that url looks right
when you start ords, check the standard out, do you see any error messages when it goes to establish the connection pool?
Hi Jeff,
Nothing obvious no. See output below:
Thanks,
Sophie.
C:\Users\stevenss\SQLDeveloper\sqldeveloper\jdk\jre\bin\java -Duser.language=en -Duser.country=GB -jar “C:\Users\stevenss\Desktop\ORDS Demo\ords-19.4.0.352.1226\ords.war” configdir “C:\Users\stevenss\Desktop\ORDS Demo\ords-19.4.0.352.1226\config”
2020-04-24T09:35:21.224Z INFO Set config.dir to C:\Users\stevenss\Desktop\ORDS Demo\ords-19.4.0.352.1226\config in: C:\Users\stevenss\Desktop\ORDS Demo\ords-19.4.0.352.1226\ords.war
C:\Users\stevenss\SQLDeveloper\sqldeveloper\sqldeveloper\bin>
C:\Users\stevenss\SQLDeveloper\sqldeveloper\jdk\jre\bin\java -Duser.language=en -Duser.country=GB -jar “C:\Users\stevenss\Desktop\ORDS Demo\ords-19.4.0.352.1226\ords.war” standalone –parameterFile “C:\Users\stevenss\Desktop\ORDS Demo\ords-19.4.0.352.1226\params\sqldev_ords_params.properties”
2020-04-24 10:35:28.066:INFO::main: Logging initialized @2811ms to org.eclipse.jetty.util.log.StdErrLog
2020-04-24 10:35:28.204:INFO:oeju.TypeUtil:main: JVM Runtime does not support Modules
2020-04-24T09:35:28.331Z INFO HTTP and HTTP/2 cleartext listening on host: localhost port: 8089
2020-04-24T09:35:28.360Z INFO Disabling document root because the specified folder does not exist: C:\Users\stevenss\Desktop\ORDS Demo\ords-19.4.0.352.1226\config\ords\standalone\doc_root
2020-04-24 10:35:28.895:INFO:oejs.Server:main: jetty-9.4.24.v20191120; built: 2019-11-22T11:09:44.612Z; git: 8b8c80157294e38f81ef8ea2358a0c49bf5db918; jvm 1.8.0_212-b10
2020-04-24 10:35:28.980:INFO:oejs.session:main: DefaultSessionIdManager workerName=node0
2020-04-24 10:35:28.980:INFO:oejs.session:main: No SessionScavenger set, using defaults
2020-04-24 10:35:28.981:INFO:oejs.session:main: node0 Scavenging every 600000ms
2020-04-24T09:35:32.274Z INFO Configuration properties for: |apex|pu|
db.connectionType=basic
db.hostname=act-xsts-12-dev.xsts-be.local
db.port=1523
db.servicename=ERDV64
db.password=******
db.username=ORDS_PUBLIC_USER
resource.templates.enabled=true
2020-04-24T09:35:32.277Z WARNING *** jdbc.MaxLimit in configuration |apex|pu| is using a value of 10, this setting may not be sized adequately for a production environment ***
2020-04-24T09:35:32.277Z WARNING *** jdbc.InitialLimit in configuration |apex|pu| is using a value of 3, this setting may not be sized adequately for a production environment ***
2020-04-24T09:35:34.403Z INFO Oracle REST Data Services initialized
Oracle REST Data Services version : 19.4.0.r3521226
Oracle REST Data Services server info: jetty/9.4.24.v20191120
2020-04-24 10:35:35.248:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@47db50c5{/ords,null,AVAILABLE}
2020-04-24 10:35:35.346:INFO:oejs.AbstractConnector:main: Started ServerConnector@6ee4d9ab{HTTP/1.1,[http/1.1, h2c]}{0.0.0.0:8089}
2020-04-24 10:35:35.348:INFO:oejs.Server:main: Started @10094ms
your pool is being created, you should be able to access it
on your rest enabled schema url, try
GET /ords/schema/metadata-catalog/
If you get a 404 on that, it’s time to open a SR with My Oracle Support
ok, sorry – was too hasty with my reply then. I’d attempted uninstalling and reinstalling and hadn’t realised I needed to set up my REST objects again. I’ve recreated the objects via the PL/SQL script I pasted below and it all works perfectly.
Thank you for your assistance!
Sophie.
Hello,
is there a way to (re)move the “structure” tab of a scheduler job from the first position or to open a tab of choice instead ?
That tab information is presented in a cool way but, in my opinion, it is not so useful and wastes time because is very slow to open when working with remote and busy databases. For me it is better to open the “run log” tab by default.
Thank you
I’ll file an ER for you.
Hi
Ive downloaded sql developer 19.4, and the Data Modeller it contains doesn’t show the new pink arrow for creating Implied Foreign Keys. Downloading Data Modeller 19.4 alone does. Am I missing a setting somewhere?? In both cases,Ive upgraded from 19.2 and imported preferences.
Greg
That shouldn’t be happening, it’s supposed to be the same code. Could be a bug, but it’s intended to be the same features.
Ive got
sql developer 19.4.0.354 build 354.1759
data modeller 19.4.350 build 350.1424
Should I raise a bug report?
Hi Jeff, we are getting a null pointer exception after setting up the ords on tomcat and we’re not sure why we are getting this error. The localhost:\ords also displays null as output along with the logo. Thank you for your help.
SEVERE: null
java.lang.NullPointerException
at oracle.dbtools.url.mapping.db.DatabaseURLMappingBase.injectPLSQLGatewayConnection(DatabaseURLMappingBase.java:936)
at oracle.dbtools.url.mapping.db.DatabaseURLMappingBase.addServices(DatabaseURLMappingBase.java:317)
at oracle.dbtools.url.mapping.URLMappingBase.doFilter(URLMappingBase.java:79)
You’re trying to run APEX.. something else?
Does it work as a standalone app?
Hi Jeff, we are trying to connect expose rest services from DBCS through standalone mode on Tomcat server. We have an external domain where we hosted the tomcat services and followed the installation guidelines on Oracle website.
https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-standalone-mode
Not sure if you take requests. A couple of things in the UI that might be nice:
1. A checkbox on the “Enter Bind Variables” dialog that lets you null out *all* variables with one click. On my custom reports I tend to have a lot of filters.
2. In the filters such as on the Connections tree view, could you add a REGEXP_LIKE option that would allow us to enter a regular expression.
Small things that don’t hinder my use of the tool.
In my organization many people using SQL developer and when someone randomly do changes in procedure we are not able to track same. Please tell me any way so that I can track who did last changes in procedure.
Start using file based source control. Shut off access to devs in DEV and force updates through a change control process.
Or add some triggers and logging tables.
Hi, I have just installed “SQL Developer Web” and I note that the built-in drop-downs of “schemas” (Oracle user accounts), the list is limited to 500 entries. – Is there a way I can increase that number? (Say 3000).
I thought perhaps that adding “3000” to the “defaults.xml” file of the ORDS installation would do the trick. (following a restart of ORDS).
Thanks
Alex
I don’t think that’s configurable – do you actually have 500 or more schemas with app objects, or are most of these just user logins?
Hi,
The database has just under 3,000 Oracle accounts. Most of those map to real users. (In place for legacy reason).
Only approx 100 accounts (including system accounts “own objects”.
Perhaps there is a way to limit the drop-down contents to only those Oracle accounts that own objects?
I need to talk to dev and probably file a bug. Stay tuned.
This is a known bug that has been fixed for our next ORDS update.
I m facing error : enablement disallowed , when enabling ords on schema
Could you please suggest way forward
Is there a way to generate DB Doc for another schema beside the connected schema?
Not this way, but if you used SQL Developer Data Modeler, you can import any schema you want and generate HTML data dictionary reports as desired.
How can I write a stored procedure to run a select statement and save the result in CSV format? I must be able to specify the following in the procedure:
– destination path where it should save the CSV result.
– Format: which should be CSV.
-Left and right enclosure: which will be double qouts “.
– Encoding: UTF-8.
See my comment to your exact same question on StackOverflow.
I want to create the create a model which has 50 odd tables and 1000+ attributes. I want to check if there is a way we can import an excel that can help us save time in creating tables and attributes manually. I am only use oracle data modeler tool.
Hi Jeff,
We package up SQL Developer and a JDK separately for mass rollout – with the former pointing to the latter. There are plans afoot to update the JDK package quarterly from now on though which means users will get prompted for the new JDK location each time this happens (which we’d like to avoid). Is there a way to make SQL Developer aware via a Windows environment variable that could be reset upon JDK update where the new JDK folder location is for it? Something like:
JDK_LOC=C:\Program Files\Java\jdk1.8.0_162
We could code up upon an updated JDK package rollout to touch any user’s product.conf file with the new location, but was hoping there might be another way to make the deployed SQL Developer package abstracted from this and not require any direct conf file editing.
A Unix alias would be perfect but I don’t think Windows has an equivalent that we can use here.
Thanks in advance!
Mikel
Are you rolling out the JDK specifically FOR SQLDev?
If so, I suggest you embed the JDK inside the SQLDev package. Then have the java deployments update the sqldev folder, and the base path would never change…
Or when you roll out the new java home, have it be a generic directory name so the path doesn’t need updated in sqldev.
Unfortunately no – the packaged JDK is intended for a few different applications, and machines might have a few installed at any point in time, hence our admin people being keen to retain a version specific folder naming approach.
I liked your idea of a generic directory and had suggested the same, but couldn’t get it agreed to, so I think we’ll just have to educate the userbase to expect to repoint SQL Dev once a quarter to the new JDK once an updated package has landed.
Thanks for your reply!
Hi Jeff,
I am using sql developer 19.4 and the format code of function and procedure is
BEGIN
apps.fnd_global.apps_initialize (
user_id => 1804,
resp_id => 20707,
resp_appl_id => 201,
x_resp_appl_id => 44
);
END;
but i want it to become
BEGIN
apps.fnd_global.apps_initialize (
user_id => 1804,
resp_id => 20707,
resp_appl_id => 201,
x_resp_appl_id => 44
);
END;
Regards,
El-Sayed
that appears to be a bug in 19.4 that’s fixed for version 20.Next
Hi Jeff,
I have a small issue with the SQL Developer. When I start a long running query and want to open a new window with a new connection, it will wait until the long running sql has finished before opening the new window and the new connection.
Is this expected behaviour?
I don’t think so, no.
I was just able to
on Connection 1, run this:
As that was running in the UI, open a connection on DB 2.
This on version 19.4
Hi Jeff,
thanks a lot for the fast response. Finally I could find out what causes the issue for me. For some DBs I need to use the Thick driver. If I deselect it it works as expected.
Tested with 12.2.0.1 and 19.3.0.0 Client and SQLDeveloper 19.4
Hi Jeff, When I try to configure ORDS, I get a failure to bind error. It ran successfully for the first time and ORDS was configured but when I try to restart it, it gives me a failure to bind error in the standalone mode. The database is DBCS.
It’s saying the port is already being used by another application. You need to free up that port, or change it to a different port.
Hi Jeff, are you talking about the 1521 port?
No the ORDS Standalone http port (8080)
We are on below database\app server versions.
SELECT * FROM v$version;
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
PL/SQL Release 12.1.0.2.0 – Production
CORE 12.1.0.2.0 Production
TNS for Solaris: Version 12.1.0.2.0 – Production
NLSRTL Version 12.1.0.2.0 – Production
WebLogic Server Version: 12.2.1.3.0
I want to enable ORDS on the database, can you please share some pointers.
1. Do we need to have APEX (mandatory)
2. once ORDS is enabled I want to use the rest api in my OracleJET app using offline-persistent-toolkit. (Will this approach work)
1. no, but it won’t hurt either
2. yes – lot of folks are doing just this
Hi Jeff/team
We have an issue when we call a rest service returning a Query – the query is a pipelined function and within that function we are trying to access the CGI env data.
This works when using a PLSQL type REST, but not via Query.
Further testing shows any local SQL in the REST code calling the CGI functions within the SQL fails.
We are using APEX 19.2 to create the calls.
example tests:
— this works:
SELECT ‘TEST’ as “x_apex_base”
FROM dual
— this fails:
SELECT owa_util.get_cgi_env(‘X_APEX_BASE’) as “x_apex_base”
FROM dual
any ideas appreciated.
many thanks
Dave C
Thanks Jeff,
Once I install ORDS and it is running on sqldeveloper I do not see the REST Services context menu option on HR schema and Enable REST Services option on table context menu is disabled.
Followed all steps still unable to solve this.
$ java -Doracle.net.tns_admin=D:\app\dakhanws\product\12.2.0\dbhome_1\network\admin -jar ords.war validate
Enter the name of the database server [localhost]:localhost
Enter the database listen port [1522]:
Enter the database SID [orcl]:
Requires to login with administrator privileges to verify Oracle REST Data Services schema.
Enter the administrator username:sys
Enter the database password for SYS AS SYSDBA:
Confirm password:
Retrieving information..
Your database connection is to a CDB. ORDS will be validated in the CDB and PDBs.
PDB PDB$SEED – validate ORDS 19.2.0.r1991647 (mode is READ ONLY, open for READ/WRITE)
Enter 1 if you want to validate ORDS or 2 to exit [1]:1
Validating Oracle REST Data Services schema version 19.2.0.r1991647 in PDB$SEED
… Log file written to C:\Users\dakhanws.CALEGIS\ords_cdb_validate_core_PDB_SEED_2020-04-07_070749_00517.log
Completed validating Oracle REST Data Services version 19.2.0.r1991647. Elapsed time: 00:00:03.966
Completed CDB validation Oracle REST Data Services version 19.2.0.r1991647.
Total Elapsed time: 00:00:04.578
you’ve already asked this question on the forums, so i’m assuming you can get what you need from over there
but
you have to login AS hr and on the HR connection, you enable the schema. and, you do this AFTER you configure ords for the database.
you can always run ords_admin.enable to enable the HR schema from an admin account if you want, a la
Hi Jeff, I get in SQLDev 19:* strength characters in script output.
For example:
select sysdate from dual;
select sysdate+1 from dual;
select sysdate+2 from dual;
SYSDATE
——————-
26.03.2020 10:36:12
潲捥彰物湴૾ഀ匀夀匀䐀䄀吀䔀⬀ ഀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀഀ㈀㜀⸀ ㌀⸀㈀ ㈀ 㨀㌀㘀㨀㈀ഀ૾ਠ景牣敟灲楮琊
SYSDATE+2
——————-
28.03.2020 10:36:12
What is wrong ? In older versions it was correct.
Thank you very much
Does this only happens on the second query?
no. here an example:
CREATE TABLE TAB (COL number(1));
insert into tab (col) values (1);
insert into tab (col) values (2);
insert into tab (col) values (3);
insert into tab (col) values (4);
insert into tab (col) values (5);
commit;
Script output:
Table TAB created.
潲捥彰物湴૾ 爀漀眀 椀渀猀攀爀琀攀搀⸀ਠ景牣敟灲楮琊
1 row inserted.
潲捥彰物湴૾ 爀漀眀 椀渀猀攀爀琀攀搀⸀ਠ景牣敟灲楮琊
1 row inserted.
潲捥彰物湴૾ 爀漀眀 椀渀猀攀爀琀攀搀⸀ਠ景牣敟灲楮琊
Commit complete.
潲捥彰物湴�
Best regards,
Ewgeni
Hi Jeff, is it only my problem?
Thank you
As far as I can tell, yes. Something weird is happening in your system – the first thing I would check is for triggers defined in your db with that text in them.
but in SQLDev v.4.2 script output is ok…
I need a reproducible test case. Check for maybe a login.sql in your newer version. In a worksheet, do a
show login
and then if a script comes back, look at it for things that might cause this
Thanks for you reply.
But don’t know why ORDS related packages are not showing up.
Dont know where is it getting created.
Log does show installation successful:-
INFO: Configuration properties for: |apex|pu|
database.api.enabled=true
db.hostname=localhost
db.password=******
db.port=1522
db.sid=orcl
db.username=ORDS_PUBLIC_USER
feature.sdw=true
resource.templates.enabled=true
restEnabledSql.active=true
If it’s a multitenant/cdb install, they’re in an ORDS_METADATA schema in each of your PDBs.
That is true IF those PDBs were open when you did the install.
You should also have a public synonym called ‘ORDS’ granted to PUBLIC that points to the ORDS package in that schema.
Hi Jeff,
When I run a query – with errors – in SQL PLUS I get feedback about the line that’s causing the error
SQL> SELECT
2 ‘A’ AS a1,
3 10 AS a2,
4 ‘B’ AS a3
5 FROM
6 dual
7 WHERE
8 1 = TO_NUMBER( ‘A1’ );
1 = TO_NUMBER( ‘A1’ )
*
ERROR at line 8:
ORA-01722: invalid number
Sql developer (19.4) just tells me something is wrong …
ORA-01722: invalid number
01722. 00000 – “invalid number”
*Cause: The specified number was invalid.
*Action: Specify a valid number.
Possible to get the line numbers as in SQLPLUS ?
Kind regards
Peggy
This is what I get in version 19.4
ORA-00923: FROM keyword not found where expected
00923. 00000 – “FROM keyword not found where expected”
*Cause:
*Action:
Error at Line: 5 Column: 1
He Jeff,
For errors on a wrong table name we get a guide error message
eg :
SELECT 1
FROM DUALS
WHERE 1 = 1;
ORA-00942: table or view does not exist
00942. 00000 – “table or view does not exist”
*Cause:
*Action:
Error at Line: 2 Column: 6
For others like ORA-01722: invalid number
SELECT 1
FROM DUAL
WHERE 1 = ‘A’;
ORA-01722: invalid number
01722. 00000 – “invalid number”
*Cause: The specified number was invalid.
*Action: Specify a valid number.
When the SQL is only a few lines the error is easy to find but on bigger SQL this gives us plenty of search time
Kind regards
Peggy
We show whatever the jdbc driver and the database gives us… Maybe try a thick connection and see if that helps.
Looks like my ORDS installation happening is multitenant/cdb install.
How can I control it to get installed into normal orcl schema.
Retrieving information..
Your database connection is to a CDB. ORDS will be uninstalled in the CDB and PDBs.
Root CDB$ROOT – uninstall ORDS
PDB PDB$SEED – uninstall ORDS 19.2.0.r1991647 (mode is READ ONLY, open for READ/WRITE)
Enter 1 if you want to uninstall ORDS or 2 to exit [1]:
Do you have a PDB? Is it open? The easiest way to get going is to run the installer against your PDB service directory, and skip the CDB.
Hi Jeff,
We have a question about the NLS settings in sql developer.
In our oracle client the setting for NLS_LANG is AMERICAN_AMERICA, but in sql developer it always stays at DUTCH.
Is there a possibilty that sql developer automatically takes the NLS setting of the oracle client?
Thank in advance
With kind regards
Steven Hertogs
No, we get that from the OS. That’s how the jdbc driver works.
Hi Jeff,
This is question is related to Basic authentication.
Thus just a uername and password in the header os the response request.
I have cretaed a HTTPS POST webservice.
Is it possible in Oracle ORDS to add a username and password in the header of response?
This is without using OAuth2 Client Credentials.
Regards,
Anibal
Why would you want the password exposed in your response header?
Adding the user is trivial, the authenticated user is available as a :user bind in your rest handler code block.