Help me in Oracle Stored procedure?

Question

I Create a Simple procedure in oracle.Here it is
-------------------------------------------------
CREATE OR REPLACE PROCEDURE
"INTERMEDIATE"."SP_PCMS_CH_DOC_STATUS_QTY" IS
BEGIN


INSERT INTO Intermediate.CH_DOC_STATUS_QTY
SELECT intermediate.cm_catalogue.STATUS, Count(intermediate.cm_catalogue.Ch_DOC_ID),
To_Date(intermediate.cm_catalogue.Create_Date,'dd-mm-yyyy')
FROM intermediate.cm_catalogue
GROUP BY intermediate.cm_catalogue.STATUS,To_Date(intermediate.cm_catalogue.Action_Date,'dd-mm-yyyy');
END;-------------------------------------
I need to Execute this stored Procedure. I use Sql *Plus Worksheet to run this. In Oracle Sql*Plus Worksheet i type like this.
Connect system/manager @myServer_name
EXECUTE intermediate.SP_PCMS_CH_DOC_STATUS_QTY
it show Error.: Not a Group BY Expression.

How to run this stored procedure in oracle and what's the error. please help

Answer 1

The error is, that "Create_Date" (which you select) is missing in the GROUP BY clause. (Syntactically, you don't need "Action_Date" within the GROUP BY clause - or do you want to select "Action_Date"?)



Additionally, you don't have to convert the ""Action_Date" within the GROUP BY clause - it will be grouped anyway.



From http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76999/e900.htm#1656



ORA-00979 not a GROUP BY expression



Cause: The GROUP BY clause does not contain all the expressions in the SELECT clause. SELECT expressions that are not included in a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must be listed in the GROUP BY clause.



Action: Include in the GROUP BY clause all SELECT expressions that are not group function arguments.

Answer 2

Have you tried running your select query outside the stored procedure to see if it is valid? It looks like you aren't grouping the column intermediate.cm_catalogue.STAT but are specifying it in the "group by" clause. Try removing it from the "group by".

Answer 3

Try to run the select part of ur query separately to see if it is giving u the expected result. If prob , Ur group_by is invalid! Check the constraints too!

Answer 4

you donto have a java saved technique. you in ordinary words have a dbms saved technique. all you do in java is call a callable fact and invoke a saved technique contained in the database