SQL & PL/SQL :: ORA-00979 In 11G But Works Fine In 10G?

Aug 1, 2011

I tested the SQL in 11G and get the ORA-00979 (not group by error) , the same SQL is working in 10G.

553322.1 - ORA-00979 IN 11.1.0.6 BUT NOT IN 9I OR 10G
814423.1 - ORA-00979 AFTER UPGRADE TO 11G

Do we have simple soluation for it if I don't have Oracle support account?

View 1 Replies


ADVERTISEMENT

Forms :: Saving As JFIF And GIF Works Fine Without Any Compression

Feb 5, 2013

I am using oracle forms 10g and basically we have a system that takes over 300 photos on a daily basis, this all works fine and with no issues except for say maybe 2-3 photos a month. Occasionally we will get a 'corrupt photo' (it not actually corrupt, it displays in everything as it should except forms) . When we encounter these photos forms just crashes out and the user is unable to query the record with the associated photo until it is deleted and a new one is taken (or alternatively if we take the photo from the database open it in paint.net and just hit save it will then work). There is no difference that we can see in the photo which doesnt work and those that do work. I have tried using WRITE_IMAGE_FILE to save the photo to disk and Read_Image_File to read from the disk to see if that makes a difference. If i save the file as jpeg and no compression it still crashes, if i save it with low compression it works fine but we lose quality which we dont want to lose. Bitmap wont work at all. Saving as JFIF and GIF works fine without any compression but we still lose quality.

The photo will display fine if we use a javabean to display it but in this instance a javabean is not an option.

One weird thing we noticed is that when we are on the form that crashes with these photos and query back a working photo first, and THEN query the 'corrupt photo' the corrupt photo displays fine, but if we go into the form and query a corrupt photo first forms crashes as explained.

View 1 Replies View Related

Application Express :: Tabular Form Works Fine For First Entries

Sep 28, 2012

Tabular form works fine for first entries, but upon going back to add more records Page 55 has a button that links to the 'Approval' page which is a tabular form (Page 56).A text field at top of this tabular form receives the pass of a varchar2 field from page X which is an identifier (not PK). Tabular form query has condition WHERE ISO_NUMBER = :P56_ISO_NUMBER

When I add records on the first pass to the tabular form and click submit all is fine and dandy.When I get out of the tabular form and go back to it and add additional records I get this error:

Error in mru internal routine: ORA-20001: Error in MRU: row= 1, ORA-20001: ORA-20001: Current version of data in database has changed since user initiated update process. current checksum = "C4B43B9A17A41D287D55DEFE9B035944", item checksum = "607C07E22C9B03E6A45AF44EDC06BB31"., update "SPICE_HUNTER1"."DOC_APPRV_DOC" set "APPRV_DOC_ID" = :b1, "DOC_INFO_ID" = :b2, "ISO_NUMBER" = :b3, "DOC_APPROVER" = :b4, "DOC_APPROVED" = :b5, "DOC_APPROVAL_DT" = :b6, "DOC_COMMENT" = :b7, "APP_USER" = :b8Here's what I've done to try to figure it out:

1. Ran debug and saw it was happening in my MRU; and
2. In the past what I have cleaned out tabular form table data because I've found that when I'm in this testing mode going back and forth with changes gets fouled up, but with a clean slate it still happens.
3. I had Hidden the ID fields, and then remembered this was one of the issues with versions of this error. (I actually logged this one to remember!). So I made them Display as Text (Saves State) and unchecked their boxes.

View 1 Replies View Related

SQL & PL/SQL :: ORA-00979 / Not A GROUP BY Expression

Jan 3, 2012

I wrote a query for minimum average salary of Job_id using inline view but it is not working..

The query is as follow

SELECT job_id, avg(salary)
FROM employees , (SELECT MIN(AVG(salary)) min_avg_sal
FROM employees
GROUP BY job_id) b
GROUP BY job_id
having avg(salary)=b.min_avg_sal;

It returns error as

################################
having avg(salary)=b.min_avg_sal
*
ERROR at line 6:
ORA-00979: not a GROUP BY expression
#########################

View 2 Replies View Related

SQL & PL/SQL :: ORA-00979 - Not A GROUP BY Expression At Runtime?

Oct 13, 2010

I ran into something that has me wondering about which errors are caught at compile-time, and which are caught at runtime. There is a pretty big package with lots of complicated queries that heavily use GROUP BY, and in the last couple of years I thought I never had the case of an ORA-00979 happening an RUNTIME, which is now happening.

The last heavy changes in that package did I make in the Oracle 9.2 days, and I *believe* (although my memory might be wrong) that back then the ORA-00979 errors came up at compile time.

So is it normal that the error doesn't show up during compile time, only during runtime, or should it be considered a bug?

SQL> CREATE TABLE test_tab(a NUMBER(1), b NUMBER(1));

Table created.

SQL> INSERT INTO test_tab VALUES (1,1);

1 row created.

SQL>
SQL> CREATE OR REPLACE PROCEDURE test_prod AS
2 v_a NUMBER;
3 v_b NUMBER;
4 BEGIN
5
6 SELECT a,b
7 INTO v_a, v_b
8 FROM test_tab
9 GROUP BY a;
10
11 END;
12 /

Procedure created.

SQL>
SQL> BEGIN
2 test_prod;
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
ORA-06512: at "PFK.TEST_PROD", line 6
ORA-06512: at line 2

View 5 Replies View Related

SQL & PL/SQL :: SP Executes Fine In One Server But Not In Another One

Jul 9, 2010

I have some trouble with a stored procedure I've created, when I run it in the server dedicated to developers it runs fine, and updates 100,000 record in 23 seconds, but when I run it in the production server it collapses and don't update anything. Here's my code.

CREATE OR REPLACE PROCEDURE cdc_sp_comp_tablas_paudit IS

TYPE t_folio_log IS TABLE OF cdc_compara_consulta_paudit.nconencfolio%TYPE;
v_folio_log t_folio_log;

CURSOR c_folios_bd IS
SELECT a.nconencfolio
FROM cdc_compara_consulta_paudit a
, cdc_consulta_encabezado b
WHERE a.nconencfolio = b.nconencfolio
[code].......

I'm using Oracle 9g,

View 10 Replies View Related

Fine Tune - Trim Down SQL To Within 255 Characters?

Feb 1, 2013

trim down the following sql to within 255 characters help:
select indate
from (
select case count(inputDate)
when 1 then inputdate
end as indate
from commLeaseBut5

[code]...

This sql is check a date field in the database for record which, if the date field is blank it should be a new record. Then the sql will assigned the current timestamp and stored to the new record. Otherwise, the sql will return the record timestamp for display.

View 7 Replies View Related

SQL & PL/SQL :: Fine Grain Access Control

Apr 23, 2010

How can i see all of the objects that are included on the policy of fine grain access control method?

View 2 Replies View Related

SQL & PL/SQL :: Tab Delimited Report Not Opening In Notepad - Fine In Textpad?

May 9, 2012

I've developed a TAB delimited sql report and the report is not opening well in notepad and the same report is opening good in textpad.

create table testex(tmple_tmpl_date date,column_string varchar2(20),cnt1 number,cnt2 number,amount number(22,2))
/
Insert into testex
(TMPLE_TMPL_DATE, COLUM_STRING, CNT1, CNT2, AMOUNT)
Values

[code]...

and here is the report code

SET FEEDBACK OFF
SET VERIFY OFF
SET UNDERLINE OFF
SET NEWPAGE NONE
SET LINESIZE 999

[code]...

View 6 Replies View Related

ORA-00932 - Sample Code Working Fine In 10g And Not Working Now In 11g

Apr 1, 2013

Below is the sample code working fine in 10g and not working now in 11g.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "PSTest" AS
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.util.List;
[code]....

we got the below error: ORA-00932: inconsistent datatypes: expected an IN argument at position 1 that is an instance of an Oracle type convertible to an instance of a user defined Java class got an Oracle type that could not be converted to a java class

Current Oracle version is Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit and the version we are upgrading is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit

View 3 Replies View Related

SQL & PL/SQL :: How Does Loop Works

Jun 27, 2011

[CODE]
DECLARE
D1 DATE:='&D1';
D2 DATE;
BEGIN
WHILE D1<=D2 LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D1,'DAY DD-MON-YYYY'));
D1:=D1+1;
END LOOP;
END;

Here I want to display all the seven days. One more question is here I'm d1<=d2 but d2 is null. So how does <= works here a null. A null is always null. What will be there in d2 how does the loop works with this comparision.

View 9 Replies View Related

SQL & PL/SQL :: Query Works In 10g But Not In 11g?

Apr 29, 2012

I have the query below that works absolutely fine in Oracle 10g but is not running in Oracle 11g. Couple of things I notices about this query.

1. If I remove the alias 'FACILITY_W_CODE' in the ORDER BY clause, query works fine in 11g. If I replace 'FACILITY_W_CODE' with the actual fields - 'F.SERVICE_AREA || F.DISTRICT_OFFICE || F.DO_GROUP || F.DO_SSC' even then it works fine.

2. If I remove the GROUP BY clause in the inside query but keep the alias 'FACILITY_W_CODE' in the outside ORDER BY clause, the query works fine.

I am not sure where the issue is, GROUP BY or the ALIAS. Also, the alias issue seems to be only where the alias is used for multiple fields.

SELECT F.SERVICE_AREA || F.DISTRICT_OFFICE || F.DO_GROUP || F.DO_SSC FACILITY_W_CODE, PF.FAC_TYPE FACILITY_TYPE, PF.FAC_IDENT LOCATION_IDENTIFIER, P.DISTRICT_CODE USER_W_CODE, P.EMAIL_ADDRESS, PF.CERTIFICATION_AUTHORITY, PF.CALLBACK_TECHNICIAN, PF.CALLBACK_ENVIRONMENTAL
FROM PEOPLE P
JOIN (
SELECT I_PF.ID, I_PF.FAC_TYPE, I_PF.FAC_IDENT,

[code]....

View 5 Replies View Related

PL/SQL :: How Where Condition Works

Jun 1, 2013

How oracle retrieves data from a table.like let say a table product as

create table product(id number,name varchar2(10))

table product dont have any index on it

now if this table conatain 500 rows and we have two query
1. select * from product where id=26
2. select * from product

does both query take same time or first query will execute in less time since it has where condition

View 2 Replies View Related

Code Works In Firefox But Not IE7?

Jan 29, 2008

I am fighting with some code to work in IE7. It is currently working in Firefox.

A little bit more on the context of the situation is that I am entering the code as a personalization on an Oracle Self Service Page. I am entering in as a raw text personalization.

[Code]
<html>
<head>
<style>
</style>
</head>
<body lang=EN-US style='tab-interval:.5in'>

[code].....

View 3 Replies View Related

SQL & PL/SQL :: DML Statements - How Does Merge Works

Jul 23, 2013

My teacher taught the lesson of DML statemnts, he told us how does merge works , but he did not give us any query for that,provide query for Merge and if possible then explain it too , I am using Oracle 10g Sql Plus.

View 3 Replies View Related

How Remap_datafile Parameter In IMPDP Works In 10g

Oct 15, 2010

I want to know how Remap_datafile paramater in IMPDP works in Oracle 10g. give me the whole scenario for two databases. One in Linux Platform and other in Windows platform. I want to import the tablesapce data from Linux to Windows Server database.

View 5 Replies View Related

SQL & PL/SQL :: How Oracle Background Process Works

Jul 12, 2011

how oracle background process works and in what sequence when we file an insert or update command?

View 4 Replies View Related

Forms :: Save Button Works Only Once?

Jun 26, 2012

declare
blk_name varchar2(60);
form_name varchar2(60);

[Code].....

this is my coding on save button. this is working fine but only once. when i enter the data second time and try to save, it didn't do anything. what should i do?

View 5 Replies View Related

SQL & PL/SQL :: SELECT Works But View Doesn't

Jun 11, 2012

I'm trying to create a view that includes both custom and delivered tables. The SQL code produces the desired results in SQL Developer. However, when I create and test the view using that code, I get very different and incorrect results. The SQL part of the view is below:

SELECT j.emplid, pt.deptid, pt.y_policy_id, pt.effdt, pt.title, pt.comments, pt.y_policy_covg, pt.doc_url
FROM psoprdefn a
, ps_job j
, ps_y_policy_tbl pt
WHERE substr(a.oprid,1,1) = '0'
AND a.emplid = j.emplid
AND j.job_indicator = 'P'
AND j.effdt = (SELECT MAX(effdt) FROM ps_job
WHERE emplid = j.emplid AND empl_rcd = j.empl_rcd AND effdt <= sysdate)
[code]....

View 8 Replies View Related

PL/SQL :: SQL Insert Works Delete Doesn't?

Jun 22, 2012

I have a website I am working on a database for, and to insert new data into the table is not a problem, even to output the table isn't an issue.

But a delete query won't work and it doesn't return any errors or echo commands that I put in the steps of the logic.

View 11 Replies View Related

Connection Using Sqlplus Works Locally And Not From Other Servers

Jul 27, 2012

I have a two node RAC on 11.2.0.3. We are using SCAN Listener here. On my node 1, i can connect to the database using tns alias: The alias looks something like:

Prod =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = x1200.prod.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cusms)
(UR = A)
)
)

sqlplus system/actdbasystem@cusms ----- works sucesfully on the prod server. Even the same prod alias copied to second tnsnames.ora in the cluster and run, it works sucesful too. But the issue is, when we copy the same alias onto any other servers and try to connect give's the following error message:

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 27 12:37:55 2012
Copyright © 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12519: TNS:no appropriate service handler found
[code]...

why is it my connection using sqlplus works locally and not from other servers?

View 2 Replies View Related

SQL & PL/SQL :: Rpad Function That Works For Multiple Chars?

Mar 6, 2011

How can I do this ?

is there an rpad that works for multiple chars?

select RPAD('test',10, 'X') from dual;

I want to be able to do something like

select RPAD('test',6, '%20') from dual;

which should return test%20%20

View 7 Replies View Related

SQL & PL/SQL :: Does Force View Option Works In Oracle 11g

Aug 13, 2010

when i am trying to excute below query.. it says ORA-00942: table or view does not exist.

create or replace force view v2 as select * from employee;

View 11 Replies View Related

Client Tools :: Spool In UTF8 Not Always Works

Oct 25, 2011

I have a problem spooling flat files in utf 8. I have 3 different sql that needs to be spooled regulary in utf8 format.the first two containing special chars is spooled without any problem, but the third that is containg only numbers and pipe-s is spooled in ansi format.

i have set nls_lang=.utf8 in my shell script, but that only worked at the first two sql the last doesnt work.

I have also tried to convert file using iconv command, but the output is still in ansi (only works if i put a special char in the ansi file then it converts to utf8 without problem) if i open the file in notepad and save as utf8 then it works, but i cannnot reach the same from unix script.

View 4 Replies View Related

SQL & PL/SQL :: How Does Operator Works For Varchar2 Datatype Columns

Nov 22, 2011

I have a table called Student and a column as name now if i write a Query

select * from student where name < 'BRIAN D'

How does the comparison will be done.

View 3 Replies View Related

Server Administration :: Whether Oracle 9i Works In Windows 2012

May 14, 2013

know whether Oracle 9i works in Windows 2012? As we are planning to upgrade.

View 1 Replies View Related

Precompilers, OCI & OCCI :: Running Query In PRO*C That Works In SQLPLUS

Sep 4, 2008

I have a query similar to the one below which is embedded in a PRO*C program. The query works fine when run in the PRO*C program against a 8i database but fails with an "ORA-02015: cannot select FOR UPDATE from remote table" error when run against a 10g database. The PRO*C program is executing the sql using "EXEC SQL".

QUERY: Select last_name, first_name from Member
where ....
FOR UPDATE OF LAST_NAME;

The other thing to note is this SQL query works fine via sqlplus in a 10g environment.

ADDITIONAL DETAILS: The above query is selecting data from a base table via a user view
VIEW: select * from
otherschema.member@connection_identifier;

This view was created in this manner to allow the user account access to the underlaying table without creating explicit permissions.

View 11 Replies View Related

Application Express :: Creating App That Works On Both Desktop And Mobile?

Nov 14, 2012

I know that there is now an option in the shared components to set the home page/theme for Desktop display as well as for Mobile display. So the app would automatically know what interface should be rendered based on the device running on. Do I need two versions of each app page (just like the login page) in order to have the app work on BOTH desktop and Mobile ? the reason I am asking is that an AEPX page can have one and only one template/theme. So if I have an app (desktop app) of 10 pages , should I replicate those 10 pages and make them mobile pages. If yes then how do I tell the to navigate differently (branch from page to page) based on the used device ?

View 3 Replies View Related

PL/SQL :: How Rownum Works For The Column Having Data Type As Varchar2

Jan 29, 2013

bookshelf_test table structure

title     varchar2(100)     y          
publisher     varchar2(20)     y          
categoryname     varchar2(20)     y          
rating     varchar2(2)     y

my query is,
select ROWNUM AS "Rank",title,publisher from (select rating,title,publisher from bookshelf_test order by rating desc ) where ROWNUM <=3

returns result ,

1     1     MY LEDGER     KOCH PRESS
2     2     TO KILL A MOCKINGBIRD     HARPERCOLLINS
3     3     THE MISMEASURE OF MAN     W.W. NORTON        

But inner query (select rating,title,publisher from bookshelf_test order by rating desc ) returns,

1     5     WONDERFUL LIFE     W.W.NORTON
2     5     THE MISMEASURE OF MAN     W.W. NORTON
3     5     TO KILL A MOCKINGBIRD     HARPERCOLLINS
4     5     MY LEDGER     KOCH PRESS
5     4     TRUMAN     SIMON SCHUSTER
6     4     GOSPEL     PICADOR
7     4     HARRY POTTER AND THE GOBLET OF FIRE     SCHOLASTIC
8     4     INNUMERACY     VINTAGE BOOKS
9     4     JOHN ADAMS     SIMON SCHUSTER
10     4     JOURNALS OF LEWIS AND CLARK     MARINER
11     4     LETTERS AND PAPERS FROM PRISON     SCRIBNER
12     4     PREACHING TO HEAD AND HEART     ABINGDON PRESS
13     4     THE SHIPPING NEWS     SIMON SCHUSTER
14     4     THE GOOD BOOK     BARD
15     4     THE DISCOVERERS     RANDOM HOUSE
16     3     THE COST OF DISCIPLESHIP     TOUCHSTONE
17     3     SHOELESS JOE     MARINER
18     3     KIERKEGAARD ANTHOLOGY     PRINCETON UNIV PR
19     3     EMMA WHO SAVED MY LIFE     ST MARTIN'S PRESS
20     3     EITHER/OR     PENGUIN
21     3     CHARLOTTE'S WEB     HARPERTROPHY
22     3     BOX SOCIALS     MARINER
23     3     ANNE OF GREEN GABLES     GRAMMERCY
24     3     WEST WITH THE NIGHT     NORTH POINT PRESS
25     3     UNDER THE EYE OF THE CLOCK     ARCADE PUB
26     3     TRUMPET OF THE SWAN     HARPERCOLLINS
27     2     COMPLETE POEMS OF JOHN KEATS     VIKING
28     1     POLAR EXPRESS     HOUGHTON MIFFLIN
29     1     GOOD DOG, CARL     LITTLE SIMON
30     1     MIDNIGHT MAGIC     SCHOLASTIC
31     1     RUNAWAY BUNNY     HARPERFESTIVAL

why final queries top 3 rows r different than inner query ?

View 3 Replies View Related

Windows / .NET :: Oracle ODBC As User Works But Fails As SYS

Feb 1, 2013

Windows 7
Oracle XE 11gR2

From Windows, I can successfully create and connect with a system data source using an user Oracle account.But when doing then same configuration process I can not create or connect using the 'SYS' account.

i get the error 'ORA-2009: connection as SYS should be as SYSDBA or SYSOPER'.

What is the solution to creating an ODBC DSN for the user 'SYS'?

View 6 Replies View Related







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