Accessing Something From Table Not Joined To Others

Jun 17, 2008

I have a schema whereby a table is not joined with other tables.

the info on that table can be gotten manually (by doing a query) and then using that info in another query. so is there a way of getting info from that table?

View 8 Replies


ADVERTISEMENT

SQL & PL/SQL :: Re-orientation Of Joined Table

Jul 22, 2010

I have the following, simplified, query involving 3 tables:

Table 1
ID---Student
1----Terry
2----Gilbert
3 ---Egbert
4 ---Norris

Table 2
ID--Base_ID--Base_Name
1 --F100 ------St. Vincents
3 --F102 ------AB Junior High
4 --F103 ------CD Middle School

Table 3
ID--Choice_order_ID--Secondary_Base_Choice
1---Co1-------------School A
1---Co2-------------School B
1---Co3-------------School C
2---Co1-------------School B
2---Co2-------------School A
2---Co3-------------(null)
3---Co1-------------School C
3---Co2-------------School A
3---Co3-------------School B

I use the following query:

select table1.student, table2.base_name
from table 1, table2,
where table1.id = table2.id (+)

which returns the student and the base name they attend, i.e.

Student---Base_Name
Terry-----St. Vincents
Gilbert----(null)

However, I need to also list their school Secondary_Base_Choices horizontally, in the following format,

I.e.

Student---Base_Name----Base_Choice_1---Base_Choice_2---Base_Choice_3
Terry-----St. Vincents----School A----------School B-----------School C
Gilbert----(NULL)---------'NO DATA'--------'NO DATA'----------'NO DATA'
Egbert----AB Junior H----School B----------School A-----------'NO DATA'

View 3 Replies View Related

SQL & PL/SQL :: ORA-01417 / A Table May Be Outer Joined To At Most One Other Table

Aug 16, 2012

I use a table taht i call C where the value of a field ("Type") is always a concat of values coming from more than 2 tables (A et C)

select A.Numero, B.date, B.commentaire,C.Libelle
from A, B, C
where A.codeLibelle = C.codeLibelle
and CONCAT(A.Numero, CONCAT(A.DemNumero, C.Libelle)) = B.Type (+)

when i execute this statement, i obtain

ORA-01417: a table may be outer joined to at most one other table

I have another request where it works fine and where i have concat of fields from only a single table:

select A.Numero, B.date, B.commentaire,C.Libelle
from A, B
where CONCAT(A.Numero, A.DemNumero) = B.Type (+)

In the first request Oracle seems to not accept a join with more than 2 tables

View 7 Replies View Related

SQL & PL/SQL :: ORA-01417 - A Table May Be Outer Joined To At Most One Other Table

Aug 19, 2010

SELECT *
FROM EMP E,DEPT D
WHERE D.DEPTNO = CASE WHEN COMM IS NULL THEN E.DEPTNO ELSE E.DEPTNO(+) END;

It is giving error on executing saying

ORA-01417 - a table may be outer joined to at most one other table.

Why it is giving this error,

How can i write such a query, since it my requirement

View 8 Replies View Related

SQL & PL/SQL :: Accessing Dmp Via External Table

Aug 5, 2011

when i am writing dump from external table, it is accessing records from dump.but when i am trying to access other dumps(create thru expdp) it is giving error.the logic i am following is mentioned below-

CREATE OR REPLACE DIRECTORY "DIR_GMS" AS 'D:Gopal_works est_env_files'

GRANT READ ON DIRECTORY dir_gms TO gopal;
GRANT WRITE ON DIRECTORY dir_gms TO gopal;

-- creating dump file in directory

CREATE TABLE emp_ext
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dir_gms
LOCATION ('emp_ext_dmp.dmp')
)
[code]......

i am able to see records.

New point:
-- taking export thru expdb
expdp hr/hr tables=EMPLOYEES directory=DIR_GMS dumpfile=HR_EMP.dmp logfile=expdpEMP.log
then i created one EXTERNAL TABLE TO access it.

CREATE TABLE emp_xt (
EMPLOYEE_ID NUMBER(5),
FIRST_NAME VARCHAR2(50),
LAST_NAME VARCHAR2(50))
ORGANIZATION EXTERNAL (
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dir_gms
LOCATION ('HR_EMP.DMP')
);

while accessing, it is giving error:

SELECT * FROM EMP_XT

ORA-29913: error IN executing ODCIEXTTABLEOPEN callout
ORA-31619: invalid DUMP FILE "D:Gopal_works est_env_filesHR_EMP.DMP"
ORA-06512: AT "SYS.ORACLE_DATAPUMP", line 19

View 13 Replies View Related

SQL & PL/SQL :: Accessing XML Values From A Table?

Jan 29, 2013

i have a table which has 2 columns.1st column has userId and the other contains an xml data as a link.on clicking that link a new file opens containing the data in xml format.

<fields>
<field key="Public Email">piyush@chand.com</field>
<field key="Location">bangalore</field>
<field key="Website" />
<field key="Birthday">0001-01-01 00:00:00</field>
<field key="Gender">Male</field>
<field key="Language">English</field>
</fields>

i need to access location of a particular userId.How can i do that?

View 6 Replies View Related

SQL & PL/SQL :: Accessing A Table Contains Array In Remote DB?

Jan 30, 2009

How to access (create Synonym and Materialized View) a Table contains an Array of type object which is on Remote Database connecting through DBLink.

View 27 Replies View Related

SQL & PL/SQL :: Accessing V$mystat Gives Table Or View Does Not Exist

Jul 1, 2010

I am using the sid of v$mystat to create a unique filename in my pl/sql procedure.

I have granted access to v$mystat to the user that is accessing it from system user as:

SQL>GRANT SELECT ON V_$MYSTAT TO ar;
Grant succeeded.

SQL> commit;

Commit complete.

now when i login as user ar and do a select on v$mystat it works fine:

SQL> select sid from v$mystat WHERE ROWNUM = 1;

SID
----------
290

However, when i do the same from my PL/SQL procedure it throws an error saying :

SQL> @FILECREATE
53 /

Warning: Function created with compilation errors.

SQL> show errors
Errors for FUNCTION FILECREATE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
22/8 PL/SQL: SQL Statement ignored
22/35 PL/SQL: ORA-00942: table or view does not exist

My PL/SQL function can be found as an attachment.

View 11 Replies View Related

Replication :: Block Corruption While Accessing Some Table

Mar 16, 2011

The database is running in archivelog mode and we have a standby with Maximum performance.There is no RMAN backup..We have noticed there is block corruption while accessing some tables.Now i would like to know are the corrupted blocks also replicated to the physical standby? Is there a way to recover the data from these corrupted blocks without shutting down the database ?

View 1 Replies View Related

SQL & PL/SQL :: Insert Statement In Trigger Accessing And A Select From A Table

Jun 6, 2010

In a trigger(on update of a table t1) I am trying to write, I am doing an insert on t2 accessing ':new' values of the update on t1.

But in my Insert statement, I am having get one of the column values from another table. How can I write my insert statement in such a way as to insert values contained in ':new' pseudo columns and a select from another table. Below is my insert statement in the trigger :
-------

IF (:old.GROUP_YELLOW <> :new.GROUP_YELLOW) THEN
INSERT INTO TEST.W_THRESHOLD_LOG
(THRESHOLD_LOG_WID, CHANGE_DATE, MEASURE_TYPE_WID, MEASURE_NAME, CUSTOMER_WID, CUSTOMER_NAME, USER_ID, CHANGED_ITEM, PREV_VALUE, NEW_VALUE)
VALUES(TEST.W_THRESHOLD_LOG_SEQ.NEXTVAL, SYSDATE, :new.MEASURE_TYPE_WID, 'Rolling Stabilty' , :new.CUSTOMER_WID, 'Customer1', 'User1', 'GROUP_YELLOW', :old.GROUP_YELLOW , :new.GROUP_YELLOW);
END IF;
-------

In the above code if the hardcoded value 'Customer1' need to be picked from another table,
i.e .

SELECT NAME FROM W_CUSTOMER_DIM WHERE CUSTOMER_WID = THRESHOLD.CUSTOMER_WID

how can I rewrite my query to the above value from the select into my insert statement..?

View 24 Replies View Related

SQL & PL/SQL :: Writing Procedure In User 2 Schema Accessing Table Product - Not Compiled?

Dec 17, 2010

I have got 2 users as user1 and user2.I have used the following statements from user 'user1':

create role GENEVAOBJECTS;
grant select, insert, update, delete on PRODUCT to GENEVAOBJECTS;
grant GENEVAOBJECTS to user2;

In the above statements, product is a table. Now, I could able to access this table from user 'user2'. But however if I write a procedure in user2 schema accessing the table product, then the procedure is not getting compiled.

create or replace procedure test_prc as
v_test number(9);
begin
select product_id into v_test
from PRODUCT where rownum=1;

[code]...

why I cannot access that table from procedure?

View 8 Replies View Related

SQL & PL/SQL :: Employees Joined In Each Month

Jan 28, 2011

getting the following kind of output.

I would like to get Each year and the number of employees joined in the corresponding month for jan, feb, mar and april from emp table.

A sample output looks like below.

YEAR JAN FEB MAR APRIL
2000 4 2 1 2
2001 2 1 1 5
2002 2 4 2 6
2004 2 4 1 4

View 7 Replies View Related

SQL & PL/SQL :: Query To Find Out Employees Who Are All Joined Before Manager

Jun 27, 2011

Query to find out employees who are all joined before Manager.

For Example the Table may look like.

EMPIDDesignationDateJoined
E101Programmer10-Jan-04
E102Programmer22-Mar-04
E103Analyst 14-Jan-05
E104Designer20-Dec-06
E105Tester 20-Nov-07
E106Manager 11-Oct-08
E107Programmer20-Nov-09
E108Coordinator12-Dec-10
E109DB Admin10-Feb-07
E110DB Analyst10-Aug-05

The out put must be..

EMPIDDesignationDateJoined
E101Programmer10-Jan-04
E102Programmer22-Mar-04
E103Analyst 14-Jan-05
E104Designer20-Dec-06
E105Tester 20-Nov-07
E109DB Admin10-Feb-07

View 4 Replies View Related

SQL & PL/SQL :: Group Functions - Number Of Employees Joined

Aug 21, 2013

I am having a table employees with columns

1.employee_id
2.department_id
3.hire_date

Display department ID, year, and Number of employees joined?

View 10 Replies View Related

Accessing File From Directory?

Oct 7, 2009

have a log file in unix server under(/usr/home/oraj/log/abc.log) I am trying to access from oracle stored procedure as fallows

src_clob BFILE := BFILENAME('/usr/home/oraj/log', 'abc.log');

Exception:
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 716
ORA-06512: at "usr.LOAD_CLOB_FROM_XML_FILE", line 39
ORA-06512: at line 1

I dont want to create directory as such ('/usr/home/oraj/log') as it is already exists and all log files sits there.

CREATE OR REPLACE PROCEDURE Load_CLOB_From_XML_File
IS
dest_clob CLOB;
src_clob BFILE := BFILENAME('/usr/home/oraj/log/', 'abc.log');
dst_offset number := 1 ;
src_offset number := 1 ;

[code]...

View 1 Replies View Related

SQL & PL/SQL :: Accessing Scripts From Different Directory?

Jun 30, 2010

I have customized my sqlprompt. I put the code for that in a script p.sql.

Now I log into sql from multiple directories as I have different scripts in different directories. How do I access p.sql which is not in the currect directory.

Also I wanted to know if I could change directories from the sql prompt.

View 6 Replies View Related

SQL & PL/SQL :: Using Index But Not Accessing Record

Jun 20, 2010

i'm create table

CREATE TABLE EQU_PARAM_MONITORINGX
(
SERIAL_NO VARCHAR2(32 BYTE) NOT NULL,

[Code]....

why accessing record not using index, but using full table scan?

View 2 Replies View Related

Accessing Oracle 11g R2 From A Client

May 2, 2013

I have installed Oracle 11g R2 on Oracle Linux and Oracle Instant Client on Windows 7. I am trying to access the server from the Oracle client but I am getting the following error:

ORA-12560: TNS: protocol adapter error

I have set the below TNSNAMES.ORA file on the client machine and the LISTENER.ORA file on the server:

#TNSNAMES.ORA

OCCELLUS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle.localdomain)(PORT = 1521))
)
CONNECT_DATA =
[code]........

The ORACLE_SID is OCELLUS.

The host name is oracle.localdomain

Pinging between the client/server works fine. I have turned off the firewall on both the client (Windows firewall) the Server (IPtables) but got the same problem!

I have also set the ORACLE_HOME, TNS_ADMIN and PATH in the Windows environment variables.

View 14 Replies View Related

JDeveloper, Java & XML :: Accessing XSD File Through PL/SQL

Feb 9, 2011

I wanted to know whether is there any utility which can

1. Create table from xml /xsd file.
2. insert records into the newly created table, through the given xml file.

View 1 Replies View Related

SQL & PL/SQL :: Accessing Data From Server By Using DBLINK

Oct 22, 2010

We are accessing data from the server ADM.WORLD by using DBLINK.We got the following error.

PL/SQL: ORA-04052: error occurred when looking up remote object
sysadm.PS_HP_INC_ELIG_VW@ADM.WORLD
ORA-00604: error occurred at recursive SQL level 1
ORA-28000: the account is locked
ORA-02063: preceding line from ADM

For that we checked in the server ADM.WORLD for the account the account is showing locked .After that we successfully accessed the object sysadm.PS_HP_INC_ELIG_VW@ADM.WORLD.

For next day also the account is locked.Why the account is frequently locking.

View 15 Replies View Related

Application Express :: Accessing APEX_COLLECTION

Jun 6, 2013

I am very new to APEX_COLLECTION. I have problem in accessing APEX_COLLECTION that I created. Below is the pl/sql code I have written:

declare
l_query varchar2(200);
cname varchar2(300);
Begin
APEX_COLLECTION.DELETE_COLLECTION(p_collection_name => 'ACTION_NAMES');
l_query := 'select name from test_table where id in (''n406'' , ''n409'' , ''d080'' , ''o4505'' , ''a1593'')';
[code]........   

It is throwing following error on execution:

ORA-01422: exact fetch returns more than requested number of rows I want to display the names collected using APEX_COLLECTION package and also use it in further processing within the pl/sql code block.

Apex info:

Application Express 4.1.0.00.32
DB details - Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Web server architecture - APEX listener
Browser(s) and version(s) used - Chrome version 24/ Firefox version 3.6 and version 18

View 4 Replies View Related

SQL & PL/SQL :: Format For Accessing Data From Other Database Or Schema?

Aug 25, 2011

Can we use this format for accesing data from other DB or Schema?

In From clause

database_name.schema_name.table_name

View 7 Replies View Related

Enterprise Manager :: Accessing OEM From A Remote Machine

Jan 25, 2011

I am having 3 oracle 10.2.0.5 version standard edition databases running on windows platform on 3 different servers. OEM is configured for all the 3 databases and we are able to access these OEM from their respective servers.

As per my knowledge, I should be able to access the OEMs of all 3 databases from my local machine. But Iam facing problem in accessing the OEMs from my local machine.

what changes need to be done so that I can access the OEMs of all 3 databases from one single local machine rather than checking it by logging into their respective servers.

View 7 Replies View Related

SQL & PL/SQL :: Create Materialized View For Accessing Data

Feb 29, 2012

1.As we can create materialized view for accessing data from other schema but same database. will it be effective or it will act as a normal view.

2.Will materialized views can be created in Fast mode for the above scenario?

View 4 Replies View Related

Forms :: Poor Performance In Accessing Remote Database?

May 13, 2010

I have installed Oracle 10g on one system and Oracle developer on another machine, means i have different machines for DB Server and Application server. It all working excellent inside the company premises, but if i want to access my Oracle DB and application server outside the company then it gives me problem..how to access application (forms and reports ) remotely outside the company...having same db.

View 2 Replies View Related

Windows :: Accessing Oracle 7 Database From Server 2008

Jan 11, 2013

We have been upgrading our servers to Server2008 and are getting..

[ORA-3134: Connections to this server version are no longer supported.]

..using the drivers we used to use in XP and Server2003 to access a legacy Oracle7 db. Connections to this db are needed for typical CRUD functionality by multiple applications, some written in Classic ASP and some in C# .NET 3.5 & 4.0. I have tried ODBC drivers (System.Data.Odbc) and also ODP (Oracle.DataAccess.Client) to no avail.

Any existing driver solution to make this connection without have to resort to a custom HLI interface?

I would think we aren't the only ones needing to access Oracle7 from Server2008.

View 7 Replies View Related

Server Administration :: System / Manager Not Accessing In Oracle9i?

May 31, 2010

Recently i am installed oracle9i in my laptop.In oracle scott/tiger accessing. But system/manager not accessing.

View 4 Replies View Related

Accessing Oracle Database From Windows Into RHEL (Linux)

Nov 12, 2012

We have a scenario where Oracle Database R1 is installed on Windows System and the Client is present on Linux. Both the system is on same network. We can access Windows -2-Windows using TNS entry.

I am unable to connect to this scenario where Database is on Windows and Client is on Linux.

View 8 Replies View Related

Accessing Oracle On VirtualBox From Host Virtual Machine?

Jul 18, 2013

I have a Host system of windows 8, installed the Virtual Box Latest edition.The guest OS is the oracle Linux that came with the Pre-Built Developer VMs from Oracle.The VM is set to HOST ONLY network setting. Now, the thing is that i want to access the Oracle in the guest machine from my windows host machine.the guest OS shows the IP address as 192.168.56.101. I can ping the IP address from the HOST machine, but cannot connect theSQL developer to the oracle in the guest OS from the host machine. It keeps on saying the Network adapter cannot connect. do not say that install the oracle in window itself. I actually want to do the same on my macbook pro as that is my primary machine.

View 10 Replies View Related

Networking And Gateways :: Accessing MS-ACCESS From Oracle Using Heterogeneous Service Agents

Nov 13, 2011

I have a requirement wherein I need to access the MS ACCESS database table from Oracle Client.I have created ODBC data source and have configured tnsnames.ora and listener.ora for the same. As a next step I am trying to configure HS init file for hsodbc connection but couldn't find the directory hs in $ORACLE_HOME (<ORACLE_HOME>hsadmininit******.ora). Any patch that needs to be applied for hsodbc connection in the server.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved