2

I'd like to extract the package name using regexp_substr.

For example:

create or replace package body something.pkg_test is

I want that:

pkg_test

I tried to use lookbehind just to ignore "create or replace package body something.":

select regexp_substr('create or replace package body something.pkg_test is','((?<!create\sor\sreplace\spackage\sbody\ssomething\.).)*$',1,1) from dual;

But it doesn’t work. So I just need to extract the package name between "create or replace package body something" and the first space or \n.

I need that because I want the package name before compile a .sql file and insert it in a table.

Lawrence
  • 172
  • 1
  • 9
  • If you want to check a package name, why not do a simple `like` check instead of extracting with regexp? `if line like 'create%or%replace%package%body%something.pkg_test%' then ...`. It's not as fool-proof but should do fine for your purpose. – Timekiller Aug 05 '19 at 21:31
  • I want to extract the package name and insert it in a table before compile. I just have the .sql file. I don't know what is the package name. I change the word "check" just to "want the package name" – Lawrence Aug 05 '19 at 21:51
  • You may also want to consider the optional keywords `EDITIONABLE` and `NONEDITIONABLE`. Even though almost nobody cares about them, a lot of tools automatically generate them and they may show up in DDL. – Jon Heller Aug 06 '19 at 01:18
  • @JonHeller you have a point. I‘ll consider it now I know how to do that. – Lawrence Aug 06 '19 at 02:34

1 Answers1

4

My guess is that, maybe you are trying to write some expression with positive look-behind:

(?<=create\sor\sreplace\spackage\sbody\ssomething\.)\S*

Or maybe, alternatively, we'd use some expression without lookarounds, such as:

create\sor\sreplace\spackage\sbody\ssomething\.(\S*).*

with capturing group 1, and our code might look like:

select regexp_replace('create or replace package body something.pkg_test is', 'create\sor\sreplace\spackage\sbody\ssomething\.(\S*).*', '\1') from dual;

not sure though.

Demo 2

Demo

The expression is explained on the top right panel of regex101.com, if you wish to explore/simplify/modify it, and in this link, you can watch how it would match against some sample inputs, if you like.

Reference

How to extract group from regular expression in Oracle?

Community
  • 1
  • 1
Emma
  • 27,428
  • 11
  • 44
  • 69