We have an application running locally where we&#39;re experiencing the following error:

&gt; ORA-12514: TNS:listener does not currently know of service requested
&gt; in connect descriptor

I&#39;ve tested the connection using `TNSPing` which resolved correctly and
I tried `SQLPlus` to try connecting, which failed with the same error as above. I used this syntax for `SQLPlus`:

    sqlplus username/password@addressname[or host name]

We have verified that:

- the TNS Listener on the server is running. 
- Oracle itself on the server is running. 

We don&#39;t know of any changes that were made to this environment. 
Anything else we can test?

ORA-12514 TNS:listener does not currently know of service requested in connect descriptor

How do I do the following?

    select top 1 Fname from MyTbl

In [Oracle 11g][1]?

  [1]: https://en.wikipedia.org/wiki/Oracle_Database#Version_numbering


How do I do top 1 in Oracle?

I have defined a name for each of the constraint for the multiple tables that I have created in Oracle SQL. 

The problem is that to drop a constraint for the column of a particular table I need to know the name that I have supplied for each constraints, which I have forgotten. 

How do I list out all the names of constraints that I have specified for each column of a table? 

Is there any SQL statement for doing so? 

Display names of all constraints for a table in Oracle SQL

I have installed Oracle Database 11g Express Edition on my pc (windows 7)
and I have installed Oracle SQL Developer as well. 

I want to create a simple database to start with, maybe with one table or two and then use Oracle SQL Developer to insert data and query it.

When I open Oracle SQL Developer, it asks me to create a new connection, therefore it assumes that a database has already been created. 

So my question is, how do I create an initial database in Oracle 11g?




How to create a new database after initally installing oracle database 11g Express Edition?

Is there a Hibernate dialect for Oracle Database 11g? Or should I use the `org.hibernate.dialect.Oracle10gDialect` that ships with Hibernate?

Hibernate dialect for Oracle Database 11g?

What are the difference between Oracle DATE and TIMESTAMP type?
Both have date and time component?
Also what is corresponding type in Java for these date types?

Difference between oracle DATE and TIMESTAMP

I have an application that I can&#39;t get connected to my Oracle Database 11g Express Edition. I created a test database in this edition, and I can connect to the database fine using Oracle SQL Developer, create tables, views etc. However, I&#39;m having a hard time getting connected via my application. Where is the connection information? In what file? I wanted to compare my connection info with what is set up in the SQL Explorer&#39;s file. I found all the *.ora files and renamed them to see if I could find what file (through the process of elimination) the connections were stored in, but I wasn&#39;t successful. Any help would be appreciated.

Where does Oracle SQL Developer store connections?

I have applied for an internship in a company and as a question they have asked me to create a schema for their company with certain requirements and mail them the *DDL* file. I have installed Oracle database 11g Express edition, but how do I create a new schema in Oracle database 11g? I have searched in the net for a solution but I could not understand what to do. And after creating a schema, which file should I mail them?

How to create a new schema/new user in Oracle Database 11g?

With help of this command ***`show databases;`*** I can see databases in *MySQL*.  

How to show the available databases in *Oracle*?

How to display databases in Oracle 11g using SQL*Plus

I installed Oracle 11g. I didn&#39;t change the passwords for SYSTEM and SYS. However now I find that the default passwords do not work. Please help.

Default passwords of Oracle 11g?

Can any one tell me whether below 2 queries are an example of Left Outer Join or Right Outer Join??

    Table Part:
    Name	     Null?       Type
    PART_ID	     NOT NULL    VARCHAR2(4)
    SUPPLIER_ID              VARCHAR2(4)
    
    PART_ID	SUPPLIER_ID
    P1	    S1
    P2	    S2
    P3	
    P4	
    
    Table Supplier:
    Name	        Null?	  Type
    SUPPLIER_ID	NOT NULL      VARCHAR2(4)
    SUPPLIER_NAME	NOT NULL  VARCHAR2(20)
    
    SUPPLIER_ID  SUPPLIER_NAME
    S1	         Supplier#1
    S2	         Supplier#2
    S3	         Supplier#3

Display all the parts irrespective of whether any supplier supplies them or not:

&lt;pre&gt;
SELECT P.Part_Id, S.Supplier_Name
FROM Part P, Supplier S
WHERE P.Supplier_Id = S.Supplier_Id (+)

SELECT P.Part_Id, S.Supplier_Name
FROM Part P, Supplier S
WHERE S.Supplier_Id (+) = P.Supplier_Id
&lt;/pre&gt;


Left Outer Join using + sign in Oracle 11g

How to uninstall / completely remove Oracle 11g (client)?

deinstall.bat doesn&#39;t work.

**Edit:**  Further discussion related to why this question was closed and deleted:  https://meta.stackexchange.com/questions/190441/why-was-this-question-related-to-uninstalling-the-oracle-database-client-deleted

How to uninstall / completely remove Oracle 11g (client)?

Can someone advise me on how to create a user in Oracle 11g and only grant that user the ability only to execute one particular stored procedure and the tables in that procedure.

I am not really sure how to do this!

How to create a user in Oracle 11g and grant permissions

How would I go about creating a database from a dump file? I do not have an existing database with the same structure on my system so it has to be complete with jobs, events, tables, and so on. 

I placed the dump and log file in E: drive

I have tried the import utility 

    E:/&gt;impdp system/tiger@oratest FILE=WB_PROD_FULL_20MAY11.dmp

But I&#39;m getting error as

    invalid argument value
    bad dump file specification
    unable to open dump file &quot;E:\app\admin\oratest\dpdump\WB_PROD_F
    ULL_20MAY11.dmp&quot; for read
    unable to open file
    unable to open file
    (OS 2) The system cannot find the file specified.

And when I see in Windows Explorer DMP file(taken from Linux server) is showing as Crash dump file

I don&#39;t understand how I can resolve this issue. Please help me to solve this issue.

I&#39;m a complete newbie on Oracle... 




How to import an Oracle database from dmp file and log file?

When I try to query objects, I end up with following error:

    ORA-01461: can bind a LONG value only for insert into a LONG column

Could someone please help me on the cause and solution of the problem?

ORA-01461: can bind a LONG value only for insert into a LONG column-Occurs when querying

How to find out the users list, which is all created in the `oracle 11g` database. Is there any `command` to find out the users list which we can execute from the Command line interface! 

How to find the users list in oracle 11g db?

I&#39;m trying to connect to a schema on 11g (v11.2.0.1.0) from a PC with 9i (v9.2.0.1) client. It seems to connect fine to some schemas, but not this one - it comes back with a `ORA-01017 Invalid Username/Password` error every time.

The username and password are DEFINITELY correct - can anyone think of a reason why this wouldn&#39;t work? 

Are there any fundamental incompatibilities between 9i and 11g?


ORA-01017 Invalid Username/Password when connecting to 11g database from 9i client

can anybody explain what is the main difference between functions and procedures in Oracle? Why must I use procedures if I can do everything with functions? 

 
 1. If I cannot call procedure in sql statement, ok, I&#39;ll write a function to do the same work.
 2. Procedures don&#39;t return values, ok, I&#39;ll return only sql%rowcount or 1(success), 0(exception) after any dml operation
 3. Both procedures and functions can pass variables to calling environment via OUT/IN OUT parameters

I heard that the main difference is in performance, &#39;procedures are faster than functions&#39;. But without any detail. 

Thanks in advance.

Functions vs procedures in Oracle

I am getting the error :

     ORA-28000: the account is locked 

**Is this a DB Issue?** When I unlock user account using the command `ALTER USER username ACCOUNT UNLOCK` temporarily it will be OK. Then after some time the same account lock happens again.

The database using is oracle `XE`

**Does anybody else have the same issue?** 





ORA-28000: the account is locked error getting frequently

I am referring to this `stackoverflow` answer:

https://stackoverflow.com/questions/1564956/how-can-i-select-from-list-of-values-in-sql-server/7285095#7285095

How could something similar be done in **Oracle**?

I&#39;ve seen the other answers on this page that use `UNION` and although this method technically works, it&#39;s not what I would like to use in my case.

So I would like to stay with syntax that more or less looks like a comma-separated list of values.

**UPDATE** *regarding the `create type table` [answer][1]*:

I have a table:

    CREATE TABLE BOOK
    (	&quot;BOOK_ID&quot; NUMBER(38,0)
    )

I use this script but it does not insert any rows to the `BOOK` table:

    create type number_tab is table of number;
    
    INSERT INTO BOOK (
        BOOK_ID
    )
    SELECT A.NOTEBOOK_ID FROM
        (select column_value AS NOTEBOOK_ID from table (number_tab(1,2,3,4,5,6))) A
    ;

Script output:

    TYPE number_tab compiled
    Warning: execution completed with warning

But if I use this script it does insert new rows to the `BOOK` table:

    INSERT INTO BOOK (
        BOOK_ID
    )
    SELECT A.NOTEBOOK_ID FROM
        (SELECT (LEVEL-1)+1 AS NOTEBOOK_ID FROM DUAL CONNECT BY LEVEL&lt;=6) A
    ;


  [1]: https://stackoverflow.com/questions/10353969/how-can-i-select-from-list-of-values-in-oracle/10354083#10354083

How can I select from list of values in Oracle

I have a query that looks something like this:
    
    SELECT DISTINCT share.rooms
    FROM Shares share
      left join share.rooms.buildingAdditions.buildings.buildingInfoses as bi
	... //where clause omitted
	ORDER BY share.rooms.floors.floorOrder, share.rooms.roomNumber,
             share.rooms.firstEffectiveAt, share.shareNumber, share.sharePercent

Which results in the following exception:

    Caused by: org.hibernate.exception.SQLGrammarException: ORA-01791: not a SELECTed expression

If I remove the DISTINCT keyword, the query runs without issue.  If I remove the order by clause, the query runs without issue.  Unfortunately, I can&#39;t seem to get the ordered result set without duplicates.  

Using the DISTINCT keyword causes this error: not a SELECTed expression

    SELECT ORDER_NUM, CUSTOMER_NUM, CUSTOMER_NAME, ADD_DAYS (ORDER_DATE, 20)
    FROM CUSTOMER, ORDERS; 

Oracle Express says ADD_DAYS invalid? Any ideas what Am I doing wrong?



Add days Oracle SQL

I am getting the above error when trying to connect to a server database from a client using service name `orcl`. It is getting connected when I am using the other service name i.e. `dms`

Below is my `listener.ora`

    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (SID_NAME = dms)
          (SERVICE_NAME = dms)
          (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)
    #      (PROGRAM = extproc)
        )
      (SID_DESC =
         (SID_NAME = orcl)
         (SERVICE_NAME = dms)
        # (GLOBAL_DBANME = orcl)
          (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)
       #  (PROGRAM = extproc)
      )
      )
    
    
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.53)(PORT = 1521))
    
        )
      )
    
    ADR_BASE_LISTENER = /home/oracle/app/oracle
    and tnsnames.ora
    ORCL =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.53)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
         #(SERVICE_NAME = orcl.localdomain)
          (ORACLE_SID = dms)
          (SERVICE_NAME = orcl)
        )
      )
    
    DMS =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.53)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = dms)
          (ORACLE_SID = dms)
        )
      )

Please tell me what to do?

ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist

I am using Oracle 11g for my web application. I want to add a column and a comment to an existing table. I can do that easily with the below commands

    ALTER TABLE product ADD product_description VARCHAR2(20)
and 

    COMMENT ON COLUMN product.product_description 
         IS &#39;This is comment for the column&#39;;

But I want to do above task in single command. I searched on internet for a command to add a column and comment in a single command but I couldn&#39;t find. I wonder if this is possible. Any suggestions would be highly appreciated.

SQL to add column and comment in table in single command

I have a table like this:

    group_id  name  
    --------  ----
    1         David
    1         John
    1         Alan
    1         David
    2         Julie
    2         Charles

And I want the following result:

    group_id  names
    --------  -----
    1         &#39;Alan, David, John&#39;
    2         &#39;Charles, Julie&#39;

I can use the following query:

    select group_id, 
           listagg(name, &#39;,&#39;) within group (order by name) as names
    from demotable
    group by group_id 

To get this (very similar result):

    group_id  names
    --------  -----
    1         &#39;Alan, David, David, John&#39;
    2         &#39;Charles, Julie&#39;

Any ideas how I can filter the names by uniqueness in the `LISTAGG` call?

How to use Oracle&#39;s LISTAGG function with a unique filter?

I have this procedure:

    CREATE OR REPLACE PROCEDURE PROC1(invoicenr IN NUMBER, amnt OUT NUMBER)
    AS BEGIN
    SELECT AMOUNT INTO amnt FROM INVOICE WHERE INVOICE_NR = invoicenr;
    END;

So when I run it like this it returns absolutely nothing:

    DECLARE
        amount NUMBER;
    BEGIN
        PROC1(1000001, amount);
        dbms_output.put_line(amount);
    END;

BTW I use DreamCoder for Oracle. Is there a problem with the procedure itself or with the way I call it? There is an entry in the INVOICE table with INVOICE_NR equal to 1000001.

Calling a stored procedure in Oracle with IN and OUT parameters

How do I grant a user DBA rights in Oracle? I guess something like:

    CREATE USER NewDBA
    IDENTIFIED BY passwd;
    
    GRANT DBA TO NewDBA WITH ADMIN OPTION;

Is it the right way, or...

Granting DBA privileges to user in Oracle

I just installed Oracle 11g, and tried to start Oracle SQL developer so as to start writing queries.

It is asking me:

    Enter the full pathname for the java.exe file .

Where do I find this? I did a global search for `java.exe` and am sure did not get some Oracle related pdf files. Also my Oracle is installed out of users/vas.


All Oracle11g Solutions on Gang of Coders

Total of 28 Oracle11g Solutions