Skip to Main Content
  • Questions
  • testing stored procedure in sqlDeveloper

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Bradford.

Asked: September 25, 2015 - 6:44 pm UTC

Last updated: September 26, 2015 - 2:55 am UTC

Version: sqldeveloper 4.1.0.19

Viewed 1000+ times

You Asked

I have a procedure defined by others that I am trying to call in sqldeveloper run window.

The run declaration is as follows
--------------------------------------------------------------
DECLARE
PV_USR_ID VARCHAR2(200);
PV_COL_T_ENV_DETAILS MYAPP.COL_T_ENV_DETAILS;
BEGIN
PV_USR_ID := '****';
-- Modify the code to initialize the variable
-- PV_COL_T_ENV_DETAILS := NULL;

MYAPP_PKG.INS_APP_ENV_DETAILS_AIP(
PV_USR_ID => PV_USR_ID,
PV_COL_T_ENV_DETAILS => PV_COL_T_ENV_DETAILS
);
--rollback;
END;
----------------------------------------------------------------

PV_COL_T_ENV_DETAILS is defined as

create or replace TYPE col_t_env_details IS TABLE OF VARCHAR2 (10) ;
----------------------------------------------------------------

I am not certain of the syntax for initializing this variable such that sqlDeveloper will run it.

and Connor said...

To initialise it for testing, you could do this:

PV_COL_T_ENV_DETAILS MYAPP.COL_T_ENV_DETAILS := 
    MYAPP.COL_T_ENV_DETAILS('x1','x2','x3');


A documentation link of relevance:

http://docs.oracle.com/database/121/LNPLS/composites.htm#LNPLS005


Also an excellent resource for all things related to SQL Developer is Jeff Smith's blog. Here's a link to debugging PLSQL with SQL Developer.

http://www.thatjeffsmith.com/archive/2015/06/everything-you-ever-wanted-to-know-about-the-plsql-debugger/

Hope this helps

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library