Home » SQL & PL/SQL » Client Tools » SQR and Oracle 10g issue (Variables assigned inside a sql block looses their value outside of sql block.)
SQR and Oracle 10g issue [message #274696] Tue, 16 October 2007 19:22 Go to next message
Messages: 1
Registered: October 2007
Location: Melbourne,Australia
Junior Member
Hi I am new to SQR and in the middle of investigating several issues relating to 10g.
We are using Hyperion SQR server and Oracle

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


!	Get field delimiter character
	$_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 ;;

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)
     g_result          PLS_INTEGER;;
     g_errdescr        VARCHAR2(250);;  
     g_SysMsgKey       VARCHAR2(250);;    
     g_lockid          PLS_INTEGER;;
     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;;
    when others then
    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 ('***********************************')

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?



[mod-edit]added code tags.

[Updated on: Wed, 17 October 2007 01:13] by Moderator

Report message to a moderator

Re: SQR and Oracle 10g issue [message #274748 is a reply to message #274696] Wed, 17 October 2007 01:17 Go to previous message
Messages: 7062
Registered: December 2001
Senior Member
I've added the tags [CODE] and [/CODE] to your post to improve readability. More tips? Check out the forum quidelines in the yellow bar on top of each forum.

This looks like a scope question: the values are only known in the scope of the SQL statement. I don't know SQR myself (in fact, I had to look it up Embarassed) and I doubt whether a lot of people here do. I'm curious.

Previous Topic: Using SQLPLUS on zOS to talk to remote database
Next Topic: PL/SQL developer poor performance
Goto Forum:

Current Time: Sun Jan 22 12:50:26 CST 2017

Total time taken to generate the page: 0.13764 seconds