Default values in Oracle packaged procedures

pts.
Tags:
Oracle
SQL
What is the best way to conceal default packaged procedure parameter values? I thought of this way: Make default values NULL in the specification Make default values whatever you want them to be in the package body. Wrap (encrypt hex) package body. I need this in order to obfuscate the use of this package to other consultants that work on our client's database instance. Do you know a slicker way to do this? Overloading? Wrapper procedures? Example below. PROCEDURE track_init( i_sch VARCHAR2 := NULL, i_first NUMBER := NULL, i_seq VARCHAR2 := NULL, i_pg VARCHAR2 := NULL, i_reppg VARCHAR2 := NULL, i_track VARCHAR2 := NULL); PROCEDURE track_init( i_sch VARCHAR2 := glb.sch, i_first NUMBER := 0, i_seq VARCHAR2 := 'SEQNUM', i_pg VARCHAR2 := 'PGNO', i_reppg VARCHAR2 := 'REPPGNO', i_track VARCHAR2 := glb.tcrf)

Answer Wiki

Thanks. We'll let you know when a new response is added.

If you need to hide values from people who is working in the same schema then your idea with wrapper does not work, because wrapper does not obfuscate literals. Since schema is the same then anybody can dig into wrapped package body and find values there.
(By the way, starting from 9i you are prohibited to have different defaults in spec and body)

One of the easy ways to do that is to wrap some simple encryption and decryption procedures in package body like this:

————– script ——————–
create or replace package pkg is
— default string is just value of: select enc(‘my_default’) from dual
procedure hi(p varchar2 := ‘nz`efgbvmu’);
end;
/

create or replace package body pkg is

function enc(t varchar2) return varchar2 is
v varchar2(128) := null;
x pls_integer := 1;
y pls_integer := x;
begin
for I in 1..length(t) loop
v := v || chr( ascii(substr(t, I, 1)) + y );
end loop;
return v;
end;

function dec(t varchar2) return varchar2 is
v varchar2(128) := null;
x pls_integer := 1;
y pls_integer := x;
begin
for I in 1..length(t) loop
v := v || chr( ascii(substr(t, I, 1)) – y );
end loop;
return v;
end;

procedure hi(p varchar2 := ‘nz`efgbvmu’) is
begin
dbms_output.put_line(‘param=’||dec(p));
end;

end;
/

————- script end ——————–

Wrap this source with wrapper and just call pkg.hi() procedure to see how it works.

good luck

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Holtmeyer
    What is the point in trying to confuse the other consultants? WHO WOULD THAT BENEFIT? In my experience, the job of most consultants is to actually help the client; fueding with other consultants really doesn't benefit the client. (Hopefully I totally misunderstood the intent in your email!) You may look into creating a schema to hold your 'secret' development efforts and simply don't grant permissions to the other consultants -- of course you would have to generate grants to whatever process tries to access the code in this new schema. S.Holtmeyer
    0 pointsBadges:
    report
  • NOCOPY
    Dear S.Holtmeyer The idea is to protect the intellectual property. I wrote the program for our company so we are the only ones who should be able to use it unless we licence the use of it. It's simple as that. Actually the initial example i provided doesn't work. All I really need to do is set the arguments to null defaults and populate the other private variables in the package body with nvl(i_input_var,actual_default). This manupulation will not be visible in the source code because it is wrapped. That does it. Thank you all. NOCOPY
    0 pointsBadges:
    report
  • NOCOPY
    nitro9, I did not see your suggestion before I replied to Holtmeyer. Thank you for your tips and the code. The problem with my example is that it takes defaults from the speck and not the body so I end up with null defaults. For this application I don't think the competitors are going to delve into the wrapped code and if they do then they really have nothing else to do, and they aren't smart enough for this stuff either :-) I may use your code against a more dangerous enemy sometime though. :-) Muchas Gracias.
    0 pointsBadges:
    report
  • NOCOPY
    nitro9, I did not see your suggestion before I replied to Holtmeyer. Thank you for your tips and the code. The problem with my example is that it takes defaults from the speck and not the body so I end up with null defaults. For this application I don't think the competitors are going to delve into the wrapped code and if they do then they really have nothing else to do, and they aren't smart enough for this stuff either :-) I may use your code against a more dangerous enemy sometime though. :-) Muchas Gracias.
    0 pointsBadges:
    report
  • Hopkihc
    Default values are intellectual property? Sorry... I couldn't resist. :)
    0 pointsBadges:
    report
  • NOCOPY
    It's okay hopkihc, you are forgiven. Do you give away db passwords? :-) Let's say you are looking at the package speck. Can you tell what track_init does? What programs to use to access cached results of track_init?
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following