Jump to content


- - - - -

Returning variables to PRO4 from PLSQL?


23 replies to this topic

#1 Guest_kwong_*

Guest_kwong_*
  • Guests

Posted 13 April 2004 - 12:05 PM

I am using pro4 version 4.6, doing glovia development.

my statement of sql run in this way

PLSQLIN()
SQL
BEGIN
SQLPROC.PROCNAME( :CCN, :##OUT_AMT);
END
ENDSQL
PLSQLOUT()

Well, I am able the get what I want... but the timing when I got the ##OUT_AMT, its already rounded.

For example... I am suppose to get 122.23 in SQL, but I get 122 in PRO4 instead.

Any expert here can help ??!! This will really help me a lot... as I dont intend to write the value of the AMT into a temporary file that will affect my program performance.


Thanks again!!

#2 guesty_mcguest

guesty_mcguest

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 14 April 2004 - 10:19 AM

The "steamroller" method works...

In your pl/sql multiply the result by 100.

In your pro-iv divide the result returned by pl/sql by 100.
:)


Now, anybody got any clues on returning strings from pl/sql??

#3 Guest_kwong_*

Guest_kwong_*
  • Guests

Posted 15 April 2004 - 01:24 AM

what a good idea man!! Thanks a lot!!

In fact, I have created a tmp file to pass all the variable out, seem that I gonna change my program again to avoid heavy read /write of my temporary file. Thanks again!!!


Well, for string variable, I also having problem passing it out other than using a temparary file.


Anyway... thanks a lot!!!

#4 Chris Mackenzie

Chris Mackenzie

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 368 posts
  • Gender:Male
  • Location:Bristol, United Kingdom

Posted 15 April 2004 - 07:29 AM

Might be too obvious, but you can define a pl/sql
function that returns any string and use it in an
SQL query in BR of a dummy file def with an alpha
field to take the result.
The content and views expressed in this message are those
of the poster and do not represent those of any organisation.

#5 guesty_mcguest

guesty_mcguest

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 15 April 2004 - 09:34 AM

Well whaddya know!

I'm sure I tried this years ago and I couldn't get it to work with a function as a column - must have just been doing something wrong. :)

So, the easiest, and most easy to maintain method for solving kwong's original problem is just to have the pl/sql routine convert the number to a string and return that and then pro-iv can convert it to a number.

#6 Chris Mackenzie

Chris Mackenzie

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 368 posts
  • Gender:Male
  • Location:Bristol, United Kingdom

Posted 15 April 2004 - 12:00 PM

So, the easiest, and most easy to maintain method for solving kwong's original problem is just to have the pl/sql routine convert the number to a string


Not sure about that, seems easy enough to write it as a function
that returns a number. Maybe PL/SQL functions didn't work in an
earlier P4 release but it is fine now (v5.5 rel 2.1).

BTW: PLSQLIN & PLSQLOUT -what do they do?
The content and views expressed in this message are those
of the poster and do not represent those of any organisation.

#7 Joseph Bove

Joseph Bove

    ProIV Guru

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

Posted 15 April 2004 - 01:30 PM

Chris,

Thanks for asking about PSQLIN and PSQLOUT.

I can't find reference to them in the Logic guide, but are hoping that they may solve a different issue for me. I am looking to a simple one line SQL statement and have access to the numerical result of it:

select total(column_name) from table

I haven't found the way to do this yet in looking through the manual.

Regards,

Joseph

#8 Rob Donovan

Rob Donovan

    rob@proivrc.com

  • Admin
  • 1,640 posts
  • Gender:Male
  • Location:Spain

Posted 15 April 2004 - 01:46 PM

Hi,

PSQLIN & PSQLOUT will be global logics that are specific to his application.

As for your query...

If you setup a File Def in ProIV for the oracle table DUAL (just one column say DUMMY-COL, Alpha, length 30), you can then use this to get info back from SQL statements.

If you put something like

SELECT COUNT(*) FROM MYTABLE

In default logic of a LS, and then put DUAL as the primary file, you can then put some logic in ARNE, and look at the DUMMY-COL to get the value from COUNT(*). You will have to do a CONV() to convert it from a string.

HTHs,

Rob D.

#9 Cleve Haynes

Cleve Haynes

    Expert

  • Members
  • PipPipPipPip
  • 172 posts
  • Gender:Male

Posted 15 April 2004 - 01:47 PM

Joseph - I think you'll find that PSQLIN and PSQLOUT are Glovia specific global logics.

Cleve

#10 Chris Mackenzie

Chris Mackenzie

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 368 posts
  • Gender:Male
  • Location:Bristol, United Kingdom

Posted 15 April 2004 - 02:23 PM

If you put something like

SELECT COUNT(*) FROM MYTABLE

In default logic of a LS, and then put DUAL as the primary file, you can then put some logic in ARNE, and look at the DUMMY-COL to get the value from COUNT(*). You will have to do a CONV() to convert it from a string.


Why primary file? It can be any file.
What has DUAL got to do with it? The P4 file def need not
point to any physical table as the explicit SQL overrides it.
And you can have a fildef with a numeric result, no need to
convert.

/pedant off.
The content and views expressed in this message are those
of the poster and do not represent those of any organisation.

#11 Joseph Bove

Joseph Bove

    ProIV Guru

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

Posted 15 April 2004 - 02:29 PM

Rob, Cleve,

Thanks for the posts.

Rob,

At a conceptual level I'm missing or not appreciating is how DUAL will store the answer of the select statement. Would the SQL be something like this instead?

DUMMY is one a row table.
Structure DUMMY.ALPHA_ANSWER varchar2(30), DUMMY.NUMERIC_ANSWER number(12.6)

SQL
update DUMMY set NUMERIC_ANSWER= select count(*) from table
commit
select * from DUMMY
ENDSQL

Then simply read the NUMERIC_ANSWER column?

Regards,

Joseph

#12 Rob Donovan

Rob Donovan

    rob@proivrc.com

  • Admin
  • 1,640 posts
  • Gender:Male
  • Location:Spain

Posted 15 April 2004 - 02:30 PM

Hi,

I just thought that this was a more 'complete' way of doing it, since its exloting the way ProIV does stuff anyway, and it may not work one day, but putting in a valid table name might make it scrape past any 'bugs'.

I used to get a problem with numerics, in past versions before... that was why I did it with a Alpha.

Rob D.

#13 Joseph Bove

Joseph Bove

    ProIV Guru

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

Posted 15 April 2004 - 06:46 PM

Rob,

Ok - thanks I now appreciate what is happening. A statement like:

select count (*) from table

returns 1 row. If you use a dummy table, then the column contains the results of the sql statement.

Thanks for the tip. It works well.

Regards,

Joseph

#14 Guest_Just a Pinoy in Manila_*

Guest_Just a Pinoy in Manila_*
  • Guests

Posted 16 April 2004 - 12:02 AM

:-"
You may want to try this code to get count , sum or total.

SQL
BEGIN
SELECT COUNT(*) INTO :#VAR
WHERE CLAUSE ;
END ;
ENDSQL

#15 Rob Donovan

Rob Donovan

    rob@proivrc.com

  • Admin
  • 1,640 posts
  • Gender:Male
  • Location:Spain

Posted 16 April 2004 - 04:09 AM

Hi,

I like that... thanks for the tip :)

Rob D.



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users