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));