Skip to Main Content
  • Questions
  • I am trying to access a function stored in a package and it gives me invalid sql statement error

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Avisha.

Asked: November 07, 2016 - 11:36 am UTC

Last updated: November 08, 2016 - 6:39 am UTC

Version: ORACLE 11 XE

Viewed 1000+ times

You Asked

HERE IS MY PACKAGE AND ITS BODY:

CREATE OR REPLACE PACKAGE TBOOK AS
FUNCTION TBOOK1(JID VARCHAR2) RETURN VARCHAR2;
END;

CREATE OR REPLACE PACKAGE BODY TBOOK AS
JID2 VARCHAR2(6);
JID1 VARCHAR2(6);
FUNCTION TBOOK1(JID VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
JID1:= JID;
SELECT JID INTO JID2 FROM JOURNEY_DETAILS WHERE JOURNEY_DETAILS.JID=JID1;
RETURN JID2;
END TBOOK1;
END TBOOK;
/

WELL I AM TRYING TO PASS THE JOURNEY_ID(JID) IN THE FUNCTION AND ONCE IT IS CHECKED THAT IT EXISTS OR NOT FROM THE JOURNEY_DETAILS TABLE THE SAME JID PASSED IS RETURNED.
WHILE I M RUNNING THIS CODE IN ORACLE 11 XE IT GIVES ME AN ERROR:INVALID SQL STATEMENT.COULD YOU PLEASE HELP ME?

and Connor said...

Can you show us a test case demonstrating the issue. Here's my test case in SQL Plus, edited just enough since I dont have your tables. It works fine for me

SQL> CREATE OR REPLACE PACKAGE TBOOK AS
  2  FUNCTION TBOOK1(JID VARCHAR2) RETURN VARCHAR2;
  3  END;
  4  /

Package created.

SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY TBOOK AS
  2  JID2 VARCHAR2(6);
  3  JID1 VARCHAR2(6);
  4  FUNCTION TBOOK1(JID VARCHAR2)
  5  RETURN VARCHAR2
  6  IS
  7  BEGIN
  8  JID1:= JID;
  9  SELECT 'x' JID INTO JID2 FROM dual;
 10  RETURN JID2;
 11  END TBOOK1;
 12  END TBOOK;
 13  /

Package body created.

SQL>
SQL> select tbook.tbook1('test') from dual;

TBOOK.TBOOK1('TEST')
---------------------------------------------------------
x



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library