Skip to Main Content

Java Development Tools

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Invalid column index error in jdeveloper calling pl Sql procedure

kp2000Nov 2 2016 — edited Nov 8 2016

--

I am having a DB procedure that accepts 1 variable and returns 2 variables. I am calling the below inside PFR. I have no idea to call as a  Function as my proc. returns 2 values

I get INVALID COLUMN INDEX ERROR . Please advise. PL_SQL pkg attached below


              //java method

               CallableStatement  cStmt = requisitionamimpl.getOADBTransaction().createCallableStatement("begin tke_us_cus_fd_39_pk_exp_stat.GET_ORG_NAME(:1,:2,:3); end;", 1);     

                            try

                            {

                                cStmt.setString(1,V_Proj_No);

                            ((OracleCallableStatement)cStmt).registerOutParameter(2, OracleTypes.VARCHAR, 0, 240);

                              ((OracleCallableStatement)cStmt).registerOutParameter(3, OracleTypes.VARCHAR, 0, 240);

                             

                                cStmt.execute();

                                V_Org_Name = cStmt.getString(2);

                                V_Org_Id  = cStmt.getString(3);

                            pageContext.writeDiagnostics(this, "V_Org_Name No From Fun-->"+V_Org_Name, OAFwkConstants.PROCEDURE);

                            cStmt.close();

                            }

                            catch (Exception e)

                            {

                                 e.printStackTrace();

                                pageContext.writeDiagnostics(this, "Exception in calling GET_ORG_NAME_PROCEDURE  " +  e.getMessage(), OAFwkConstants.PROCEDURE);          

                            }

proc pkg

===========================

CREATE OR REPLACE PACKAGE test as

PROCEDURE GET_ORG_NAME(V_Proj_No IN VARCHAR2, x_exp_org_name OUT VARCHAR2, x_exp_org_id OUT NUMBER);

END ;

/

CREATE OR REPLACE PACKAGE BODY test IS

  

    BEGIN

   PROCEDURE GET_ORG_NAME(V_Proj_No IN VARCHAR2, x_exp_org_name OUT VARCHAR2, x_exp_org_id OUT NUMBER)

   --RETURN VARCHAR2

   AS

    V_ORG_NAME VARCHAR2(240);

    --v_exp_org_id NUMBER;

    BEGIN

      

          SELECT ORG.NAME

                ,PROJ.CARRYING_OUT_ORGANIZATION_ID

           INTO x_exp_org_name

               ,x_exp_org_id

          FROM HR_ORGANIZATION_UNITS ORG, PA_PROJECTS_ALL PROJ

           WHERE PROJ.CARRYING_OUT_ORGANIZATION_ID = ORG.ORGANIZATION_ID

          AND PROJ.SEGMENT1 = V_Proj_No;     

         

    --RETURN  V_ORG_NAME;                                                                 

    EXCEPTION

    WHEN OTHERS THEN

     fnd_file.put_line(fnd_file.log, 'Exception in PKG.GET_ORG_NAME - ' || SQLERRM);

    END;

END tke_us_cus_fd_39_pk_exp_stat;

/

This post has been answered by Muddasar on Nov 3 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 6 2016
Added on Nov 2 2016
5 comments
998 views