Oracle: Detect if user exist or not

internetfreakz
20th February 2007 16:17 UTC

Oracle: Detect if user exist or not
Hello,

Is there some code (for an nsi script) to detect if a user already exist in an oracle database when i give the username and a database?

Who can I implement that?

iceman_k
20th February 2007 16:44 UTC

Save the following script to checkuserexists.sql:


SET VERIFY OFF
WHENEVER SQLERROR EXIT SQL.SQLCODE
VARIABLE NUM NUMBER;
BEGIN
    BEGIN
        SELECT COUNT(*) INTO :NUM FROM ALL_USERS WHERE USERNAME=UPPER('&1');
    EXCEPTION WHEN OTHERS THEN
        :NUM := SQLCODE;
    END;
END;
/
EXIT :NUM

You can run it as follows:


SetOutPath $TEMP
File checkuserexists.sql
Push $1
nsExec::Exec "sqlplus.exe -S sys/manager@orcl @checkuserexists.sql SOMEUSER"
Pop $1
IntCmp $1 0 not_exist oracleerror
IntCmp $1 1 0 0 oracleerror
MessageBox MB_OK "User exists"
goto done
not_exist:
MessageBox MB_OK "User doesn't exist"
goto done
oracleerror:
MessageBox MB_OK|MB_ICONSTOP "Oracle Error: $1"
done:
Pop $1

Replace sqlplus.exe with the correct path, and SOMEUSER with the user name.
Replace sys/manager@orcl with the correct oracle login information- this should ideally be SYSTEM.

internetfreakz
21st February 2007 01:23 UTC

Thanks for your answer but is it possible there's something wrong? When I run the script, I get always the message "user exists", even if I give a user that's not in my database :(

iceman_k
21st February 2007 01:36 UTC

Works for me. Attach your script.

internetfreakz
21st February 2007 03:49 UTC

Hellow,

My complete nsi script is in attach.

First, the function for the custom page 'Oracle' will be executed, then the function for the custom page 'sql'.

After that, the wizard starts to execute my first section 'pisad'.

In the next section 'database', i will check if a given user (in the custom page 'sql') is already exists or not.

There, I get everytime 'user exist'.

Thanks for your response!

iceman_k
21st February 2007 05:10 UTC

It appears to me that checkuserexists.sql is not being extracted to $TEMP properly.
This code looks suspicious:


        File /r "sql"

        ; De locatie van sqlplus bepalen
        StrCpy $0 "$oradir\BIN\sqlplus.exe"
        
        GetFullPathName /SHORT $6 $INSTDIR
        StrCpy $4 "$6\sql\checkuserexists.sql"

SetOutPath $TEMP
File checkuserexists.sql
Push $1
nsExec::Exec "$0 -S system/$db_passsystem@XE @checkuserexists.sql $db_user"

Is checkuserexists.sql in the same directory as the script or is it in the sql subdirectory?

Perhaps it should be:


        File /r "sql"

        ; De locatie van sqlplus bepalen
        StrCpy $0 "$oradir\BIN\sqlplus.exe"
        
        GetFullPathName /SHORT $6 $INSTDIR
        StrCpy $4 "$6\sql\checkuserexists.sql"

Push $1
nsExec::Exec "$0 -S system/$db_passsystem@XE @$4 $db_user"

internetfreakz
21st February 2007 09:45 UTC

I changed my database section to the following but the problem is not solved with that :(

Section "Database" Database
MessageBox MB_OK "Database sectie uitvoeren - Eerst handmatig de gebruiker nog aanmaken!"

SetOutPath "$INSTDIR"
File /r "sql"

; De locatie van sqlplus bepalen
StrCpy $0 "$oradir\BIN\sqlplus.exe"

GetFullPathName /SHORT $6 $INSTDIR
StrCpy $4 "$6\sql\checkuserexists.sql"

Push $1
nsExec::Exec "$0 -S system/$db_passsystem@XE @$4 $db_user"
Pop $1
IntCmp $1 0 not_exist oracleerror
IntCmp $1 1 0 0 oracleerror
MessageBox MB_OK "User exists"
goto done
not_exist:
MessageBox MB_OK "User doesn't exist"
goto done
oracleerror:
MessageBox MB_OK|MB_ICONSTOP "Oracle Error: $1"
done:
Pop $1

;GetFullPathName /SHORT $6 $INSTDIR

; Na eventueel die twee scripts te hebben uitgevoerd moeten er ook nog scripts voor de
; applicatie van PISAD worden geÃ¯nstalleerd.

GetFullPathName /SHORT $6 $INSTDIR
StrCpy $4 "$6\sql\opvullen_tab.sql"
nsExec::ExecToLog '$0 $db_user/$db_passuser@XE @"$4"'

iceman_k
21st February 2007 14:06 UTC

Attach your sql.ini file.

internetfreakz
21st February 2007 14:16 UTC

I have rename the file to .txt just to upload it

iceman_k
21st February 2007 14:19 UTC

FYI, using the registry to find the Oracle home is not reliable starting from Oracle 10g. You should use C:\Program Files\Oracle\Inventory\ContentsXML\inventory.xml.

iceman_k
21st February 2007 14:27 UTC

I don't know what to tell you :) - it works for me with your sql.ini.
Here's something to try:
Before actually executing the sql, pop up a messagebox with the command string:


MessageBox MB_OK "$0 -S system/$db_passsystem@XE @$4 $db_user"

Then execute that exact command from the DOS prompt, followed by:


echo %ERRORLEVEL%

The ERRORLEVEL should show you the return code from the sql.
If the user exists, you will see 1 otherwise you will see 0.

internetfreakz
21st February 2007 16:15 UTC

Hello,

The messagebox gives me the command that we expect but the echo gives me a value 0 :(

When I do the query at the sql prompt, I get the correct value (1) ...

There is a 'time out' of 1 to 2 minutes between the messagebox and the echo, is this normal?

And is there a simple script to extract the Oracle dir from the file C:\Program Files\Oracle\Inventory\ContentsXML\inventory.xml ?

iceman_k
21st February 2007 17:44 UTC

Originally posted by internetfreakz
Hello,

The messagebox gives me the command that we expect but the echo gives me a value 0 :(

When I do the query at the sql prompt, I get the correct value (1) ...

Strange. I don't see this problem. Maybe it is some nsExec problem. Try executing it using Exec.

There is a 'time out' of 1 to 2 minutes between the messagebox and the echo, is this normal?

No, it should not take that long.

And is there a simple script to extract the Oracle dir from the file C:\Program Files\Oracle\Inventory\ContentsXML\inventory.xml ?

You can probably parse it using the xml plugin.
Extract the home(s) from the xml file and then retrieve the directory. There may be multiple homes if the user has installed multiple versions of Oracle.

internetfreakz
21st February 2007 18:50 UTC

Originally posted by iceman_k
Strange. I don't see this problem. Maybe it is some nsExec problem. Try executing it using Exec.

Same problem :(

No, it should not take that long.

Some problem with de db? But what kind of problem, any idea?

You can probably parse it using the xml plugin.
Extract the home(s) from the xml file and then retrieve the directory. There may be multiple homes if the user has installed multiple versions of Oracle.

[/quote]

Hmm, that's new for me but I take a look at that in a few hours ;)

One question yes: How can I find the xml file when the user installs his Oracle software in an other directory than the default? (That was the reason why I look in the register...)

iceman_k
21st February 2007 19:49 UTC

ReadRegStr HKLM Software\Oracle inst_loc