How do I execute a procedure from SQL Developer?

Question

I have a Store Procedure like below and how to execute from SQL Developer?

PROCEDURE CFR.GET_ALL_ERROR_HISTORY
Argument Name  Type       In/Out Default? 
-------------- ---------- ------ -------- 
T1_CURSOR      REF CURSOR OUT             
P_QUERY_TYPE   NUMBER     IN              
P_DATE_START   DATE       IN     DEFAULT  
P_DATE_END     DATE       IN     DEFAULT  
P_COMP_NUMBER   NUMBER     IN     DEFAULT  
P_COMP_GROUP_ID NUMBER     IN     DEFAULT

When running my code in Visual Studio

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GET_ALL_ERROR_HISTORY'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GET_ALL_ERROR_HISTORY'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

My VS Code

    Dim oracleParameter(3) As OracleParameter
    oracleParameter(0) = New OracleParameter()
    oracleParameter(1) = New OracleParameter()
    oracleParameter(2) = New OracleParameter()
    oracleParameter(3) = New OracleParameter()

    oracleParameter(0) = cmd.Parameters.Add("T1_Cursor", dbType:=Oracle.ManagedDataAccess.Client.OracleDbType.RefCursor, ParameterDirection.Output)
    oracleParameter(1) = cmd.Parameters.Add("p_Date_Start", dbType:=Oracle.ManagedDataAccess.Client.OracleDbType.Date, val:=dteStart, ParameterDirection.Input)
    oracleParameter(2) = cmd.Parameters.Add("p_Date_End", dbType:=Oracle.ManagedDataAccess.Client.OracleDbType.Date, val:=dteEnd, ParameterDirection.Input)
    oracleParameter(3) = cmd.Parameters.Add("p_Query_Type", dbType:=Oracle.ManagedDataAccess.Client.OracleDbType.Decimal, val:=intQueryType, ParameterDirection.Input)

I am new to procedure and if you can guide me with how to solve this. I can solve all the rest on my own

thatjeffsmith · Answer 1 · 2020-02-06T15:57:46.807

1

There's the code way.

In a SQL Worksheet:

begin
 CFR.GET_ALL_ERROR_HISTORY(1, sysdate, sysdate+1, 1, 2);
end;
/

Note you'll need to declare a local sys refcursor variable and supply that in the call if you want to catch the value, then you'll use the PRINT command to print the output.

That's not a best practice, using the order of the parameters to dictate what value is assigned to which input...better to use named notation in your call, so similar to

DECLARE
  P_EMP_ID NUMBER;
  P_START_DATE DATE;
  P_END_DATE DATE;
  P_JOB_ID VARCHAR2(10);
  P_DEPARTMENT_ID NUMBER;
BEGIN
  P_EMP_ID := 1;
  P_START_DATE := sysdate-365;
  P_END_DATE := sysdate;
  P_JOB_ID := 'SALES';
  P_DEPARTMENT_ID := 101;

  ADD_JOB_HISTORY(
    P_EMP_ID => P_EMP_ID,
    P_START_DATE => P_START_DATE,
    P_END_DATE => P_END_DATE,
    P_JOB_ID => P_JOB_ID,
    P_DEPARTMENT_ID => P_DEPARTMENT_ID
  );
END;
/

or

EXEC CFR.GET_ALL_ERROR_HISTORY(1,...)

And there's the GUI way.

Open the procedure from the tree, and hit the Execute button. The nice thing there is, we'll grab that OUT refcursor for you and show you the results.

edited Feb 06 '20 at 15:57

answered Feb 06 '20 at 15:38

thatjeffsmith

20,522
6
37
120

the Green play button is the "Execute" button - it will create your anonymous plsql block to execute your stored procedure for you – thatjeffsmith Feb 06 '20 at 15:45
thank you. when I type desc procedureName, i have 6 parameters but I am only sending in 4. What do we do here? – software is fun Feb 06 '20 at 15:46
your description clearly shows there are 6 parameters. 4 appear to have default values so you CAN omit them if you want. – thatjeffsmith Feb 06 '20 at 15:49
Thanks. I see what you mean. when calling my procedure, how do I know what each value in the parenthesis maps to in the procedure? Is var rc refcursor exec :rc := CFR.GET_ALL_ERROR_HISTORY(1, "02/01/2010", "02/01/2020", 1); correct – software is fun Feb 06 '20 at 15:52
best practice is to use named (vs positional) notation...i was in a hurry so i used positional – thatjeffsmith Feb 06 '20 at 15:54
i updated my question to show the named parameters in the call as well – thatjeffsmith Feb 06 '20 at 15:58
Though, this: `P_EMP_ID => P_EMP_ID,` is a little bit confusing; which is which? Maybe, to avoid confusion, use different local variable names (e.g. use prefix `l_` or `v`), or specify the *owner*, e.g. `add_job_history.P_EMP_ID => P_EMP_ID,` – Littlefoot Feb 06 '20 at 16:17

How do I execute a procedure from SQL Developer?

1 Answers1