Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2011
    Posts
    38

    Unanswered: how to check query write in the procedure

    hi friend
    can any on let me know how i can check the query which i write in the procedure....
    for exa.
    CREATE OR REPLACE PROCEDURE SP_EMP_LVTYPE_UPDATE(
    in_elmLtmcode IN VARCHAR2 DEFAULT NULL,
    in_elmEmcode IN VARCHAR2 DEFAULT NULL,
    in_elmCmmcode IN VARCHAR2 DEFAULT NULL,
    in_elmTotdays IN VARCHAR2 DEFAULT NULL,
    in_elmBaldays IN VARCHAR2 DEFAULT NULL,
    in_elmEncashdays IN VARCHAR2 DEFAULT NULL,
    in_emcode2 IN VARCHAR2 DEFAULT NULL,
    in_qryId IN NUMBER DEFAULT NULL,
    retype OUT NUMBER)

    AS
    elmLtmcode VARCHAR2(500);
    elmEmcode VARCHAR2(20);
    elmCmmcode VARCHAR2(20);
    elmTotdays VARCHAR2(500);
    elmBaldays VARCHAR2(100);
    elmEncashdays VARCHAR2(100);
    qryId NUMBER(10,0);
    StoO_selcnt INTEGER;
    StoO_error INTEGER;
    StoO_rowcnt INTEGER;
    StoO_crowcnt INTEGER := 0;
    StoO_fetchstatus INTEGER := 0;
    StoO_errmsg VARCHAR2(255);
    StoO_sqlstatus INTEGER;
    LTMcode VARCHAR2(20);
    LPos NUMBER(10,0);
    Totdays VARCHAR2(20);
    Tpos NUMBER(10,0);
    Baldays VARCHAR2(20);
    Bpos NUMBER(10,0);
    Encashdays VARCHAR2(20);
    Epos NUMBER(10,0);
    Epos1 NUMBER(10,0);
    count1 NUMBER(10,0);
    count2 NUMBER(10,0);
    encdays VARCHAR2(20);
    lvtaken VARCHAR2(20);
    elmEncashdays1 VARCHAR2(50);
    ResigRecords number(10,0);
    totEncashdays NUMBER;
    emcode2 VARCHAR2(100);
    BEGIN
    SP_EMP_LVTYPE_UPDATE.elmLtmcode := SP_EMP_LVTYPE_UPDATE.in_elmLtmcode;
    SP_EMP_LVTYPE_UPDATE.elmEmcode := SP_EMP_LVTYPE_UPDATE.in_elmEmcode;
    SP_EMP_LVTYPE_UPDATE.elmCmmcode := SP_EMP_LVTYPE_UPDATE.in_elmCmmcode;
    SP_EMP_LVTYPE_UPDATE.elmTotdays := SP_EMP_LVTYPE_UPDATE.in_elmTotdays;
    SP_EMP_LVTYPE_UPDATE.elmBaldays := SP_EMP_LVTYPE_UPDATE.in_elmBaldays;
    SP_EMP_LVTYPE_UPDATE.elmEncashdays := SP_EMP_LVTYPE_UPDATE.in_elmEncashdays;
    SP_EMP_LVTYPE_UPDATE.emcode2:=SP_EMP_LVTYPE_UPDATE .in_emcode2 ;
    SP_EMP_LVTYPE_UPDATE.qryId := SP_EMP_LVTYPE_UPDATE.in_qryId;
    SP_EMP_LVTYPE_UPDATE.retype := 0;
    IF SP_EMP_LVTYPE_UPDATE.qryId = 1 THEN
    BEGIN
    NULL;
    SP_EMP_LVTYPE_UPDATE.elmLtmcode := LTRIM(RTRIM(SP_EMP_LVTYPE_UPDATE.elmLtmcode)) || ',';
    SP_EMP_LVTYPE_UPDATE.LPos := INSTR(SP_EMP_LVTYPE_UPDATE.elmLtmcode,',',1);
    SP_EMP_LVTYPE_UPDATE.elmTotdays := LTRIM(RTRIM(SP_EMP_LVTYPE_UPDATE.elmTotdays)) || ',';
    .
    .
    .
    .
    .

    UPDATE EMP_LVTYPE_MAP
    SET
    ELMLTMCODE = CAST(SP_EMP_LVTYPE_UPDATE.LTMcode AS VARCHAR2(20)),
    ELMEMCODE = SP_EMP_LVTYPE_UPDATE.elmEmcode,
    ELMCMMCODE = SP_EMP_LVTYPE_UPDATE.elmCmmcode,
    ELMTOTDAYS = CAST(SP_EMP_LVTYPE_UPDATE.Totdays AS VARCHAR2(20)),
    ELMBALDAYS = CAST(SP_EMP_LVTYPE_UPDATE.Baldays AS NUMBER(5,2)),
    ELMENCASHDAYS = CAST(SP_EMP_LVTYPE_UPDATE.Encashdays AS NUMBER(5,2)),
    UPDATEDBY = SP_EMP_LVTYPE_UPDATE.emcode2
    WHERE ELMLTMCODE = CAST(SP_EMP_LVTYPE_UPDATE.LTMcode AS VARCHAR2(20))
    and ELMEMCODE = SP_EMP_LVTYPE_UPDATE.elmEmcode
    and ELMCMMCODE = SP_EMP_LVTYPE_UPDATE.elmCmmcode;
    StoO_rowcnt := SQL%ROWCOUNT;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
    WHEN OTHERS THEN
    StoO_error := SQLCODE;
    StoO_errmsg := SQLERRM;
    END;
    END;
    END IF;

    i write this query.and passing the value of paramter of this procedure by my java application by the right click of procedure name and click test meenu and passing the value in the procedure.
    this show that ur prcedure is working properly but when i m direct run this procedure through the application the updation query is not working....
    can any one suggest me how i can check this updation query...

    there is any method to check the query such like dbms_output.put_line().

    ??

  2. #2
    Join Date
    Nov 2011
    Posts
    2
    you should post the error message also. may be user privileges to update the table, table lock, or privileges to CREATE/REPLACE procedure. there can be several cause.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    A frequent problem involving PLSQL, "not working" is that
    privileges acquired via ROLE do not apply within named PL/SQL procedures.

    We need actual error code & message plus the line number where the error is thrown.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by donadarsh View Post
    you should post the error message also. may be user privileges to update the table, table lock, or privileges to CREATE/REPLACE procedure. there can be several cause.
    I am quite certain there is no error because of that exception hiding section (EXCEPTION WHEN OTHERS) which is masking all ones. However, I would say the procedure is working as designed - whichever exception is raised, fill some local variables (which are lost after procedure end) and pretend that everything ended correctly.

    Removal of that EXCEPTION section might be the first step for understanding what is the reason for "not working".

    there is any method to check the query such like dbms_output.put_line().
    Probably not without changing the interface. In PL/SQL, you may implement logging as shown in this thread on AskTom: http://asktom.oracle.com/pls/asktom/...66400346817259
    (maybe it would be good renaming LOG_ERROR to more descriptive one avoid confusion and then use it wherever you want for storing whatever you need)

  5. #5
    Join Date
    Jul 2011
    Posts
    38
    hi sir

    thanks for ur reply...

    actually friend i want to know that there is any method or function or way to check the query which i write in the procedure....
    in this problem, the procedure is compile without any error and when i test this procedure by the right click of procedure name and click the test menu then other window is open.
    here is test script
    begin
    -- Call the procedure
    sp_emp_lvtype_update(in_elmltmcode => :in_elmltmcode,
    in_elmemcode => :in_elmemcode,
    in_elmcmmcode => :in_elmcmmcode,
    in_elmtotdays => :in_elmtotdays,
    in_elmbaldays => :in_elmbaldays,
    in_elmencashdays => :in_elmencashdays,
    in_emcode2 => :in_emcode2,
    in_qryid => :in_qryid,
    retype => :retype);
    end;

    and passing the parameter value in this procedure by hard codded which is coming from my application. and click the dbms output tag it show all the dbms_output and update the table.(means procedure is working properly (i think)).
    but when i direct run this procedure through my application and passing the value of parameter by the application, it (procedure) not working and it will not update the table

  6. #6
    Join Date
    Jul 2011
    Posts
    38
    CREATE OR REPLACE PROCEDURE SP_EMP_LVTYPE_UPDATE(
    in_elmLtmcode IN VARCHAR2 DEFAULT NULL,
    in_elmEmcode IN VARCHAR2 DEFAULT NULL,
    in_elmCmmcode IN VARCHAR2 DEFAULT NULL,
    in_elmTotdays IN VARCHAR2 DEFAULT NULL,
    in_elmBaldays IN VARCHAR2 DEFAULT NULL,
    in_elmEncashdays IN VARCHAR2 DEFAULT NULL,
    in_emcode2 IN VARCHAR2 DEFAULT NULL,
    in_qryId IN NUMBER DEFAULT NULL,
    retype OUT NUMBER)

    AS
    elmLtmcode VARCHAR2(500);
    elmEmcode VARCHAR2(20);
    elmCmmcode VARCHAR2(20);
    elmTotdays VARCHAR2(500);
    elmBaldays VARCHAR2(100);
    elmEncashdays VARCHAR2(100);
    qryId NUMBER(10,0);
    StoO_selcnt INTEGER;
    StoO_error INTEGER;
    StoO_rowcnt INTEGER;
    StoO_crowcnt INTEGER := 0;
    StoO_fetchstatus INTEGER := 0;
    StoO_errmsg VARCHAR2(255);
    StoO_sqlstatus INTEGER;
    LTMcode VARCHAR2(20);
    LPos NUMBER(10,0);
    Totdays VARCHAR2(20);
    Tpos NUMBER(10,0);
    Baldays VARCHAR2(20);
    Bpos NUMBER(10,0);
    Encashdays VARCHAR2(20);
    Epos NUMBER(10,0);
    Epos1 NUMBER(10,0);
    count1 NUMBER(10,0);
    count2 NUMBER(10,0);
    encdays VARCHAR2(20);
    lvtaken VARCHAR2(20);
    elmEncashdays1 VARCHAR2(50);
    ResigRecords number(10,0);
    totEncashdays NUMBER;
    emcode2 VARCHAR2(100);
    BEGIN
    SP_EMP_LVTYPE_UPDATE.elmLtmcode := SP_EMP_LVTYPE_UPDATE.in_elmLtmcode;
    SP_EMP_LVTYPE_UPDATE.elmEmcode := SP_EMP_LVTYPE_UPDATE.in_elmEmcode;
    SP_EMP_LVTYPE_UPDATE.elmCmmcode := SP_EMP_LVTYPE_UPDATE.in_elmCmmcode;
    SP_EMP_LVTYPE_UPDATE.elmTotdays := SP_EMP_LVTYPE_UPDATE.in_elmTotdays;
    SP_EMP_LVTYPE_UPDATE.elmBaldays := SP_EMP_LVTYPE_UPDATE.in_elmBaldays;
    SP_EMP_LVTYPE_UPDATE.elmEncashdays := SP_EMP_LVTYPE_UPDATE.in_elmEncashdays;
    SP_EMP_LVTYPE_UPDATE.emcode2:=SP_EMP_LVTYPE_UPDATE .in_emcode2 ;
    SP_EMP_LVTYPE_UPDATE.qryId := SP_EMP_LVTYPE_UPDATE.in_qryId;
    SP_EMP_LVTYPE_UPDATE.retype := 0;
    IF SP_EMP_LVTYPE_UPDATE.qryId = 1 THEN
    BEGIN
    NULL;
    SP_EMP_LVTYPE_UPDATE.elmLtmcode := LTRIM(RTRIM(SP_EMP_LVTYPE_UPDATE.elmLtmcode)) || ',';
    SP_EMP_LVTYPE_UPDATE.LPos := INSTR(SP_EMP_LVTYPE_UPDATE.elmLtmcode,',',1);
    SP_EMP_LVTYPE_UPDATE.elmTotdays := LTRIM(RTRIM(SP_EMP_LVTYPE_UPDATE.elmTotdays)) || ',';
    SP_EMP_LVTYPE_UPDATE.Tpos := INSTR(SP_EMP_LVTYPE_UPDATE.elmTotdays,',',1);
    SP_EMP_LVTYPE_UPDATE.elmBaldays := LTRIM(RTRIM(SP_EMP_LVTYPE_UPDATE.elmBaldays)) || ',';
    SP_EMP_LVTYPE_UPDATE.Bpos := INSTR(SP_EMP_LVTYPE_UPDATE.elmBaldays,',',1);
    SP_EMP_LVTYPE_UPDATE.elmEncashdays := LTRIM(RTRIM(SP_EMP_LVTYPE_UPDATE.elmEncashdays)) || ',';
    SP_EMP_LVTYPE_UPDATE.Epos := INSTR(SP_EMP_LVTYPE_UPDATE.elmEncashdays,',',1);
    dbms_output.put_line('hello1'||SP_EMP_LVTYPE_UPDAT E.emcode2);
    IF ( ( REPLACE(SP_EMP_LVTYPE_UPDATE.elmLtmcode, ',', '') IS NOT NULL) and ( REPLACE(SP_EMP_LVTYPE_UPDATE.elmTotdays, ',', '') IS NOT NULL) and ( REPLACE(SP_EMP_LVTYPE_UPDATE.elmBaldays, ',', '') IS NOT NULL) and ( REPLACE(SP_EMP_LVTYPE_UPDATE.elmEncashdays, ',', '') IS NOT NULL)) THEN
    BEGIN
    dbms_output.put_line('hello2'||SP_EMP_LVTYPE_UPDAT E.emcode2);
    <<i_loop1>>
    WHILE ( SP_EMP_LVTYPE_UPDATE.LPos > 0 and SP_EMP_LVTYPE_UPDATE.Tpos > 0 and SP_EMP_LVTYPE_UPDATE.Bpos > 0 and SP_EMP_LVTYPE_UPDATE.Epos > 0) LOOP
    BEGIN
    dbms_output.put_line('hello3'||SP_EMP_LVTYPE_UPDAT E.emcode2);

    SP_EMP_LVTYPE_UPDATE.LTMcode := LTRIM(RTRIM(SUBSTR(SP_EMP_LVTYPE_UPDATE.elmLtmcode , 0,SP_EMP_LVTYPE_UPDATE.LPos - 1)));
    SP_EMP_LVTYPE_UPDATE.Totdays := LTRIM(RTRIM(SUBSTR(SP_EMP_LVTYPE_UPDATE.elmTotdays , 0,SP_EMP_LVTYPE_UPDATE.Tpos - 1)));
    SP_EMP_LVTYPE_UPDATE.Baldays := LTRIM(RTRIM(SUBSTR(SP_EMP_LVTYPE_UPDATE.elmBaldays , 0,SP_EMP_LVTYPE_UPDATE.Bpos - 1)));
    SP_EMP_LVTYPE_UPDATE.Encashdays := LTRIM(RTRIM(SUBSTR(SP_EMP_LVTYPE_UPDATE.elmEncashd ays, 0,SP_EMP_LVTYPE_UPDATE.Epos - 1)));
    IF ( SP_EMP_LVTYPE_UPDATE.LTMcode IS NOT NULL and SP_EMP_LVTYPE_UPDATE.Totdays <> '0' and SP_EMP_LVTYPE_UPDATE.Baldays <> '0' and SP_EMP_LVTYPE_UPDATE.Encashdays <> '0') THEN
    BEGIN
    BEGIN
    StoO_error := 0;
    StoO_rowcnt := 0;

    dbms_output.put_line('hello4'||SP_EMP_LVTYPE_UPDAT E.emcode2);
    dbms_output.put_line('hello5'||(SP_EMP_LVTYPE_UPDA TE.LTMcode) );




    UPDATE EMP_LVTYPE_MAP
    SET
    ELMLTMCODE = CAST(SP_EMP_LVTYPE_UPDATE.LTMcode AS VARCHAR2(20)),
    ELMEMCODE = SP_EMP_LVTYPE_UPDATE.elmEmcode,
    ELMCMMCODE = SP_EMP_LVTYPE_UPDATE.elmCmmcode,
    ELMTOTDAYS = CAST(SP_EMP_LVTYPE_UPDATE.Totdays AS VARCHAR2(20)),
    ELMBALDAYS = CAST(SP_EMP_LVTYPE_UPDATE.Baldays AS NUMBER(5,2)),
    ELMENCASHDAYS = CAST(SP_EMP_LVTYPE_UPDATE.Encashdays AS NUMBER(5,2)),
    UPDATEDBY = SP_EMP_LVTYPE_UPDATE.emcode2
    WHERE ELMLTMCODE = CAST(SP_EMP_LVTYPE_UPDATE.LTMcode AS VARCHAR2(20))
    and ELMEMCODE = SP_EMP_LVTYPE_UPDATE.elmEmcode
    and ELMCMMCODE = SP_EMP_LVTYPE_UPDATE.elmCmmcode;
    StoO_rowcnt := SQL%ROWCOUNT;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
    WHEN OTHERS THEN
    StoO_error := SQLCODE;
    StoO_errmsg := SQLERRM;
    END;
    END;
    END IF;
    SP_EMP_LVTYPE_UPDATE.elmLtmcode := SUBSTR(SP_EMP_LVTYPE_UPDATE.elmLtmcode, SP_EMP_LVTYPE_UPDATE.LPos + 1, LENGTH(SP_EMP_LVTYPE_UPDATE.elmLtmcode));
    SP_EMP_LVTYPE_UPDATE.LPos := INSTR(SP_EMP_LVTYPE_UPDATE.elmLtmcode,',',1);
    SP_EMP_LVTYPE_UPDATE.elmTotdays := SUBSTR(SP_EMP_LVTYPE_UPDATE.elmTotdays, SP_EMP_LVTYPE_UPDATE.Tpos + 1, LENGTH(SP_EMP_LVTYPE_UPDATE.elmTotdays));
    SP_EMP_LVTYPE_UPDATE.Tpos := INSTR(SP_EMP_LVTYPE_UPDATE.elmTotdays,',',1);
    SP_EMP_LVTYPE_UPDATE.elmBaldays := SUBSTR(SP_EMP_LVTYPE_UPDATE.elmBaldays, SP_EMP_LVTYPE_UPDATE.Bpos + 1, LENGTH(SP_EMP_LVTYPE_UPDATE.elmBaldays));
    SP_EMP_LVTYPE_UPDATE.Bpos := INSTR(SP_EMP_LVTYPE_UPDATE.elmBaldays,',',1);
    SP_EMP_LVTYPE_UPDATE.elmEncashdays := SUBSTR(SP_EMP_LVTYPE_UPDATE.elmEncashdays, SP_EMP_LVTYPE_UPDATE.Epos + 1, LENGTH(SP_EMP_LVTYPE_UPDATE.elmEncashdays));
    SP_EMP_LVTYPE_UPDATE.Epos := INSTR(SP_EMP_LVTYPE_UPDATE.elmEncashdays,',',1);
    END;
    END LOOP;
    SP_EMP_LVTYPE_UPDATE.retype := 1;
    END;
    ELSE
    BEGIN
    SP_EMP_LVTYPE_UPDATE.retype := 0;
    END;
    END IF;
    END;
    END IF;

    this is my whole procedure.
    check it sir...

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by vishwas View Post
    actually friend i want to know that there is any method or function or way to check the query which i write in the procedure....
    There are not many things you may do from database as it does not see the application data and its interface quite limited. One way would be implement logging into a new table in autonomous transaction, as I posted in my previous post, and check the table content after running it. Did you have any problem with reading the link describing it? If so, maybe it is time to hire Oracle developer/consultant.

    You may try to read DBMS_OUTPUT buffer in the application using GET_LINE function and display it wherever you want. You may find short description with code samples e.g. here: http://psoug.org/reference/dbms_output.html
    Note, that DBMS_OUTPUT.PUT_LINE does not *write* anything anywhere - it just fills the buffer and it is on the caller (application) to display its content after running that code. SQL*Plus does it after setting serveroutput on. Toad does it in a special tab after enabling it. In fact, I have never seen any real application (not tool like the above mentioned ones) implementing it, but you may give it a try.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    eliminate/delete/remove all/every/100% EXCEPTION handler code.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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