Create Oracle procedure error - declare custom type

I'm attempting to create a procedure in Oracle Express Server (Applica开发者_运维百科tion Express 2.1.0.00.39) using the web interface.

This is the SQL I'm running via the SQL Commands option in the web interface

CREATE OR REPLACE PROCEDURE my_procedure (listOfNumbers num_list, 
                                          v_value varchar2)

IS

  PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

  UPDATE my_table
     SET my_column = v_value
   WHERE my_row_id IN (SELECT column_value 
                         FROM TABLE(listOfNumbers));

  COMMIT;

END;

UPDATE:

Changed SELECT column_value FROM TABLE to SELECT column_value FROM TABLE(listOfNumbers) but now I get the following error:

PLS-00201: identifier 'num_list' must be declared

UPDATE 2:

Here is how I created my type:

CREATE OR REPLACE TYPE  "num_list" as table of NUMBER(38,1)
/

Seems the error is being caused on the parameter declaration line:

(listOfNumbers num_list, v_value varchar2)

Below is the object details as displayed by the Oracle Database Express Edition web interface.

Try ...TABLE(CAST(listOfNumbers AS num_list)).

The SQL parser simply sees a bind placeholder in place of listOfNumbers, and since it's a custom type you need to tell it what type it is.

This will only work if num_list has been defined as a type in the schema, not just declared as a type in a PL/SQL block.

Your code works - providing the array type has been declared correctly (see below). As you are still having a problem I suspect that is where you are going wrong. But you need to post the code you are using to create the NUM_LIST type in order for us to correct it.

My test data:

SQL> select * from my_table
  2  /

MY_COLUMN             MY_ROW_ID
-------------------- ----------
APC                           1
XYZ                           2
JFK                           3

SQL>

In order to use a type in a SQL statement we must create it as a SQL object:

SQL> create type num_list as table of number;
  2  /

Type created.

SQL>
SQL>
SQL> create or replace procedure my_procedure
  2      (listofnumbers num_list,
  3                    v_value varchar2)
  4  is
  5  begin
  6
  7    update my_table
  8       set my_column = v_value
  9     where my_row_id in (select column_value
 10                           from table(listofnumbers));
 11
 12  end;
 13  /

Procedure created.

SQL>

Executing the procedure:

SQL> declare
  2    n num_list := num_list(1,3);
  3  begin
  4    my_procedure (n , 'FOX IN SOCKS');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>

And lo!

SQL> select * from my_table
  2  /

MY_COLUMN             MY_ROW_ID
-------------------- ----------
FOX IN SOCKS                  1
XYZ                           2
FOX IN SOCKS                  3

SQL>  

Apparently I was creating the type with quotes around the name:

The following didn't work:

CREATE OR REPLACE TYPE  "NUMBER_T" as table of NUMBER(38,1)

When I did it without the quotes and then created the procedure, it was able to recognize it.

The following did work:

CREATE OR REPLACE TYPE  NUMBER_T as table of NUMBER(38,1)

I'm not sure why, but it worked.