SQL Plus Variable Arguments

Use parameters in SQL*Plus

The following steps were completed:

You started SQL*Plus, logged in as PETSTORE, and executed the FUNNY script, passing Dog and 01-NOV-99 as parameters. To do this, you typed SQLPLUS PETSTORE/GREATPETS @FUNNY Dog 01-NOV-99 and pressed Enter.
You saw the results displayed in the window. Now you start another script file and pass two more parameters by typing START FUNNIER "All wet" 10 and pressing Enter.
You saw the results of the query and that ended the simulation.

Specifying Datatypes

You do not pass parameters to an external procedure directly. Instead, you pass them to the PL/SQL subprogram that registered the external procedure. So, you must specify PL/SQL datatypes for the parameters. Each PL/SQL datatype maps to a default external datatype. (In turn, each external datatype maps to a C datatype.)

Parameter Datatype Mappings

PL/SQL Type	Supported External Types	Default External Type
BINARY_INTEGER,	CHAR, UNSIGNED CHAR, SHORT,	INT,
BOOLEAN	UNSIGNED SHORT, INT
PLS_INTEGER	UNSIGNED INT, LONG, UNSIGNED LONG,SB1,UB1, SB2,UB2, SB4, UB4, SIZE_T
NATURAL	CHAR, UNSIGNED CHAR, SHORT,	UNSIGNED INT,
NATURALN,	UNSIGNED SHORT, INT,	LONG,
POSITIVE,	UNSIGNED INT,LONG
POSITIVEN,	UNSIGNED LONG, SB1, UB1, SB2
SIGNTYPE	UB2, SB4, UB4, SIZE_T
FLOAT, REAL	FLOAT	FLOAT
DOUBLE PRECISION	DOUBLE	DOUBLE
CHAR, CHARACTER LONG, ROWID, VARCHAR, VARCHAR2	STRING	STRING
LONG RAW, RAW	RAW	RAW
BFILE, BLOB, CLOB	OCILOBLOCATOR	OCILOBLOCATOR

In some cases, you can use the PARAMETERS clause to override the default datatype mappings. For example, you can re-map the PL/SQL datatype BOOLEAN from external datatype INT to external datatype CHAR.