Unlock oracle user account

[chuanye]

Dear all,
I am trying to create an utility inside the clarion app, the utility can unlock oracle user account. Currently I am doing that within sqlplus.
Any idea? Thanks in advance.

Kind regards
cy

 

[ShankarJ]

Hi!

Use a Dummy table and send the SQL statement via a PROP:SQL to the Oracle Server.

Which version of Clarion are you using?

Regards

 

[chuanye]

Hi ShankarJ,
I'm using c6.3, so I can send eg "ALTER USER SCOTT ACCOUNT UNLOCK" via PROP:SQL, right?
Thanks.

Kind regards
cy

 

[ShankarJ]

Hi!

Exactly. Since you are using Clarion 6.3, you can take benefit of the TURBOSQL driver string for your dummy table. An example is shown below ::

DummyTable                   FILE,DRIVER('ODBC', '/TURBOSQL = TRUE'),OWNER(MyOdbcConnectString),PRE(DUM),BINDABLE,THREAD
Record                         RECORD,PRE()
DummyDate                        STRING(8)
DummyDate_GRP                    GROUP,OVER(BUSINESS_DATE)
DummyDate_DT                       DATE
DummyDate_TM                       TIME
                                 END
DummyNumber                      REAL
DummyString                      CSTRING(21)
                               END
                             END

  CLOSE(DummyTable)
  OPEN(DummyTable)
  IF ERRORCODE()
     MESSAGE(CLIP(ERRORFILE()) & ' : ' & ERROR() & ' [' & ERRORCODE() & ']|' & 'File Error : ' & FILEERROR() & ' [' & FILEERRORCODE() & ']','E R R O R - OPENSQL()')
     RETURN
  END

  DummyTable{PROP:SQL} = 'ALTER USER SCOTT ACCOUNT UNLOCK'

  IF ERRORCODE()
     MESSAGE(CLIP(ERRORFILE()) & ' : ' & ERROR() & ' [' & ERRORCODE() & ']|' & 'File Error : ' & FILEERROR() & ' [' & FILEERRORCODE() & ']','E R R O R - PROP:SQL')
  ELSE
!    .... In case your query returns a result set, LOOP through rows as shown below and if not nothing ....
     Row# = 0
     LOOP
        NEXT(DummyTable)

        IF ERRORCODE() THEN BREAK.

        Row# += 1
     END
  END

  CLOSE(DummyTable)

Regards

 

[ShankarJ]

Hi!

Should be ::

DummyDate                        STRING(8)
DummyDate_GRP                    GROUP,OVER(DummyDate)
DummyDate_DT                       DATE
DummyDate_TM                       TIME
                                 END

I have just defined some dummy columns but you would normally define the columns based on the result set.

Regards