Tools to Work with Stored Procedures in Oracle, in a Team

Oracle Table Usage Across Stored Procedures

If I understand this correctly, you're trying to search for occurrence of a table in all stored procs. In that case, you can use this query:

When searching for occurrences of SP in your schema

SELECT * FROM user_source WHERE text LIKE '%tab_name%';

When searching for occurrences of SP in all schemas

SELECT * FROM all_source WHERE text LIKE '%tab_name%';

Stored Procedures on Oracle SQL Developer

Oracle uses PL/SQL programming language for their stored procedures. Here is an info about PL/SQL in wiki

This is a good source too.

Oracles provides a lot of tools to make the programmer's life easier, but my advice is to start as simple as you can to get familiar with the language..

and... Stored Procedures in PL/SQL

Run Stored Procedure in SQL Developer?

With simple parameter types (i.e. not refcursors etc.) you can do something like this:

SET serveroutput on;
DECLARE
    InParam1 number;
    InParam2 number;
    OutParam1 varchar2(100);
    OutParam2 varchar2(100);
    OutParam3 varchar2(100);
    OutParam4 number;
BEGIN
    /* Assign values to IN parameters */
    InParam1 := 33;
    InParam2 := 89;

    /* Call procedure within package, identifying schema if necessary */
    schema.package.procedure(InParam1, InParam2,
        OutParam1, OutParam2, OutParam3, OutParam4);

    /* Display OUT parameters */
    dbms_output.put_line('OutParam1: ' || OutParam1);
    dbms_output.put_line('OutParam2: ' || OutParam2);
    dbms_output.put_line('OutParam3: ' || OutParam3);
    dbms_output.put_line('OutParam4: ' || OutParam4);
END;
/

---

Edited to use the OP's spec, and with an alternative approach to utilise :var bind variables:

var InParam1 number;
var InParam2 number;
var OutParam1 varchar2(100);
var OutParam2 varchar2(100);
var OutParam3 varchar2(100);
var OutParam4 number;

BEGIN
    /* Assign values to IN parameters */
    :InParam1 := 33;
    :InParam2 := 89;

    /* Call procedure within package, identifying schema if necessary */
    schema.package.procedure(:InParam1, :InParam2,
        :OutParam1, :OutParam2, :OutParam3, :OutParam4);
END;
/

-- Display OUT parameters
print :OutParam1;
print :OutParam2;
print :OutParam3;
print :OutParam4;

SQL to search objects, including stored procedures, in Oracle

i'm not sure if i understand you, but to query the source code of your triggers, procedures, package and functions you can try with the "user_source" table.

select * from user_source

ORM style mapping of Oracle Stored Procedures with .Net

The new Oracle beta Entity Framework driver lets you do that. You can map SPs into the model and either to entities (if they return the equivalent of a table) or create a "complex type" which is a class built around what the SP returns.

I don't know how many SPs your calling, but for the ones I've tried it's worked out.

Another option is to write your own library that just calls the procedures and returns the results as .net classes, but that will require a lot of setup work on your part in terms of repetitive code (mapping parameters to procedures in Oracle gets tedious real fast).

edit - Here's a config file entry to use a stored procedure where the results are coming from a cursor that's an OUT parameter.

  <oracle.dataaccess.client>
    <settings>
      <add name="ENVMSTR.P_ORG_UNIT_R_BY_STAFF.RefCursor.RESULT_CURSOR_P" value="implicitRefCursor bindinfo='mode=Output'" />
    </settings>
  </oracle.dataaccess.client>

edit 2 - And the stored procedure in question:

create or replace
PROCEDURE                 P_ORG_UNIT_R_BY_STAFF 
(
  STAFF_ID_P IN NUMBER
, RESULT_CURSOR_P OUT SYS_REFCURSOR  
) AS 
BEGIN
  OPEN RESULT_CURSOR_P FOR
    select *
      from dept_organizational_unit
      start with deptorgunit_cd = (select deptorgunit_cd from staff where staff_id = STAFF_ID_P)
      connect by prior deptorgunit_parent_cd = deptorgunit_cd;
END P_ORG_UNIT_R_BY_STAFF;

---

Related Topics