Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2004
    Location
    Jaipur, India
    Posts
    40

    Unanswered: Forcefully Break From Stored Procedure

    HI THERE,
    WELL I AM CREATING A PROCEDURE, THERE REQUIRED SOME FORCEFULLY CONDITIONAL BREAKING FROM THE PROCEDURE.
    I HAD RAISE THE EXCEPTIONS BUT IT SEEMS THAT THIS IS NOT WORKING. I AM SENDING THE CODE..
    IF ANYONE CAN PLEASE HELP.
    RGDS
    AMIT

    CODE:-----------------------------------------------------------
    create or replace procedure order_ins(
    custId orders.customerid%type,
    empId orders.employeeid%type,
    ordDt orders.orderdate%type,
    reqDt orders.requireddate%type,
    shpDt orders.shippeddate%type,
    Shpvia orders.shipvia%type,
    frght orders.freight%type,
    shpNm orders.shipname%type,
    shpADD orders.shipaddress%type,
    shpCity orders.shipcity%type,
    shpRegn orders.shipregion%type,
    shpPostCd orders.shippostalcode%type,
    shpCntry orders.shipcountry%type
    ) is



    ordId number(10);
    MaxId number(10);
    CountRec number;
    sErrDesc varchar2(50);

    BEGIN
    select count(*) into countrec from orders;

    if countrec = 0 then
    ordid := 1;
    else
    select max(orderid) into maxid from orders;
    maxid:= maxid + 1;
    end if;

    if length(trim(custid)) = NULL then
    sErrDesc := 'Customer Id can not be Blank';
    RAISE Blank_Entry;
    else if length(trim(empId)) = NULL then
    sErrDesc := 'Employee Id can not be Blank';
    RAISE Blank_Entry;
    else if length(trim(orddt)) = NULL then
    sErrDesc := 'Order Date can not be Blank';
    RAISE Blank_Entry;
    end if;


    insert into orders(
    orderid,
    customerid,
    employeeid,
    orderdate,
    requireddate,
    shippeddate,
    shipvia,
    freight,
    shipname,
    shipaddress,
    shipcity,
    shipregion,
    shippostalcode,
    shipcountry)
    values(
    ordid,
    custid,
    empid,
    orddt,
    reqdt,
    shpdt,
    shpvia,
    frght,
    shpnm,
    shpadd,
    shpcity,
    shpregn,
    shippostcd,
    shipcntry);

    COMMIT;
    EXCEPTION
    BEGIN
    WHEN Blank_Entry THEN
    DBMS_OUTPUT.PUT_LINE( 'ERROR : ' || sErrDesc);
    ROLLBACK;
    END;
    END;

  2. #2
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    declare the user-defined exception - blank_entry

    eg:
    Code:
    declare
       a number;
       invalid_a exception;
    begin
       a := 0;
       if a = 0 then
               raise invalid_a;
       end if;
    exception
       when invalid_a then
               dbms_output.put_line('error');
    end;
    you can also define the error number for this exception in declaration.
    Oracle can do wonders !

  3. #3
    Join Date
    Mar 2004
    Posts
    9

    Re: Forcefully Break From Stored Procedure

    Originally posted by amitkanodia
    HI THERE,
    WELL I AM CREATING A PROCEDURE, THERE REQUIRED SOME FORCEFULLY CONDITIONAL BREAKING FROM THE PROCEDURE.
    I HAD RAISE THE EXCEPTIONS BUT IT SEEMS THAT THIS IS NOT WORKING. I AM SENDING THE CODE..
    IF ANYONE CAN PLEASE HELP.
    RGDS
    AMIT

    CODE:-----------------------------------------------------------
    create or replace procedure order_ins(
    custId orders.customerid%type,
    empId orders.employeeid%type,
    ordDt orders.orderdate%type,
    reqDt orders.requireddate%type,
    shpDt orders.shippeddate%type,
    Shpvia orders.shipvia%type,
    frght orders.freight%type,
    shpNm orders.shipname%type,
    shpADD orders.shipaddress%type,
    shpCity orders.shipcity%type,
    shpRegn orders.shipregion%type,
    shpPostCd orders.shippostalcode%type,
    shpCntry orders.shipcountry%type
    ) is



    ordId number(10);
    MaxId number(10);
    CountRec number;
    sErrDesc varchar2(50);

    BEGIN
    select count(*) into countrec from orders;

    if countrec = 0 then
    ordid := 1;
    else
    select max(orderid) into maxid from orders;
    maxid:= maxid + 1;
    end if;

    if length(trim(custid)) = NULL then
    sErrDesc := 'Customer Id can not be Blank';
    RAISE Blank_Entry;
    else if length(trim(empId)) = NULL then
    sErrDesc := 'Employee Id can not be Blank';
    RAISE Blank_Entry;
    else if length(trim(orddt)) = NULL then
    sErrDesc := 'Order Date can not be Blank';
    RAISE Blank_Entry;
    end if;


    insert into orders(
    orderid,
    customerid,
    employeeid,
    orderdate,
    requireddate,
    shippeddate,
    shipvia,
    freight,
    shipname,
    shipaddress,
    shipcity,
    shipregion,
    shippostalcode,
    shipcountry)
    values(
    ordid,
    custid,
    empid,
    orddt,
    reqdt,
    shpdt,
    shpvia,
    frght,
    shpnm,
    shpadd,
    shpcity,
    shpregn,
    shippostcd,
    shipcntry);

    COMMIT;
    EXCEPTION
    BEGIN
    WHEN Blank_Entry THEN
    DBMS_OUTPUT.PUT_LINE( 'ERROR : ' || sErrDesc);
    ROLLBACK;
    END;
    END;

    I guess u should also initailize the variables used locally , for eg maxid... null + 1 will give you null

  4. #4
    Join Date
    Feb 2004
    Location
    Jaipur, India
    Posts
    40
    Thanks for ur help but after declaring the user defined exception it gives the following error:
    -------
    79/2 PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
    of the following:
    begin declare end exit for goto if loop mod null pragma raise
    return select update while <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> <<
    close current delete fetch lock insert open rollback
    savepoint set sql execute commit forall
    <a single-quoted SQL string>
    --------
    Please advice
    RGDS
    Amit


    Originally posted by cmasharma
    declare the user-defined exception - blank_entry

    eg:
    Code:
    declare
       a number;
       invalid_a exception;
    begin
       a := 0;
       if a = 0 then
               raise invalid_a;
       end if;
    exception
       when invalid_a then
               dbms_output.put_line('error');
    end;
    you can also define the error number for this exception in declaration.

  5. #5
    Join Date
    Feb 2004
    Location
    Jaipur, India
    Posts
    40

    Re: Forcefully Break From Stored Procedure

    Thanks for ur advice. But initializing too can't help... n the same error occurs..
    rgds
    Amit
    Originally posted by visheetal
    I guess u should also initailize the variables used locally , for eg maxid... null + 1 will give you null

  6. #6
    Join Date
    Mar 2004
    Posts
    9
    Originally posted by amitkanodia
    Thanks for ur help but after declaring the user defined exception it gives the following error:
    -------
    79/2 PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
    of the following:
    begin declare end exit for goto if loop mod null pragma raise
    return select update while <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> <<
    close current delete fetch lock insert open rollback
    savepoint set sql execute commit forall
    <a single-quoted SQL string>
    --------
    Please advice
    RGDS
    Amit
    Amit

    I ran the code posted here but did not get any error...

  7. #7
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Originally posted by amitkanodia
    Thanks for ur help but after declaring the user defined exception it gives the following error:
    -------
    79/2 PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
    of the following:
    begin declare end exit for goto if loop mod null pragma raise
    return select update while <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> <<
    close current delete fetch lock insert open rollback
    savepoint set sql execute commit forall
    <a single-quoted SQL string>
    --------
    Please advice
    RGDS
    Amit
    post the modified code. Ensure that your exception declaration is in the declare block. And is terminated with a ;

    syntax is

    Code:
    declare
      ...
      ...
    begin
      ...
      ...
    exception
      ...
      ...
    end;
    /
    your error indicates that it is a syntax error, maybe something missing or something at wrong place.
    Oracle can do wonders !

  8. #8
    Join Date
    Feb 2004
    Location
    Jaipur, India
    Posts
    40
    What have u done??
    Please specify...
    RGDS
    Amit
    Originally posted by visheetal
    Amit

    I ran the code posted here but did not get any error...

  9. #9
    Join Date
    Feb 2004
    Location
    Jaipur, India
    Posts
    40
    The modified code is as follows----
    ----------
    create or replace procedure order_ins(
    custId orders.customerid%type,
    empId orders.employeeid%type,
    ordDt orders.orderdate%type,
    reqDt orders.requireddate%type,
    shpDt orders.shippeddate%type,
    Shpvia orders.shipvia%type,
    frght orders.freight%type,
    shpNm orders.shipname%type,
    shpADD orders.shipaddress%type,
    shpCity orders.shipcity%type,
    shpRegn orders.shipregion%type,
    shpPostCd orders.shippostalcode%type,
    shpCntry orders.shipcountry%type
    ) is



    ordId number(10) := 0;
    MaxId number(10) := 0;
    CountRec number := 0;
    sErrDesc varchar2(50);
    Blank_Entry EXCEPTION;

    BEGIN
    select count(*) into countrec from orders;

    if countrec = 0 then
    ordid := 1;
    else
    select max(orderid) into maxid from orders;
    maxid:= maxid + 1;
    end if;

    if length(trim(custid)) = NULL then
    sErrDesc := 'Customer Id can not be Blank';
    RAIse Blank_Entry;
    else if length(trim(empId)) = NULL then
    sErrDesc := 'Employee Id can not be Blank';
    RAISE Blank_Entry;
    else if length(trim(orddt)) = NULL then
    sErrDesc := 'Order Date can not be Blank';
    RAISE Blank_Entry;
    end if;


    insert into orders(
    orderid,
    customerid,
    employeeid,
    orderdate,
    requireddate,
    shippeddate,
    shipvia,
    freight,
    shipname,
    shipaddress,
    shipcity,
    shipregion,
    shippostalcode,
    shipcountry)
    values(
    ordid,
    custid,
    empid,
    orddt,
    reqdt,
    shpdt,
    shpvia,
    frght,
    shpnm,
    shpadd,
    shpcity,
    shpregn,
    shppostcd,
    shpcntry);

    COMMIT;
    EXCEPTION
    BEGIN
    WHEN Blank_Entry THEN
    DBMS_OUTPUT.PUT_LINE( 'ERROR : ' || sErrDesc);
    ROLLBACK;
    END;
    END;
    ---------------
    the exception declaration is in the declare block

    Originally posted by cmasharma
    post the modified code. Ensure that your exception declaration is in the declare block. And is terminated with a ;

    syntax is

    Code:
    declare
      ...
      ...
    begin
      ...
      ...
    exception
      ...
      ...
    end;
    /
    your error indicates that it is a syntax error, maybe something missing or something at wrong place.

  10. #10
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171

    Re: Forcefully Break From Stored Procedure

    Originally posted by visheetal
    I guess u should also initailize the variables used locally , for eg maxid... null + 1 will give you null
    Not required here. In this case, select into the variable is doing the initialization.
    Oracle can do wonders !

  11. #11
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Originally posted by amitkanodia
    The modified code is as follows----
    ----------
    create or replace procedure order_ins(
    custId orders.customerid%type,
    empId orders.employeeid%type,
    ordDt orders.orderdate%type,
    reqDt orders.requireddate%type,
    shpDt orders.shippeddate%type,
    Shpvia orders.shipvia%type,
    frght orders.freight%type,
    shpNm orders.shipname%type,
    shpADD orders.shipaddress%type,
    shpCity orders.shipcity%type,
    shpRegn orders.shipregion%type,
    shpPostCd orders.shippostalcode%type,
    shpCntry orders.shipcountry%type
    ) is



    ordId number(10) := 0;
    MaxId number(10) := 0;
    CountRec number := 0;
    sErrDesc varchar2(50);
    Blank_Entry EXCEPTION;

    BEGIN
    select count(*) into countrec from orders;

    if countrec = 0 then
    ordid := 1;
    else
    select max(orderid) into maxid from orders;
    maxid:= maxid + 1;
    end if;

    if length(trim(custid)) = NULL then
    sErrDesc := 'Customer Id can not be Blank';
    RAIse Blank_Entry;
    else if length(trim(empId)) = NULL then
    sErrDesc := 'Employee Id can not be Blank';
    RAISE Blank_Entry;
    else if length(trim(orddt)) = NULL then
    sErrDesc := 'Order Date can not be Blank';
    RAISE Blank_Entry;
    end if;


    insert into orders(
    orderid,
    customerid,
    employeeid,
    orderdate,
    requireddate,
    shippeddate,
    shipvia,
    freight,
    shipname,
    shipaddress,
    shipcity,
    shipregion,
    shippostalcode,
    shipcountry)
    values(
    ordid,
    custid,
    empid,
    orddt,
    reqdt,
    shpdt,
    shpvia,
    frght,
    shpnm,
    shpadd,
    shpcity,
    shpregn,
    shppostcd,
    shpcntry);

    COMMIT;
    EXCEPTION
    BEGIN
    WHEN Blank_Entry THEN
    DBMS_OUTPUT.PUT_LINE( 'ERROR : ' || sErrDesc);
    ROLLBACK;
    END;
    END;
    ---------------
    the exception declaration is in the declare block
    In this code I see error
    "PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:
    pragma when"

    which is because you have a BEGIN in your EXCEPTION block... remove begin/end in the exception block.
    Oracle can do wonders !

  12. #12
    Join Date
    Feb 2004
    Location
    Jaipur, India
    Posts
    40
    In the case when i remove the BEGIN and END in the exception block then there also occurs the end-of-file error....
    i am giving the errors.
    -----
    80/2 PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
    of the following:
    begin declare end exit for goto if loop mod null pragma raise
    return select update while <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> <<
    close current delete fetch lock insert open rollback
    savepoint set sql execute commit forall
    <a single-quoted SQL string>

    88/0 PLS-00103: Encountered the symbol "end-of-file" when expecting
    one of the following:
    begin function package pragma procedure form
    --------------

    Originally posted by cmasharma
    In this code I see error
    "PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:
    pragma when"

    which is because you have a BEGIN in your EXCEPTION block... remove begin/end in the exception block.

  13. #13
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    oh dear .. my mistake.

    I was all the time presenting a plsql block whereas you are having a stored proc.

    here is a quote from the PLSQL users's guide reference

    Exceptions can be declared only in the declarative part of a PL/SQL block, subprogram, or package. You declare an exception by introducing its name, followed by the keyword EXCEPTION. In the following example, you declare an exception named past_due:
    for a stored proc, you either create a package, declare it in package and then use in your stored proc. Else do how the guide sugegsts:

    PHP Code:
    CREATE PROCEDURE raise_salary (emp_id NUMBERincrease NUMBER) AS
       
    current_salary NUMBER;
    BEGIN
       SELECT sal INTO current_salary FROM emp 
          WHERE empno 
    emp_id;
       IF 
    current_salary IS NULL THEN
          
    /* Issue user-defined error message. */
          
    raise_application_error(-20101'Salary is missing');
       ELSE
          
    UPDATE emp SET sal current_salary increase
             WHERE empno 
    emp_id;
       
    END IF;
    END raise_salary
    Refer:
    PL/SQL User's Guide and Reference, chapter 6 - error handling.
    Oracle can do wonders !

  14. #14
    Join Date
    Mar 2004
    Posts
    50

    Arrow

    Hi ,

    The problem is not in the exception, but somewhere else.

    Look at the 'If' clause.. it has an 'Else If' clause..
    there is no 'Else If ' clause in Oracle. The correct clause is 'ElsIf'.
    Thats the one that is causing this problem.

    And the exceptions can be declared in procedures too..
    try the code below.. it will work fine.
    However, if u replace the Elsif by Else If , then u'll start getting the same error that you were getting.


    CREATE OR REPLACE PROCEDURE p IS
    v_num NUMBER;
    my_exception EXCEPTION;
    BEGIN
    v_num:=0;
    IF (v_num=0) THEN
    RAISE my_exception;
    ELSIF v_num<> 0 THEN
    dbms_output.put_line('hi');
    END IF;
    EXCEPTION
    WHEN my_exception THEN
    dbms_output.put_line ('hello');
    END;


    Ishan.

  15. #15
    Join Date
    Feb 2004
    Location
    Jaipur, India
    Posts
    40
    Hi Ishan,
    Thanx a lot.. I really appreciate this..
    RGDS
    Amit
    Originally posted by ishanbansal
    Hi ,

    The problem is not in the exception, but somewhere else.

    Look at the 'If' clause.. it has an 'Else If' clause..
    there is no 'Else If ' clause in Oracle. The correct clause is 'ElsIf'.
    Thats the one that is causing this problem.

    And the exceptions can be declared in procedures too..
    try the code below.. it will work fine.
    However, if u replace the Elsif by Else If , then u'll start getting the same error that you were getting.


    CREATE OR REPLACE PROCEDURE p IS
    v_num NUMBER;
    my_exception EXCEPTION;
    BEGIN
    v_num:=0;
    IF (v_num=0) THEN
    RAISE my_exception;
    ELSIF v_num<> 0 THEN
    dbms_output.put_line('hi');
    END IF;
    EXCEPTION
    WHEN my_exception THEN
    dbms_output.put_line ('hello');
    END;


    Ishan.

Posting Permissions

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