A no parameter function that returns a string

Database Guide : Appendixes : Function Calls using Native Oracle : A no parameter function that returns a string

Create a no parameter Function using a SQL Plus window:

SQL> edit

Wrote file afiedt.buf

1 CREATE OR REPLACE FUNCTION myNoParamFunction RETURN VARCHAR2 IS

2 BEGIN

3 RETURN 'JimmyJoe says, myNoParamFunction Function';

4* END;

SQL> /

Function created.

Call the function:

SQL> set serveroutput on

SQL> edit

Wrote file afiedt.buf

1 DECLARE

2 v_returnVal VARCHAR2(50);

3 BEGIN

4 v_returnVal := myNoParamFunction;

5 DBMS_OUTPUT.PUT_LINE('v_returnVal = ' || v_returnVal);

6* END;

SQL> /

The last forward slash executes the above SQL. The results of executing the above code are:

v_returnVal = JimmyJoe says, myNoParamFunction Function

PL/SQL procedure successfully completed.

Call the function from Smalltalk:

"Setup the connection"

(AbtDatabaseConnectionSpec forDbmClass: AbtOracle10DatabaseManager databaseName: 'orcl') connect.

connection := AbtDbmSystem activeDatabaseConnection

flushCache;

autoCommit: true;

commitUnitOfWork;

yourself.

"Variable names"

funcName := 'myNoParamFunction'.

returnValueName := 'return_value_str'.

"Return type"

inOutParams := AbtCompoundType new

addField: (AbtOracleVarCharField new

name: returnValueName;

count: 100).

"Value returned"

aDict := Dictionary new.

aDict at: returnValueName put: String new.

"Function spec"

functionSpec := AbtFunctionSpec new

functionName: funcName;

useSqlCall: true;

parameters: inOutParams.

"Invoke the function"

resultSet := connection invokeFunctionSpec: functionSpec withInputDictionary: aDict.

resultSet

The result of inspecting the above script is:

$C:\Users\documentation\Documents\vastePublisher\stable\VAS Documentation Word\images\388e-iii.jpg$

Last modified date: 06/01/2018