Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2007
    Posts
    7

    Unanswered: How to Use OUT in procedures

    Hi Guys,

    Can any one quote me an example of using OUT in procedures? And I am assuming that IN is for input parameter and OUT is for output parameter. Is that right?

    What I exactly require is , I have to retrieve particular value from a query,which I have to use in a java file.

    my procedure looks like,

    CREATE OR REPLACE PROCEDURE DEBITDOMINVPENDING(tripstartdate in varchar2,tripenddate in varchar2,TRAVELTYPE IN number,ISEDIT IN OUT boolean,
    P_RECORDSET OUT SYS_REFCURSOR) AS
    ISEDIT1 boolean;
    BEGIN
    IF ' CMINVOICEMASTERMAINTAINANCE.STATUS=invoice_cancell ed ' THEN
    ISEDIT1 := TRUE;
    END IF;

    OPEN P_RECORDSET FOR

    'SELECT DISTINCT
    CMTRAVELINSTRUCTION.TINUMBER AS TINUMBER,
    TO_DATE(CMCOMMENTS.COMMENTDATE) AS APPROVEDDATE,
    CMTRAVELINSTRUCTION.STARTDATE AS STARTDATE,
    CMVENDOR.VENDORNAME AS VENDORNAME,
    CM_USERINFO.EMPLOYEECODE AS EMPNO,
    CMTRAVELINSTRUCTION.TRAVELLERNAME AS EMPNAME,
    CMTRAVELITINERARY.PAYROLLAREA AS VENDORAREA,
    CMTRAVELINSTRUCTION.PAYROLLAREA AS EMPAREA


    FROM

    CMTRAVELINSTRUCTION

    INNER JOIN CMINVOICEMASTERMAINTAINANCE ON
    CMTRAVELINSTRUCTION.TRAVELINSTRUCTIONID=CMINVOICEM ASTERMAINTAINANCE.TRAVELINSTRUCTIONID

    INNER JOIN USER_ ON
    USER_.USERID=CMTRAVELINSTRUCTION.TRAVELLERID
    INNER JOIN CM_USERINFO ON
    CM_USERINFO.USERID=CMTRAVELINSTRUCTION.TRAVELLERID
    INNER JOIN CMTRAVELITINERARY ON
    CMTRAVELITINERARY.TRAVELINSTRUCTIONID=CMTRAVELINST RUCTION.TRAVELINSTRUCTIONID AND
    CMTRAVELITINERARY.PAYROLLAREA IS NOT NULL
    INNER JOIN CMVENDOR ON
    CMVENDOR.PAYROLLAREA = CMTRAVELINSTRUCTION.PAYROLLAREA
    INNER JOIN CMCOMMENTS ON
    CMCOMMENTS.TRAVELINSTRUCTIONID=CMTRAVELINSTRUCTION .TRAVELINSTRUCTIONID AND
    CMCOMMENTS.COMMENTERROLE IS NOT NULL AND CMCOMMENTS.COMMENTERROLE = Approver
    WHERE
    CMINVOICEMASTERMAINTAINANCE.OVERSEAS = TRAVELTYPE AND (CMINVOICEMASTERMAINTAINANCE.STATUS=debit_returned OR
    CMINVOICEMASTERMAINTAINANCE.STATUS=invoice_cancell ed)
    and
    TO_DATE(CMTRAVELINSTRUCTION.STARTDATE) BETWEEN (tripstartdate) AND (tripenddate)

    ORDER BY CMTRAVELINSTRUCTION.TINUMBER ';

    END;
    /
    SHOW ERRORS;

    I am getting error like this;

    5/1 PL/SQL: Statement ignored
    5/4 PLS-00382: expression is of wrong type

    I have to retrieve the value of isedit1 in my java file based upon the following condition


    IF ' CMINVOICEMASTERMAINTAINANCE.STATUS=invoice_cancell ed ' THEN
    ISEDIT1 := TRUE

    Can I do this, invoking accessing particular table's column before writing query.

    Can anyone help me out in this regard.

    Thanks in Advance.
    Suresh Midde

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, OUT parameter means the variable used as the argument is modified by the procedure.

    This line makes no sense and is rejected by Oracle:

    Code:
    IF ' CMINVOICEMASTERMAINTAINANCE.STATUS=invoice_cancell ed ' THEN
    That's equivalent to writing:

    Code:
    IF ' Hello World ' THEN
    I have no idea what this line is meant to do really - does that table only have one row? If not how do we know which row's status you want?

    Perhaps you want something like this:

    Code:
    SELECT c.status
    INTO l_status -- a new variable: l_status CMINVOICEMASTERMAINTAINANCE.status%TYPE;
    FROM CMINVOICEMASTERMAINTAINANCE c
    WHERE <some condition that returns one row>;
    
    IF l_status = 'invoice_cancelled' THEN
       isedit1 := TRUE;
    END IF;

Posting Permissions

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