Jump to content


Photo
- - - - -

Global Logic - SQL


10 replies to this topic

#1 cdi_rajesh

cdi_rajesh

    Newbie

  • Members
  • Pip
  • 8 posts
  • Gender:Male

Posted 03 August 2006 - 08:09 AM

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

#2 Balaram

Balaram

    Member

  • Members
  • PipPip
  • 20 posts
  • 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 :eek:

$_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

Chris Mackenzie

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 368 posts
  • 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?
The content and views expressed in this message are those
of the poster and do not represent those of any organisation.

#4 Rob Donovan

Rob Donovan

    rob@proivrc.com

  • Admin
  • 1,640 posts
  • 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.

#5 cdi_rajesh

cdi_rajesh

    Newbie

  • Members
  • Pip
  • 8 posts
  • 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

#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

#7 Jeff Hon

Jeff Hon

    Member

  • Members
  • PipPip
  • 29 posts
  • 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; ")

#8 cdi_rajesh

cdi_rajesh

    Newbie

  • Members
  • Pip
  • 8 posts
  • 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…!!!???

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

#10 cdi_rajesh

cdi_rajesh

    Newbie

  • Members
  • Pip
  • 8 posts
  • Gender:Male

Posted 10 August 2006 - 12:00 AM

Thanks for your suggestion and I have tried them already. For some reason my script is not been called out from PROIV. I guess I need to investigate further and try to identify the problem.

Thanks

#11 Joseph Bove

Joseph Bove

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 756 posts
  • Gender:Male
  • Location:Ramsey, United States

Posted 15 August 2006 - 09:45 PM

cdi_rajesh,

Suggestion: Turn on the SQL trace

SQLTRACE=8
TR_PATH=some_file_name

This will let you see - with some effort - if your SQL is being run or not.

hth,

Joseph



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users