Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2003
    Location
    USA
    Posts
    4

    Question Unanswered: 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.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: oracle procedure help

    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

  3. #3
    Join Date
    Jan 2003
    Location
    USA
    Posts
    4

    Re: oracle procedure help

    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.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: oracle procedure help

    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:

    Code:
    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:

    Code:
    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;
    /

  5. #5
    Join Date
    Apr 2003
    Posts
    1

    Smile

    SQL>exec fix_disc_amt;
    or
    SQL>execute fix_disc_amt;

    semicolon is optional

  6. #6
    Join Date
    Aug 2002
    Posts
    4

    calling function on sql prompt...

    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;

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: calling function on sql prompt...

    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

  8. #8
    Join Date
    Aug 2002
    Posts
    4

    problem in calling function through jdbc..

    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);

  9. #9
    Join Date
    Mar 2004
    Location
    Indonesia
    Posts
    1

    newbie!!

    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

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Berwin,

    in Oracle, syntax for your procedure would be something like this:
    PHP Code:
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •