CODEWITHSUNDEEP
sqloracle-databasestored-proceduresoracle11g
Ahmad Gunawan
Ahmad Gunawan

Reputation: 45

Oracle : Execute Stored Procedure with table type input variable

When I tried to debug an existing app, I found 1 method which is execute a stored procedure showing an error. I want to execute the Stored Procedure on my Oracle SQLDeveloper, given procedure :

type numTableType is table of NUMBER index by binary_integer;

PROCEDURE up_correction_status(p_exp_check_id IN numTableType, p_status_id NUMBER, p_user_id NUMBER, p_return OUT NUMBER);

but, how to declare value of numTableType ?

I tried to :

var p_return NUMBER;
execute "PKG_PDM_DASHBOARD".up_approved_corr_status(1, 2, 11, :p_return);
print p_return;

Found this error :

Error starting at line 2 in command: execute "PKG_PDM_DASHBOARD".up_approved_corr_status(1, 2, 11, :p_return) Error report: ORA-06550: line 1, column 8: PLS-00306: wrong number or types of arguments in call to 'UP_APPROVED_CORR_STATUS' ORA-06550: line 1, column 8: PL/SQL: Statement ignored

just want to execute it and get return value

Upvotes: 0

Views: 6376

Answers (1)

Pavel Gatnar
Pavel Gatnar

Reputation: 4053

Your type usage is possible within a package. Outside a package you have to create type.

For execution of procedures with such complex type parameters use a pl/sql anonymous block.
Execution template: 

declare
  X tableType;
begin
  X(1) := 1;
  P(X);
end;

Upvotes: 2

Related Questions