1

Im in need of finding all the refcurs in our system that have a specific field. Important to mention that all said cursors are outs of procedures that themselves are in packages.

ive located all of the candidates with

SELECT *
FROM   user_arguments ua
WHERE  ua.data_type = 'REF CURSOR';

but now im not sure how to continue.

Going over user_source, and finding the open and end of each refcur, then checking if between those lines for the field im searching for exists, then finding the start and finish of the procedure is a possibility but im trying to find a less brute force way to do it.

Ive tried to fiddle around with dbms_metadata.get_ddl and dbms_sql.describe_columns but to no avail.

Is there a way to do it without starting code journies around user_source? Thank you!

KlesierTheSurvivor
  • 29
  • 3
  • Cursor have no any structure unless it is opened. You need to search for source code of procedures to find all possible places. But it would be impossible to manage if there were some dynamic SQL. – astentx Jan 08 '23 at 17:08
  • Are they weakly or strongly typed ref cursors ? If the type owner/name/subname are populated in USER_ARGUMENTS then you can track through that. Otherwise dbms_sql.to_cursor_number followed by describe_columns is your best best. – Gary Myers Jan 09 '23 at 04:39
  • They are weakly typed cursors, SYS_REFCURSOR to be specific, so none of those are filled in USER_ARGUMENTS. dbms_sql.to_cursor_number will require going over each procedure and simulating running it? – KlesierTheSurvivor Jan 09 '23 at 14:35
  • Is the goal to find a column alias that is returned by the query or to find a specific source column that is selected within the query? – Jason Seek Well Jan 10 '23 at 14:46
  • the goal is to find if a specific source column is given as output in the cursor – KlesierTheSurvivor Jan 11 '23 at 15:02
  • If you are on 12.2 or above AND you have a database account that meets these [requirements](https://docs.oracle.com/en/database/oracle/oracle-database/19/adfns/plscope.html#GUID-CF97DD72-26DD-4699-A499-1E4C35EA5246) then [PLSCOPE](http://stevenfeuersteinonplsql.blogspot.com/2018/07/the-plscope-resource-center.html) is what you want. – Jason Seek Well Jan 12 '23 at 01:14

1 Answers1

0

Here is something that could help you to get what you are looking for. This one selects code of a procedure with OUT parameter of type REF CURSOR. The rows could be looped in pl/sql or looked-up for something any other way (regexp maybe). The thing is that you can get lines of code and all the rest info combining data from these three sources like below:

Select  
    p.OBJECT_NAME, p.OBJECT_ID, 
    a.ARGUMENT_NAME, a.DATA_TYPE,
    s.LINE, s.TEXT "TEXT_ROWS"
From 
    all_procedures p
Left Join 
    sys.all_arguments a on p.OBJECT_ID = a.OBJECT_ID
Left Join 
    all_source s ON(s.NAME = p.OBJECT_NAME And s.TYPE = p.OBJECT_TYPE)
where 
    p.OWNER = 'YourOwner' And 
    a.IN_OUT = 'OUT' And 
    a.DATA_TYPE = 'REF CURSOR';
--  
--  Result for one sample procedure:
/*
|OBJECT_NAME  | OBJECT_ID  |ARGUMENT_NAME   |DATA_TYPE     |LINE |  TEXT_ROWS                                                  |
|-------------|----------- |--------------- |------------- |---- |-------------------------------------------------------------|
|GET_EMP_RS   | 354968     |P_RECORDSET     |REF CURSOR    | 1   |"PROCEDURE get_emp_rs (p_deptno    IN  emp.deptno%TYPE,"     |
|GET_EMP_RS   | 354968     |P_RECORDSET     |REF CURSOR    | 2   |"                      p_recordset OUT SYS_REFCURSOR) AS "   |
|GET_EMP_RS   | 354968     |P_RECORDSET     |REF CURSOR    | 3   |"BEGIN "                                                     |
|GET_EMP_RS   | 354968     |P_RECORDSET     |REF CURSOR    | 4   |"  OPEN p_recordset FOR"                                     |
|GET_EMP_RS   | 354968     |P_RECORDSET     |REF CURSOR    | 5   |"    SELECT ename,"                                          |
|GET_EMP_RS   | 354968     |P_RECORDSET     |REF CURSOR    | 6   |"           empno,"                                          |
|GET_EMP_RS   | 354968     |P_RECORDSET     |REF CURSOR    | 7   |"           deptno"                                          |
|GET_EMP_RS   | 354968     |P_RECORDSET     |REF CURSOR    | 8   |"    FROM   emp"                                             |
|GET_EMP_RS   | 354968     |P_RECORDSET     |REF CURSOR    | 9   |"    WHERE  deptno = p_deptno"                               |
|GET_EMP_RS   | 54968      |P_RECORDSET     |REF CURSOR    |10   |"    ORDER BY ename;"                                        |
|GET_EMP_RS   | 354968     |P_RECORDSET     |REF CURSOR    |11   |END get_emp_rs;                                              |
*/
d r
  • 3,848
  • 2
  • 4
  • 15