RelationalDBDesignRelationalDBDesign





PL/SQL Parameters   «Prev  Next»

SQL Plus Variable Arguments

Use parameters in SQL*Plus

The following steps were completed:
  1. 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.
  2. 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.
  3. 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_INTEGERUNSIGNED INT, LONG,
UNSIGNED LONG,SB1,UB1, SB2,UB2, SB4, UB4, SIZE_T
NATURALCHAR, UNSIGNED CHAR, SHORT,UNSIGNED INT,
NATURALN, UNSIGNED SHORT, INT,LONG,
POSITIVE,UNSIGNED INT,LONG
POSITIVEN, UNSIGNED LONG, SB1, UB1, SB2
SIGNTYPEUB2, SB4, UB4, SIZE_T 
FLOAT, REAL FLOAT FLOAT
DOUBLE PRECISIONDOUBLE DOUBLE
CHAR, CHARACTER
LONG, ROWID,
VARCHAR, VARCHAR2
STRING STRING
LONG RAW, RAWRAW RAW
BFILE, BLOB, CLOBOCILOBLOCATOR 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.