Jump to content


- - - - -

calling Oracle plsql from pro IV


3 replies to this topic

#1 Guest_bob neville_*

Guest_bob neville_*
  • Guests

Posted 20 October 1999 - 08:47 AM

We are trying to call an Oracle pl/sql module from pro iv
(version 4.6) on an NT platform. On every call we get the error message '366 - invalid SQL'. The pl/sql runs fine
under Oracle.
Somewhere I saw that to call the module you put the following in your pro iv code

SQL

exec module_name($var1, $var2 ....) - the same as a normal
Oracle sql plus call

ENDSQL

I am an Oracle person not pro iv so now I am stuck !!

Any help would be appreciated.

We are on Oracle 8.0.5.1.5a and all of our normal
user written embedded SQL works fine.

#2 Guest_bob neville_*

Guest_bob neville_*
  • Guests

Posted 20 October 1999 - 08:54 AM

The latest on this is that we can call the module using
literals i.e.

SQL

exec module_name('A','B'....)

ENDSQL

and it works OK.

The problem appears when we pass scratch variables as parameters.

#3 Joseph Serra

Joseph Serra

    Advanced

  • Members
  • PipPipPip
  • 93 posts
  • Gender:Male
  • Location:Melbourne, Australia

Posted 20 October 1999 - 05:18 PM

Bob

I remember doing some Oracle stuff through ProIV, and I seem to recall that if you want to pass variable parameters through to SQL statements, then for some unknown reason you should put a colon infront of them.

Eg

SQL

select PHONENO
from PHONE
where STATUS = :$STATUS

ENDSQL

I hope this helps.
Regards

Joseph

#4 Ralph Gadsby

Ralph Gadsby

    Expert

  • Members
  • PipPipPipPip
  • 154 posts
  • Gender:Male
  • Location:United Kingdom

Posted 21 October 1999 - 01:04 PM

I don't really know musch about PLSQL, however when calling a SELECT statement from between the SQL/ENDSQL you need to use a colon in front of variables. Having looked through the system that I am currently working on there is a call to PLSQL as follows:

$$SQL_CMD = 'BEGIN module_name(' + ''' + $P1 + '',''
+ $P2 + '',' + CONV(#P3) + ',' + CONV(#P4) + ',' +
CONV(#P5) + ','' + $P6 + '','' + $P7 + '','' + $P8 +
'','' + $P9 + ''); END;'
#STAT = SYS-SQL($$SQL_CMD)

Hope this helps.



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users