SQL & PL/SQL :: Connect By And Rollups
Feb 10, 2011
I am trying to rollup a measure of a fact for a hierarchical dimension. The 2 entities - Dimension and the fact are described as below
Dimension Table - This maintains the hierarchical structure of the dimension in a parent child relationship format
DROP TABLE tmpDimension;
CREATE TABLE tmpDimension
(
DimValVARCHAR2 (20),
ParentDimValVARCHAR2 (20)
);
[code]...
Also, is there a way to make the group By levels dynamic. I cheated my way through by using a substr which makes it very depenent on 1 letter dimVal values
View 5 Replies
ADVERTISEMENT
Jul 7, 2012
I have been troubling with this issue for the last two weeks...tried so much, but could not able to resolve....
sqlplus username/password@IPAddress:port/ServiceName
I am able to connect to DATABASE from SQLAssistant only when I provide string like ABOVE........If I dont mention the SERVICE, m unable to connect to DB.Now the biggest issue is with SQL Developer.....m not at all able to connect to DB using SQL Developer.
View 3 Replies
View Related
Aug 17, 2010
I have installed oracle developer 6i. in sqlplus i want to put my username and password but nothing in the connect string field.
View 1 Replies
View Related
Oct 6, 2010
I have the following computer/setup:
Acer laptop, Intel Processor w/ Windows 7 Professional (64-bit)
Delphi 2010 Professional
Oracle XE (+ the Oracle XE client that comes with it)
I cannot can't to the Oracle database from Delphi. The error I keep getting is:
Alias is not currently opened. [Oracle][ODBC][Ora]ORA-12154:TNS: could not resolve the connet identifier specified.
Here's what I've done:
1. Create an ODBC connection to my Oracle database
- run C:WindowsSysWOW64odbcad32.exe to open the ODBC administrator.
- Data source name: bt_user
- TNS Server Name: localhost/xe
- user bt_user
- Clicked "test connection" -- Success.
2. Open Delphi and drop a TDatabase component on my main form.
- Set the Alias Name property to "bt_user" (the alias I set up in ODBC)
- Set database name to "bt_user".
- Checked "connected".
Alternatively, I tried using the Delphi ADO components (the dbGo components).
- Drop TADOConnection component on my form.
- Select "connection string" and open the dialog.
- Click "build string".
- Select "Microsoft OLE DB Provider for ODBC Drivers" from the selection.
- In "use data source", select "bt_user" from the drop-down list (my ODBC connection from #1)
- Click "test connection".
Again the same error.
My questions are:
1. Can Delphi work with Oracle XE (Express Edition)?
2. If so, then do I need to install the "Instant Client"?
3. Will the instant client for Oracle 10g work with Oracle 10g XE?
4. Do I need to install any additional ODBC driver(s)?
5. Why was I successful in creating the ODBC connection in Windows, but it failed in Delphi?
6. Will Visual Studio.NET (C#) work with Oracle XE, and what are the steps for setting that up?
View 2 Replies
View Related
Jan 9, 2010
i have installed oracle 10g and toad but i don't know how to connect toad with database
View 1 Replies
View Related
Oct 9, 2012
I am Using Toad 9.3 and try to connect Oracle DB 11g through SYS but fail, because it is showing ORA-01031:Insufficient privileges.Although the same, I can connect through SQL * Plus. Is there any way to connect SYS (in TOAD).
View 1 Replies
View Related
Nov 10, 2011
For example, I have 3 database, database 1 and database 2 connect together by database link db_link1, database 2 and database 3 connect together by database link db_link2.
I want to ask: Can I connect from database 1 to database 3 ? ( Of course don't use database link between database 1 and 3, just use database db_link1 and db_link2 )
Both db_link1 and db_link2 are public database link.
Can I restrict user access by 2 these public database link ?
View 1 Replies
View Related
Nov 1, 2011
I installed Oracle Express on my Windows 2003 Enterprise server. And right off the bat I cannot connect to it via command line. I have googled and read many sites, but am still getting the same error, no matter what I do. I am newbie again (was a DBA in 1990...when I used to have the same error and fix it in seconds...not sure why this problem still lingers in Oracle) so most likely I am overlooking something. Below are my tnsnames.ora and liserner.ora files.
tnsnames.ora
CODEXE =
(DESCRIPTION =
[Code]....
View 1 Replies
View Related
Mar 24, 2011
Had 10.2.0.3 on Win Server 2003 and ran patch set 3. Now have 10.2.0.4 but cannot login. Here is what I get along with other info.
-------
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 24 14:27:31 2011
Copyright © 1982, 2007, Oracle. All Rights Reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
--------
My listener service is running. Listener.ora is:
---------
# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = W2K3-TX-ORA)(PORT = 1521))
)
)
-----------
Running TNSPING:
C:\oracle\product\10.2.0\db_1\network\ADMIN>tnsping orcl
TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 24-MAR-2011 14:35:35
Copyright © 1997, 2007, Oracle. All rights reserved.
Used parameter files:
C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = W2K3-TX-ORA)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME
= ORCL)))
OK (60 msec)
View 3 Replies
View Related
Nov 26, 2012
I've stucked with a query. Let me explain the situation: I have a table that i store the IDs of logically equal records.
For example;
A = B
B = C
X = Y
Z = Y
My query must return all equivalent records. If you call the query with parameter 'A', the result set must contain B and C. And if you call the query with parameter 'Y', the result set will contain X AND Z. I have thought that i can write the query wity using start with connect by statement. But the query does not work as i expected. Here is my code and sample data:
create table temptable (ID1 number,ID2 number);/
insert into temptable values(11,12);/
insert into temptable values(12,13);/
insert into temptable values(13,14);/
insert into temptable values(13,15);/
SELECT distinct ID1 from
(
SELECT * FROM temptable
START WITH ID1 = 13 OR ID2 = 13
CONNECT BY NOCYCLE
[code]...
In my sample the equality definitions is;
11 = 12
12 = 13
13 = 14
13 = 15
When i call the query with parameter 13, i'm expecting to get 11,12,14,15. But it returns only 12,14 and 15.
View 4 Replies
View Related
Feb 18, 2011
I want access to the Oracle as manager. What is the user name and password. I tried:
Username: system
Password: manager
But it gives an error. I work on oracle 11g
View 3 Replies
View Related
Mar 6, 2013
created one user and granted connect, resource.
SQL> create user xyz identified by welcome123;
but while connecting to that user it's not connecting. saying
SQL> conn xyz/welcome123
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL>
but i am sure i create user.i though to drop and recreate that user. But it is saying connected user can't be drop.then i checked for the connected session of xyz user. but shocked, there is no session for xyz user.
View 7 Replies
View Related
Mar 12, 2013
I hv no problem connecting oracle at localhost. but when connecting using another pc on the same LAN, the sqldeveloper returned an error saying
IO Error: The Network Adapter could not establish the connection on the linux where oracle installed, iptables is disable, but firewalld is enabled, so I did a
'firewall-cmd permanent zone=home --add-port=1521/tcp'
yet the remote pc still prompt the same IO error. what should I do to have oracle accept connection from the LAN and WAN ( I hv setup virtual server on router)
View 12 Replies
View Related
Feb 11, 2013
I am not able to connect to database due to ORA-257. So manually i deleted the archivelog files in diskgroup. however it is showing 13gb free space after deletiion. Archive log files generated due to Import job is running.
In below ways i have tried to connect to database.
1) Through SQLPLUS (Not able to Connect)
2) Through RMAN i tried to connect (Not able to connect)
3) I tried to connect impdp to stop the running job. It was also failed
View 7 Replies
View Related
Dec 23, 2010
I have installed oracle 10g on Red Hat Linux and I am able to connect as well.The listener and DB is up and running and I am able to coonect as well from the server.
But I have installed the oracle client (TOAD) on some windows machine and from there I am not able to connect . I am directly connecting by giving IP and other details and using TNS as well but I am not able to connect from client machine. I am getting ORA 12170 - TNS timeout error.
[root@rhel1 admin]# sqlplus system/system@db102
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Dec 23 11:16:04 2010
Copyright © 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
SQL>
View 1 Replies
View Related
Feb 23, 2009
I do not know how to create a connection between oracle to SQL server using topology manager on my application. I understand that I must create a logical and physical architecture to create the connection and the logical and physical architecture is running under topology manager.
View 1 Replies
View Related
Sep 27, 2013
Im getting this error when trying to connect toad to my test database using virtualbox. This is my first time trying to do this on a Mac as well:
java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
The settings in toad are correct as far as host, port #, and sid. My listener is up, and it is registered with the database. My tnsnames.ora file is correct. No firewall. I can ping my virtualbox from my local machine. I placed my tnsnames.ora file on my local machine, and tried to connect that way as well. I have no clue what else the Network adapter is looking for.
View 1 Replies
View Related
Jun 21, 2012
When we tried to query a data from the sql server we are getting the below generic error message i
SQL> select * from all_users@testing
2 /
select * from all_users@testlink1
*
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from TESTLINK1
below are the details setup / configuration done by us:
os :windows 2008 server.
database "oracle standard edition
Command Line input /output:
C:\Users\aorenstein>C:\oracle\product\11.2.0\db_1\bin\dg4odbc
Oracle Corporation --- WEDNESDAY JUN 20 2012 10:20:54.308
Heterogeneous Agent Release 11.2.0.1.0 - 64bit Production Built with
Oracle Database Gateway for ODBC
[code]....
View 1 Replies
View Related
Sep 25, 2011
Is it possible to connect 2 database schemes at the same time? I want to update a table from one scheme to another with the following update statement.
Update scheme1.table Set (col1, col2, col3, col4) = (select col1, col2, col3, col4 from scheme2.table);
If there is a way of doing this, I want to do this in some procedure?
View 22 Replies
View Related
Dec 13, 2011
I would like to join 2 tables which are both using valid_from valid_to.
CREATE TABLE TEST_HIST1
(
ID INTEGER,
DESC VARCHAR2(7 BYTE),
VALID_FROM DATE,
VALID_TO DATE
[code]...
The end result I would like to see is:
ID | Name | Descr | Valid_from | Valid_to
1 | Name1 | Descr1 | 2001/01/01 | 2001/12/31
1 | Name2 | Descr2 | 2002/01/01 | 2002/12/31
1 | Name2 | Descr3 | 2002/06/01 | 2002/12/31
1 | Name3 | Descr3 | 2003/01/01 | 2003/12/31
1 | Name3 | Descr4 | 2004/01/01 | 3000/12/31
View 10 Replies
View Related
Jan 11, 2005
I'm trying to connect to multiple Oracle 10g databases (failover) over the JDBC thin client, and when I run the following, I get: java.sql. SQLException: Io exception: NL Exception was generated
connection = DriverManager.getConnection("jdbc:oracle:thin:@DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = dbdev) (PORT = 1521))) (CONNECT_DATA = (INSTANCE_NAME = orcldev)))", "scott", "tiger");
I eventually want to put multiple ADDRESSes in that string, but right now I'm trying to get it to work with just one! I've tried multiple variations of this, like changing INSTANCE_NAME to SID or SERVICE_NAME, and using the internal IP address for the HOST dbdev (see below). Here's the tnsnames.ora file used by SQL*Plus on the same client machine:
ORCLDEV.world =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.132)(PORT = 1521))
(CONNECT_DATA =
(SID = orcldev)
)
)
View 7 Replies
View Related
Feb 25, 2010
Is it possible to connect SQL Server 2000 with Forms6i?
View 1 Replies
View Related
Nov 18, 2010
I have to connect to sql*plus to get explain plan.When I conect in Toad, the credentials are as:-
User/schema :- TestDb
Password :- TestDb
Under direct tab:-
Host :- 172.17.252.96 Port :- 1521
Service Name :- xe
I am not able to connect to sql*plus using:-
Username :- TestDb
Password :- TestDb
Host String :- 172.17.252.96
Not sure how to login when I have IP address
I also tried login as:-
Username :- TestDb
Password :- TestDb
Host String :- xe
View 5 Replies
View Related
Jul 18, 2011
while new form open i need to connect the data base.
View 5 Replies
View Related
Oct 24, 2010
I extracted the script of creating user in my database in SQL navigator and Toad tools but it is displaying encrypted password. But I am unable to connect with that password.
To get the original password then I will be able to connect my database.
View 3 Replies
View Related
Aug 26, 2010
How can I connect forms6i to run on SQL database ?
View 4 Replies
View Related
Sep 10, 2008
I'm writing PRO C program with oracle 8i. My process first connect to the database with using ora_connect(), proceed the task then diconnect with using ora_disconnect(). This would always in a loop, recently after few cycle of connect and disconnect, when the next issue connect statement, it was unable to connect and return me ora-00604 and the process end. Even i restart the process, after few cycle also the same return me ora-00604 during the connect.
View 3 Replies
View Related
Jul 17, 2012
OS: RHEL 5.7 64 bit
DB: 11.2.0.2 SE 64 bit
We have a scenairo in which we have two servers one is a Oracle server and the other is SQL server and we need to enable automated Emails from the oracle DB server. As the Oracle server is not whitelisted and we have a SQL server which is already whitelisted so is it possible to connect from Oracle server to Sql server so that the automated emails will be generated from the sql server (as it is whitelisted) and we do not want to whitelist the oracle server.
View 3 Replies
View Related
Aug 29, 2012
My database in on 10.2.0.3 and facing hang problem when I am connecting to sqlplus.
bash-3.00$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Aug 29 00:54:03 2012
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-01075: you are currently logged on
As per Metalink note(215858.1) suggested in forums I have followed steps and produced results below.
bash-3.00$ sqlplus -prelim
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Aug 28 23:28:02 2012
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Enter user-name: / as sysdba
SYS on test>oradebug help
SYS on test>oradebug setmypid
Statement processed.
SYS on test>oradebug hanganalyze 12
[code]...
Now I have got struck how to analyze above results.
View 3 Replies
View Related
Apr 1, 2013
I am upgrading an application from 9i to 11g and I've been told that the connect by prior sql is broken. While I am waiting for the example and the error message, is 11g pickier about looping than 9i was? some documentation on 10 or 11 changes to hierarchical queries?
View 1 Replies
View Related
