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
0
Medha
2/14/2000 7:54:27 PM
sybase.powerbuilder.datawindow 28057 articles. 5 followers. Follow

3 Replies
680 Views

Similar Articles

[PageSpeed] 25

Can you show some code?

--
Terry Dykstra (TeamSybase)
Canadian Forest Oil Ltd.
Check out Sybase Developer's Network: http://www.sybase.com/sdn

Medha Pai <shonoo_NO_SPAM@hotmail.com> wrote in message
news:zHSkMVyd$GA.324@forums.sybase.com...
> 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


0
Terry
2/14/2000 11:50:26 PM
Here are Code excerpts  :

I have noticed that I can have only one set of 
'exception handling code'. If I have both of them
Powerbuilder will cause a GPF.
The datawindow is a simple grid datawindow.

Thanks for your help

Medha

Procedure      proc_sample
 ( p_hour_date IN date ,
  C2 IN OUT    PACKAGE_sample.c_hourly_)
   IS
-- variable declarations 

  -- Exceptions 
  NULL_PARAMETER EXCEPTION;
  NO_DATA_FOUND EXCEPTION;

>> Code to do stuff
  > This is the block that causes problems 
    -- check for congestion for any hour in the current date 
    BEGIN
        select   Count(c.Congestion_flag )
        into    l_congest_flag
        from    cototals.hourly_congestion  C
        where   C.FK_org_id = 1
        and     c.hour_date  = l_cur_date
        and     c.congestion_flag = 1;
             EXCEPTION
        WHEN NO_DATA_FOUND THEN
            l_congest_flag := 0 ;
        WHEN OTHERS Then
            l_congest_flag:=0;
    END;


>> more code to do other processing

-- Error handling section at the bottom
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
        l_errortext := 'skjhfkjd' ;
    WHEN NULL_PARAMETER THEN
        dbms_output.put_line('Null_Parameter');
    WHEN OTHERS THEN
        dbms_output.put_line('Exception OTHERS - '||SQLCODE||',
'||SUBSTR(SQLERRM,1,200));

OPEN C2 FOR
   SELECT * FROM hourly_status_t
   order by settlement_type, app_type, execution_order ; 
END; -- Procedure 




On Mon, 14 Feb 2000 16:50:26 -0700,
 in powersoft.public.powerbuilder.datawindow
Terry Dykstra <dontreply_tdykstra@cfol.ab.ca> wrote: 
>Can you show some code?
>
>--
>Terry Dykstra (TeamSybase)
>Canadian Forest Oil Ltd.
>Check out Sybase Developer's Network: http://www.sybase.com/sdn
>
>Medha Pai <shonoo_NO_SPAM@hotmail.com> wrote in message
>news:zHSkMVyd$GA.324@forums.sybase.com...
>> 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
>
>
0
Medha
2/15/2000 2:20:40 PM
As far as I know, you should have only one EXCEPTION block in your
procedure.
Use the RAISE method to set desired error handling.

--
Terry Dykstra (TeamSybase)
Canadian Forest Oil Ltd.
Check out Sybase Developer's Network: http://www.sybase.com/sdn

Medha Pai <shonoo_NO_SPAM@hotmail.com> wrote in message
news:9Gw5V$7d$GA.305@forums.sybase.com...
> Here are Code excerpts  :
>
> I have noticed that I can have only one set of
> 'exception handling code'. If I have both of them
> Powerbuilder will cause a GPF.
> The datawindow is a simple grid datawindow.
>
> Thanks for your help
>
> Medha
>
> Procedure      proc_sample
>  ( p_hour_date IN date ,
>   C2 IN OUT    PACKAGE_sample.c_hourly_)
>    IS
> -- variable declarations
>
>   -- Exceptions
>   NULL_PARAMETER EXCEPTION;
>   NO_DATA_FOUND EXCEPTION;
>
> >> Code to do stuff
>   > This is the block that causes problems
>     -- check for congestion for any hour in the current date
>     BEGIN
>         select   Count(c.Congestion_flag )
>         into    l_congest_flag
>         from    cototals.hourly_congestion  C
>         where   C.FK_org_id = 1
>         and     c.hour_date  = l_cur_date
>         and     c.congestion_flag = 1;
>              EXCEPTION
>         WHEN NO_DATA_FOUND THEN
>             l_congest_flag := 0 ;
>         WHEN OTHERS Then
>             l_congest_flag:=0;
>     END;
>
>
> >> more code to do other processing
>
> -- Error handling section at the bottom
>   EXCEPTION
>     WHEN NO_DATA_FOUND THEN
>         l_errortext := 'skjhfkjd' ;
>     WHEN NULL_PARAMETER THEN
>         dbms_output.put_line('Null_Parameter');
>     WHEN OTHERS THEN
>         dbms_output.put_line('Exception OTHERS - '||SQLCODE||',
> '||SUBSTR(SQLERRM,1,200));
>
> OPEN C2 FOR
>    SELECT * FROM hourly_status_t
>    order by settlement_type, app_type, execution_order ;
> END; -- Procedure
>
>
>
>
> On Mon, 14 Feb 2000 16:50:26 -0700,
>  in powersoft.public.powerbuilder.datawindow
> Terry Dykstra <dontreply_tdykstra@cfol.ab.ca> wrote:
> >Can you show some code?
> >
> >--
> >Terry Dykstra (TeamSybase)
> >Canadian Forest Oil Ltd.
> >Check out Sybase Developer's Network: http://www.sybase.com/sdn
> >
> >Medha Pai <shonoo_NO_SPAM@hotmail.com> wrote in message
> >news:zHSkMVyd$GA.324@forums.sybase.com...
> >> 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
> >
> >


0
Terry
2/17/2000 9:29:33 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 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...

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

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

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

Datawindow with Oracle stored procedure
We are using dw with Oracle stored procedure result set as output reference (The method that mentioned in faxline 44553). As this method required us to set DBPARM with PBDBMS=0, which enable the stored procedure to return result set to a dw. One problem we encounter is that after we use this PBDBMS=0 setting, all other dw with simple SQL won't work any more. When try to update a dw with simple SQL, PB complaints about the record has been changed, but the data actually is unchanged. Our dw have setting for Where Clause for Update/Delete is 'Key & Updateable columns&...

Stored Procedure
Hi, I created a datawindow by using stored proc as datasource in PB 6.0. Db is sqlany 5.5.04. On one machine, when I tried to retrieve same datawindow from same database, it is retrieving. But on another machine, it's giving me the error column list do not match. Is it related to pbodb60.ini file? Or with Powerbuilder profile( dbparm )? I tried to change static bind = 0 to 1, but result is same. Has anyone seen before? What is the problem? TIA -- Mahendra Dhamdhere, CPDA Email:mahendrad@hotmail.com Pl. Remove nospam_ from email id ...

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

Stored Procedure datawindow with Oracle
Is it possible to create stored proc datawindow with oracle and update it? ...

Problem with stored procedure datawindow
This is a multi-part message in MIME format. ------=_NextPart_000_0014_01C08078.36D407B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have converted a datawindow that had a SQL Select to a Stored = Procedure that is retrieved on a page using the HTML generator. Now the = datawindow will not retrieve. I have tried setting the weight of the = datawindow in the dw painter to all options and still no luck. I have = even hardcoded values in the retrieveex script and the datawindow does = not return any rows. The SQL Select dat...

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

Oracle stored procedures and Datawindows
Using an additional OUT parameter with an Oracle stored procedure does not display the updated value back on the datawindow . Any ideas ? Assuming you are the same person as Spidey, please don't repost the same question. If not, I apologize. Did you change the Ref Cursor to reflect the additional column(s)? Did you change on the datawindow add the additional column(s). You add them in using the 'View, Column Specifications' sheet. Rightclick on the list of columns and append or insert a new row. -- Terry Dykstra (TeamSybase) http://powerbuilder.codeXchange.syba...

Datawindow with Stored Procedure Problem
I get the message "Select error: Requested result set number 1 not found" from a datawindow when my Stored Procedure does not get a result set. It works fine when there is data returned. Normally, when no data is returned I just get zero rows returned without an error message. The Stored Procedure works fine when no data is returned outside of PB. I am using PB 7.0.2 C3 with Sybase 11. Any one have any thoughts on this one? TIA Ted Jones If you are using a datawindow, you HAVE to have a result set. That result set could contain 0 rows. If your stored procedure has a...

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

Web resources about - problem with Oracle Stored procedure datawindow - 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: 11/28/2015 5:53:27 AM