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?

Save the following script to checkuserexists.sql:

code:


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:

code:


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.

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 :(

Works for me. Attach your script.

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!

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

code:


        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:

code:


        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"

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"'

Attach your sql.ini file.

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

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.

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:

code:


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

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

code:


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.

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 ?

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

No, it should not take that long.

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.

Same problem :(

Quote:

No, it should not take that long.

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

Quote:

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...)

ReadRegStr HKLM Software\Oracle inst_loc