Hot answers tagged oracle-sql-developer
12
How do you log onto courgette? Would that username identify you?
You can check that by running
select sys_context('userenv', 'os_user')
from dual;
The USERENV namespace can retrieve a lot of different information about the user and their environment. Find out more.
6
There are many ways to export data from Oracle and automate the functionality. Be sure to understand exactly what the data export is being used for, though. If it is for interop between systems, then export in a format your receiving system can understand. If it is for backup purposes, go for the exp/expdp (data pump) method because a database backup needs ...
5
Unless I am missing something, your query would be something like this:
select created, count(*) CreatedCount
from yourtable
group by created
order by created;
See SQL Fiddle with Demo
Or if you have a time associated with the date, you can use TRUNC:
select trunc(created), count(*) CreatedCount
from yourtable
group by trunc(created)
order by ...
5
The Oracle client (aka driver) knows who you are in your operating system (because that code runs on your computer).
This information is transmitted as part of the login process.
Depending on your application and driver type (OCI/JDBC) it even transmits information like your computer's name.
If you can, run a SELECT * FROM v$session WHERE sid = ...
3
Oracle 11g doesn't support the LIMIT clause, though the impending 12c release is rumored to support it.
Anyway, you can do this using an analytic windowing function:
select * from
(
select salary, row_number() over (order by salary desc) as rn
from emp
)
where rn = 4;
You can also do this using rownum, but I find the above way to look cleaner.
...
3
Asuming this is to transport data to an other system. It that case this will work:
set colsep ";"
set linesize 9999
set trimspool on
set heading off
set pagesize 0
set wrap off
set feedback off
set newpage 0
set arraysize 5000
spool you csv_file.csv
select rows from your tables;
spool off
If you don't want a header line, change to heading off
If this is ...
3
The solution is very simple. The string of the query is just too long.
I'm trying to optimize a stored procedure using dynamic sql with a parameter to decide if the sql is executed or output.
I just copied the output into a fresh sql developer pane and tried to use the tuning advisor.
The problem is that the generated output has a lot of trailing blanks, ...
3
Well, if sqlplus is screwing your dbms_metadata.get_ddl output, why not select the output in a CLOB and write the CLOB to filesystem.
e.g.
DECLARE
data CLOB;
objType varchar2(30) := 'TABLE';
objSchema varchar2(30) := 'SCOTT';
objName varchar2(30) := 'EMP';
fname varchar2(256) := objType || '_' || objSchema || '_' || objName || '.sql';
...
2
Try this:
COTT3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = COTT3)
)
)
If that doesn't work, replace "SERVICE_NAME" with "SID".
Put this in $ORACLE_HOME/network/admin/listener.ora:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
...
2
As far as I know, Import/Export and Data Pump are command-line-only types of tools. But if you really wanted to use that functionality from within SQL Developer (or SQL Plus), Data Pump uses the built-in DBMS_DATAPUMP and DBMS_METADATA packages. You should be able to call those packages directly, so I would start there. There's documentation available in ...
2
In SQL*Plus and SQL Developer you need a / at the end of a PL/SQL block.
variable pStartDateBegin VARCHAR2(10);
variable pEndDateFinish VARCHAR2(10);
begin
select '01-01-2000', '30-11-2011'
into :pStartDateBegin,:pEndDateFinish
from dual;
end;
/
[...]
2
The reason you are having problems with dbms_metadata.get_ddl is that it outputs CLOBs which can be up to 4GB in size. By default, SQL*Plus and Oracle SQL Developer truncate long text so they don't trash the client with large gobs of text.
It's very easy to override this behavior in SQL*Plus with a few SET commands and get clean DDL.
The script you need ...
2
/** **/
DECLARE
lfFilelog UTL_FILE.FILE_TYPE;
lspath varchar2(50) := 'c:\';
lsfile varchar2(50) := 'file';
BEGIN
lfFilelog := UTL_FILE.FOPEN(lspath,lsfile||'.txt','w');
FOR r IN (SELECT FROM table) LOOP
UTL_FILE.PUT_LINE(lfFilelog ,r.row);
END LOOP;
UTL_FILE.FCLOSE_ALL;
EXCEPTION
WHEN UTL_FILE.INVALID_OPERATION THEN
...
2
SQL Server uses autocommit mode by default. This cannot be changed permanently.
There are two ways implicit transactions (non-autocommit) can be turned on:
At the server level such that new sessions use it by default, using sp_configure 'user options' -- this may or may not work depending on how SQL Developer was implemented.
For a session, using SET ...
2
Look at these lines :
CREATE TABLE DEPARTMAN
( DEPARTMAN_ID NUMBER(*, 20) NOT NULL,
DEPARTMAN_AD VARCHAR2(40) NOT NULL,
DEPARTMAN_SOYAD VARCHAR2(40) NOT NULL),
CONSTRAINT DEPARTMAN_PK PRIMARY KEY (DEPARTMAN_ID)
);
You have an extra left parenthesis before the CONSTRAINT. After you remove it, it should look like this:
CREATE TABLE DEPARTMAN
( ...
2
As the documentation says implicitly, you can create synonyms in the current DB only. So the first @PACOMNET in the synonym name is unnecessary (and causing the error). You should instead connect to the PACOMNET database and run
CREATE SYNONYM B2H.BILLING_USER_ACTIVITY
FOR B2H.BILLING_USER_ACTIVITY@PACOMNET.US.ORACLE.COM;
2
This can't be done without some kind of workaround, so here's one for you.
You can recompile the PL/SQL after creation & raise an exception if the recompilation fails. This will cause SQL*Plus to exit on failure.
For example:
test.sql:
create or replace procedure foo
as
begin
this is an error;
end;
/
exec execute immediate 'alter procedure foo ...
2
From what you are describing it seems like you are attempting to display all records from Station_Series along with matching records from Control_Point. If that is what you are attempting, perhaps the following makes sense?
SELECT
CP.ID "Control Point ID"
, CP.Station "Station"
, CP.Station_Series "Station_Series"
, ...
2
Sounds like the network buffer is being filled but not flushed. SQL*Net performance in this area is often managed by setting tcp.nodelay=yes in sqlnet.ora; also look at the send_buf_size and recv_buf_size parameters.
SQL*Dev working and not SQL*Plus are what pointed me to this type of troubleshooting approach. SQL*Developer uses jdbc behind the scenes to ...
1
In the tree view on the left-hand-side, expand the tree for your database connection. Expand "Other Users", then "XYZ", then "Views". Double-click on the view name "ABC_CHAR" - this will open the column list. Click "SQL" above the column list to see the query that created the view.
The other way to achieve this is to use SQL:
select text
from all_views
...
1
if one enters the statement in sqlplus (Oracle10.2, Solaris 5.10) I get the following is displayed:
SQL> CREATE SYNONYM BILLING_USER_ACTIVITY@PACOMNET
2 FOR B2H.BILLING_USER_ACTIVITY@PACOMNET.US.ORACLE.COM;
CREATE SYNONYM BILLING_USER_ACTIVITY@PACOMNET
*
ERROR at line 1:
ORA-00905: missing keyword
After the ...
1
In Oracle, DDL on remote database is not permitted. One likely reason is that a distributed transaction commit can not be initiated at the remote site (you can't COMMIT@remote) and since DDL statements include a commit they are not permitted.
You would get an ORA-02021 with other DDL statements:
DDL operations are not allowed on a remote database
You ...
1
You can't do that in Visio. However, you can reverse engineer a database using Visio. The answer to the question below should help you.
How can I import the contents of an Oracle database into Visio to create an Entity Relationship Diagram?
1
When you have permission to perform an action at the command line but not within a definer's rights stored procedure (and I hope you're using a stored procedure here, not a stored function), the problem is almost certainly that the privilege you need has been granted through a role, not directly to the user that owns the procedure. Inside a definer's rights ...
Only top voted, non community-wiki answers of a minimum length are eligible
