Problem in creating stored procedure datawindow in oracle

Hi Everybody,

I am using OBDC driver to connect to ORACLE( 10g Release 2 ) for my
Power Builder( PB8 ) application.

I have created following package and stored procedure

CREATE OR REPLACE PACKAGE a_demo IS
 TYPE my_result_set IS REF CURSOR RETURN work_orders%ROWTYPE;
END a_demo;


CREATE OR REPLACE PROCEDURE proc_demo (out_result IN OUT
a_demo.my_result_set)
 AS
 BEGIN
 OPEN out_result
 FOR SELECT * FROM work_orders;
END proc_demo;

But when iam creating datawindow through this stored procedure, it is
giving error like

Cannot Create Datawindow
'SQLSTATE = 37000
[oracle][ODBC] Syntax error or access violation.

1 execute SYSMAN.PROC_DEMO;0 OUT_RESULT = :OUT_RESULT'


can any body help me, why this error is coming ?
How can i create the stored procedure datawindow through ODBC driver,
Is there any setting to be set ?

---== Posted via the PFCGuide Web Newsreader ==---
http://www.pfcguide.com/_newsgroups/group_list.asp 
0
ranjit
5/23/2006 12:15:21 PM
sybase.powerbuilder.datawindow 28057 articles. 5 followers. Follow

4 Replies
976 Views

Similar Articles

[PageSpeed] 37

Make sure your ODBC driver supports the REF CURSOR method.  Many
(including at least when I last attempted it the ODBC driver supplied
by Oracle) do not.

On 23 May 2006 05:15:21 -0700, "ranjit" <ranjit.tech@gmail.com> wrote:

>Hi Everybody,
>
>I am using OBDC driver to connect to ORACLE( 10g Release 2 ) for my
>Power Builder( PB8 ) application.
>
>I have created following package and stored procedure
>
>CREATE OR REPLACE PACKAGE a_demo IS
> TYPE my_result_set IS REF CURSOR RETURN work_orders%ROWTYPE;
>END a_demo;
>
>
>CREATE OR REPLACE PROCEDURE proc_demo (out_result IN OUT
>a_demo.my_result_set)
> AS
> BEGIN
> OPEN out_result
> FOR SELECT * FROM work_orders;
>END proc_demo;
>
>But when iam creating datawindow through this stored procedure, it is
>giving error like
>
>Cannot Create Datawindow
>'SQLSTATE = 37000
>[oracle][ODBC] Syntax error or access violation.
>
>1 execute SYSMAN.PROC_DEMO;0 OUT_RESULT = :OUT_RESULT'
>
>
>can any body help me, why this error is coming ?
>How can i create the stored procedure datawindow through ODBC driver,
>Is there any setting to be set ?
>
>---== Posted via the PFCGuide Web Newsreader ==---
>http://www.pfcguide.com/_newsgroups/group_list.asp 

Bruce Armstrong [TeamSybase]
http://www.teamsybase.com

In Defense of Top Posting
http://alpage.ath.cx/toppost/toppost.htm

Preach the gospel at all times. If necessary, use words. - Francis of Assisi
http://www.needhim.org
0
Bruce
5/23/2006 1:28:11 PM
Hi,

Can you tell me a way in which i can check whether my ODBC driver supports REF
CURSOR method (or) not.


On 23 May 2006 06:28:11 -0700,
 in sybase.public.powerbuilder.datawindow
Bruce Armstrong [TeamSybase] <NOCANSPAM_bruce.armstrong@teamsybase.com> wrote: 
>Make sure your ODBC driver supports the REF CURSOR method.  Many
>(including at least when I last attempted it the ODBC driver supplied
>by Oracle) do not.
>
>On 23 May 2006 05:15:21 -0700, "ranjit" <ranjit.tech@gmail.com> wrote:
>
>>Hi Everybody,
>>
>>I am using OBDC driver to connect to ORACLE( 10g Release 2 ) for my
>>Power Builder( PB8 ) application.
>>
>>I have created following package and stored procedure
>>
>>CREATE OR REPLACE PACKAGE a_demo IS
>> TYPE my_result_set IS REF CURSOR RETURN work_orders%ROWTYPE;
>>END a_demo;
>>
>>
>>CREATE OR REPLACE PROCEDURE proc_demo (out_result IN OUT
>>a_demo.my_result_set)
>> AS
>> BEGIN
>> OPEN out_result
>> FOR SELECT * FROM work_orders;
>>END proc_demo;
>>
>>But when iam creating datawindow through this stored procedure, it is
>>giving error like
>>
>>Cannot Create Datawindow
>>'SQLSTATE = 37000
>>[oracle][ODBC] Syntax error or access violation.
>>
>>1 execute SYSMAN.PROC_DEMO;0 OUT_RESULT = :OUT_RESULT'
>>
>>
>>can any body help me, why this error is coming ?
>>How can i create the stored procedure datawindow through ODBC driver,
>>Is there any setting to be set ?
>>
>>---== Posted via the PFCGuide Web Newsreader ==---
>>http://www.pfcguide.com/_newsgroups/group_list.asp 
>
>Bruce Armstrong [TeamSybase]
>http://www.teamsybase.com
>
>In Defense of Top Posting
>http://alpage.ath.cx/toppost/toppost.htm
>
>Preach the gospel at all times. If necessary, use words. - Francis of Assisi
>http://www.needhim.org
0
Ranjit
5/25/2006 3:44:23 AM
You have to check the ODBC conformance level.  I believe you need a
level 2 driver to do a REF CURSOR.  You certainly need a level 2
driver before out parameters from a procedure are supported.

On 24 May 2006 20:44:23 -0700, "Ranjit" <ranjit.tech@gmail.com> wrote:

>Hi,
>
>Can you tell me a way in which i can check whether my ODBC driver supports REF
>CURSOR method (or) not.
>
>
>On 23 May 2006 06:28:11 -0700,
> in sybase.public.powerbuilder.datawindow
>Bruce Armstrong [TeamSybase] <NOCANSPAM_bruce.armstrong@teamsybase.com> wrote: 
>>Make sure your ODBC driver supports the REF CURSOR method.  Many
>>(including at least when I last attempted it the ODBC driver supplied
>>by Oracle) do not.
>>
>>On 23 May 2006 05:15:21 -0700, "ranjit" <ranjit.tech@gmail.com> wrote:
>>
>>>Hi Everybody,
>>>
>>>I am using OBDC driver to connect to ORACLE( 10g Release 2 ) for my
>>>Power Builder( PB8 ) application.
>>>
>>>I have created following package and stored procedure
>>>
>>>CREATE OR REPLACE PACKAGE a_demo IS
>>> TYPE my_result_set IS REF CURSOR RETURN work_orders%ROWTYPE;
>>>END a_demo;
>>>
>>>
>>>CREATE OR REPLACE PROCEDURE proc_demo (out_result IN OUT
>>>a_demo.my_result_set)
>>> AS
>>> BEGIN
>>> OPEN out_result
>>> FOR SELECT * FROM work_orders;
>>>END proc_demo;
>>>
>>>But when iam creating datawindow through this stored procedure, it is
>>>giving error like
>>>
>>>Cannot Create Datawindow
>>>'SQLSTATE = 37000
>>>[oracle][ODBC] Syntax error or access violation.
>>>
>>>1 execute SYSMAN.PROC_DEMO;0 OUT_RESULT = :OUT_RESULT'
>>>
>>>
>>>can any body help me, why this error is coming ?
>>>How can i create the stored procedure datawindow through ODBC driver,
>>>Is there any setting to be set ?
>>>
>>>---== Posted via the PFCGuide Web Newsreader ==---
>>>http://www.pfcguide.com/_newsgroups/group_list.asp 
>>
>>Bruce Armstrong [TeamSybase]
>>http://www.teamsybase.com
>>
>>In Defense of Top Posting
>>http://alpage.ath.cx/toppost/toppost.htm
>>
>>Preach the gospel at all times. If necessary, use words. - Francis of Assisi
>>http://www.needhim.org

Bruce Armstrong [TeamSybase]
http://www.teamsybase.com

In Defense of Top Posting
http://alpage.ath.cx/toppost/toppost.htm

Preach the gospel at all times. If necessary, use words. - Francis of Assisi
http://www.needhim.org
0
Bruce
5/25/2006 7:15:37 AM
If you're talking Oracle here, use the Merant/Intersolv driver that comes 
with PB.  The Oracle ODBC driver will not work with PB.

-- 
Terry Dykstra (TeamSybase)
http://powerbuilder.codeXchange.sybase.com/
http://www.pb9books.com
product enhancement requests:
http://my.isug.com/cgi-bin/1/c/submit_enhancement

"Bruce Armstrong [TeamSybase]" <NOCANSPAM_bruce.armstrong@teamsybase.com> 
wrote in message news:64ma72pg9qjd3l22eaarhhp93blbp23k5f@4ax.com...
>
> You have to check the ODBC conformance level.  I believe you need a
> level 2 driver to do a REF CURSOR.  You certainly need a level 2
> driver before out parameters from a procedure are supported.
>
> On 24 May 2006 20:44:23 -0700, "Ranjit" <ranjit.tech@gmail.com> wrote:
>
>>Hi,
>>
>>Can you tell me a way in which i can check whether my ODBC driver supports 
>>REF
>>CURSOR method (or) not.
>>
>>
>>On 23 May 2006 06:28:11 -0700,
>> in sybase.public.powerbuilder.datawindow
>>Bruce Armstrong [TeamSybase] <NOCANSPAM_bruce.armstrong@teamsybase.com> 
>>wrote:
>>>Make sure your ODBC driver supports the REF CURSOR method.  Many
>>>(including at least when I last attempted it the ODBC driver supplied
>>>by Oracle) do not.
>>>
>>>On 23 May 2006 05:15:21 -0700, "ranjit" <ranjit.tech@gmail.com> wrote:
>>>
>>>>Hi Everybody,
>>>>
>>>>I am using OBDC driver to connect to ORACLE( 10g Release 2 ) for my
>>>>Power Builder( PB8 ) application.
>>>>
>>>>I have created following package and stored procedure
>>>>
>>>>CREATE OR REPLACE PACKAGE a_demo IS
>>>> TYPE my_result_set IS REF CURSOR RETURN work_orders%ROWTYPE;
>>>>END a_demo;
>>>>
>>>>
>>>>CREATE OR REPLACE PROCEDURE proc_demo (out_result IN OUT
>>>>a_demo.my_result_set)
>>>> AS
>>>> BEGIN
>>>> OPEN out_result
>>>> FOR SELECT * FROM work_orders;
>>>>END proc_demo;
>>>>
>>>>But when iam creating datawindow through this stored procedure, it is
>>>>giving error like
>>>>
>>>>Cannot Create Datawindow
>>>>'SQLSTATE = 37000
>>>>[oracle][ODBC] Syntax error or access violation.
>>>>
>>>>1 execute SYSMAN.PROC_DEMO;0 OUT_RESULT = :OUT_RESULT'
>>>>
>>>>
>>>>can any body help me, why this error is coming ?
>>>>How can i create the stored procedure datawindow through ODBC driver,
>>>>Is there any setting to be set ?
>>>>
>>>>---== Posted via the PFCGuide Web Newsreader ==---
>>>>http://www.pfcguide.com/_newsgroups/group_list.asp
>>>
>>>Bruce Armstrong [TeamSybase]
>>>http://www.teamsybase.com
>>>
>>>In Defense of Top Posting
>>>http://alpage.ath.cx/toppost/toppost.htm
>>>
>>>Preach the gospel at all times. If necessary, use words. - Francis of 
>>>Assisi
>>>http://www.needhim.org
>
> Bruce Armstrong [TeamSybase]
> http://www.teamsybase.com
>
> In Defense of Top Posting
> http://alpage.ath.cx/toppost/toppost.htm
>
> Preach the gospel at all times. If necessary, use words. - Francis of 
> Assisi
> http://www.needhim.org 


0
Terry
5/25/2006 2:31:39 PM
Reply:

Similar Artilces:

Oracle stored procedure problems with datawindows
Currently using oracle 8 and using a stored procedure as a source for a datawindow. when the procedure is asscoiated with the datawindow it comes up with an oracle error stating that the variabels have not been initalised. The stored proc has two in variables and an inout. Has any one had this problem and found a solution. I had this problem. But I was necessary execute a stored procedure from the script. When I checked my connect string to Oracle database in the connect script and my connect string in the database profile I found a solution this problem. For example: My con...

problem with Oracle Stored procedure datawindow
Hi : I have a datawindow with a PL/SQL stored procedure that uses REF cursors. There is one block in the PL/SQL script that has its own exception handling code to essentially ignore the no_data_found condition. When this code is included the datawindow causes a GPF. IF I don't include the code the datawindow does not GPF, but the procedure ends without really completing the process and hence no data is retrieved . I am using Oracle 8i database and Powerbuilder 7.0.1 Any tips/work arounds are greatly appreciated Thanks Medha Pai Can you show some code? -- ...

Problem with Datawindow Stored Procedure Closing in Oracle
Has anyone run into a problem where they are using a stored procedure as the source for a datawindow, but Oracle is reporting that the cursor does not close (even if this window and datawindow are destroyed?). Is there a manual way to close the cursor (opened in a datawindow)? Thanks - Joe Slawsky (NOSPAMFORME_jslawsky@planetdx.com) www.planetdx.com What version of PB? Joe, Slawsky wrote: > > Has anyone run into a problem where they are using a > stored procedure as the source for a datawindow, but Oracle > is reporting that the cursor does not close (even i...

Create datawindow using Oracle stored procedure
I'm using PB 7.0.2 and Oracle 8i to try and create a datawindow with a stored procedure. When using the wizard and it's selecting the stored procedure I receive a validation error on some stored procedure which has different owner than the database connection. I have put the owner name in the database preference that I am connected to and still get the error. Is there a way to tell it specifically where to get the stored procedure or does PB 7 even accommodate this option? Putting the owner name in the database preferences just filters out the list of objects in table...

Unable to create datawindow through Oracle Stored Procedure
Hello everyone, I have installed Oracle 8.0.5 on windows 2000 (at home just for my lab practice). When I have created datawindow through oracle stored procedure, I am getting following error message, Cannot create datawindow ORA-20999: ORA-01001:invalid cursor|| ORA-06512: "at SYSTEM:SP__DW_GROUPS", line 17 ORA-06512: at line 1 Nothing wrong with Oracle stored procedure and Package. They deployed to oracle database without error. Anyone can guide me what I have missing here. Thanks in Advance Adnan ...

How do we create datawindow object using Oracle Stored Procedures
I am working on Oracle 7.3 and want to create datawindow object using stored procedure. If stored procedure says Select * from tabletame where condition; If, we use cursor and define a table in stored procedure than how will it go back and display in datawindow. How this data will go back to datawindow object. From: Sunil sunil_k_gupta@hotmail.com ...

PowerBuilder 8 and Oracle 8 Stored Procedure Datawindow
I worked many years with Sql Server and it was easy to do, but I don't know how to do in Oracle: How a Oracle 8 Stored Procedure can return to PowerBuilder 8 the result of a query or a recordset (in Sql Server is as easy as put a select sentence at the end of the procedure). I don't want to use temporary tables for a concurrence issue. Can anyone give me a help? Thanks in advance, Ra�l La Torre You need a REF CURSOR. See enclosed datawindow examples. -- Terry Dykstra (TeamSybase) http://powerbuilder.codeXchange.sybase.com/ http://www.pb9books.com product ...

Create DataWindow by using Oracle 7.3 Stored Procedure
Hi, There, I am using PowerBuilder Enterprise/32 version 5.0.03 and Oracle 7.3. When I try to create a DataWindow by using Stored Procedure, I can't get the screen to define the result set. However, if I connect to the local ODBC, it works fine. Any tips? Thanks. De Liu dliu@novell.com >> Can't define result set << Oracle procedures are a little tricky when you first start to use them. You need to use Oracle Packages and Records to get the result set to be read in. I would suggest looking at www.teamps.com and follow the links to Bruce Armstrong'...

How to create a Stored Procedure DataWindow using PB5.0.02 and Oracle 7.2 ?
Hello As you can see I'm trying to create a DW Stored Procedure DataWindow using PB5.0.02 and Oracle 7.2 If I create a SQL Select DataWindow it works but if I try I Stored Procedure DataWindow, It shows the list of my stored procedures, the source code but when I click at OK button, nothing happens Please, Reply this to me as soon as possible at my e-mail: macorrea@sti.com.br Thank you very much in Advance Maur�cio A. Correa macorrea@sti.com.br It seems to me that the stored procedure in question dont have any result set declared. TO have a datawindow based on ...

store procedure datawindow problem
In sybase ASE 11.5, I wrote such a store procedure: CREATE PROCEDURE up_myproc (@arg_parm1 char(40), @arg_parm2 char(40) ) AS CREATE TABLE #temp_alertlist( steprec_id decimal(18,0), ifalert char(1), type char(255), content_1 char(255) null, content_2 char(255) null, content_3 char(255) null, content_4 char(255) null, windowname char(255) null ) Insert into #temp_alertlist values(....) SELECT * FROM #temp_alertlist In pb8, I made a datawindow from this store procedure, when I preview it, it returns the correct results, but when I put this datawindow in my ...

Problems about datawindow that is created from procedure.
Hi everybody, I am using PB 6.5.1, Oracle8i I have some problems of retrieving data from Oracle db. We need create a datawindow from a procedure that is locates in a package,this package has beed tested ok, this procedure has three arguments, two of them are passed in, the rest one is for out, the execute statements in datawindow as following: "execute package_name.procedure_name;0 argument1 = :varible1,argument2 = :varible2" But when we run this datawindow, the error occurs as following: "wrong number or types of arguments in call to the package" Tha...

Problems with stored procedures in a datawindow
Hi- I'm using an external datawindow which uses stored procedures for insert,=20 delete and update. I always insert a row and if I want to delete or modify a row, I fill in=20 the datawindow with the corresponding information. In order to execute the = appropriate stored procedure, I change the status flags of the row to=20 DataModified! using the SetItemStatus method (and call the deleteRow()=20 method if I want to delete the row). After that, I set the transaction I=20 want the datawindow to use and call the datawindow's of=5Fupdate() method. It executes the stored p...

problem with a stored procedure datawindow
I often work with datawindow based on oracle stored procedure and i never had any problem except with one. This datawindow works fine during 3 hours (approximately) and then becomes invalid. The error is : ORA-06553: PLS-707: structure non prise en charge ou erreur interne [2601] ORA-06550: Ligne 1, colonne 16:|PL/SQL: Statement ignored| which seems to be an internal error... I thought it was an instance problem and i shutdown the database and re-start it but the problem is still the same. Datawindow based on stored procedure used a cursor that is never closed. Can the problem be ...

Datawindow against Stored Procedure problem
Has anyone had problems creating a datawindow against a database stored procedure? I have a simple stored procedure that returns a result set. When I try to create a tabular datawindow against the stored procedure, I click 'finish' and PocketBuilder simply disappears (without even the standard 'do you want to send the report to Microsoft' dialog box!). Thanks! Michael Frank ASA9 version 9.0.2 build 3044 PocketBuilder 2.0.1 build 688 I have no problems with this (I am running ASA 9.0.2.3124 in my tests). "Michael Frank" <mfrank.removeme...

Web resources about - Problem in creating stored procedure datawindow in oracle - sybase.powerbuilder.datawindow

PowerBuilder - Wikipedia, the free encyclopedia
PowerBuilder is an integrated development environment owned by Sybase , a division of SAP . It has been in use since 1991, peaking around 1998 ...

Intelligent Versus Surrogate Keys
Should I use business columns as primary key fields for tables in thedatabase, or generate artificial primary key values? When business-related ...

chevy volt
ProEXR File Description =Attributes= channels (chlist) compression (compression): Zip dataWindow (box2i): [0, 0, 5119, 2475] displayWindow (box2i): ...

Enable - A comprehensive solution for the localization of PowerBuilder applications Untitled Document ...
Enable is localization/ translation tool used by PowerBuilder developers to make their applications multilingual.Enable is a powerful tool that ...

comp.lang.clipper.visual-objects - Google Groups
comp.lang.clipper.visual-objects 1-20 von 33006 Themen werden angezeigt Integrity of a file marcosm...@gmail.com 09:35 Combobox value Lu 08:12 ...

Sybase PowerBuilder tool readied for Microsoft's .Net
Developers can choose Win32 or newer style .Net development

PowerToTheBuilder - PowerBuilder UI Controls
HOME - COMPONENTS - PB UI DESIGNER - DOWNLOAD\PURCHASE - SUPPORT - FAQ - ABOUT Take Control of your UI with PB Ultimate Suite PowerToTheBuilder.com ...

Beautiful iPad Mini 3 Concept Reimagines Apple’s Tablet With iPhone 6 Design, Gold Color [Images]
The iPhone 6 may be grabbing all of the recent headlines due to its impending announcement, but spare a thought for those who prefer to stay ...

Java Networking - Real's HowTo
Real's HowTo Custom Search Java Language String and Number Applet AWT Swing Environment IO Javascript interaction JDBC Thread Networking JSP ...

Business Intelligence Products - Database Management, Data Warehousing & Mobility Software - Sybase Inc ...
Sybase offers a variety of Business Intelligence (BI) software like products for database management, data warehousing and mining, data integration ...

Resources last updated: 12/7/2015 12:09:11 PM