Home » Developer & Programmer » Forms » Variable Substitution (DS10g)
Variable Substitution [message #434760] Thu, 10 December 2009 11:02 Go to next message
rkgoyal98
Messages: 39
Registered: October 2009
Member

Hello,

I am a newbee here. I am working on a project. In one of the form i am required to do variable substitution in one of the calculation. Code for the same is like this.

DECLARE
 V_RULE VARCHAR2(2000);
BEGIN
  SELECT RULE INTO V_RULE FROM PRMARUT WHERE EDCODE = :BLK_PRMAMAS.EDCODE;
  :AMT := SELECT &V_RULE FROM DUAL;
END;

RULE column in the table contains a set of calculations that are to be executed to get the value.
& works in SQLPLUS but gives error in forms 10 pl/sql compilation.
how can this be done.
Regards




EDIT: fixed code tags, you only need 1 on each side of the code block - CM

[Updated on: Thu, 10 December 2009 11:04] by Moderator

Report message to a moderator

Re: Variable Substitution [message #434762 is a reply to message #434760] Thu, 10 December 2009 11:07 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can't - variable substitution is something sqlplus does. It has no meaning in forms.

I suspect you need to use dynamic sql here. Give an example of the data v_rule contains.
Re: Variable Substitution [message #434776 is a reply to message #434760] Thu, 10 December 2009 11:57 Go to previous messageGo to next message
rkgoyal98
Messages: 39
Registered: October 2009
Member

Hello,

some of the rules i am quoting here, while there 5 such rules for difftent conditions to be selected on the basis of value in one of the items on the block
	((200*3+(:cons-200)*3.3)*1.09)+60*:unit

	or

	((:cons*3)*1.09)+60*:unit

	or
	
	((60*4+(:cons-60)*4.5)*1.09)+170

we also have some of the programes in PRO*C which we will like to convert here in PL/SQL within the forms,. These programmes are using exec sql execute immediate as in this code
kindly advise me how to do simmilar things in forms PL/SQL
		sprintf(sql_stmt1.arr,"UPDATE NPAYCAL_TEMP SET PRE_RULEVAL = 
				(SELECT %s FROM NPAYCAL_TEMP WHERE PRE_RULE = '%')"
						,pre_rule_flag.arr,pre_rule_flag.arr);
		sql_stmt1.len = strlen(sql_stmt1.arr);
		EXEC SQL EXECUTE IMMEDIATE :sql_stmt1;
		EXEC SQL SELECT NVL(PRE_RULEVAL,0) INTO :pre_ruleval FROM NPAYCAL_TEMP;
Re: Variable Substitution [message #434780 is a reply to message #434760] Thu, 10 December 2009 12:13 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's pretty similar, suggest you read up on execute immediate in the documentation.
Unfortunately you can't use it in forms so you're going to have to move the code into a stored procedure in the datatbase and call that from your form, passing the relevant datablock items.
Something like this:

DECLARE
 V_RULE VARCHAR2(2000);
BEGIN
  SELECT RULE INTO V_RULE FROM PRMARUT WHERE EDCODE = p_EDCODE;
  EXECUTE IMMEDIATE 'SELECT '||v_rule||'from dual' into p_amt;
END;
Re: Variable Substitution [message #434977 is a reply to message #434760] Sat, 12 December 2009 10:12 Go to previous messageGo to next message
rkgoyal98
Messages: 39
Registered: October 2009
Member

Hello,

I have worked on the code today and made my code as under
FUNCTION CAL_ELECAMT(P_STATE CHAR,P_QUARTERTYPE CHAR,P_FROMDATE DATE,P_TODATE DATE,P_MONTHS NUMBER,P_CONS NUMBER ) RETURN NUMBER
AS
	P_RULE VARCHAR(2000);
    P_FROMDATE1 DATE;
	P_TODATE1 DATE;
	P_FROMDATE2 DATE;
	P_TODATE2 DATE;
	P_RATEFROMDATE DATE;
	P_RATETODATE DATE;
	R_AMT1 NUMBER;
	R_AMT2 NUMBER;
	R_AMT NUMBER;
	SQLSTM VARCHAR2(2000);
	P_UNIT NUMBER;
	P_UNITRATE NUMBER;
	P_ELECRATE NUMBER;
	P_EDRATE NUMBER;
BEGIN
	SELECT FROMDATE,NVL(TODATE,SYSDATE) INTO P_RATEFROMDATE,P_RATETODATE FROM PRMAQTRELRATE WHERE STATE = P_STATE AND QUARTERTYPE = P_QUARTERTYPE
								AND P_CONS BETWEEN STARTRANGE AND ENDRANGE 
								AND P_FROMDATE BETWEEN FROMDATE AND NVL(TODATE,SYSDATE);
	IF P_TODATE > P_RATETODATE THEN
		P_FROMDATE1 := P_FROMDATE;
		P_TODATE1 := P_RATETODATE;
		P_FROMDATE2 := P_RATETODATE+1;
		P_TODATE2 := P_TODATE;
 /*       SELECT RULE INTO P_RULE FROM PRMAQTRELRATE WHERE STATE = P_STATE AND QUARTERTYPE = P_QUARTERTYPE
								AND P_CONS BETWEEN STARTRANGE AND ENDRANGE
								AND P_FROMDATE1 BETWEEN FROMDATE AND NVL(TODATE,SYSDATE);
								
		SQLSTM := 'SELECT '||P_RULE||'INTO R_AMT1 FROM PRMAQTRELRATE WHERE STATE = P_STATE AND QUARTERTYPE = P_QUARTERTYPE
								AND P_CONS BETWEEN STARTRANGE AND ENDRANGE
								AND P_FROMDATE1 BETWEEN FROMDATE AND NVL(TODATE,SYSDATE)';
		EXECUTE IMMEDIATE SQLSTM;
		SELECT RULE INTO P_RULE FROM PRMAQTRELRATE WHERE STATE = P_STATE AND QUARTERTYPE = P_QUARTERTYPE
								AND P_CONS BETWEEN STARTRANGE AND ENDRANGE 
								AND P_FROMDATE2 BETWEEN FROMDATE AND NVL(TODATE,SYSDATE);
		SQLSTM := 'SELECT '||P_RULE||'INTO R_AMT2 FROM PRMAQTRELRATE WHERE STATE = P_STATE AND QUARTERTYPE = P_QUARTERTYPE
								AND P_CONS BETWEEN STARTRANGE AND ENDRANGE 
								AND P_FROMDATE2 BETWEEN FROMDATE AND NVL(TODATE,SYSDATE)';
		EXECUTE IMMEDIATE SQLSTM;
		R_AMT := NVL(R_AMT1,0)+NVL(R_AMT2,0);*/
	ELSE
	   BEGIN
    	   SELECT UNIT,UNITRATE,ELECRATE,EDRATE,RULE_FORMULLA INTO P_UNIT,P_UNITRATE,P_ELECRATE,P_EDRATE,P_RULE FROM PRMAQTRELRATE 
                        WHERE STATE = P_STATE AND QUARTERTYPE = P_QUARTERTYPE
								AND P_CONS BETWEEN STARTRANGE AND ENDRANGE 
								AND P_FROMDATE BETWEEN FROMDATE AND NVL(TODATE,SYSDATE);
		END;
		EXECUTE IMMEDIATE 'SELECT ' ||P_RULE || ' FROM DUAL' INTO R_AMT;
		
		/* VALUE OF P_RULE 
            ROUND((((200*3)+(P_CONS-200)*P_ELECRATE))+(P_CONS*P_EDRATE)+(P_UNIT*P_UNITRATE),0)
            
            THIS STATEMENT WORKS FINE IF RUN WITHOUT EXECUTE IMMEDIATE
            SELECT ROUND((((200*3)+(P_CONS-200)*P_ELECRATE))+(P_CONS*P_EDRATE)+(P_UNIT*P_UNITRATE),0) INTO R_AMT FROM DUAL;
            */
	END IF;
	RETURN R_AMT;
END CAL_ELECAMT;


However i get error ora-00904 and am not able to work out the reason
Kindly help me
regards

[Updated on: Sat, 12 December 2009 10:15]

Report message to a moderator

Re: Variable Substitution [message #434979 is a reply to message #434760] Sat, 12 December 2009 10:28 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well you're missing some spaces for starters.
And if you look at my example the INTO <variable> bit goes outside the dynamic string, which is not what you've done.
Re: Variable Substitution [message #434981 is a reply to message #434760] Sat, 12 December 2009 10:40 Go to previous messageGo to next message
rkgoyal98
Messages: 39
Registered: October 2009
Member

Hello,

your statement was as under

EXECUTE IMMEDIATE 'SELECT '||v_rule||'from dual' into p_amt;


I modified this into my statement as under
EXECUTE IMMEDIATE 'SELECT ' ||P_RULE || ' FROM DUAL' INTO R_AMT;


do you mean space before || and after P_RULE AND AGAIN AFTER ||

INTO i have also put after dynamic clause, kindly help me if i am missing something more

Regards
Re: Variable Substitution [message #434982 is a reply to message #434760] Sat, 12 December 2009 11:09 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
There are spaces missing immediately before two of the INTO's, but since those intos are in the wrong place I wouldn't worry about the space two much.

Standard way of debugging dynamic sql is output the entire dynamic string using dbms_output or similar. If after putting the INTO's in the right place you are still getting errors, do that to see what the problem is.
Re: Variable Substitution [message #434984 is a reply to message #434977] Sat, 12 December 2009 11:48 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
R_AMT := ROUND((((200*3)+(P_CONS-200)*P_ELECRATE))+(P_CONS*P_EDRATE)+(P_UNIT*P_UNITRATE),0);
Re: Variable Substitution [message #434986 is a reply to message #434760] Sat, 12 December 2009 20:02 Go to previous messageGo to next message
rkgoyal98
Messages: 39
Registered: October 2009
Member

Hello,

After further trial and error i could make it work like this
FUNCTION CAL_ELECAMT(P_STATE CHAR,P_QUARTERTYPE CHAR,P_FROMDATE DATE,P_TODATE DATE,P_MONTHS NUMBER,P_CONS NUMBER ) RETURN NUMBER
AS
	P_RULE VARCHAR(2000);
    P_FROMDATE1 DATE;
	P_TODATE1 DATE;
	P_FROMDATE2 DATE;
	P_TODATE2 DATE;
	P_RATEFROMDATE DATE;
	P_RATETODATE DATE;
	R_AMT1 NUMBER;
	R_AMT2 NUMBER;
	R_AMT NUMBER;
	SQLSTM VARCHAR2(2000);
	P_UNIT NUMBER;
	P_UNITRATE NUMBER;
	P_ELECRATE NUMBER;
	P_EDRATE NUMBER;
BEGIN
	SELECT FROMDATE,NVL(TODATE,SYSDATE) INTO P_RATEFROMDATE,P_RATETODATE FROM PRMAQTRELRATE WHERE STATE = P_STATE AND QUARTERTYPE = P_QUARTERTYPE
								AND P_CONS BETWEEN STARTRANGE AND ENDRANGE 
								AND P_FROMDATE BETWEEN FROMDATE AND NVL(TODATE,SYSDATE);
	IF P_TODATE > P_RATETODATE THEN
		P_FROMDATE1 := P_FROMDATE;
		P_TODATE1 := P_RATETODATE;
		P_FROMDATE2 := P_RATETODATE+1;
		P_TODATE2 := P_TODATE;
 		R_AMT := NVL(R_AMT1,0)+NVL(R_AMT2,0);
	ELSE
	   BEGIN
    	   SELECT UNIT,UNITRATE,ELECRATE,EDRATE,RULE_FORMULLA INTO P_UNIT,P_UNITRATE,P_ELECRATE,P_EDRATE,P_RULE FROM PRMAQTRELRATE 
                        WHERE STATE = P_STATE AND QUARTERTYPE = P_QUARTERTYPE
								AND P_CONS BETWEEN STARTRANGE AND ENDRANGE 
								AND P_FROMDATE BETWEEN FROMDATE AND NVL(TODATE,SYSDATE);
		END;
		SQLSTM := 'SELECT '||P_RULE||' FROM DUAL ';
		EXECUTE IMMEDIATE  SQLSTM INTO R_AMT USING P_CONS,P_ELECRATE,P_CONS,P_EDRATE,P_UNIT,P_UNITRATE;
	END IF;
	RETURN R_AMT;
END CAL_ELECAMT;


But Problem with this is
1. DBMS_OUTPUT was showing correct systax but there was still an error so i tried "execute immediate ..... into .... useing ...." method and the same worked fine as in the above code.

2. Since p_rule is having different values and different no. of variable selected dynamically as such using clause also needs to be changed which is not possible.

3. It appears that execute immediate has some conditions that is causing some trouble but i am not able to get detailed article on the execute immediate
Regards
Re: Variable Substitution [message #434991 is a reply to message #434986] Sun, 13 December 2009 03:58 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
rkgoyal98 wrote on Sun, 13 December 2009 02:02

2. Since p_rule is having different values and different no. of variable selected dynamically as such using clause also needs to be changed which is not possible.


In that case you might be better off using dbms_sql instead.

rkgoyal98 wrote on Sun, 13 December 2009 02:02

3. It appears that execute immediate has some conditions that is causing some trouble but i am not able to get detailed article on the execute immediate

What trouble? And did you try looking it up in the documentation?
Re: Variable Substitution [message #434999 is a reply to message #434760] Sun, 13 December 2009 09:27 Go to previous messageGo to next message
rkgoyal98
Messages: 39
Registered: October 2009
Member

Hello,

After debuging i found that variables can not be used directly in dynamic sql and we have to bind them and later ass "using" clause. However as no. of variables were also changing as such I solved the problem by putting the values of variables in a temprary table. Execute statement is now called on the columns of table. The programe is now running fine.
Regards
Re: Variable Substitution [message #435000 is a reply to message #434999] Sun, 13 December 2009 09:36 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
> However as no. of variables were also changing as such I solved the problem by putting the values of variables in a temprary table.
>Execute statement is now called on the columns of table. The programe is now running fine.

1) data is extracted from existing table
2) data is placed in temp table
3) data is obtained from temp table to construct dynamic SQL
4) dynamic SQL is executed to obtain desired answer.

I doubt this will scale well in multi-user production environment.

[Updated on: Sun, 13 December 2009 09:36]

Report message to a moderator

Re: Variable Substitution [message #435001 is a reply to message #434760] Sun, 13 December 2009 09:52 Go to previous messageGo to next message
rkgoyal98
Messages: 39
Registered: October 2009
Member

Hello,

That is an eye opener for me. I will have to look at it further. Can you please suggest me some hints for solving it. Of course i will also look at dbms_sql
Regards.
Re: Variable Substitution [message #437535 is a reply to message #435001] Mon, 04 January 2010 23:21 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Did you get this to work?

David
Previous Topic: tab exit
Next Topic: Oracle Forms Hebrew font Problem
Goto Forum:
  


Current Time: Thu Sep 29 07:21:01 CDT 2016

Total time taken to generate the page: 0.17049 seconds