CODEWITHSUNDEEP
oracle-databasefunctionplsqlpackage
iwan
iwan

Reputation: 7569

How to call sql function ABC defined in package DEF using TOAD ORACLE

I have Oracle Package named DEF , with 1 function inside named ABC that accept 1 string argument. May I know how to call this function ABC directly in TOAD Editor?

Thanks in advance.

CREATE OR REPLACE PACKAGE HPQ_IF.def AS
  FUNCTION def(p_sql IN VARCHAR2)
  RETURN VARCHAR2;
END def;
/


  FUNCTION abc(p_sql IN VARCHAR2)
  RETURN VARCHAR2
  IS
  j NUMBER;

  BEGIN
    dbms_output.put_line(p_sql);
    RETURN 'Done';
  END abc;

Last error (using first answer below):

[Error] Execution (6: 31): ORA-06550: line 6, column 31:
PLS-00302: component 'abc' must be declared
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored

Upvotes: 2

Views: 23659

Answers (2)

Scott A
Scott A

Reputation: 7844

In the TOAD SQL buffer:

DECLARE
   v_sql VARCHAR2(255);
   v_return VARCHAR2(255);
BEGIN
   v_sql := 'the string'; -- or get it from anywhere else
   v_return := HPQ_IF.def.abc(v_sql);
   dbms_output.put_line(v_return);
   -- do anything else you want to do with the return value here...
END;

If you don't want to do anything with the return value other than display the information:

SELECT HPQ_IF.def.abc('the string') FROM DUAL;

However, for a PL/SQL function to be called in SQL (SELECT, INSERT, etc), it must be free from certain side effects (ie. it can't modify certain kinds of state within the database). To be called as a column in a SELECT, it must not modify the database (insert or update, for example), perform DDL, or commit a transaction.

You can also use Execute Procedure from within the Schema Browser.

Upvotes: 4

Justin Cave
Justin Cave

Reputation: 231831

A function returns a value so you have to return the result somewhere.

You can call the function from a SQL statement

SELECT def.abc( 'Some string' )
  FROM dual;

Or you can return the result into a local variable in PL/sQL

DECLARE
  l_result VARCHAR2(100);
BEGIN
  l_result := def.abc( 'Some string' );
END;

Upvotes: 3

Related Questions