Tim Van Wassenhove home

As i already wrote, last couple of days i’ve been experimenting with PL/SQL. At work we use Toad for Oracle but since TOADSoft only offers a limited freeware version i decided to write my code with GVim and use SQL*Plus at home. Here are a couple of lines i added to my login.sql file

DEFINE _EDITOR='gvim -c "set filetype=sql"'
SET SERVEROUTPUT ON
SET LINESIZE 120
SET AUTOCOMMIT OFF
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';

In a stored procedure i created and filled an instance of NUMBER_TABLE (CREATE TYPE NUMBER_TABLE AS TABLE OF NUMBER) and my stored procedure tried to select all the rows in that table (SELECT * FROM V_NUMBER_TABLE). Apparently the engine didn’t know this type @runtime despite the fact that i declared it in my stored procedure (V_NUMBER TABLE NUMBER_TABLE := NUMBER_TABLE();) and the engine compiled the package without errors. I got round that problem as following

SELECT * FROM (CAST(V_NUMBER_TABLE AS NUMBER_TABLE));