Forum Stats

  • 3,768,641 Users
  • 2,252,827 Discussions
  • 7,874,667 Comments

Discussions

SQR and Oracle 10g issues

Hi I am new to SQR and in the middle of investigating several issues relating to 10g.
We are using Hyperion SQR server 8.5.0.0.0.566 and Oracle 10.2.0.3.0

Currently, I have encountered several issues relating to variables loosing their values outside of sql block in the sqr script.

Eg:

! Get field delimiter character
!
BEGIN-SQL
BEGIN
$_G_Delim := pkg_K.k_Fld_Delim ;;
$_K_MinDate := pkg_K.k_MinDate ;;
$_K_MaxDate := pkg_K.k_MaxDate ;;
$_G_BaseCurrency := pkg_Util.SysPropVal ('CURRBASE','N');;
$_G_DocumentStyle := pkg_feature.val('DOCSTYLE') ;;
END ;;
END-SQL

DO DISP ($_G_BaseCurrency)
DO DISP ($_G_DocumentStyle)

In the above example, inside the sql block, values were successfully assigned to the variables. but outside the sql block, variables lost their assigned value and current value is NULL.

Another example:
! Set Lock to indicate PrintServer is active
!
LET $_G_OK = 'N'
LET $_G_ID = TO_CHAR(#_sqr-pid)
!
BEGIN-SQL
DECLARE
g_result PLS_INTEGER;;
g_errdescr VARCHAR2(250);;
g_SysMsgKey VARCHAR2(250);;
g_lockid PLS_INTEGER;;
BEGIN
g_lockid := null;;
g_result := pkg_lock.setlock(ikeycode => 'PID',
ikeyid => $_G_ID,
iCallProg => 'PrintServer-1',
oerrdescr => g_errdescr,
oSysMsgKey => g_SysMsgKey,
olockid => g_lockid);;
if g_result = 0 then
$_G_OK := 'Y';;
$_G_ServerLockId := to_char(g_lockid);;
end if;;
EXCEPTION
when others then
NULL;;
END;;
END-SQL
!
IF $_G_OK <> 'Y'
DO DISP ('***********************************')
DO DISP ('Could not get access to Server PID')
DO DISP ($_G_ID)
DO DISP ('Assume there is a problem')
DO DISP ('***********************************')
STOP
END-IF

In the example, pkg_lock successfully returned lockId and therefore $_G_OK is set to 'Y' inside the sql block. but once outside the sql block, $_G_OK is NULL and therefore falling into the error condition.

Same code is working ok under 9i.

Can any one help me to resolve this problem?
Thanks in advance.
This discussion has been closed.