Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    Join Date
    Dec 2003
    Posts
    42

    Unanswered: execute procedure with out parameters

    Hi,
    i want to execute a procedure which contains all out parameters. please let me know how to execute the procedure.

    create or replace procedure kk(name OUT varchar2,age OUt varchar2,addres OUT varchar2) is
    -- -
    --
    --
    --
    -end;
    /

    please tell me how to execute this procedure

    thank you

  2. #2
    Join Date
    Nov 2003
    Posts
    87
    You can call your procedure like this

    declare
    p1 varchar2(20);
    p2 varchar2(3);
    p3 varchar2(200);
    begin
    kk(p1,p2,p3);
    end;

  3. #3
    Join Date
    Jan 2004
    Location
    India
    Posts
    62

    Smile

    Or you can create variable on the sql prompt and pass them as out parameters while calling the procedure.
    sql > variable v1 varchar2 (30)
    sql > variable v2 varchar2 (3)
    sql > variable v3 varchar2 (200)
    sql > set autoprint on
    sql > exec kk (:v1, :v2, :v3)

    This will display the result of v1, v2, v3 on the sql prompt.
    This method is useful only if you want to execute the proc from sql prompt.
    If you do not set autoprint on, then you can display the values of v1, v2, v3 by using
    print v1
    print v2 like this.
    Regards,

    Rushi

  4. #4
    Join Date
    May 2010
    Posts
    18
    select * from s_org_ext where hier_level=v_hier;
    is this statement true or false
    i m executing this in cursor
    error is coming in this line
    that = is d error
    can u tell me which is the comparison operator in plpsql?

  5. #5
    Join Date
    May 2010
    Posts
    18
    PLS-00103: Encountered the symbol "SORGEXT_CUR" when expecting
    one of the following:
    := . ( @ % ;

  6. #6
    Join Date
    May 2010
    Location
    Bangalore
    Posts
    16

    Thumbs up

    Hi N.Rekha Reddy
    The Assignment Operator in PLSQL is :=
    The error message too suggests the same.

    Thanks,
    Richa


    Quote Originally Posted by N.REKHA REDDY View Post
    PLS-00103: Encountered the symbol "SORGEXT_CUR" when expecting
    one of the following:
    := . ( @ % ;

  7. #7
    Join Date
    May 2010
    Posts
    18
    HI,
    RIcha,
    thanks for ur reply........i have 1 doubt...
    := is assignment operator
    i want to compare two variables,so i am using = operator
    but it is showing that error, is der are any other operator to compare in plsql,if der plz tell me.

  8. #8
    Join Date
    May 2010
    Location
    Bangalore
    Posts
    16
    Can you please send me the exact code?

    Quote Originally Posted by N.REKHA REDDY View Post
    HI,
    RIcha,
    thanks for ur reply........i have 1 doubt...
    := is assignment operator
    i want to compare two variables,so i am using = operator
    but it is showing that error, is der are any other operator to compare in plsql,if der plz tell me.

  9. #9
    Join Date
    May 2010
    Posts
    18
    k,i m sending the following code,

    create or replace procedure batch_update

    is

    v_hier number(38):=1;


    vmax_hier number(3):=5;

    begin

    loop

    cursor sorgext_cur

    is

    select * from s_org_ext where hier_level=v_hier ;

    open sorgext_cur;

    CURSOR sorgext1_cur IS SELECT * FROM s_org_ext;
    var_rows number(3);
    batches number(3) := 1;

    batch_size number(3) := 50;
    sorgext_rec s_org_ext%ROWTYPE;



    begin
    dbms_output.put_line ('********************************** ');
    dbms_output.put_line ('The sorgext id will be updated with batch number and batch record number. Each batch will have ' || batch_size || ' number of records.');
    dbms_output.put_line ('---------------------------------- ');

    open sorgext1_cur;

    loop


    EXIT WHEN sorgext1_cur%NOTFOUND ;

    for counter in 1 .. batch_size loop

    fetch sorgext1_cur into sorgext1_rec;


    var_rows := account1_cur%rowcount;

    update s_org_ext set batch_record_num = var_rows where id= sorgext1_rec.id;

    update s_org_ext set batch_number = batches where id= sorgext1_rec.id;

    end loop;

    dbms_output.put_line ('Batch number '|| Batches || ' completed ...... Number of records updated = ' || var_rows || '.');

    batches := batches + 1;



    end loop;


    close sorgext1_cur;

    EXCEPTION

    WHEN OTHERS THEN
    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
    commit;

    end ;

    v_hier:=v_hier+1;

    exit when vmax_hier=v_hier;

    close sorgext_cur;


    end loop;


    exception

    WHEN OTHERS THEN

    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

    commit;

    end;

  10. #10
    Join Date
    May 2010
    Location
    Bangalore
    Posts
    16
    Hi,
    Try closing the cursor "close sorgext_cur;" before EXCEPTION and let me know whether it works or not.
    I'm not sure this might be the problem but there are chances. Lets try this and solve the problem step by step.

    Thanks
    Richa

    Quote Originally Posted by N.REKHA REDDY View Post
    k,i m sending the following code,

    create or replace procedure batch_update

    is

    v_hier number(38):=1;


    vmax_hier number(3):=5;

    begin

    loop

    cursor sorgext_cur

    is

    select * from s_org_ext where hier_level=v_hier ;

    open sorgext_cur;

    CURSOR sorgext1_cur IS SELECT * FROM s_org_ext;
    var_rows number(3);
    batches number(3) := 1;

    batch_size number(3) := 50;
    sorgext_rec s_org_ext%ROWTYPE;



    begin
    dbms_output.put_line ('********************************** ');
    dbms_output.put_line ('The sorgext id will be updated with batch number and batch record number. Each batch will have ' || batch_size || ' number of records.');
    dbms_output.put_line ('---------------------------------- ');

    open sorgext1_cur;

    loop


    EXIT WHEN sorgext1_cur%NOTFOUND ;

    for counter in 1 .. batch_size loop

    fetch sorgext1_cur into sorgext1_rec;


    var_rows := account1_cur%rowcount;

    update s_org_ext set batch_record_num = var_rows where id= sorgext1_rec.id;

    update s_org_ext set batch_number = batches where id= sorgext1_rec.id;

    end loop;

    dbms_output.put_line ('Batch number '|| Batches || ' completed ...... Number of records updated = ' || var_rows || '.');

    batches := batches + 1;



    end loop;


    close sorgext1_cur;

    EXCEPTION

    WHEN OTHERS THEN
    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
    commit;

    end ;

    v_hier:=v_hier+1;

    exit when vmax_hier=v_hier;

    close sorgext_cur;


    end loop;


    exception

    WHEN OTHERS THEN

    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

    commit;

    end;

  11. #11
    Join Date
    May 2010
    Posts
    18
    can u tell me which exception before, and i tried it is not working


    actually i have opened the cursor in loop, so i have to close the cursor before the loop right?

  12. #12
    Join Date
    May 2010
    Location
    Bangalore
    Posts
    16
    Copy this code - check n let me know.
    I think this too will give the error - but want to cross check.

    Thanks
    Richa

    ================================================== =======
    create or replace procedure batch_update

    is

    v_hier number(38):=1;


    vmax_hier number(3):=5;

    begin

    loop

    cursor sorgext_cur

    is

    select * from s_org_ext where hier_level=v_hier ;

    open sorgext_cur;

    CURSOR sorgext1_cur IS SELECT * FROM s_org_ext;
    var_rows number(3);
    batches number(3) := 1;

    batch_size number(3) := 50;
    sorgext_rec s_org_ext%ROWTYPE;



    begin
    dbms_output.put_line ('********************************** ');
    dbms_output.put_line ('The sorgext id will be updated with batch number and batch record number. Each batch will have ' || batch_size || ' number of records.');
    dbms_output.put_line ('---------------------------------- ');

    open sorgext1_cur;

    loop


    EXIT WHEN sorgext1_cur%NOTFOUND ;

    for counter in 1 .. batch_size loop

    fetch sorgext1_cur into sorgext1_rec;


    var_rows := account1_cur%rowcount;

    update s_org_ext set batch_record_num = var_rows where id= sorgext1_rec.id;

    update s_org_ext set batch_number = batches where id= sorgext1_rec.id;

    end loop;

    dbms_output.put_line ('Batch number '|| Batches || ' completed ...... Number of records updated = ' || var_rows || '.');

    batches := batches + 1;



    end loop;


    close sorgext1_cur;
    close sorgext_cur;


    end loop;

    EXCEPTION

    WHEN OTHERS THEN
    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
    commit;

    end ;

    v_hier:=v_hier+1;

    exit when vmax_hier=v_hier;



    exception

    WHEN OTHERS THEN

    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

    commit;

    end;

  13. #13
    Join Date
    May 2010
    Location
    Bangalore
    Posts
    16
    IGNORE previous reply - try this

    Thanks
    Richa
    ==========================================
    create or replace procedure batch_update

    is

    v_hier number(38):=1;


    vmax_hier number(3):=5;

    begin

    loop

    cursor sorgext_cur

    is

    select * from s_org_ext where hier_level=v_hier ;

    open sorgext_cur;

    CURSOR sorgext1_cur IS SELECT * FROM s_org_ext;
    var_rows number(3);
    batches number(3) := 1;

    batch_size number(3) := 50;
    sorgext_rec s_org_ext%ROWTYPE;



    begin
    dbms_output.put_line ('********************************** ');
    dbms_output.put_line ('The sorgext id will be updated with batch number and batch record number. Each batch will have ' || batch_size || ' number of records.');
    dbms_output.put_line ('---------------------------------- ');

    open sorgext1_cur;

    loop


    EXIT WHEN sorgext1_cur%NOTFOUND ;

    for counter in 1 .. batch_size loop

    fetch sorgext1_cur into sorgext1_rec;


    var_rows := account1_cur%rowcount;

    update s_org_ext set batch_record_num = var_rows where id= sorgext1_rec.id;

    update s_org_ext set batch_number = batches where id= sorgext1_rec.id;

    end loop;

    dbms_output.put_line ('Batch number '|| Batches || ' completed ...... Number of records updated = ' || var_rows || '.');

    batches := batches + 1;



    end loop;


    close sorgext1_cur;
    close sorgext_cur;


    end loop;

    EXCEPTION

    WHEN OTHERS THEN
    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
    commit;

    end ;

    v_hier:=v_hier+1;

    exit when vmax_hier=v_hier;

    WHEN OTHERS THEN

    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

    COMMIT;

    END;

  14. #14
    Join Date
    May 2010
    Posts
    18
    hi,,richa i executed dis ,it is showing

    following errors


    SQL> show errors
    Errors for PROCEDURE BATCH_UPDATE:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    14/8 PLS-00103: Encountered the symbol "SORGEXT_CUR" when expecting
    one of the following:
    := . ( @ % ;

    71/1 PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
    of the following:
    end not pragma final instantiable order overriding static
    member constructor map

    79/1 PLS-00103: Encountered the symbol "V_HIER" when expecting one of
    the following:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    end not pragma final instantiable order overriding static
    member constructor map

    79/17 PLS-00103: Encountered the symbol ";" when expecting one of the
    following:
    ) , * & = - + < / > at in is mod remainder not rem
    <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
    LIKE4_ LIKEC_ between || member SUBMULTISET_

    83/1 PLS-00103: Encountered the symbol "WHEN" when expecting one of
    the following:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    begin case declare end exception exit for goto if loop mod
    null pragma raise return select update while with
    <an identifier> <a double-quoted delimited-identifier>
    <a bind variable> << close current delete fetch lock insert
    open rollback savepoint set sql execute commit forall merge
    pipe
    The symbol "exception" was substituted for "WHEN" to continue.

    SQL>

  15. #15
    Join Date
    May 2010
    Location
    Bangalore
    Posts
    16
    Try this --
    ==================
    create or replace procedure batch_update

    is

    v_hier number(38):=1;


    vmax_hier number(3):=5;

    begin

    loop

    cursor sorgext_cur

    is

    select * from s_org_ext where hier_level=v_hier ;

    open sorgext_cur;

    CURSOR sorgext1_cur IS SELECT * FROM s_org_ext;
    var_rows number(3);
    batches number(3) := 1;

    batch_size number(3) := 50;
    sorgext_rec s_org_ext%ROWTYPE;



    begin
    dbms_output.put_line ('********************************** ');
    dbms_output.put_line ('The sorgext id will be updated with batch number and batch record number. Each batch will have ' || batch_size || ' number of records.');
    dbms_output.put_line ('---------------------------------- ');

    open sorgext1_cur;

    loop


    EXIT WHEN sorgext1_cur%NOTFOUND ;

    for counter in 1 .. batch_size loop

    fetch sorgext1_cur into sorgext1_rec;


    var_rows := account1_cur%rowcount;

    update s_org_ext set batch_record_num = var_rows where id= sorgext1_rec.id;

    update s_org_ext set batch_number = batches where id= sorgext1_rec.id;

    end loop;

    dbms_output.put_line ('Batch number '|| Batches || ' completed ...... Number of records updated = ' || var_rows || '.');

    batches := batches + 1;



    end loop;


    close sorgext1_cur;

    EXCEPTION

    WHEN OTHERS THEN
    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
    commit;

    end ;

    v_hier:=v_hier+1;

    exit when vmax_hier=v_hier;
    close sorgext_cur;


    end loop;

    WHEN OTHERS THEN

    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

    COMMIT;

    END;

Posting Permissions

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