Syntax Error/Issues w/ Out Variables when Calling Procedure in TOAD IV SQL Window

Question

Goal: I have a procedure that I want to call in the SQL window of Toad to look at the the data it's pulling, but am running across issues in how I'm going about it and would like to know what the syntax should look like.

Specifics:

I have a procedure that takes IN three var, and OUTs three var. For simplicity the names and types are the following:

var1 VARCHAR2
var2 VARCHAR2
var3 NUMBER (length: 22)
var4 PL/SQL TABLE (length: 22)
var5 PL/SQL TABLE (length: 22)
var6 PL/SQL TABLE (length: 22)

In the SQL window I have the following:

DECLARE
paramOut4 TABLE("22");
paramOut5 TABLE("22");
paramOut6 TABLE("22");
BEGIN
SOME_SCHEMA.SOME_PROCEDURE('someString', 4, 2, paramOut4, paramOut5, paramOut6);
END;

Which gives me the error:

ORA-06550: line 2, column 21: PLS-00103: Encountered the symbol ")" when expecting one of the following:

an identifier a double-quoted delimited-identifier LONG_
double ref char time timestamp interval date binary national
character nchar The symbol "an identifier" was substituted for ")" to continue. ORA-06550: line 3, column 21: PLS-00103: Encountered the symbol ")" when expecting one of the following:

an identifier a double-quoted delimited-identifier LONG_
double ref char time timestamp interval date binary national
character nchar The symbol "an ORA-06550: line 4, column 21: PLS-00103: Encountered the symbol ")" when expecting one of the following:

an identifier a double-quoted delimited-identifier LONG_
double ref char time timestamp interval date binary national
character nchar The symbol "an

Even though it should be a number and not a string for the table length, I added double quotes because without them it complains about:

ORA-06550: line 2, column 17: PLS-00103: Encountered the symbol "22" when expecting one of the following:

pragma an identifier a double-quoted delimited-identifier current

And I have the paren there because when I just had "TABLE" it complained about:

The following error has occurred:

ORA-06550: line 2, column 16: PLS-00103: Encountered the symbol ";" when expecting one of the following:

(

pablomatico pablomatico · Accepted Answer · 2013-05-30T09:49:56

You have to define a table type before declaring the variables. For example you could define a type called t_table to declare variables for number arrays:

DECLARE
  TYPE t_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  var4 t_table;
  var5 t_table;
  var6 t_table;
BEGIN
  SOME_SCHEMA.SOME_PROCEDURE('someString', 4, 2, paramOut4, paramOut5, paramOut6);
END;

Syntax Error/Issues w/ Out Variables when Calling Procedure in TOAD IV SQL Window

1 Answers