[sqr-users] Dynamic database links

["Debbie Larney" <debbie.larney@thicare.com>]

Hello Everyone,

I was wondering if it was possible to dynamically define database links in sqr. 
I am trying to access a vendor's database via a database link. I know that the 
database link is set up properly because if I hardcode the link name in my sql 
statement I am able to run my program to success. The problem I have is that I 
want to define this value dynamically based on run control parameters. I get 
compile errrors when I try this dynamically because the database link variable 
name is not determined until after the program compiles.

I presume I could define the tables in the database that I am executing my 
program from but I really don't want to have to do that. Anyone have any great 
ideas.

Snippets of my code and the errors from the log file are listed below. Thanks 
in advance for any assistance
--Definition of the database link
if $kronos_db = 'KPRD'
      let $dblink   = {dblink_kprd}--This is defined gloablly
else
  if $kronos_db = 'KTST'
      let $dblink   = {dblink_ktst}--This is defined gloablly
  else
    if $kronos_db = 'KDEV'
        let $dblink   = {dblink_kdev}--This is defined gloablly
    else
      if $kronos_db = 'KSYS'
          let $dblink   = {dblink_ksys}--This is defined gloablly
 
      end-if
    end-if
  end-if
end-if 
--One of the procedures with a compile error
!-----------------------------------------------------------------------------!
! Procedure  :  Check-New-BU
! Called From:  Get-RC-Params
! Description:  Checks to see if payrules already exist for the selected 
!               clone to bu
!-----------------------------------------------------------------------------!
begin-procedure Check-New-BU
   move 'Check-New-BU' to $operation_name
   #ifdef debugf
      show 'Procedure: ' $operation_name
   #end-if

let $found_new_bu = 'N'

begin-select loops=1
a.name  

   Let $found_new_bu = 'Y'

   show 'Error: new BU payrules already exist = ' $new_bu

from payruleids [$dblink] a                  
where substr(a.name,1,5) = $new_bu 


End-Select

end-procedure Check-New-BU

error below notice that the dblink is not defined at compile time and not part 
of the sql

SQR 5528) ORACLE OCIStmtExecute error 942 in cursor 32: 
ORA-00942: table or view does not exist 
SQL: SELECT a.name from payruleids 
Error on line 260: (SQR 3716) Error in SQL statement. 



******************************************************************** 
CONFIDENTIALITY NOTICE: 
The information contained in this e-mail is legally privileged and confidential 
information intended only for the use of the individual or entity to whom it is 
addressed.  If the reader of this message is not the intended recipient, you 
are hereby notified that any viewing, dissemination, distribution, or copying 
of this e-mail message is strictly prohibited.  If you have received and/or are 
viewing this e-mail in error, please immediately notify the sender by reply 
e-mail, and delete this e-mail from your system.

Thank you. 
******************************************************************** 



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users