0

I inherited an awesome 600 line Stored Procedure in which I need to debug. I'm trying to debug by right clicking on the name of the stored procedure, clicking execute using sql debugger. This brings up a table in which I can manually enter my parameters. Great. Except one of the parameters is a OracleArray vArray INPUT parameter, and I'm not sure how to actually enter something like this as a parameter? I'm not familiar with Oracle.

Elroy Jetson
  • 938
  • 11
  • 27
  • 2
    check this https://stackoverflow.com/questions/2885575/passing-an-array-of-data-as-an-input-parameter-to-an-oracle-procedure – Moudiz Jun 22 '18 at 14:46
  • How is the actual type defined? I don't have Toad but it should be able to bring up a definition via some right-clickery. You'll need to declare one of those in your test block, e.g. `l_something somepackage.sometype := somepackage.sometype('x','y','z');` – William Robertson Jun 22 '18 at 17:43

1 Answers1

2

Your question doesn't have a lot of details, so I'll have to give a vague answer. Here's how you can call a procedure (named other_procedure) which takes a VARRAY argument or other complicated setup. Once you've declared a test procedure, you can execute it with the debugger and then step into the procedure you actually care about.

CREATE OR REPLACE PROCEDURE test_procedure IS
  -- (size and type of the varray should match the one in other_procedure)
  TYPE my_array_t IS VARRAY(4) OF VARCHAR2(20); 
  -- define and initialize your array
  test_array my_array_t := my_array_t('one','two','three');
BEGIN
  -- call the procedure
  other_procedure(test_array);
END;
/

If you're still having trouble, edit your question to provide more details (like the procedure definition and varray definition) and we can give a more specific answer.

kfinity
  • 8,581
  • 1
  • 13
  • 20