Hi,
Never got this before and kind of strange i am facing a problem.
When i try to execute the below statement using global logic. I am getting an error saying "015 - SUBSCRIPT VALUE NOT WITHIN RANGE OF DIMENSION - L -0.235023=0245024=02 TEST ORA-06550: line 1, column 47: PLS-00103:".
When i comment out the DECLARE statment and hard code the parameter values. The function works fine, but with DECLARE i am having problem.
Global Logic contains below statements:
P PARMS($_ACTION)
$_CURFUNCT = 'ABC'
$_ACTION = 'ADD'
$_LINK_FUNCTION = 'MENU'
SQL
DECLARE
v_action VARCHAR2(8) := :$ACTION;
v_curfunct VARCHAR2(8) := :$CURFUNCT;
v_link_function VARCHAR2(8) := :$LINK_FUNCTION;
BEGIN
performance_logging_package.write_pro4_log(v_action, v_curfunct, v_link_function);
END;
ENDSQL
Any help is appreciated
Thanks
Click the link below to see the new game I'm developing!
10 replies to this topic
#2
Balaram
-
- Members
-
- 20 posts
Member
- Gender:Male
- Location:INDIA
Posted 03 August 2006 - 08:17 AM
Hello Sir,
P PARMS($_ACTION)
did you try like this .. i do not know whether this will work or not.. but i guess no HARM IN TRYING
$_CURFUNCT = 'ABC'
$_ACTION = 'ADD'
$_LINK_FUNCTION = 'MENU'
SQL
DECLARE
v_action VARCHAR2(8) := $ACTION;
v_curfunct VARCHAR2(8) := $CURFUNCT;
v_link_function VARCHAR2(8) := $LINK_FUNCTION;
BEGIN
performance_logging_package.write_pro4_log(:v_action,:v_curfunct,:v_link_functio
);
END;
ENDSQL
P PARMS($_ACTION)
did you try like this .. i do not know whether this will work or not.. but i guess no HARM IN TRYING
$_CURFUNCT = 'ABC'
$_ACTION = 'ADD'
$_LINK_FUNCTION = 'MENU'
SQL
DECLARE
v_action VARCHAR2(8) := $ACTION;
v_curfunct VARCHAR2(8) := $CURFUNCT;
v_link_function VARCHAR2(8) := $LINK_FUNCTION;
BEGIN
performance_logging_package.write_pro4_log(:v_action,:v_curfunct,:v_link_functio
);
END;
ENDSQL
#3
Chris Mackenzie
-
- Members
-
- 372 posts
ProIV Guru
- Gender:Male
- Location:Bristol, United Kingdom
Posted 03 August 2006 - 01:50 PM
Have you checked that none of your scratch
vars (with or without the underscore - whichever
you are actually using - obviously in your example
it will never work as the vars set up before the SQL
are not actually used in the SQL) are not declared as arrays?
vars (with or without the underscore - whichever
you are actually using - obviously in your example
it will never work as the vars set up before the SQL
are not actually used in the SQL) are not declared as arrays?
The content and views expressed in this message are those
of the poster and do not represent those of any organisation.
of the poster and do not represent those of any organisation.
#4
Rob Donovan
-
- Admin
-
- 1,652 posts
rob@proivrc.com
- Gender:Male
- Location:Spain
Posted 03 August 2006 - 01:55 PM
Hi,
I have had lots of problems before, trying to use SQL in Global Logics.
It seems very buggy, just so you know...
Rob.
I have had lots of problems before, trying to use SQL in Global Logics.
It seems very buggy, just so you know...
Rob.
#5
cdi_rajesh
-
- Members
-
- 8 posts
Newbie
- Gender:Male
Posted 03 August 2006 - 10:38 PM
Sorry i forgot to correct them in the post. But in my actual function they are right (see below) and still having problem.
$_CURFUNCT = 'ABC'
$_ACTION = 'ADD'
$_LINK_FUNCTION = 'MENU'
SQL
DECLARE
v_action VARCHAR2(8) := :$_ACTION;
v_curfunct VARCHAR2(8) := :$_CURFUNCT;
v_link_function VARCHAR2(8) := :$_LINK_FUNCTION;
BEGIN
performance_logging_package.write_pro4_log(v_action, v_curfunct, v_link_function);
END;
ENDSQL
Thanks
$_CURFUNCT = 'ABC'
$_ACTION = 'ADD'
$_LINK_FUNCTION = 'MENU'
SQL
DECLARE
v_action VARCHAR2(8) := :$_ACTION;
v_curfunct VARCHAR2(8) := :$_CURFUNCT;
v_link_function VARCHAR2(8) := :$_LINK_FUNCTION;
BEGIN
performance_logging_package.write_pro4_log(v_action, v_curfunct, v_link_function);
END;
ENDSQL
Thanks
#6
Guest_Guest_*
Guest_Guest_*
-
- Guests
Posted 04 August 2006 - 03:53 AM
My knowledge of this is not great, so apologies in advance, but do you need the DECLARE??
Could you not just pass in the PRO-IV scratch vars as arguments into the procedure call??
e.g.
$_CURFUNCT = 'ABC'
$_ACTION = 'ADD'
$_LINK_FUNCTION = 'MENU'
SQL
BEGIN
performance_logging_package.write_pro4_log(:$_ACTION, :$_CURFUNCT, :$_LINK_FUNCTION);
END;
ENDSQL
Could you not just pass in the PRO-IV scratch vars as arguments into the procedure call??
e.g.
$_CURFUNCT = 'ABC'
$_ACTION = 'ADD'
$_LINK_FUNCTION = 'MENU'
SQL
BEGIN
performance_logging_package.write_pro4_log(:$_ACTION, :$_CURFUNCT, :$_LINK_FUNCTION);
END;
ENDSQL
#7
Jeff Hon
-
- Members
-
- 29 posts
Member
- Gender:Male
- Location:Melbourne, Australia
Posted 06 August 2006 - 10:53 PM
Yes, I do not believe the DECLARE section is necessary in PRO-IV. I would re-write your embedded PLSQL to something like this:
$_CURFUNCT = 'ABC'
$_ACTION = 'ADD'
$_LINK_FUNCTION = 'MENU'
#X = SYS-SQL(" BEGIN performance_logging_package.write_pro4_log(' " +
$_ACTION + " ' , ' " +
$_CURFUNCT + " ' , ' " +
$_LINK_FUNCTION +
" ); END; ")
$_CURFUNCT = 'ABC'
$_ACTION = 'ADD'
$_LINK_FUNCTION = 'MENU'
#X = SYS-SQL(" BEGIN performance_logging_package.write_pro4_log(' " +
$_ACTION + " ' , ' " +
$_CURFUNCT + " ' , ' " +
$_LINK_FUNCTION +
" ); END; ")
#8
cdi_rajesh
-
- Members
-
- 8 posts
Newbie
- Gender:Male
Posted 08 August 2006 - 04:10 AM
I tired another approach, instead of using Global Logic, I am using Global Update. Now that I am not getting any error, but the strange part is it’s not executing the SQL block (when I can check on backend, I don’t see any new records).
Strange…!!!???
Strange…!!!???
#9
Guest_Guest_*
Guest_Guest_*
-
- Guests
Posted 09 August 2006 - 03:56 AM
Again, I am no expert, but I would try the following:
1. Just to be sure, execute the PL/SQL package in an anonymous block, using an editor of your choice. Ensure that the row(s) have been inserted.
2. If 1 works, then it may be a commit issue. As your proc seems to be a logging package, try adding the directive PRAGMA AUTONOMOUS_TRANSACTION (apologies if syntax is not correct) into the proc and at the end issue an explicit COMMIT. (NB. The autonomous transaction can be useful with logging packages, as if a rollback occurs during program execution, logging is not rolled back. Be careful with its use otherwise.)
3. Run your logic from PRO-IV and check if rows have been inserted.
4. If this hasn't worked, then it may be a case that your embedded SQL hasn't executed at all (as you suggested) and I would investigate other ways via PRO-IV to execute the package.
Apologies if you have already tried this approach.
1. Just to be sure, execute the PL/SQL package in an anonymous block, using an editor of your choice. Ensure that the row(s) have been inserted.
2. If 1 works, then it may be a commit issue. As your proc seems to be a logging package, try adding the directive PRAGMA AUTONOMOUS_TRANSACTION (apologies if syntax is not correct) into the proc and at the end issue an explicit COMMIT. (NB. The autonomous transaction can be useful with logging packages, as if a rollback occurs during program execution, logging is not rolled back. Be careful with its use otherwise.)
3. Run your logic from PRO-IV and check if rows have been inserted.
4. If this hasn't worked, then it may be a case that your embedded SQL hasn't executed at all (as you suggested) and I would investigate other ways via PRO-IV to execute the package.
Apologies if you have already tried this approach.
Reply to this topic
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users
Click the link below to see the new game I'm developing!
