FAQ Database Discussion Community


Oracle Sql : create column/table with a for loop

sql,oracle,for-loop,oracle-sqldeveloper
I want to create a table with a column that will hold integer values from 1 to 2000. Then I want insert records into the table with each value (1,2,3,...,2000). I tried using a for loop to do so, but I don't succeed. Can someone help me ?' Thanks...

How to select actual year in Oracle Date?

sql,oracle,oracle10g,oracle-sqldeveloper
I need to select the employees that registered in the company this year; the field is a date type(field name :datemp) so i used : SELECT employee_name FROM employees WHERE datemp BETWEEN to_date(01-JAN-2015) AND to_date (31-DEC-2015) is there another method i can say that the year of a field that...

Oracle SQL - Update procedure only non null values

sql,oracle-sqldeveloper
I have a simple update procedure. So, i wish to update only fields they are not null values. How can i do it? PROCEDURE UpdateCustomerInfo( CustomerID IN NUMBER, CustomerType IN VARCHAR2, CustomerName IN VARCHAR2, CustomerGender IN VARCHAR2, CustomerBirthday IN DATE) AS BEGIN UPDATE CUSTOMER_INFO SET CUSTOMER_TYPE =CustomerType, CUSTOMER_NAME =CustomerName, CUSTOMER_GENDER...

How to insert images and videos into SQLDeveloper [closed]

sql,oracle,oracle-sqldeveloper
Please let me know how to insert images and videos to table using SQLDeveloper.

Specify different conditions for different columns in the same Query

sql,oracle-sqldeveloper
How can I specify different conditions for different columns in the same Query? According to my understanding, conditions are specified in where clause that are applied to whole table. But if I want a single condition to single column and there are multiple columns which has different conditions with the...

Is it possible to disable oracle trigger on Oracle Unit Test startup

oracle,unit-testing,oracle-sqldeveloper
We have a table that has a trigger used to write the row to a different database, that we don't want to execute during out unit tests. Is there a way to disable and reenable the trigger during startup and tear down? In the start up I have tried BEGIN...

SQL Datatype to use when inserting money

sql,oracle,oracle-sqldeveloper
I am using Oracle SQL database and I have to insert a monetary value(salary) as part of a row. For some strange reason the money command isnt working, is there any alternates that would work with this? Data input format: £00,000.000 CREATE TABLE staff (staffno CHAR(6) NOT NULL , staffsurname...

IMPLICIT and EXPLICIT join

sql,oracle,oracle-sqldeveloper
im trying to rewrite the following code: W_WHERE := ' PD.NIF(+) = p.NIF and pd.num_colegiado(+) = p.num_colegiado AND PD.FECHA_INICIO(+) <= SYSDATE AND NVL(PD.FECHA_FIN(+), SYSDATE) >= SYSDATE AND D.ID_DIRECCION(+) = PD.ID_DIRECCION AND p.num_colegiado = coleg.num_colegiado'; into normal JOIN notation, could anybody help me ? PS. PD is for PERSONA_DIRECCION table and...

misunderstanding in formating a phone number using oracle pl/sql

oracle,oracle11g,oracle-sqldeveloper
CREATE OR REPLACE PROCEDURE format_phone_number (p_phone_number IN OUT VARCHAR2) IS BEGIN p_phone_number := '(' || SUBSTR(p_phone_number,1,3) || ')' || '-' || SUBSTR(p_phone_number,4,3) || '.' || SUBSTR(p_phone_number,7,4); END format_phone_number; ------------------------------------------------- DECLARE v_number VARCHAR2(25) := '8002019201'; BEGIN format_phone_number(v_number); DBMS_OUTPUT.PUT_LINE(v_number); END; Output is ok (800)-201.9201. Question is why after i cut from the...

Connection parameters in SQL Developer

oracle,connection,database-connection,oracle-sqldeveloper,sqlplus
How to establish connection to remote DB in Oracle SQL Developer if there only login, password and DB name are available? Connection is successful in SQLPlus and PL/SQL Dev, but SQL Developer require hostname and port, but I don't know them....

Insert values into table B based on column from table A?

sql,oracle11g,oracle-sqldeveloper
I have 2 tables, a Users table and a User_permissions table. A user can have many permissions and each permission can be assigned to many different users, although this relationship has not been configured into the database (not my decision!). Let's say 2 new permissions are created - an admin...

Restrict the date range to 20 days

sql,oracle,oracle-sqldeveloper,date-arithmetic
I have a user form where user selects From_date & To_date to search between those days, there is no way to restrict the user not to enter more than 20 days, this must be done in the store procedure. How can I return the results after adding 20 days to...

Get column or alias name from a provided query in Oracle sql

sql,oracle,oracle-sqldeveloper
For example, I have an sql query SELECT * FROM EMPLOYEE INNER JOIN DEPARTMENT ON EMPLOYEE.DEP_ID = DEPARTMENT.DEP_ID Now, i want to get all the column name of the return result: DEP_ID, EMP_ID,..... I'm developing a j2ee web like http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all where you input sql query and get result table...

Difference between EXEC_SQL, EXECUTE IMMEDIATE, and inline SQL

plsql,oracle-sqldeveloper
I've been going over some PL/SQL (In Oracle SQL Developer), and have seen several different formats of SQL being called. For the consistency and speed of current and future code, I'd like to know which is the preferred choice. There are 3 types I've seen. 1) Inline SQL: CREATE TABLE...

How do I view the Explain Plan in Oracle Sql developer?

sql,oracle,oracle-sqldeveloper,explain-plan
I have few SQL queries which has very low query running performance and I want to check the query execution plan for this query. I am trying to execute the below query but its not showing any query execution plan. Its only display message plan FOR succeeded. I dont know...

Which version of oracle should I learn as a beginer? [closed]

database,oracle,oracle11g,oracle-sqldeveloper
I am beginner in Oracle. I have seen there are many oracle version such as XE, Enterprises, Personal, Liet and so on. I would like to learn Oracle but I am very confuse which oracle version should I learn first?

Nested tables - ORA-00902: invalid datatype

sql,oracle,oracle-sqldeveloper,sqldatatypes,sqlexception
I have a problem when I want to identify data type for my variable "price" as object type "rsd". When I create the table it retuns SQL Exception: ORA-00902: invalid datatype CREATE OR REPLACE TYPE "RSD" AS OBJECT ( currency number(9,2), MEMBER FUNCTION get_currency RETURN number) INSTANTIABLE NOT FINAL; CREATE...

Fetching random column value in Oracle [closed]

sql,oracle,oracle-sqldeveloper
Suppose I have a table product_details with 10 distinct products in it.1st column is product_ID which is primary key. Now I want to insert 90 random entries into order_details which has a column product_ID which is referencing product_details.product_ID How will I generate 100 random entries out of given 10 entries...

Select first element from subquery with order by

sql,oracle-sqldeveloper
I am trying to use a sub-query to pick out the first element of a table with some other filtering on it. I am working based off a comment on this answer to a similar question. My query differs slightly because I am doing some filtering on the table generated...

SQL Server with Oracle SQL Developer 4.1 - can't view tables

sql-server,oracle,azure,oracle-sqldeveloper
I cannot seem to browse the tables on my Azure SQL Server database from within Oracle SQL Developer (Version 4.1 - using jtds-1.3.1.jar plugin). I can connect and perform all queries (inserts, selects, etc.), but when I try to expand it to view the tables, I see this error: An...

SQL - missing keyword in case when syntax

sql,oracle,oracle-sqldeveloper
I am getting this error message missing keyword Any suggestions? thanks CASE WHEN substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4)>=0 and substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4) < 4000 then 'ASSET' ELSE CASE WHEN substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4)>=4000 and substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4) < 8000 then 'LIABILITY' ELSE CASE WHEN substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4)>=8000 and substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4) < 9000...

SPOOL command doesnt save result in query

sql,oracle-sqldeveloper,spool
I'm using SQL Developer and I'm trying to save result of a query into the text file using spool command. spool D:\file.txt SELECT * FROM TABLE SPOOL OFF When I open created file it only has my query in it: "SELECT * FROM TABLE" but not result of it. What...

Oracle SQL Developer - node “tables” missing from navigation pane

oracle,oracle-sqldeveloper
I must have done something to my configuration because my SQL Developer is missing the "tables" node in the lefthand navigation tree -- note the absence of "Tables" as very first item, between the DB connection node and "Views": Where in SQL Developer can a user configure what nodes are...

Oracle QSL Developer -> connected to subversion -> show “quick diff” in Worksheet

oracle,svn,oracle-sqldeveloper
Hi I use Oracle SQL developer to develop PSQL .sql files I managed to connect these files with subversion, If I go on the History tab: I can see the diff Is there a setting in SQL Developer that I can see a quick diff in the Worksheet without looking...

How to use Oracle trigger (and a domain table) in place of check constraint to enforce column range

sql,oracle,plsql,triggers,oracle-sqldeveloper
This trigger (Oracle 12c) is intended to stop the insertion and updating of rows in a table (MainTable aka Room) where a column (price) is larger than a variable. The value of the variable depends on another column (type). There are three 'type's (S, D, K) and the allowed max...

Can you return multiple COUNTs in one SQL statement?

java,sql,oracle-sqldeveloper
I'm using Java and SQL, and I'm trying to count all the goats in a petting zoo. I know that I can use the following query: SELECT COUNT(1) FROM PettingZoo pz WHERE pz.animalType = 'GOAT' Simple enough. Now, let's say goats come in one of three colors: brown, black, or...

Trouble adding metadata to a table in Oracle

oracle,oracle-sqldeveloper,oracle-spatial
I created a table in Oracle with this code: CREATE TABLE Roads ( TYPE VARCHAR2(40), ADMN_CLASS VARCHAR2(20), TOLL_RD VARCHAR2(10), RTE_NUM1 VARCHAR2(3), RTE_NUM2 VARCHAR2(3), ROUTE VARCHAR2(40), LOCATION MDSYS.SDO_GEOMETRY); Then I wanted to add metadata to the LOCATION column which will hold the geometry: INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES...

How to fire a Trigger when a variable surpasses/exceed a specific Date

sql,database,oracle,triggers,oracle-sqldeveloper
I have a table with an atributte of type "DATE" and I want to fire a Trigger when the atributte achieve a specific date. For example: Table A: a int primary key; d Date; A(1, '15-05-2015'); When this date arrives I want to fire a specific trigger. Thanks....

DatabaseConnections has no JNDI context so cannot list connections

sql,oracle,oracle-sqldeveloper
Just opened the Oracle SQL Developer and i'm getting this error: Failed to create naming Context for db connections at url: file:/C:/Users/.../AppData/Roaming/SQL Developer/system3.2.20.09.87/o.jdeveloper.db.connection.11.1.1.4.37.59.48/connections.xml SEVERE 95 69513 oracle.jdevimpl.db.adapter.DefaultContextWrapper Failed to create naming Context for db connections at url: file:/C:/Users/.../AppData/Roaming/SQL Developer/system3.2.20.09.87/o.jdeveloper.db.connection.11.1.1.4.37.59.48/connections.xml SEVERE 96 0...

Difference between dba_SEGMENTS and dba_data_files

mysql,sql,database,oracle11g,oracle-sqldeveloper
Shouldn't these two queries give the same result? First is a sum of all segments while other is the sum of all data files. They are giving different results for me. While the first one gives me 149522MB, the other gives 214973.34 MB select sum(bytes)/1024/1024 MB from dba_segments; select sum(bytes)/1024/1024...

How can I check active connection in Oracle?

oracle,hibernate,oracle-sqldeveloper,c3p0
We have used connection pooling using hibernate, we want to track active connection in the pool. Is it possible to find out using query OR any DB tool?

How to disable dots as delimiters in sqldeveloper (import data from csv)?

csv,oracle-sqldeveloper,delimiter
I am trying to import data to oracle database from csv file. In csv file i use "," as separator. I have problem with column "amount" where I have to put amount of 2 decimal places separated by a dot. I don`t know why, sql developer treats the dot as...

How can I change the output of SqlPlusresults with PLSQL?

sql,shell,plsql,oracle-sqldeveloper,sqlplus
I have a script that contains plsql codes. The output of the code is so complex. The code is here: #!/usr/bin/ksh $ORACLE_HOME/bin/sqlplus<<EOF x/[email protected] set serveroutput on set sqlnumber off set sqlblanklines off set feedback off DECLARE is_found_rec boolean := false; CURSOR c1 IS select name from rdsolucadm.BPM_T_SEMAPHORE where value in...

ORA-00936: missing expression - what is the cause?

oracle,oracle-sqldeveloper
in Oracle SQL Developer I am runing : SELECT 'XYZ' , * FROM ALL_TABLES ; and get this error: ORA-00936: missing expression 00936. 00000 - "missing expression" *Cause: *Action: Error at Line: 14 Column: 14 it runs fine when I run: SELECT 'XYZ' , * FROM ALL_TABLES ; Many thanks...

Row-wise count group by variables

sql,oracle,oracle-sqldeveloper
I have the below query - SELECT P.PRODUCT_NUMBER, P.PRODUCT_DESCRIPTION, SUM(S.NET_AMOUNT), ROUND(STDDEV(S.NET_AMOUNT),2) AS STD_DEV --(SELECT COUNT OF NET_AMOUNT < = 1$ FROM PFI_FACT_SALES GROUPED BY THE SAME P.PRODUCT_NUMBER) AS CNT FROM PFI_DIM_PRODUCT P JOIN PFI_FACT_SALES S ON P.PRODUCT_PK_ID = S.PRODUCT_PK_ID WHERE P.PRODUCT_NUMBER = 'ABC' GROUP BY P.PRODUCT_NUMBER, P.PRODUCT_DESCRIPTION; This is the...

Update the date fields from the values returned from a subquery Oracle SQL

sql,oracle-sqldeveloper
I have an application that scans a table for IDs that do not have a processed date. When an ID is processed, a process date is added to Table 1 and the entire record is copied to another table (table 2) if it completes the application. I need to compare...

SQL QUALIFY equivalent HIVE query

sql,hive,oracle-sqldeveloper,hiveql,row-number
I'm trying to create a HIVE query from an Oracle SQL query. Essentially I want to select the first record, sorted descending by UPDATED_TM, DATETIME, ID_NUM. SELECT tbl1.NUM AS ID, tbl1.UNIT AS UNIT, tbl2.VALUE AS VALUE, tbl1.CONTACT AS CONTACT_NAME, 'FILE' AS SOURCE, CURDATE() AS DATE FROM DB1.TBL1 tbl1 LEFT JOIN...

Inserting TIME into table SQL

sql,oracle,oracle-sqldeveloper
I'm trying to insert a TIME value in a table in SQL Developer, but I get the current month returned. I am using Date as the constraint when creating the table. Sorry for the poorly written code, I'm not familiar with SQL. Thanks INSERT INTO DELIVERY(ORDER_ID, DELIVERY_ID, DELIVERY_DATE, DELIVERY_TIME) VALUES(1,...

Oracle SQL Group By a String Aggregated Field

sql,oracle,oracle11g,group-by,oracle-sqldeveloper
I'm having a problem grouping a field in my query. Here is an example of what I'm talking about: Example: AIR_DT DOL_GAP_TIME MATL_SIZE 15-JAN-15 8:00 AM 30 15-JAN-15 8:00 AM 25 15-JAN-15 9:00 AM 5 15-JAN-15 9:00 AM 10 15-JAN-15 9:00 AM 5 15-JAN-15 9:00 AM 20 Those with same...

Insert with multiple selects

sql,oracle,oracle-sqldeveloper,sql-insert,insert-select
I have a SQL request that return some ora-01427 error: single-row subquery returns more than one row INSERT INTO my_table (value0, value1, value2, value3) VALUES((SELECT MAX(value0) FROM my_table), '5', (SELECT DISTINCT(value2) FROM another_table), '8'); The thing is, I need two hard-coded values, I need a value from a select that...

Is Schema in Oracle is equivalent to Database in Microsoft SQL Server?

oracle11g,sql-server-2012,oracle-sqldeveloper
I am new to Oracle database and I wanted to create a database in Oracle. I followed this link to create a database: http://www.fehily.com/books/createdb/createdb_oracle_11g_2.html In Microsoft SQL Server, when we create a database, we use the create database command and the database creation is instantaneous [within fraction of seconds], but...

Oracle declaring and Using Dates

oracle,oracle-sqldeveloper
So I have been using SQL Server for about 7 years now, just changed jobs and a lot of our production stuff is in Oracle SQL Developer. Day 1, not enjoying Oracle but hoping for the best. I am trying to do what I would think would be extremely simply...

SQL not recognizing column alias in where clause

sql,oracle,oracle-sqldeveloper
I am only a beginner in SQL, but I've come across this annoying error. SQL is having an issue with the WHERE clause of this script: SELECT ITEM_ID, ITEM_PRICE, DISCOUNT_AMOUNT, QUANTITY, (ITEM_PRICE*QUANTITY) AS price_total, (DISCOUNT_AMOUNT*QUANTITY) AS discount_total, ((ITEM_PRICE-DISCOUNT_AMOUNT)*QUANTITY) AS item_total FROM ORDER_ITEMS WHERE item_total > 500 ORDER BY item_total; I...

Rearranging a table converting values to column names [closed]

sql,oracle,oracle-sqldeveloper
As a result of an XML implementation on achieving a dynamic data entry tool, I was left with the below table TRGID C_NAME I_ID COLUMNID TARGET_DESC DEPT_NAME VALUE 20158 Outlook 2 1000 Production Operations 2.501 20158 Outlook 4 1000 Production Operations 1.5 20158 Outlook 3 1000 Production Operations 0.2 20158...

How to copy everything from a Database . Pl Sql

database,oracle,copy,oracle-sqldeveloper
I am on SQL developer and I want to copy everything(I mean and the data) from one database to another. I tried with -->Tools-->Database Copy . But it copied only the structure without the data. Can someone help me.?

SQL MERGE INTO( has issue when trying to insert into database by using subquery)

sql,database,oracle,oracle-sqldeveloper
MERGE INTO DWCUST dc USING A2CUSTMELB cm ON (dc.firstname=cm.fname AND dc.surname=cm.sname and dc.postcode=cm.postcode) WHEN MATCHED THEN UPDATE SET dc.DWSOURCEIDMELB=cm.custid WHEN NOT MATCHED THEN INSERT (DWCUSTID, DWSOURCEIDMELB, FIRSTNAME, SURNAME, GENDER, PHONE, POSTCODE, CITY, STATE, CUSTCATNAME) VALUES (DWCUST_SEQ.NEXTVAL, cm.custid, cm.fname, cm.sname, upper(cm.gender), cm.phone, cm.postcode, cm.city, cm.state, (select cc.custcatname from a2custcategory cc where...

dbms_out returning column 1: PLS-00428: an INTO clause is expected in SELECT statement

oracle,oracle-sqldeveloper
Running this query in sql-developer : SET SERVEROUTPUT ON BEGIN DBMS_OUTPUT.PUT_LINE ('schema > table > key '); SELECT 'table_name' TABLE_NAME , t.* FROM table_name t ; END ; I get this message : Error report - ORA-06550: line 4, column 1: PLS-00428: an INTO clause is expected in this SELECT...

In a package: ORA-00942 table or view does not exist

oracle,plsql,datatable,package,oracle-sqldeveloper
So, I think I've scoured all the similar posts and can't solve mine. In a pkg spec, I declared: TYPE record_type IS RECORD (Pat_Nbr VARCHAR2(4), Trt_Procedure VARCHAR2(5), Phys_ID VARCHAR2(3), Phys_Name VARCHAR2(20), Phys_Specialty VARCHAR2(18)); TYPE table_type IS TABLE OF record_type INDEX BY BINARY_INTEGER; t_Pat_Trt table_type; PROCEDURE BuildPatTbl (t_PatTrt OUT table_type, PatTblCount...

How to identify previous scheduled date and next scheduled date among many dates in scheduled table via procedure

oracle,oracle11g,oracle10g,oracle-sqldeveloper
I have one table which is containing scheduled dates like 01/01/2015, 01/02/2015, 01/03/2015, 01/04/2015, 01/05/2015.... Based on today's date i have to identify what is previous schedule date and next schedule date via stored procedure. For example today's date is 25/03/2015. Then previous schedule date is 01/02/2015 and next schedule...

Does all the database instances or services has same listener port in Oracle?

oracle,oracle11g,oracle-sqldeveloper
I am trying to understand the oracle database environment in my office. In the Oracle SQL developer in my office, there are multiple connections with same port and host, but with different services. From oracle documentation, I have read this: Port: This is the listener port for the database. The...

'PLS-00103 Encountered the symbol “+” …' error when trying to concatenate some strings in a trigger

sql,triggers,oracle-sqldeveloper
I have a SQL table where a particular record ID (the primary key) could have several Contact IDs. I want to create a trigger where each time a new row is added, it will check whether the record ID exists, and if it does it will add the new Contact...

How to insert time into a DATE type?

sql,oracle,oracle-sqldeveloper,oracle-apex
I inserted a tuple using the TO_DATE function, but it turns out to be '05/01/2015' when I do the query. There is no time shown. Anything is wrong? INSERT INTO course_section VALUES(1,'MWF',TO_DATE('10:00:00 A.M.','HH:MI:SS A.M.')); How to fix it?...

Check if record exists, if it does add 1 (inside procedure)

plsql,oracle-sqldeveloper
I am trying to check if an email address already exists, and add 1 if it does (and 1 if even the email+1 exists and so on). But so far I can't even figure out how to check if it exists, inside a procedure. if exists (select 1 from table...

How can I know if a field value that represent the primary key of a table is automatically generated when the user insert a new record into the table?

sql,database,oracle,oracle11g,oracle-sqldeveloper
I am not so into database and I have the following doubt. I am working on an Oracle database using Oracle SQL Developer as "IDE". Here I can examine a table named coda_rx that have some fields. One of this field is named PK_CODA (it is the first one) and...

How to count rows over multiple joins

oracle-sqldeveloper
Hi I have a complex (for my SQL standard) count I need to perform over multiple tables I'd love some help with. I have three tables: RELEASE ID |METADATA_ID|Is_Active|Creation_Source|Release_Status 123456|123 | Y | A1 |Active 134567|124 | Y | A1 |Active 145678|125 | N | A2 |Closed RELEASE_METADATA ID |UPC...

Changing Primary Key in Oracle

oracle,django-models,oracle-sqldeveloper
I'm updating a table that was originally poorly designed. The table currently has a primary key that is the name of the vendor. This serves as a foreign key to many other tables. This has led to issues with the Vendor name initially being entered incorrectly or with typos that...

Using mysql with oracle sql developer

mysql,oracle,oracle-sqldeveloper
I have configured mysql db with oracle sql developer version 4.0. But create table, create database all the options for mysql databases are disabled. I was able to create a new database only by running the create database command. Can any tell me a workaround for this. Is it not...

SQL Developer does not connect with SID as defined in tnsnames.ora

oracle,oracle-sqldeveloper
My tnsnames.ora looks like this: ORACLR_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) (CONNECT_DATA = (SID = CLRExtProc) (PRESENTATION = RO) ) ) ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)...

Update column with String prefix

sql,sql-update,oracle-sqldeveloper
Im triying to UPDATE a column named INV_SUPPLIER, I must add ES prefix to INV_SUPPLIER if the column does not start with ES AND does not contain NO_VALIDO AND IS NOT NULL Column declaration: Nullable Default INV_SUPPLIER VARCHAR2(32 BYTE) Yes When I select: SELECT * FROM EFAC_INVOICE f WHERE f.INV_SUPPLIER...

ORA-00907 missing right parenthesis In Crystal - Works in Oracle Developer

sql,oracle,oracle11g,crystal-reports,oracle-sqldeveloper
I have an issue with a query, it works fine in Oracle Developer with {?Month} replaced with '2015-01-01' but in Crystal it is giving me the error ORA-00907 missing right parenthesis when using the query below. Parameter Parameter Name: Month Prompting text: Enter Month Value Type: String (also tried date)...

sql developer hangs on startup - what can I do?

oracle-sqldeveloper
At present I can not run it (SQL Developer 4.1) because it hangs on "Restoring Editors" while starting. I suppose I've done it by exiting it before by killing sql developer process because it was hanged on fetching objects to Schema Browser so long... Maybe I would clean some temporary...

update value from a table if from other two table values match

oracle11g,oracle-sqldeveloper
I'm trying to update around 800 rows and would like to update table_c it two values from table_A and table_b match. Table_a and table_c are related by value_id. Example: CREATE TABLE TABLE_A (VALUE_ID INTEGER, PERSON_ID INTEGER, LAST_NAME VARCHAR2(15), FIRST_NAME VARCHAR2(15)); CREATE TABLE TABLE_B (VALUE_ID INTEGER, LAST_NAME VARCHAR2(15), FIRST_NAME VARCHAR2(15), ALIAS_ID...

PLSQL generate random integer

sql,oracle,plsql,oracle11g,oracle-sqldeveloper
In Oracle Sql developer 11g, how do I generate a random integer and assign it to a variable? This is what I've tried so far: S_TB := SELECT dbms_random.value(1,10) num FROM dual; With this code I got error: S_TB := SELECT dbms_random.value(1,10) num FROM dual Error report - Unknown Command...

how to write sql query from sas based on if then statements

sql,sas,oracle-sqldeveloper
I don't really have any advanced sql knowledge on how to phrase if/then statements in sql (whether to use case, for example) and/or formatting variables in the same query so I was wondering if someone could help with this sas code and phrasing it in the correct format into sql:...

Deadlock in Oracle 3

oracle,oracle11g,oracle-sqldeveloper,deadlock
I have found following errors on Oracle.. I cant understand what is happening here. Cant understand which session is waiting for what.... Also is it about Raw level lock or something? Can anyone please help me? *** 2015-03-10 10:23:30.288 DEADLOCK DETECTED ( ORA-00060 ) [Transaction Deadlock] The following deadlock is...

Set Escape in a View

sql,oracle,oracle-sqldeveloper
I currently am attempting to run a script which contains a case statement where I need to match on the string 'Prod. & Mat.' When running the script in a worksheet, I simply precede my entire script by running, set escape '\' Then I run my select statement which includes...

how to order sql query with union ( ORA-00904) [duplicate]

sql,oracle,oracle-sqldeveloper
This question already has an answer here: SQL: how to use UNION and order by a specific select? 4 answers I have huge query and i have to order by desc but i couldn't do that. I think there is something wrong with the union but i couldn't figure...

Export table dump using SQL DEVELOPER

oracle,oracle-sqldeveloper,oracle12c
I need export table dump using SQL DEVELOPER I do: View->DBA then right click on data pump and then data pump export wizard In opened export wizard window, I choose Tables type, then click next and in second step, there are not displayed any tables: Though, I have several tables...

oracle incorrect primary key inserted in the table

sql,ruby-on-rails,oracle,oracle11g,oracle-sqldeveloper
I am quite baffled by my current situation: My ruby on rails application is generating the following query: INSERT INTO "MEMBER_ROLES" ("ID", "NAME", "ORG_ID") VALUES (:a1, :a2, :a3) [["id", 55], ["name", "test6"], ["org_id", "2"]] The query seemed to have worked and I can see the above record in my database...

PL/SQL Adding hours to timestamp parameter

plsql,oracle-sqldeveloper
I got parameter :dateFrom which gonna be used as an argument in a function as a TIMESTAMP. I need to add to :dateFrom + 7 hours, how can I do that?...

Oracle SQL Developer(4.0.0.12)

sql,oracle-sqldeveloper
First time posting here, hopes it goes well. I try to make a query with Oracle SQL Developer, where it returns a customer_ID from a table and the time of the payment from another. I'm pretty sure that the problems lies within my logicflow (It was a long time I...

Error while opening oracle-sqldeveloper

java,oracle-sqldeveloper
When i open oracle-sqldeveloper for it is asking for full pathname for java.exe. I have installed a java and added the path like this C:\Program Files\Java\jre1.8.0_45\bin But after adding the path i am getting the following. ...

ora-30926 when using two primary keys

plsql,oracle-sqldeveloper
I'm getting the following error ORA-30926: unable to get a stable set of rows in the source tables when attempting to execute the following statement: MERGE INTO new_table nt USING ( select c.id, cd.evaluation_date, cd.population_total_count, cd.population_urban_count, cd.population_birth_rate_per1k, cf.gdp_total_dollars, cf.gdp_per_capita_dollars from countries c, country_demographics cd, country_financials cf where c.id = cd.country_id...

How to write mysql TIMESTAMPDIFF function in Oracle sql query

sql,oracle,datetime,oracle-sqldeveloper
I have query in Mysql which return minutes using TIMESTAMPDIFF in table. But now i have migrated my data to Oracle. So i want to use the same query to get the TIMESTAMPDIFF in a table in Oracle. Oracle also dont support NOW() function in mysql. The PROCESS_START_DATE column in...

Not able to see the results by using Dbms_Output.Put_Line

oracle,oracle-sqldeveloper,plsqldeveloper
I am not able to see the results by using Dbms_Output.Put_Line in sql developer. The result is shown only once. When I execute it again, it is not shown. I checked a lot of workarounds described in google, but those haven't worked for me. I am executing following: set serveroutput...

retrieving the duplicate SSN from the huge number of employess all of them

sql,oracle-sqldeveloper
I have written this query for getting the employees with same SSN numbers. But this query is giving me the all the employess, can't figure out whats wrong with it. SELECT a.empid, a.NAME, a.ssn FROM p_data a WHERE ssn IN (SELECT ssn FROM p_data b WHERE b.ssn = a.ssn GROUP...

Edit Sequence values using sql developer interface

sql,oracle,oracle11g,sequence,oracle-sqldeveloper
I try to change the value of LAST_NUMBER in a sequence in sql developer v4 using the graphical interface only. When I click the edit icon next to the value I am unable to change the field. What I see is following: My question is: is there a way to...

Automatically export result sets into csv

sql,oracle,oracle11g,oracle-sqldeveloper
I have to run several queries on an oracle 11g database, within SQLDeveloper 3.1. For example: select * from product; select * from customer; select * from prices; At the moment I am exporting the resultsets "per hand", I simply right-clickonto the result and thenexport` it. I would like to...

substr function for substract the column value basis of another column in oracle

oracle,oracle11g,oracle10g,oracle-sqldeveloper
How to use substr function in oracle to substract the column value based on another column vale in same table. For example:suppose table abc having some column value like a=01-CEDAPR while B=AB_52MM_01-CEDAPR Now i want to populate the column c on the basis of value AB_52MM. can any one suggest...

How to see the table just modified in a script with Sql Developer

sql,oracle,select,plsql,oracle-sqldeveloper
I've this block in sql developer: begin delete from temp; insert into temp values (1); dbms_output.put_line('Done'); end; If I hit F5 the script runs fine. The table is updated with the value 1 and the "Script output" tab says 'Done'. However I would like to automatically visualize the table that...

Error Testing PL/SQL Insert Stored Proc

oracle,plsql,oracle-sqldeveloper
I'm new to Oracle and stored procedures. I'm having some issues getting a simple stored proc I wrote to run using SQL Developer. Here is my stored proc: CREATE OR REPLACE PROCEDURE sp_user_create( user_row IN t_user%ROWTYPE) IS BEGIN INSERT INTO T_USER (FIRST_NAME,LAST_NAME,EMAIL) VALUES(user_row.FIRST_NAME, user_row.LAST_NAME, user_row.EMAIL); COMMIT; EXCEPTION WHEN OTHERS THEN...

Error(s) parsing SQL: Unexpected token near *!*

sql,oracle-sqldeveloper
Error(s) parsing SQL: Unexpected token near *!* in the following: SELECT RESOURCEID AS RESOURCEID, MAX(ROWKEY) ROWKEY, MIN(PRODUCTIONDATE) PRODUCTIONDATE, CAST(SUM(UNITS) AS *!*NUMERIC (9)) UNITS, MAX(BUCKETSTART) BUCKETSTART FROM( Unexpected token near *!* in the following: SELECT RESOURCEID AS RESOURCEID, MAX(ROWKEY) ROWKEY, MIN(PRODUCTIONDATE) PRODUCTIONDATE, CAST(SUM(UNITS) AS NUMERIC *!*(9)) UNITS, MAX(BUCKETSTART) BUCKETSTART FROM( Expecting...

Differences in these 2 query

sql,oracle11g,oracle-sqldeveloper
Is this 2 query different? Im getting different results in Oracle SQL, can you explain the difference? ***First Query*** SELECT * FROM TABLE1, TABLE2 WHERE TABLE1.COL_ID = TABLE2.COL_ID AND TABLE1.COL1 = 'ABC' AND TABLE2.COL1 = 'ABC' AND TABLE1.COL2 = 2015 AND TABLE1.COL3 = 'X'; ***Second Query*** SELECT * FROM TABLE1,...