Home » SQL & PL/SQL » SQL & PL/SQL » Linking Perl procedure with Oracle Package
Linking Perl procedure with Oracle Package [message #270647] Thu, 27 September 2007 13:34 Go to next message
Agus211
Messages: 39
Registered: September 2007
Member
Hi, I'd like to know if someone could explain me how can I create and call Perl procedures and functions(there are like 10) from a PLSQL Package which only should retrieve the values and use them sometimes.


Thanks in advance!.

Report message to a moderator

Re: Linking Perl procedure with Oracle Package [message #270648 is a reply to message #270647] Thu, 27 September 2007 13:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Oracle does not know or care that they are written in PERL.
You need to invoke them as you would any other executable by calling an EXTERNAL PROCEDURE or by using Java.

It might be easier to have the PERL code invoke the DB code.

Report message to a moderator

Re: Linking Perl procedure with Oracle Package [message #270666 is a reply to message #270647] Thu, 27 September 2007 13:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68763
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
And if you want to use external procedure, use C not Perl as programming language.

In fact, if you posted your real problem maybe we can give a more appropriate and accurate answer.

Regards
Michel

Report message to a moderator

Re: Linking Perl procedure with Oracle Package [message #270671 is a reply to message #270647] Thu, 27 September 2007 14:03 Go to previous messageGo to next message
Agus211
Messages: 39
Registered: September 2007
Member
Hi, thanks for answering so fast. The problem is that we have a package with a lot of procedures and functions that consult an LDAP tree to obtain users information. The thing is that when we stress it out, we always have WAIT's and EXCEPTION's like:
ERROR: -31203 - ORA-31203: DBMS_LDAP: PL/SQL - Init Failed.
ERROR: -31204 - ORA-31204: DBMS_LDAP: PL/SQL - Invalid LDAP Session.

And we don't have any problem with the LDAP.
This happens arbitrarily, we can't reproduce the problem. Testing, we realized that the DBMS_LDAP was having problems or causing the WAIT's. So, we decided to REUSE a PERL code that we have that makes all the same things that our PLSQL package does. As we need it to be transparent for the user, we also decided that the procedure calls should stay the same, but the backend change.

That's the reason why PLSQL should call PERL procedures.

Report message to a moderator

Re: Linking Perl procedure with Oracle Package [message #270672 is a reply to message #270647] Thu, 27 September 2007 14:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
 
31203, 00000, "DBMS_LDAP: PL/SQL - Init Failed."
// *Cause: There has been an error in the DBMS_LDAP Init operation.
// *Action: Please check the host name and port number, or report
//          the error number and description to Oracle Support.

I suspect that your PL/SQL procedure has a bug; perhaps a data dependent bug.

Report message to a moderator

Re: Linking Perl procedure with Oracle Package [message #270681 is a reply to message #270672] Thu, 27 September 2007 14:23 Go to previous messageGo to next message
Agus211
Messages: 39
Registered: September 2007
Member
All of the procedures and functions obtain the Host and Port from a table that contains all the services available. If one fails, all of them should, and they don't. Plus, this happens only when we stress it out with a lot of concurrent users. It's like DMBS_LDAP has problems, like a bottle neck.

Report message to a moderator

Re: Linking Perl procedure with Oracle Package [message #270737 is a reply to message #270681] Thu, 27 September 2007 22:38 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Calling Perl from PL/SQL is just plain bad design. It might solve the problem you are having, but it will introduce countless new problems.

If you want to use a language other than PL/SQL, there are two generally accepted methods:
- Server-based: As suggested above, call External Procedures in C or Java (I don't think others are supported, certainly Perl is not)
- Client-based: Drive your program FROM Perl, and embed Perl::DBI calls to Oracle. The same methodology works with other client-based technologies; including Pro*C, PHP, and anything that can link to OCI.
Ross Leishman

Report message to a moderator

Re: Linking Perl procedure with Oracle Package [message #271011 is a reply to message #270647] Fri, 28 September 2007 13:40 Go to previous messageGo to next message
Agus211
Messages: 39
Registered: September 2007
Member
Hi, Im investigating how to do it in Perl, but I also adviced my superiors that Perl is not recommended. Im afraid that in case we have a problem(having Perl code in the Database), Oracle denies me support. Could this happen?.

Report message to a moderator

Re: Linking Perl procedure with Oracle Package [message #271013 is a reply to message #271011] Fri, 28 September 2007 13:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68763
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
There is no problem, you CAN'T have Perl code in Oracle.
Maybe you can have an external C procedure that spawn Perl program but Oracle does not care it your responsabity if this does not work.
For myself I didn't do it like this, to many points of failure.

The opposite Perl code call Oracle procedure is fully supported.

Regards
Michel

Report message to a moderator

Re: Linking Perl procedure with Oracle Package [message #271016 is a reply to message #270647] Fri, 28 September 2007 13:56 Go to previous messageGo to next message
Agus211
Messages: 39
Registered: September 2007
Member
For myself I didn't do it like this, to many points of failure.

Hi Michel, thank you for answering so fast. Could you explain me some points of failure it could have to advice my superiors?. Perl has a module called extproc_perl-2.51 that allowes you to create external procedures for Oracle. The thing is that every documentation says that Oracle supports Java and C. I hope I can make them understand this thing you tell me.

Thank you for all the help.

Report message to a moderator

Re: Linking Perl procedure with Oracle Package [message #271024 is a reply to message #271016] Fri, 28 September 2007 14:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68763
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
External programs work with the help of the listener that spawn a process when you want to execute your external procedure.
So point of failures:
- listener
- listener communication
- process spawning
- procedure itself and its dependencies
...

You also must have a specific listener for this to prevent from security issues.

Regards
Michel

Report message to a moderator

Re: Linking Perl procedure with Oracle Package [message #271035 is a reply to message #270647] Fri, 28 September 2007 14:42 Go to previous message
Agus211
Messages: 39
Registered: September 2007
Member
Yes, I've read some of this stuff that the DBA has to change to make this work. I'm trying to stop this process and keep making efforts to solve the problem that the DBMS_LDAP is having as I think this is the correct way to do this. I hope we can figure out the reason that's causing those exceptions and bottle necks. Thanks Michel for your help.

Report message to a moderator

Previous Topic: Script to create synonyms for users granted a particular role
Next Topic: package
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jul 24 14:42:05 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.