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

Reg
Sutharsan

[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
Maaher
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.

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


Current Time: Mon Dec 05 23:58:11 CST 2016

Total time taken to generate the page: 0.05015 seconds