SQL & PL/SQL :: Translating Update Query In Access To Oracle

Apr 13, 2011

This is my working query in ms access...

UPDATE Caxnode AS A INNER JOIN Caxnode AS B ON A.node_alias = B.node_alias SET A.partition_Type = 'LDOM', A.node_mode = 'LOGICAL', A.host_id = b.host_id, A.num_of_proc = b.num_of_proc WHERE (((A.node_mode)='virtual' Or (A.node_mode)='regular') AND ((B.partition_Type)='LDOM'));

This doesn't work in oracle, I Googled and read that update doesn't work with inner join in oracle.

View 2 Replies


ADVERTISEMENT

Translating Query From SQL Server To Oracle Syntax

Sep 10, 2009

Translate following SQL query from SQL Server syntax to Oracle syntax.

SELECT ID,
[LMT(MTR)] = MAX(case when TYPE = 'LMT' then VALUE end),
[AAD(KGM)] = MAX(case when TYPE = 'AAD' then VALUE end),
[VOL(MTQ)] = MAX(case when TYPE = 'VOL' then VALUE end)
FROM yourtable
GROUP BY ID

View 2 Replies View Related

SQL & PL/SQL :: MS Access Query With Linked Tables To Oracle DB

Mar 2, 2011

The code was originally an MS Access Query with linked tables to the Oracle DB however trying to remove the need for access and have it drop directly into Excel using Recordset.

SELECT
SWPRO.CASE_INFORMATION.CASEDESC,
SWPRO.OUTSTANDING_MAIL.CASENUM,
CASE_DATA_1.FIELD_VALUE,
SWPRO.OUTSTANDING_MAIL.SENTDATE,
SWPRO.OUTSTANDING_MAIL.DEADDATE
FROM

[code]....

View 20 Replies View Related

Translating PL/SQL Into SQL 2000 Native Language?

Sep 14, 2007

I would need to translate the Oracle PL/SQL procedure to SQL Server 2000 SP4.

This is a package (body) that needs to be translated into a form that SQL server 2000 can unserstand it.

AS
FUNCTION object_name_fix (sobjectstring IN VARCHAR2)
RETURN VARCHAR2
AS

[Code].....

View 1 Replies View Related

SQL & PL/SQL :: UPDATE Query Opens Multiple Oracle Sessions

Feb 24, 2011

We recently migrated from 9i to 11g.

One UPDATE query runs for 2min and it opens multiple oracle sessions. Almost 40 sessions. Once the UPDATE is done, the sessions close.

When monitoring V$SESSION, found that the newly opened sessions have a PROGRAM value like "oracle@server (P001)"

The UPDATE query is as below. The query works fine, no issue with performance.

SET t1.text =
( SELECT text
FROM table2 t2
WHERE t1.col1 = t2.col1
AND t1.col2 = t2.col2
AND ROWNUM < 2
[code].......

View 13 Replies View Related

PL/SQL :: Oracle 11g / R2 Query Result Cache - Incremental Update

Oct 15, 2012

In Oracle 11g/R2, I created replica of HR.Employees table & executed the following statement (+Although using SUM() function is non-logical in this case, but just testifying the result+)

STEP - 1

SELECT      /+ RESULT_CACHE */ employee_id, first_name, last_name, SUM(salary)*
FROM           HR.Employees_copy
WHERE      department_id = 20
GROUP BY      employee_id, first_name, last_name;

EMPLOYEE_ID      FIRST_NAME LAST_NAME     SUM(SALARY)
-------------------------------------------------------------------------------------------------------
202           Pat           Fay          6000
201           Michael           Hartstein     13000

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3837552314
--------------------------------------------------------------------------------------------------
| Id | Operation           | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT      | | 2 | 130 | 4 (25)| 00:00:01 |
| 1 | RESULT CACHE      | 3acbj133x8qkq8f8m7zm0br3mu | | | | |
| 2 | HASH GROUP BY      | | 2 | 130 | 4 (25)| 00:00:01 |
|* 3 | TABLE ACCESS FULL     | EMPLOYEES_COPY | 2 | 130 | 3 (0)| 00:00:01 |
  
  Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
*690* bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

STEP - 2

INSERT INTO HR.employees_copy
VALUES(200, 'Dummy', 'User','Dummy.User@email.com',NULL, sysdate, 'MANAGER',5000, NULL,NULL,20);

STEP - 3

SELECT      /*+ RESULT_CACHE */ employee_id, first_name, last_name, SUM(salary)
FROM           HR.Employees_copy
WHERE      department_id = 20
GROUP BY      employee_id, first_name, last_name;

EMPLOYEE_ID      FIRST_NAME LAST_NAME SUM(SALARY)
--------------------------------------------------------------------------------------------------
202      Pat      Fay      6000
201      Michael      Hartstein      13000
200      Dummy User      5000

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 3837552314

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |          | 3 | 195 | 4 (25)| 00:00:01 |
| 1 | RESULT CACHE | 3acbj133x8qkq8f8m7zm0br3mu | | | | |
| 2 | HASH GROUP BY | | 3 | 195 | 4 (25)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMPLOYEES_COPY | 3 | 195 | 3 (0)| 00:00:01 |
    
 Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
*714* bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

In the execution plan of STEP-3, against ID-1 the operation RESULT CACHE is shown which shows the result has been retrieved directly from Result cache. Does this mean that Oracle Server has Incrementally Retrieved the resultset?

Because, before the execution of STEP-2, the cache contained only 2 records. Then 1 record was inserted but after STEP-3, a total of 3 records was returned from cache. Does this mean that newly inserted row is retrieved from database and merged to the cached result of STEP-1?

If Oracle server has incrementally retrieved and merged newly inserted record, what mechanism is being used by the Oracle to do so?

View 2 Replies View Related

PL/SQL Query To Import ACCESS Database

Jul 11, 2007

I am New to Oracle and PL/SQL. How to import ACCESS database to Oracle(10g) using PL/SQL.

View 1 Replies View Related

Application Express :: ORA-24247 / Network Access Denied By Access Control List

Apr 5, 2013

When I invoke SOAP Web-Service using APEX_WEB_SERVICE.MAKE_REQUEST, then I'm able to get response from web-service. However all German character are replaced by JUNK data. However data is coming fine when I test web-services using SOAP UI.

I tried to invoke web-service using UTL_HTTP. However when I use UTL_HTTP, then I'm getting following error.

ORA-24247: network access denied by access control list

<li> Why German characters are replaced by Junk data while invoking web-service from APEX, while it's working fine from SOAP UI

<li> When I can access web-service successfully using APEX_WEB_SERVICE, then why it's throwing ORA-24247 error when I call using UTL_HTTP?

DB: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
APEX: 4.0.2.00.07
Web-Server: EPG
SELECT * FROM NLS_DATABASE_PARAMETERS;

[code]...

View 4 Replies View Related

ORA-24247 / Network Access Denied By Access Control List (ACL)

Dec 12, 2012

I am having oracle database version 11.2.0.3.0 standard edition, where one of my users requirement is that he wanted to send mails from oracle database but he is getting below error

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 267
ORA-06512: at "SYS.UTL_SMTP", line 161
ORA-06512: at "SYS.UTL_SMTP", line 197
ORA-06512: at "HF_REPORTING.SEND_MAIL", line 12
ORA-06512: at line 18

is it possible to send mails from oracle database in STANDARD Edition?

View 1 Replies View Related

SQL & PL/SQL :: ORA-24247 / Network Access Denied By Access Control List (ACL)

Nov 15, 2011

While Running the proc,it's raising the below error.

Error report:

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 115
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "LISTER_SHIFT.SOLN_GENERIC_SENDMAIL", line 33
ORA-06512: at line 1

[code]....

View 12 Replies View Related

Oracle 11g Access From Different OS?

Feb 11, 2013

I have Oracle 11g installed in Windows 7 in a machine, now i have Linux distribution (OS) installed in the same machine(Dual boot). I have created database and have database tables available on the oracle database in Windows 7.

Is it possible to connect to the database from Linux distribution using shell script and access the data, execute a stored procedure etc?

View 10 Replies View Related

SQL & PL/SQL :: MS Access 97 To Oracle 10g

Jul 20, 2010

I have a plan to migrate all MS Access 97 based applications into Oracle as backend database and .Net as Front end. I have done migration of MS Access data to Oracle using export, linked tables, etc. from time to time.

View 10 Replies View Related

SQL & PL/SQL :: Update Query Using Join

Jun 26, 2012

How to update single table column using join query

Example:

Update table1 t1,table2 t2
set t1.column2 = 'Y'
where t1.column1 = t2.column1

View 8 Replies View Related

SQL & PL/SQL :: Update Query For 2 Lac Rows

Jun 12, 2013

I have 2 tables with 3 common columns (Col1, col2,Primary). One common column name is primary (oracle key word).Around 2 lakhs rows to be updated and No indexes are used on these tables. I need to write an update query as shown below.

Emp table

Col1col2primay
100101y
103104n
201105y
100101y

Dept table

Col1col2primay
100101null
103104null
000656null

Update query Result

Col1col2primary
100101y
103104n

View 16 Replies View Related

SQL & PL/SQL :: Update With Recursive Query

Aug 5, 2010

I have this SQL select to give me all the nodes of a tree starting at a particular node:

SELECT tree.node_id, LEVEL depth_level
FROM tree_of_nodes nodes
START WITH nodes.node_id = 1000
CONNECT BY PRIOR nodes.node_id = nodes.parent_id

I need to update a column called dept_level, which is essentially the same as Oracle's LEVEL. Something like this statement is what I need, but obviously this statement doesn't work:

update tree_of_nodes
set depth_level = LEVEL
START WITH nodes.node_id = 1000
CONNECT BY PRIOR nodes.node_id = nodes.parent_id

I've tried inline views and other approaches and have not found a method that works.

View 3 Replies View Related

Access Oracle DB Using Web Interface

Feb 10, 2012

We have implemented PeopleSoft with an Oracle database. What I need to do is provide clients with access to information stored in that database. Some additional information may come from other external databases, however for right now I am just trying to crawl, not run.

I am working on getting table information from the PS provider, so that I can understand what information is available. I am also working with the clients to determine if there are pieces of information that they need access to which are not currently native to the PS implementation. I need to identify these for step 2 of the process. My superiors were looking at using an SOA architecture using SOAP to move the data. I am open to suggestions and since the project is in its infancy, I have time to alter perceptions before it is too late.

View 1 Replies View Related

Access 2000 With Oracle 9i

Jan 23, 2008

I have linked all my tables in access 2000 to my oracle 9I database, created an odbc connection and I can open smaller tables, however when I try to open tables that are accessing more data from other tables, i get the error "ODBC -- call failed, [Microsoft][ODBC Driver Manager] Function sequence error (#0)" Am I using the wrong ODBC Driver? I installed the Oracle 10g SQL*Plus that should have included the correct driver for this setup.

The strange thing is that I do not think it is a driver issue because I can access the smaller tables in the set.

View 1 Replies View Related

Moving From MS Access To Oracle SQL Plus

Nov 20, 2012

I need to find information on the cost and difficulty in implementing the change from MS Access to Oracle.

View 3 Replies View Related

Simple Update Query Not Returning More Than One Row?

Sep 9, 2009

I've just started with the Oracle SQL and come from a heavy MS SQL background and I understand that here are some natural differences in the syntax but I'm stumped as to why the following sql represents a problem:

update MASTERMICODES t1
set t1.TEMPTA = ( select t2.TAFCODE
from TA_FEATURES t2
where t2.FCODE = t1.FCODE
)

It returns Error report:
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
*Cause:
*Action:

I want it to return more than one row...in fact I want it to make on all rows that have the same fcode between tables.

View 2 Replies View Related

SQL & PL/SQL :: Query To Update Current Value In DBA View To New One

Jul 18, 2012

I am looking to build a query to update a current value in a DBA view to a new one.i.e. updating directories based on the current value:

CREATE OR REPLACE DIRECTORY 'DIRECTORY_NAME' AS 'DIRECTORY_PATH'(substr(directory_path, 1,5) + '/&dbname' {i.e. this is different for every database name }+ 'DIRECTORY_PATH'(string after /xyz/)
WHERE DIRECTORY_NAME in
( select DIRECTORY_NAME
from DBA_DIRECTORIES
WHERE DIRECTORY_PATH
like '/xyz/%'
)

i.e. resulting output should be:

CREATE OR REPLACE DIRECTORY 'ABC' AS '/xyz/DBNAME/abc/def/';

(when the directory previously was 'xyz/abc/def/') i.e. basically inserting the db name into the directory.where DBNAME is a variable more directories are added frequently so therefore this needs to be a dynamic procedure to change the directories in the db.

View 26 Replies View Related

SQL & PL/SQL :: Query To Update Duplicate Records?

Sep 20, 2011

The requirement is I have a table (TAB1), wherein I have 3 columns, ID, LID and STATUS.

The value in ID column = ID_SEQ.NEXTVAL,and LID will be either 0 or 1 and the possible values for STATUS are 'ED','CP', NULL. The ID column is not suppose to have duplicate values, but there is no check on the table for the same.

Someone has updated the existing data and ID column is containing duplicate values. Wherever LID = 0 and STATUS = NULL and if only if ID is duplicated then the ID_SEQ.NEXTVAL has to be assigned to ID field, so that there are no more duplicate values.

CREATE TABLE tab1 (id NUMBER , lid NUMBER, status VARCHAR2(10));

Existing Data
------------------
INSERT INTO tab1 VALUES (1,0, 'ED');
INSERT INTO tab1 VALUES (1,0, 'CP');
INSERT INTO tab1 VALUES (1,0, NULL);
INSERT INTO tab1 VALUES (1,0, NULL);
INSERT INTO tab1 VALUES (1,0, NULL);
INSERT INTO tab1 VALUES (1,0, NULL);

[code]....

get the result using a single update statement.

View 5 Replies View Related

SQL & PL/SQL :: How To Update Two Tables In Single Set Or Query

Nov 22, 2011

How to update two tables in single set or single query ?

View 8 Replies View Related

SQL & PL/SQL :: Update Query Using Case When Exists?

Jan 4, 2013

I am trying to use the below query

update t_emp set TTL_FLG =
CASE
WHEN EXISTS
(SELECT 1 from Schema1.T_STG_LW_EMP E
WHERE E.Employee = Schema2.T_emp.EMPLOYEE_NUMBER
AND E.JB_CODE like '%TP%' or E.JB_CODE like '%DGD%' or E.JB_CODE like '%PDD%'
or E.JB_CODE like '%YND%'
)
THEN 'Y'
ELSE 'N'
END;

View 1 Replies View Related

Forms :: Query Update Check

Nov 19, 2010

We have a form which contain the multiple record.

I require to disable the record during the query, if the certain flag (Y) is activiate. And their as allow to display the next record without the flag (N) is editable.

how to done it.

View 1 Replies View Related

PL/SQL :: Update Statement In Join Query

Nov 16, 2012

I've seen this example numerous places, and tried to implement it, but I keep getting an "invalid identifier" error message, despite the fact that I've got the table and column specifically identified.For instance, my query reads like:

UPDATE tbl1
SET tbl1.EMPID =
(SELECT  tbl2.EMPIDA  FROM tbl2 
WHERE LOWER(tbl1.EMAILCOL) =  LOWER(tbl2.EMAILCOL2)
)
WHERE tbl2.EMPIDA IN ('Z1O435','S8M4722','M0D5156')
AND EXISTS
(SELECT tbl2.EMPIDA
FROM tbl2
WHERE  tbl1.EMAILCOL= tbl2.EMAILCOL2 );

But I'll keep getting flagged at the tbl2.EMPIDA column reference. I have not tried this in SQL Plus, just in TOAD, but it seems to repeatedly fail.I have had to dump records to standalone Access tables and link back to perform the updates.

View 12 Replies View Related

SQL & PL/SQL :: Update Query - Invalid Character

Mar 21, 2011

There is a table copper_terminal with columns

small_world_id,address_number,street_name.

I need to update small_world_id with address_number,street_name I am trying to write as

SQL> update COPPER_TERMINAL set smallworld_id=ADDRESS_NUMBER||_||STREET_NAME whe
re copper_terminal_id='45';
update COPPER_TERMINAL set smallworld_id=ADDRESS_NUMBER||,||STREET_NAME where co
pper_terminal_id='45'
*
ERROR at line 1: ORA-00911: invalid character

How to correct this??

View 4 Replies View Related

PL/SQL :: Update SAME Column Name In Two Tables From ONE Query

Oct 15, 2012

is it possible to update a same column name in two tables.

I have two tables in same schema

(1)table name
pem.igp_parent
column name
igp_no.
igp_type

(2)table name
pem.igp_child
column name
igp_no.
igp_type

i want to update igp_no column in one query, how it would be possible.

View 2 Replies View Related

Networking And Gateways :: MS Access To Oracle DB

Jul 13, 2010

I have an Access DB which I'm using to connect to an Oracle DB at work. There are several Oracle DB's that I can connect to using Access but this one I'm not able to connect to. I get the Oracle error ORA-25153 Temporary Table Space is empty.

I don't have the ability to make changes to the Oracle DB only to read and query.

View 1 Replies View Related

Oracle 10g On Window 7 - Cannot Access EM By Using Command

Mar 6, 2012

I installed oracle 10g version 10.2.0.1.0 on window 7 sqlplus is working but when i try to access oracle EM by using command "emctl start dbconsole" it give error

"OC4J Configuration issue. E:\oracle\oracle\product\10.2.0\db_1/oc4j/j2ee/OC4J_Console_192.168.1.3_orcl not found".

although i have assigned ip address as well .

View 3 Replies View Related

Update Statement - Query Hanging Or Processing

Oct 6, 2010

I am running one update statement which is running almost one hour still no response.

I would like to know either query is processing or hanging(suppose to finish the update within few minutes).

Is there any way or sql to find either the statement(my update query) is running or hanging.

View 4 Replies View Related







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