oracle procedure help

i have this procedure written and compiled. ran the procedure, came out with 'procedure created'. no compilation errors. Now, my question is how do I run/execute this procedure...? No parameters in this procedure.
my procedure name is FIX_DISC_AMT.

At the SQL prompt, I called this procedure
SQL> fix_disc_amt ();
did not work...! ! :-(

thanks in advance.Originally posted by hnvhelp
i have this procedure written and compiled. ran the procedure, came out with 'procedure created'. no compilation errors. Now, my question is how do I run/execute this procedure...? No parameters in this procedure.
my procedure name is FIX_DISC_AMT.

At the SQL prompt, I called this procedure
SQL> fix_disc_amt ();
did not work...! ! :-(

thanks in advance.
This is Oracle and SQL Plus, isn't it? Then:

SQL> exec fix_disc_amt|||Originally posted by andrewst
This is Oracle and SQL Plus, isn't it? Then:

SQL> exec fix_disc_amt

Thank you Tony..., it works. Now I have a new error/problem.
Yes, it is in Oracle7; PL/SQL Release 2.3.4.3.0

First here is my code for my create procedure:

CREATE OR REPLACE PROCEDURE fix_disc_amt
IS
CURSOR disc_amt_fix IS
SELECT cust_id, div_code, order_id, sum(total_demand_amt), sum(order_disc_amt)
FROM order_item
WHERE order_disc_amt > 0
GROUP BY cust_id, div_code, order_id;
BEGIN
OPEN disc_amt_fix;
DECLARE
xcustid NUMBER;
xdivcode CHAR;
xorderid CHAR;
xtotaldemand NUMBER;
xorderdiscamt NUMBER;
BEGIN
FETCH disc_amt_fix
INTO xcustid, xdivcode, xorderid, xtotaldemand, xorderdiscamt;
UPDATE order_item
SET disc_amt=xorderdiscamt * (total_demand_amt / xtotaldemand)
WHERE cust_id = xcustid
AND div_code = xdivcode
AND order_id = xorderid;
END;
END;
/

after creating the procedure, I executed. Here is the error I get:
SQL> exec fix_disc_amt
begin fix_disc_amt; end;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "PCT5.FIX_DISC_AMT", line 17
ORA-06512: at line 1

SQL>
Any idea, Please help.
Once again thank you for your assistance.|||Originally posted by hnvhelp
Thank you Tony..., it works. Now I have a new error/problem.
Yes, it is in Oracle7; PL/SQL Release 2.3.4.3.0

First here is my code for my create procedure:

CREATE OR REPLACE PROCEDURE fix_disc_amt
IS
CURSOR disc_amt_fix IS
SELECT cust_id, div_code, order_id, sum(total_demand_amt), sum(order_disc_amt)
FROM order_item
WHERE order_disc_amt > 0
GROUP BY cust_id, div_code, order_id;
BEGIN
OPEN disc_amt_fix;
DECLARE
xcustid NUMBER;
xdivcode CHAR;
xorderid CHAR;
xtotaldemand NUMBER;
xorderdiscamt NUMBER;
BEGIN
FETCH disc_amt_fix
INTO xcustid, xdivcode, xorderid, xtotaldemand, xorderdiscamt;
UPDATE order_item
SET disc_amt=xorderdiscamt * (total_demand_amt / xtotaldemand)
WHERE cust_id = xcustid
AND div_code = xdivcode
AND order_id = xorderid;
END;
END;
/

after creating the procedure, I executed. Here is the error I get:
SQL> exec fix_disc_amt
begin fix_disc_amt; end;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "PCT5.FIX_DISC_AMT", line 17
ORA-06512: at line 1

SQL>
Any idea, Please help.
Once again thank you for your assistance.
This could be either a string to number conversion error, or an overflow.
I suspect it is one of the variables declared as CHAR, since the length is not specified and defaults to 1.

The best way to avoid such errors is to "anchor" variables to the cursor or table being processed using %TYPE or (better) %ROWTYPE. I would do this:

CREATE OR REPLACE PROCEDURE fix_disc_amt
IS
CURSOR disc_amt_fix IS
SELECT cust_id, div_code, order_id, sum(total_demand_amt) sum_total_demand_amt, sum(order_disc_amt) sum_order_disc_amt
FROM order_item
WHERE order_disc_amt > 0
GROUP BY cust_id, div_code, order_id;
daf_rec disc_amt_fix%ROWTYPE;
BEGIN
OPEN disc_amt_fix;
FETCH disc_amt_fix
INTO daf_rec;
CLOSE disc_amt_fix;

UPDATE order_item
SET disc_amt=daf_rec.sum_order_disc_amt * (total_demand_amt / daf_rec.sum_total_demand_amt)
WHERE cust_id = daf_rec.cust_id
AND div_code = daf_rec.div_code
AND order_id = daf_rec.order_id;
END;
/

Actually, this is a strange piece of code, because it only fetches 1 row from the cursor - what if there is more than one? If you meant to process all rows it is better written using a FOR loop:

CREATE OR REPLACE PROCEDURE fix_disc_amt
IS
CURSOR disc_amt_fix IS
SELECT cust_id, div_code, order_id, sum(total_demand_amt) sum_total_demand_amt, sum(order_disc_amt) sum_order_disc_amt
FROM order_item
WHERE order_disc_amt > 0
GROUP BY cust_id, div_code, order_id;
BEGIN
FOR daf_rec IN disc_amt_fix LOOP
UPDATE order_item
SET disc_amt=daf_rec.sum_order_disc_amt * (total_demand_amt / daf_rec.sum_total_demand_amt)
WHERE cust_id = daf_rec.cust_id
AND div_code = daf_rec.div_code
AND order_id = daf_rec.order_id;
END LOOP;
END;
/|||SQL>exec fix_disc_amt;
or
SQL>execute fix_disc_amt;

semicolon is optional|||hi i have created a package and a function inside that package , this function returns a cursor
how can i call this function on sql prompt..
when i try to run using exec command it gives this error

SQL>exec test50pack.test50_select(10)
BEGIN test50pack.test50_select(10); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'TEST50_SELECT' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

could this be because the function is returning a cursor ... if this is the problem is there any way we can store the returned cursor and display the contents of that cursor on sql prompt...

code for creating the package and function is as follows..

create or replace package test50pack as
type owntype is ref cursor return test50%ROWTYPE;
function test50_select (uid NUMBER) return owntype;
end test50pack;

create or replace package body test50pack as function test50_select(uid NUMBER) return owntype is
rc owntype ;
begin
open rc for select * from test50 where col1 = uid;
return rc;
end;
end test50pack;|||Originally posted by prashantbist
hi i have created a package and a function inside that package , this function returns a cursor
how can i call this function on sql prompt..
when i try to run using exec command it gives this error

SQL>exec test50pack.test50_select(10)
BEGIN test50pack.test50_select(10); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'TEST50_SELECT' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

could this be because the function is returning a cursor ... if this is the problem is there any way we can store the returned cursor and display the contents of that cursor on sql prompt...

code for creating the package and function is as follows..

create or replace package test50pack as
type owntype is ref cursor return test50%ROWTYPE;
function test50_select (uid NUMBER) return owntype;
end test50pack;

create or replace package body test50pack as function test50_select(uid NUMBER) return owntype is
rc owntype ;
begin
open rc for select * from test50 where col1 = uid;
return rc;
end;
end test50pack;

In SQL Plus, do this:

SQL> variable c refcursor
SQL> exec :c := test50pack.test50_select(10)
SQL> print c|||Hi ..
i am trying to call a stored function in a package through jdbc.. but i am
getting this exception..

error code ORA-17059
java.sql.SQLException: Fail to convert to internal representation
at
com.tcs.mastercraft.mcutil.DM_errHandler.DMErrHand ler(DM_errHandler.java:307
)
at
com.tcs.mastercraft.mcutil.DM_errHandler.DMErrHand ler(DM_errHandler.java:94)
at DB2.PerformanceTest50.Get(PerformanceTest50.java:4 465)
at DB2.PerformanceTest50.Get(PerformanceTest50.java:4 593)
at DB2.PerformanceTest50.Oper_PTest50(PerformanceTest 50.java:5064)
at
DB2.PerformanceTest50_Oper_PTest50_305_drv.xlmain( PerformanceTest50_Oper_PTe
st50_305_drv.java:58)
at
DB2.PerformanceTest50_Oper_PTest50_305_drv.main(Pe rformanceTest50_Oper_PTest
50_305_drv.java:119)

The package and function are as follows..

create or replace package test50pack as
type owntype is ref cursor return test50%ROWTYPE;
function test50_select (uid NUMBER) return owntype;
end test50pack;

create or replace package body test50pack as function test50_select(uid
NUMBER) return owntype is
rc owntype ;
begin
open rc for select * from test50 where col1 = uid;
end;
end test50pack;

and my calling code is

cst = con.prepareCall ("{ ? = call test50pack.test50_select (?) }");
cst.registerOutParameter (1, OracleTypes.CURSOR);
cst.setInt (2, h_col1);
cst.execute ();
rs=(ResultSet)cst.getObject(1);|||Hi,

I am just begin to using oracle, now i am using oracle 9.2. Before oracle most of the time i only use MS-Sql Server 2000. I have one problem in here that i want to make some simple stored procedure.Say i just want to select sysdate from dual ,i want to put it into stored proc but when i try :
create or replace Test ()
as
begin
select sysdate from dual
end

that i believe it will work in sqlserver(beside the sysdate function and dual which sqlserver dont have),it not worked in oracle, i using TOAD as 3rd party application to access my oracle. So i hope that u can help me what the different in using stored proc in oracle and sqlserver , for example if i want to have the above querry to run in oracle

thanx|||Berwin,

in Oracle, syntax for your procedure would be something like this:

create or replace procedure test
as
dummy date;
begin
select sysdate into dummy from dual;
end;

You explicitly have to say it is a procedure; if it has no parameters you don't need brackets; you have to have INTO in such an example.