0

I'm just writing a tool to automate a process. Out of it, I just want it to do fetch the stored procedure name if I give the line number of a package of many procs.

Is it possible to do so, or is there some other way to get the procedure name with line number or "specific text"?

vinoth kumar
  • 27
  • 1
  • 6
  • 2
    If you have more than one procedure how does the line number help? There might be 2 procedures with the same line number... What are you actually attempting to achieve? This feels like you've worked out the solution but you need help with the problem. – Ben Nov 03 '14 at 10:34
  • Are you talking about the line number within a package? – Peter Lang Nov 03 '14 at 10:38
  • yup.....within the package – vinoth kumar Nov 03 '14 at 11:03
  • 1
    Yes, it is possible ... under certain condition. Have a look here ... http://stackoverflow.com/questions/26279075/how-to-get-information-on-all-types-of-data-declared-in-a-given-package/26279773#26279773 – peter.hrasko.sk Nov 03 '14 at 11:45
  • @nop77svk hi i think u can help me on this fillowin link's qn too... http://stackoverflow.com/questions/26714100/automate-bulk-of-update-queries-in-pl-sql/26714419#26714419 Notice me for any further info. thanks – vinoth kumar Nov 03 '14 at 14:35
  • If this is for an exception handler please be sure to also include the line number anyway, since procedure names can be ambiguous. – Jon Heller Nov 04 '14 at 05:26

2 Answers2

0

Have a look at all_procedures

Maybe you can figure it out by using the column subprogram_id.

It seems like, this column identifies the procedures as they are defined in the package header.

Dom84
  • 852
  • 7
  • 20
  • Too much work, too indeterminate result. Rather have a look on the PL/Scope ... http://stackoverflow.com/questions/26279075/how-to-get-information-on-all-types-of-data-declared-in-a-given-package/26279773#26279773 – peter.hrasko.sk Nov 03 '14 at 11:48
0

Try to select from the SYS view ALL_SOURCE querying by the owner, package name and line number or owner package name and text.

Below are two examples that assume that you will replace MYOWNER,MYPACKAGE, Myprocedure and MYLINENUMBER with your own information.

select * from ALL_SOURCE
   where OWNER = 'MYOWNER' and
         TYPE='PACKAGE' and
         NAME='MYPACKAGE' and
         TEXT like 'PROCEDURE Myprocedure%'

select * from ALL_SOURCE
   where OWNER = 'MYOWNER' and
         TYPE='PACKAGE' and
         NAME='MYPACKAGE' and
         LINE = MYLINENUMBER
Gui
  • 296
  • 1
  • 7