?
Solved

How to use StoredProc to get data from Oracle database function?

Posted on 2005-05-11
9
Medium Priority
?
557 Views
Last Modified: 2010-04-05
i want to get data from oracle databe function GETLIFEPOINTS:

FUNCTION getLifePoints(casinoId number, clientId string, schema string) RETURN ResultSet IS
            rs ResultSet;
            sqlQuery String(2048);
            BEGIN
                  -- search by casino and client
                  IF casinoId = -1 THEN
                        sqlQuery := 'SELECT ID_TYPPTS, SUM(NBR_PT_VIE) NBR_PT_VIE';
                        sqlQuery := sqlQuery || ' FROM GALAXIS.PTKPTS';
                        sqlQuery := sqlQuery || ' WHERE ID_CLI = :a';
                        sqlQuery := sqlQuery || ' group by ID_TYPPTS';
                        open rs for sqlQuery using clientId;
                  ELSE
                        sqlQuery := 'SELECT ID_TYPPTS, SUM(NBR_PT_VIE) NBR_PT_VIE';
                        sqlQuery := sqlQuery || ' FROM GALAXIS.PTKPTS';
                        sqlQuery := sqlQuery || ' WHERE ID_CASINO = :a';
                        sqlQuery := sqlQuery || ' AND ID_CLI = :b';
                        sqlQuery := sqlQuery || ' group by ID_TYPPTS';
                        open rs for sqlQuery using casinoId, clientId;
                  END IF;
                  RETURN rs;
            END;

I added StoredProc on form entered database, storedprocname and then i want enabe active
i enter password and get:

General SQL error
BDE error: 13059 [$33] [$ 3]

How to get Result?

0
Comment
Question by:selas
  • 5
  • 4
9 Comments
 

Author Comment

by:selas
ID: 13979439
I added params:
casinoId number - integer - input
clientId string - string - input
schema string - string - input
ResultSet - Dataset - result

and now i get:
Could not find object
Bde Error 8712 [$22] [$8]
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 13979798
your resultset in oracle should be defined as

Type ResultSet IS REF CURSOR;

additional you should not use a function, use a procedure like

FUNCTION getLifePoints(casinoId in number
                                , clientId in string
                                , schema in string
                                , AResultSet OUT ResultSet)
IS
...

additional be aware that you cannot attach a dbgrid as visualizing tool,
because the REF CURSOR is UNIDirectional

somewhere at ex ex i have a full sample, digging now

meikl ;-)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 13979846
well some links

http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_21026299.html
http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_20842305.html

btw. the cursor must be an IN OUT like

FUNCTION getLifePoints(casinoId in number
                                , clientId in string
                                , schema in string
                                , AResultSet IN OUT ResultSet)
IS

hope this helps

meikl ;-)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:kretzschmar
ID: 13980312
a typo
FUNCTION  should be PROCEDURE
;-)
0
 

Author Comment

by:selas
ID: 13983939
I cant change Function, because its not mine product
i need only to get result by delphi
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 13984350
is it possible for you to add an oracle wrapper proc like

PROCEDURE getLifePoints_proc(casinoId in number
                                , clientId in string
                                , schema in string
                                , AResultSet IN OUT ResultSet)
IS
BEGIN
  --call the function here
  AResultSet := getLifePoints(casinoId,clientId,schema);
END;

meikl ;-)
 
0
 

Author Comment

by:selas
ID: 13988490
but if i have this function, how to use it with Delphi
maybe you can write sample?

FUNCTION cancelConsumption
     (
          casinoId      number     ,
          clientId      string     ,
          datHeu           date     ,
          articleCode      string     ,
          dettyppts      string     ,
          free           string     ,
          vouncher      string     ,
          codCanUsr      string     ,
          canCmt           string     ,
          schema           string
     ) RETURN number AS
     language java name 'mc.mis.database.storedprocedure.consumption.OraConsumption.cancelConsumption(
          int               ,
          java.lang.String     ,
          java.sql.Timestamp     ,
          java.lang.String     ,
          java.lang.String     ,
          java.lang.String     ,
          java.lang.String     ,
          java.lang.String     ,
          java.lang.String     ,
          java.lang.String) return int';
0
 
LVL 27

Accepted Solution

by:
kretzschmar earned 2000 total points
ID: 13988805
to do it simplified i would use a tquery-object with an sql-statement like

select [schemaname.packagename.]cancelConsumption(ParametersHere) from dual


[] means if needed

and then simple open the query

meikl ;-)

0
 

Author Comment

by:selas
ID: 14001766
I tried to use StoredProc

i entered databasename: 192.168.0.160
chosed Storedprocname: GETLIFEPOINTS
i saw 4 new params: return_value, casinoid, clientid, schema
Changed values of Params to: ..., 1051, 7384, GALAXIS

And then i want to activate i got this error:
Key violation.
[Oracle][ODBC][Ora]ORA-06550: line 1, column 13:
PLS-00201: identifier 'GETLIFEPOINTS' must be declared
ORA-06550: line 1,
column 7:
PL/SQL: Statement ignored

Strange i saw in mis.consumption privileges
Galaxis_role | execute: Yes
all other users NO

Maybe this is Problem, i have now other passwords
and i dont know how to create procedure
i use Toad
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Integration Management Part 2
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month16 days, 3 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question