Datapump and network links

I was asked to develop a solution which copied data from one schema to another. The solution was required to be generic so would work if the schemas resided on different databases.

I thought this was a good situation to utilise at Data Pump rather than a one off set of scripts.

When I reviewed the Oracle documentation the following was needed:
- A database link between source and target databases
- Specify NETWORK_LINK in the parameter file

Creation of a database link can be done as follows:

 CREATE DATABASE LINK loopback  
 CONNECT TO scott  
 IDENTIFIED BY "xxx"   
 USING 'orcl11g';  

As can be seen in the code above, the link is called "loopback". It creates to the user SCOTT using the password XXX and connects to the orcl11g database.

Next step is to confirm the link is working:

 SELECT COUNT(*)  
 FROM  emp@loopback;  
 COUNT(*)  
 ----------  
    14  

Works fine. Now for the datapump parameter file.

 CONTENT=ALL  
 FULL=N  
 LOGFILE=import.log  
 METRICS=YES  
 NETWORK_LINK=loopback  
 REMAP_SCHEMA=scott:top  
 TABLES=scott.emp  
 TABLE_EXISTS_ACTION=TRUNCATE  

The important parameters are:
- CONTENT, when ALL then metadata and data is transferred
- NETWORK_LINK, set to use the LOOPBACK database link created above
- REMAP_SCHEMA, specifies the source and target schemas
- TABLES, only copy the EMP table between the schemas. Note that I have prefixed the EMP table with the source schema name - this prevents the errors ORA-39166: Object TOP.EMP was not found.
ORA-31655: no data or metadata objects selected for job

Now for the datapump import. I used the following command

 impdp top parfile=scott_imp.par  

Finally, check that the table is populated in the target schema

 SELECT COUNT(*)  
 FROM top.emp;  
 COUNT(*)  
 ----------  
    14  

As an aside, I received the following error when testing:

 ORA-31631: privileges are required  
 ORA-39149: cannot link privileged user to non-privileged user  

To resolve, I granted the roles IMP_FULL_DATABASE and EXP_FULL_DATABASE for both the source and target users.

Timestamp with time zone

One of the databases I support is based in America. The application code makes extensive use of the TIMESTAMP WITH TIME ZONE data type to ensure that the date/time fields are correctly displayed according to the time zone. Lets try querying on using the TIMESTAMP WITH LOCAL TIME ZONE column.

Here's a simple example. I create a small table with 10,000 rows in Oracle 11.2.0.3.

 CREATE TABLE dates(  
  id NUMBER,  
  lt TIMESTAMP WITH LOCAL TIME ZONE  
 );

 INSERT /*+ APPEND */  
 INTO   dates  
 SELECT level,  
        TRUNC(SYSTIMESTAMP) + level  
 FROM   dual  
 CONNECT BY level <= 10000;  

 ALTER TABLE dates ADD CONSTRAINT pk_dates PRIMARY KEY (id);  
 CREATE INDEX i_dates_03 ON dates(lt);  
 exec dbms_stats.gather_table_stats(user, 'dates');  

The key thing to note is the index on the "lt" column. Lets run a simple query against the table, using the TO_TIMESTAMP function.

 SELECT *  
 FROM   dates  
 WHERE  lt = TO_TIMESTAMP('28/02/2013 00:00:00','dd/mm/yyyy hh24:mi:ss');

 --------------------------------------------------  
 | Id | Operation                  | Name         |  
 --------------------------------------------------  
 |  0 | SELECT STATEMENT           |              |  
 |  1 | TABLE ACCESS BY INDEX ROWID| DATES        |  
 |* 2 |  INDEX RANGE SCAN          | I_DATES_03   |  
 --------------------------------------------------  
 Predicate Information (identified by operation id):  
 ---------------------------------------------------  
   2 - access("LT"=TIMESTAMP' 2013-02-28 00:00:00.000000000')  

Note that an the index is being used to access the table. The access predicate on step 2 confirms this.

What if a TO_TIMESTAMP_TZ function is applied? I thought that this should also use the index since we are applying a timezone/timestamp comparison to a field stored as TIMESTAMP WITH LOCAL TIME ZONE.

 SELECT *
 FROM   dates
 WHERE  lt = TO_TIMESTAMP_TZ('28/02/2013 00:00:00 0:00','dd/mm/yyyy hh24:mi:ss tzh:tzm');

 -----------------------------------  
 | Id | Operation        | Name    |  
 -----------------------------------  
 |  0 | SELECT STATEMENT |         |  
 |* 1 | TABLE ACCESS FULL| DATES   |  
 -----------------------------------  
 Predicate Information (identified by operation id):  
 ---------------------------------------------------  
   1 - filter(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("LT"))=  
        SYS_EXTRACT_UTC(TO_TIMESTAMP_TZ('28/02/2013 00:00:00 0:00','dd/mm/yyyy hh24:mi:ss tzh:tzm')))  

This is what surprised me - the index is not being used. Instead, a full table scan and an INTERNAL_FUNCTION is used (which would have decreased the speed of my update operation had I not noticed it).

What I decided to do was use an alter session command so my session was in the correct time zone:

 ALTER SESSION SET TIME_ZONE='-5:00';  

As well as this I converted my TO_TIMESTAMP_TZ function calls to be TO_DATE.

SQL*Loader using a CSV file

This is the first of a couple of articles which are about SQL*Loader. SQL*Loader allows data to be loaded into an Oracle database from a file on the server.

There are a couple of things we need to know about before we try an example.

1) Input file
Contains the data to be loaded. It can be in any format supplied by the source system - comma separated, tab separated, positional.

2) Control file
A detailed definition of the data in the file which enables Oracle to turn it into fields and load it. Besides the data layout, the file can also contain metadata about the data, for example date format information and the number of records to skip when loading.

Input File
For this example, I created a file containing comma separated data. Although I am using commas in this example, other characters can be used.

 Graham,Wallace,01/01/1970  
 David,"Gilmour",05/05/1957  
 "Roger",Waters,10/10/1980  

Things to note:
1) the fields are separated ("terminated") by a comma
2) the fields are also sometimes ("optionally") surrounded ("enclosed") by double quotes
3) the date field contains a consistent date format (day/month/year)

Control File
A sample control file can be seen below. Note that options to be applied to the load can be specified on the command line and within the control file.

 LOAD DATA  
 INFILE      'input_file.csv'  
 BADFILE     'first_bad.txt'  
 DISCARDFILE 'first_dsc.txt'  
 APPEND  
 INTO TABLE person  
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'  
 TRAILING NULLCOLS
 (  
   forename  CHAR,  
   surname   CHAR,  
   dob       DATE "dd/mm/yyyy",
   person_id "person_seq.nextval"
 )  

To explain further, we'll look at the file line by line.

 LOAD DATA  

This tells SQL*Loader that the file contains instructions for load.

 INFILE   'input_file.csv'   

This details where the input file can be found.

 BADFILE   'first_bad.txt'   

When records fail parsing because of a missing field or error, they are written to the BADFILE.

 DISCARDFILE 'first_dsc.txt'  

If there are records which are not inserted or rejected (for example if there is a condition on the load) then records are written to the DISCARDFILE.
Note that default values are used for the BADFILE and DISCARDFILE if they are not specified in the control file or on the command line.

 APPEND 
 INTO TABLE person

This details the method of loading data into the target table as well as the name of the target table, in this case "person".
The four possible methods that can be specified are:
- INSERT inserts the new data and expects the target table to be empty otherwise an error occurs
- APPEND inserts the new data with no impact to the existing data
- REPLACE uses a delete to remove the existing data
- TRUNCATE uses a truncate to remove the existing data

 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'  

This tells SQL*Loader that the each field in the input file is terminated by a comma and might have a double quote surrounding it. If you look back at the input data, you will see that some fields are quoted.

 TRAILING NULLCOLS  

This this tells SQL*Loader that there may be extra fields at the end of the file which are null.

 (  
   forename  CHAR,  
   surname   CHAR,  
   dob       DATE "dd/mm/yyyy",
   person_id "person_seq.nextval"
 )

Finally we get to the point in the file where we define the layout of the data. There are three fields in the data: forename, surname and finally date of birth, which has the mask dd/mm/yyyy applied to it.
The fourth field is a virtual field which does not appear in the file. It tells SQL*Loader to use person_seq.nextval to populate the person_id field.

Oracle Table
The final things we need are 1) to create the person table to hold the data from the input file and 2) create the sequence we refer to above.

 CREATE TABLE person   
 ( PERSON_ID NUMBER,   
  FORENAME VARCHAR2(100),   
  SURNAME  VARCHAR2(100),   
  DOB      DATE
 );  

CREATE SEQUENCE person_seq
MINVALUE 1 
MAXVALUE 9999 
INCREMENT BY 1 
START WITH 1;

Invoking SQL*Loader
Loader is invoked using the sqlldr program from the command line.

 sqlldr userid=top control=first.ctl log=first_log.txt  

The parameters for the command above are:
- userid. The user being used to run SQL*Loader
- control. The name of the control file to be used for the load
- log. The file to which the load will write output

A zipped version of the files I used can be found here.

SQL*Plus bind and substitution variables

Is it possible to create generic grant scripts for a release across all of the environments which are used on the path to production eg SIT, UAT?

Each of the environments in use have "read only" and "fire fighter" users created to prevent the schema owner password being too widely used. To complicate things, the read only and fire fighter user names do not remain constant. Grant scripts are created on a per environment basis which means the same scripts are not being run more than once - increasing the risk that errors are made when the scripts are amended.

The scenario is best described in the following script:

CREATE USER uat    IDENTIFIED BY uat;  
CREATE USER ro_uat IDENTIFIED BY ro_uat;
CREATE USER ff     IDENTIFIED BY ff_uat;  
-- Grant privileges to connect

connect uat/uat
CREATE TABLE x
(x_id NUMBER);

GRANT SELECT ON x TO ro_uat;
GRANT SELECT, INSERT, UPDATE, DELETE ON x to ff_uat;

I have created an application owner user and two users to be used for read only and update. The GRANT statements need to be executed once per environment - and amended on deployment to each new environment.

The solution uses a mixture of substitution and bind variables. A substitution variable is identified in a SQL*Plus script with & or && and are used to allow repeated use within a script. A bind variable in this context is a variable which is used in SQL*Plus and can be referenced within SQL or PL/SQL executed as part of the script.

My first attempt to solve the problem resulted in the following script:

VARIABLE ro VARCHAR2(30);  -- Declare bind variables to use in block below
VARIABLE ff VARCHAR2(30);  
-- Create an anonymous block to populate the variables
DECLARE  
  v_user VARCHAR2(30);  
BEGIN  
  -- Get the user
  SELECT user   
  INTO   v_user  
  FROM   dual;  
  -- Set variables depending on the user, notice the use of the ':' to signify a bind variable
  CASE v_user  
    WHEN 'PROD' THEN   
      :ro := 'ROPROD';  
      :ff := 'FFPROD';  
    WHEN 'UAT' THEN  
      :ro := 'ROUAT';  
      :ff := 'FFUAT';  
    WHEN 'DEV' THEN  
      :ro := 'RODEV';  
      :ff := 'FFDEV';  
  END CASE;  
END;  
/  
-- Grant permissions
GRANT SELECT ON x TO :ro;
GRANT SELECT ON x TO :ro
                     *
ERROR at line 1:
ORA-00987: missing or invalid username(s)

We cannot use a bind variable here.

Lets try converting the bind variable to a substitution variable.

-- This code can be run after the code above   
-- Ensure the parameters are reset 
undefine ro_param   
undefine ff_param   
-- create a user variable. new_value stores the result of the query in the variable  
-- note that the column in the query must match the column name in the command  
column rousr format a30 new_value ro_param   
column ffusr format a30 new_value ff_param   
SELECT :ro AS rousr,   
       :ff AS ffusr   
FROM dual; 

GRANT SELECT ON x TO &&first;
old   1: GRANT SELECT ON x TO &&first
new   1: GRANT SELECT ON x TO RODEV

Grant succeeded.  

Please note that "set define on" should be set in the script to ensure that the && characters are identified and used properly. "set verify on" is also useful - it displays the lines marked old and new in the script above.

SQL*Plus Scripting

I have been doing a lot of work for implementations with SQL scripts recently.

SQL scripts are extremely useful for automation purposes. I also like to output the contents of my scripts to a file so I have a permanent record.

When I first started looking at the scripts, they typically looked like the following:

 DROP TABLE t;  
 CREATE TABLE t  
 (  
  t_id NUMBER,  
  t_txt VARCHAR2(50)  
 );  
 CREATE UNIQUE INDEX pk_t ON t(t_id);  
 ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY t(t_id) USING INDEX;  
 exit  

Running this script through SQL*Plus produces the following output.

 top@ORCL11G> @sql_scripts.sql  
 Table dropped.  
 Table created.  
 Index created.  
 Table altered.  

Bare bones, isn't it? The problems at first sight are
- No output to a file, so no permanent record
- No record on the screen of which command is currently executing - imagine a situation with a large script containing long running updates - how can progress be assessed?

Lets amend the script to be the following:

 spool s2_log.txt  
 set echo on  
 DROP TABLE t;  
 CREATE TABLE t  
 (  
  t_id NUMBER,  
  t_txt VARCHAR2(50)  
 );  
 CREATE UNIQUE INDEX pk_t ON t(t_id);  
 ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY t(t_id) USING INDEX;  
 exit  

The "spool" command tells Oracle to direct output to a file. The "set echo on" command is SQL*Plus specific and tells Oracle to send the SQL statement it is executing to the screen.

Please see the sample output below:

 top@ORCL11G> DROP TABLE t;  
 Table dropped.  
 top@ORCL11G> CREATE TABLE t  
  2 (  
  3  t_id NUMBER,  
  4  t_txt VARCHAR2(50)  
  5 );  
 Table created.  
 top@ORCL11G>  
 top@ORCL11G> CREATE UNIQUE INDEX pk_t ON t(t_id);  
 Index created.  
 top@ORCL11G>  
 top@ORCL11G> ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (t_id) USING INDEX;  
 Table altered.  

Far more user friendly! As can be seen, SQL*Plus outputs exactly the commands and the results.

Some of the other useful commands I use are:
host - host command is also very useful - it executes and Operating System command without leaving SQL*Plus. For example, if you forget the name of the script you need to run, you can enter "host ls" at the prompt and it will list the contents of the current directory.

set timing on - prints the execution time per statement to the screen

set serveroutput on - Shows output from PL/SQL blocks when dbms_output is used

connect user/password - Allows the script to log on as a different user to execute commands in the other schema

There are far more options that I have covered here. The Oracle documentation shows all of the possibilities.

That's it for the moment - I will write another post regarding bind variables in scripts.

Invokers rights

If synonyms are not an option, how can invoker rights be implemented to allow users access to stored PL/SQL code?

The sample configuration is as follows:

- User app1, contains application tables and code

- User firefight will have update privileges on the tables and execute on all stored code

- User read_only has read only (select) privileges on the tables and only has execute on stored code which does not update

The problems are:

- What if there is more than one application owner with duplicate table names? Synonyms are problematic for firefight user because the correct schema needs to be established prior to update particularly when using stored PL/SQL

- The read_only user obviously cannot have execute privileges to a package which provides update privileges. In essence this means that the user cannot see these packages at all.

Create demo users:

 CREATE USER app1 IDENTIFIED BY app1;  
 CREATE USER read_only IDENTIFIED BY read_only;  
 CREATE USER firefight IDENTIFIED BY firefight;  
 GRANT CREATE SESSION TO app1, read_only, firefight;  
 GRANT CREATE TABLE, CREATE PROCEDURE, CREATE SEQUENCE TO app1;  
 GRANT UNLIMITED TABLESPACE TO app1;  

Connect as app1 and create a table:

 CREATE TABLE t1(artist_id   NUMBER,   
                 artist_name VARCHAR2(50 CHAR),   
                 CONSTRAINT t1_pk PRIMARY KEY (artist_id));  
 CREATE SEQUENCE artist_id_seq START WITH 1 INCREMENT BY 1;  

 GRANT SELECT ON t1 TO read_only;  
 GRANT SELECT, INSERT, UPDATE, DELETE ON t1 TO firefight;
 GRANT SELECT ON artist_id_seq TO read_only, firefight;

Create a simple API allowing update and insert privileges and populate the table with some sample rows.

 CREATE OR REPLACE PACKAGE t1_api  
 AS  
  PROCEDURE do_insert(pi_artist_name IN t1.artist_name%TYPE);  
  PROCEDURE do_update(pi_artist_id  IN t1.artist_id%TYPE,   
            pi_artist_name IN t1.artist_name%TYPE);  
 END t1_api;  
 /  
 show errors  
 -- By default uses DEFINER privileges  
 CREATE OR REPLACE PACKAGE BODY t1_api  
 AS  
  PROCEDURE do_insert(pi_artist_name IN t1.artist_name%TYPE)  
  IS  
  BEGIN  
   INSERT INTO t1(artist_id, artist_name)  
   VALUES(artist_id_seq.nextval, pi_artist_name);  
   COMMIT;  
  END do_insert;  
  PROCEDURE do_update(pi_artist_id  IN t1.artist_id%TYPE,   
            pi_artist_name IN t1.artist_name%TYPE)  
  IS  
  BEGIN  
   UPDATE t1  
   SET  artist_name = pi_artist_name  
   WHERE artist_id = pi_artist_id;  
   COMMIT;  
  END do_update;  
 END t1_api;  
 /  
 show errors

 exec t1_api.do_insert('Pink Floyd');  
 exec t1_api.do_insert('The Smiths');  
 exec t1_api.do_insert('The Beatles');  
 COMMIT;  

Given the scenario above, read_only cannot be given any privileges on the package at all otherwise they will be able to update the contents of the table. This results in the following error:

 GRANT EXECUTE ON t1_api TO firefight;

 CONNECT read_only/read_only;  
 exec app1.t1_api.do_insert('REM');  
 /* Returns an expected error:
 Error starting at line 40 in command:
 exec app1.t1_api.do_insert('REM')
 Error report:
 ORA-06550: line 1, column 7:
 PLS-00201: identifier 'APP1.T1_API' must be declared
 ORA-06550: line 1, column 7:
 PL/SQL: Statement ignored
 06550. 00000 -  "line %s, column %s:\n%s"
 *Cause:    Usually a PL/SQL compilation error.
 *Action:
 */

 CONNECT firefight/firefight;  
 exec app1.t1_api.do_insert('Foo Fighters');  
 -- Works successfully, "anonymous block completed"

 -- Extra row can be seen in the table
 SELECT *  
 FROM  app1.t1  
 ORDER BY artist_id;

 ARTIST_ID ARTIST_NAME                                      
 ---------- --------------------------------------------------
          1 Pink Floyd                                         
          2 The Smiths                                         
          3 The Beatles                                        
          4 Foo Fighters                                       

Although this works, read_only is prevented from viewing the contents of the package - ideally, for support purposes, read_only should be able to view the contents of the packaged code. Invoker rights is ideal for this scenario.

So the next step is to amend the package to use invoker rights. Since a synonym is not an option in this case, the schema owner is prefixed to the name of the table to ensure the correct table is updated. Note that this also has the advantage of working throughout the development life cycle, as the code is moved to different environments, the application owner schema will always be updated.

Firstly, amend the calling SQL script to use variables.

 set define on verify on  

 DROP PACKAGE t1_api;
 COLUMN the_user new_value app_user;  
 SELECT user AS the_user  
 FROM  dual;  
 -- Pass the value of the compiling schema (app1) into the script to compile the stored procedure
 START t1_api_invoker.sql '&app_user' 

Now update the package to use the variable. Note the use of the double "." to ensure the object names are prefixed by the schema. &&1 references the value of the first argument passed into the script - &app_user.

 set define on verify on  
 CREATE OR REPLACE PACKAGE t1_api  
 AUTHID CURRENT_USER  
 AS  
  PROCEDURE do_insert(pi_artist_name IN t1.artist_name%TYPE);  
  PROCEDURE do_update(pi_artist_id  IN t1.artist_id%TYPE,   
            pi_artist_name IN t1.artist_name%TYPE);  
 END t1_api;  
 /  
 show errors  
 -- By default uses DEFINER privileges  
 CREATE OR REPLACE PACKAGE BODY t1_api  
 AS  
  PROCEDURE do_insert(pi_artist_name IN t1.artist_name%TYPE)  
  IS  
  BEGIN  
   INSERT INTO &&1..t1(artist_id, artist_name)  
   VALUES(&&1..artist_id_seq.nextval, pi_artist_name);  
   COMMIT;  
  END do_insert;  
  PROCEDURE do_update(pi_artist_id  IN t1.artist_id%TYPE,   
            pi_artist_name IN t1.artist_name%TYPE)  
  IS  
  BEGIN  
   UPDATE &&1..t1  
   SET  artist_name = pi_artist_name  
   WHERE artist_id = pi_artist_id;  
   COMMIT;  
  END do_update;  
 END t1_api;  
 /  
 show errors  

 -- Note that execute is now granted to read_only and firefight
 GRANT EXECUTE ON t1_api TO firefight;
 GRANT EXECUTE ON t1_api TO read_only;

Connect as read_only and run an update.

 > CONNECT read_only/read_only  
 Connected  
 > exec app1.t1_api.do_insert('REM')  
 Error starting at line 73 in command:  
 exec app1.t1_api.do_insert('REM')  
 Error report:  
 ORA-01031: insufficient privileges  
 ORA-06512: at "APP1.T1_API", line 6  
 ORA-06512: at line 1  
 01031. 00000 - "insufficient privileges"  
 *Cause:  An attempt was made to change the current username or password  
       without the appropriate privilege. This error also occurs if  
       attempting to install a database without the necessary operating  
       system privileges.  
       When Trusted Oracle is configure in DBMS MAC, this error may occur  
       if the user was granted the necessary privilege at a higher label  
       than the current login.  
 *Action:  Ask the database administrator to perform the operation or grant  
       the required privileges.  
       For Trusted Oracle users getting this error although granted the  
       the appropriate privilege at a higher label, ask the database  
       administrator to regrant the privilege at the appropriate label.  
 > SELECT *  
 FROM  app1.t1  
 ORDER BY artist_id  
  ARTIST_ID ARTIST_NAME                     
 ---------- --------------------------------------------------  
      1 Pink Floyd                       
      2 The Smiths                       
      3 The Beatles                      
      4 Foo Fighters              

This produces the expected "insufficient privileges" error.

Now run the package as firefight:

 > CONNECT firefight/firefight  
 Connected  
 > exec app1.t1_api.do_insert('Metallica')  
 anonymous block completed  
 > SELECT *  
 FROM  app1.t1  
 ORDER BY artist_id  
  ARTIST_ID ARTIST_NAME                     
 ---------- --------------------------------------------------  
      1 Pink Floyd                       
      2 The Smiths                       
      3 The Beatles                      
      4 Foo Fighters                      
      5 Metallica         

The insert was performed as expected.

Although a synonym is the easiest way to ensure invokers rights code works as expected, sometimes environment configuration means that it is not possible.
As demonstrated above, SQL scripts to create the objects can be prefix the name of the object with the schema the update should be applied to, allowing the read_only user to be granted execute privileges on the package.

Table function

I have been thinking about using table functions to parse strings and use the results in joins to other tables. Table functions can be used to make PL/SQL collections behave like tables.

How efficient are they and what effect can they have on a plan?

I am using 11.2.0.3 for these tests

The function is created as follows:

CREATE OR REPLACE TYPE t_string_table IS TABLE OF VARCHAR2(32000);
/
show errors

 CREATE OR REPLACE FUNCTION string_token(pi_input IN VARCHAR2,  
                     pi_del  IN VARCHAR2 DEFAULT ',')  
 RETURN t_string_table   
 PIPELINED  
 IS  
   l_idx  PLS_INTEGER;  
   l_list VARCHAR2(32767) := pi_input;  
   l_value VARCHAR2(32767);  
 BEGIN   
   LOOP  
     l_idx := INSTR(l_list,pi_del);  
     IF l_idx > 0 THEN  
       pipe ROW(LTRIM(RTRIM(SUBSTR(l_list,1,l_idx-1))));  
       l_list := SUBSTR(l_list,l_idx+LENGTH(pi_del));  
     ELSE  
       pipe ROW(LTRIM(RTRIM(l_list)));  
       EXIT;  
     END IF;  
   END LOOP;  
   RETURN;  
 END string_token;
 /
 show errors

Now we are good to go. First thing to establish is what cardinality is estimated by Oracle when calling the pipelined function: I am passing in 5 values

 var str  VARCHAR2(4000);  
 var delim VARCHAR2(1);  
 exec :str := '10,11,12,13,14';  
 exec :delim := ',';  
 SELECT /*+ gather_plan_statistics monitor bind_aware */
 FROM  TABLE(string_token(:str,:delim));  

Running the trace through TRCA outputs the following plan (edited to remove information about the reads):

 ID  PID  Card   Rows    Row Source Operation 
 --- ---- ------ ------- ----------------------------------------------- 
  1:  0   8168   5       COLLECTION ITERATOR PICKLER FETCH STRING_TOKEN

The estimated cardinality (8168) almost matches the block size which I am using in my database (8192).

What about a more complex example?

I have created two tables which can be used for this test

 exec dbms_random.seed(0);  
 create table from_table  
 pctfree 99  
 pctused 1  
 as  
 with generator as (  
   select --+ materialize  
       rownum id  
   from  all_objects  
   where  rownum <= 100  
 )  
 select  
   rownum       id,  
   CASE WHEN MOD(rownum,10) < 5 THEN 4 ELSE MOD(rownum,10) END num,  
   lpad(rownum,10,'0') small_vc,  
   trunc(100 * dbms_random.normal) val,  
   rpad('x',100)    padding  
 from  
   generator  v1,  
   generator  v2  
 where  
   rownum <= 1000;

 CREATE UNIQUE INDEX i_from_table_01 ON from_table(id);  
 -- Gather stats 

create table join_table
pctfree 99
pctused 1
as
with generator as (
    select  --+ materialize
            rownum  id
    from    all_objects
    where   rownum <= 1000
)
select
    rownum              id,
    CASE WHEN MOD(rownum,10) < 5 THEN 4 ELSE MOD(rownum,10) END num,
    lpad(rownum,10,'0') small_vc,
    trunc(100 * dbms_random.normal) val,
    rpad('x',100)       padding,
    (MOD(rownum, 1000) + 1) ft_id
from
    generator   v1,
    generator   v2
where
    rownum <= 10000
;

CREATE UNIQUE INDEX i_join_table_01 ON join_table(id);
CREATE INDEX i_join_table_02 ON join_table(ft_id);
-- Gather stats

Now the table function is used to create a result set which joins to the from_table.

exec :str := '10,11,12,13,14';  
exec :delim := ',';  
SELECT /*+ gather_plan_statistics monitor bind_aware */
 FROM  from_table ft  
 JOIN  TABLE(string_token(:str,:delim)) ON column_value = ft.id  
 JOIN  join_table jt ON ft.id = jt.ft_id;

ID   PID   Card    Rows               Row Source Operation              
--- ---- ------ ------- ------------------------------------------------
 1:    0  81680      50 HASH JOIN
 2:    1   8168       5  COLLECTION ITERATOR PICKLER FETCH STRING_TOKEN
 3:    1  10000   10000  HASH JOIN
 4:    3   1000    1000 . TABLE ACCESS FULL FROM_TABLE
 5:    3  10000   10000 . TABLE ACCESS FULL JOIN_TABLE

In this case, Oracle assumes that the best way to get the data from the tables is by two full table scans - note that the discrepancy between the estimated Card (8168) and the actual returned (5).

Now add a cardinality hint saying that a handful of rows are returned. Note that I have rewritten the query to use an inline view.

 exec :str := '10,11,12,13,14';  
 exec :delim := ',';  
 SELECT /*+ gather_plan_statistics monitor bind_aware */
 FROM  from_table ft  
 JOIN  (SELECT /*+ cardinality(t 5) */* FROM TABLE(string_token(:str,:delim))t) ON column_value = ft.id  
 JOIN  join_table jt ON ft.id = jt.ft_id;  

ID   PID   Card    Rows               Row Source Operation               
--- ---- ------ ------- -------------------------------------------------
 1:    0     50      50 NESTED LOOPS
 2:    1      5       5  NESTED LOOPS
 3:    2      5       5 . COLLECTION ITERATOR PICKLER FETCH STRING_TOKEN
 4:    2      1       5 . TABLE ACCESS BY INDEX ROWID FROM_TABLE
 5:    4      1       5 .. INDEX UNIQUE SCAN I_FROM_TABLE_01
 6:    1     10      50  TABLE ACCESS BY INDEX ROWID JOIN_TABLE
 7:    6     10      50 . INDEX RANGE SCAN I_JOIN_TABLE_02

A far better plan.

To summarize, consider using the CARDINALITY hint to tell Oracle how many rows are expected to be returned when using pipelined functions where possible. It should lead to better plans being used for your queries.