Using stored procedure to get the data for data window (Oracle)

Hi,
    I am trying to use the stored procedure in Oracle 7.3 to get the
data for the data window. I was able to do it successfully using the
stored procedure if it is not in the package by defining the the output
parameter for the stored procedure which is cursor. But I would like to
do the following if possible.

1. When I set the data source to stored procedure I get the list of
stored procedure which DOES NOT contain the stored procedures in
package. How to get the package stored procedures in the list. Any ideas
(Using synonymns,.....)

2.The work around I am using to solve the above problem is:
    * If the datawindow is NOT CROSSTAB.
         1. Compile the package stored procedure as stand alone
         2. Use stand alone stored procedure as  the data source
         3. go to the sqlsyntax and prepand the package name to the
stored procedure
         4. Delete the stand alone stored procedure.

    *BUT if the data window is the crosstab I cannot do the step 3, as
when I go to the sqlsyntax  it gives me the crosstab definition screen.
HOW do I get to the procedure name to modify it.
    Another problem is if I can not look at the stored procedure name
How do I debug the data window if there are problems. (I know I can
export the data window and look at the text file to get the procedure
name and prepand the package name to it, I am looking for a decent way
to do it)

Any pointer will be appreciate. I have already read the document no.
44553 and 44450 at sybase web site which helped a lot but I still have
above problems.

ASIF
====

0
Asif
7/3/1998 10:23:48 PM
sybase.powerbuilder.datawindow 28057 articles. 4 followers. Follow

3 Replies
825 Views

Similar Articles

[PageSpeed] 31

Oracle doesn't have a system table that will show the procedures within a
package.

--
Terry Dykstra [TeamPS]
Canadian Forest Oil Ltd.
Asif H. rajwani wrote in message <359D59F4.9DA12D2B@mci2000.com>...
>Hi,
>    I am trying to use the stored procedure in Oracle 7.3 to get the
>data for the data window. I was able to do it successfully using the
>stored procedure if it is not in the package by defining the the output
>parameter for the stored procedure which is cursor. But I would like to
>do the following if possible.
>
>1. When I set the data source to stored procedure I get the list of
>stored procedure which DOES NOT contain the stored procedures in
>package. How to get the package stored procedures in the list. Any ideas
>(Using synonymns,.....)
>
>2.The work around I am using to solve the above problem is:
>    * If the datawindow is NOT CROSSTAB.
>         1. Compile the package stored procedure as stand alone
>         2. Use stand alone stored procedure as  the data source
>         3. go to the sqlsyntax and prepand the package name to the
>stored procedure
>         4. Delete the stand alone stored procedure.
>
>    *BUT if the data window is the crosstab I cannot do the step 3, as
>when I go to the sqlsyntax  it gives me the crosstab definition screen.
>HOW do I get to the procedure name to modify it.
>    Another problem is if I can not look at the stored procedure name
>How do I debug the data window if there are problems. (I know I can
>export the data window and look at the text file to get the procedure
>name and prepand the package name to it, I am looking for a decent way
>to do it)
>
>Any pointer will be appreciate. I have already read the document no.
>44553 and 44450 at sybase web site which helped a lot but I still have
>above problems.
>
>ASIF
>====
>


0
Terry
7/6/1998 6:41:16 PM
This is not exactly true.  Check out the sys.all_arguments table.  This
table holds the procedures and functions as well as arguments that are in a
package.

Terry Dykstra [TeamPS] wrote in message ...
>Oracle doesn't have a system table that will show the procedures within a
>package.
>
>--
>Terry Dykstra [TeamPS]
>Canadian Forest Oil Ltd.
>Asif H. rajwani wrote in message <359D59F4.9DA12D2B@mci2000.com>...
>>Hi,
>>    I am trying to use the stored procedure in Oracle 7.3 to get the
>>data for the data window. I was able to do it successfully using the
>>stored procedure if it is not in the package by defining the the output
>>parameter for the stored procedure which is cursor. But I would like to
>>do the following if possible.
>>
>>1. When I set the data source to stored procedure I get the list of
>>stored procedure which DOES NOT contain the stored procedures in
>>package. How to get the package stored procedures in the list. Any ideas
>>(Using synonymns,.....)
>>
>>2.The work around I am using to solve the above problem is:
>>    * If the datawindow is NOT CROSSTAB.
>>         1. Compile the package stored procedure as stand alone
>>         2. Use stand alone stored procedure as  the data source
>>         3. go to the sqlsyntax and prepand the package name to the
>>stored procedure
>>         4. Delete the stand alone stored procedure.
>>
>>    *BUT if the data window is the crosstab I cannot do the step 3, as
>>when I go to the sqlsyntax  it gives me the crosstab definition screen.
>>HOW do I get to the procedure name to modify it.
>>    Another problem is if I can not look at the stored procedure name
>>How do I debug the data window if there are problems. (I know I can
>>export the data window and look at the text file to get the procedure
>>name and prepand the package name to it, I am looking for a decent way
>>to do it)
>>
>>Any pointer will be appreciate. I have already read the document no.
>>44553 and 44450 at sybase web site which helped a lot but I still have
>>above problems.
>>
>>ASIF
>>====
>>
>
>


0
Roy
7/8/1998 3:10:20 AM
I never knew about that table.  It is not even described in the Oracle7.3
Server reference guide, although it's been around for some time, since I
found it in a 7.2 database as well.  Thanks.

--
Terry Dykstra [TeamPS]
Canadian Forest Oil Ltd.
Roy Letlow wrote in message ...
>This is not exactly true.  Check out the sys.all_arguments table.  This
>table holds the procedures and functions as well as arguments that are in a
>package.
>
>Terry Dykstra [TeamPS] wrote in message ...
>>Oracle doesn't have a system table that will show the procedures within a
>>package.
>>
>>--
>>Terry Dykstra [TeamPS]
>>Canadian Forest Oil Ltd.
>>Asif H. rajwani wrote in message <359D59F4.9DA12D2B@mci2000.com>...
>>>Hi,
>>>    I am trying to use the stored procedure in Oracle 7.3 to get the
>>>data for the data window. I was able to do it successfully using the
>>>stored procedure if it is not in the package by defining the the output
>>>parameter for the stored procedure which is cursor. But I would like to
>>>do the following if possible.
>>>
>>>1. When I set the data source to stored procedure I get the list of
>>>stored procedure which DOES NOT contain the stored procedures in
>>>package. How to get the package stored procedures in the list. Any ideas
>>>(Using synonymns,.....)
>>>
>>>2.The work around I am using to solve the above problem is:
>>>    * If the datawindow is NOT CROSSTAB.
>>>         1. Compile the package stored procedure as stand alone
>>>         2. Use stand alone stored procedure as  the data source
>>>         3. go to the sqlsyntax and prepand the package name to the
>>>stored procedure
>>>         4. Delete the stand alone stored procedure.
>>>
>>>    *BUT if the data window is the crosstab I cannot do the step 3, as
>>>when I go to the sqlsyntax  it gives me the crosstab definition screen.
>>>HOW do I get to the procedure name to modify it.
>>>    Another problem is if I can not look at the stored procedure name
>>>How do I debug the data window if there are problems. (I know I can
>>>export the data window and look at the text file to get the procedure
>>>name and prepand the package name to it, I am looking for a decent way
>>>to do it)
>>>
>>>Any pointer will be appreciate. I have already read the document no.
>>>44553 and 44450 at sybase web site which helped a lot but I still have
>>>above problems.
>>>
>>>ASIF
>>>====
>>>
>>
>>
>
>


0
Terry
7/8/1998 7:09:04 PM
Reply:

Similar Artilces:

Whan can i do? make "string" data type convert to "window" data type, and use a "window" name to get it ancestor "window name", thanks! i use pb5
Rich, With a string window name, you can open a window of that type if you need to. You could, of course, query that window object for any attributes. Another option is to use the LibraryExport function to export the definition of the window which is specified by the string. You can then parse the resulting file to get information on the ancestor object. HTH Sanjiv. "Rich" <windows_ce@netease.com> wrote in message news:2Xsr1WW0AHA.178@forums.sybase.com... > > > ...

How to use Oracle 7.3 stored procedures as datawindows' data sources?
Hello! We are trying to use stored procedures as datawindows' datasources using PB5.0.03, Oracle 7.3.3, SQL*Net 2.3.3, clients with Windows 95, and the server with NT 4.0. When we try to do that, we receive an ORA-06550 error refering to a PLS-00306 error (wrong number or type of arguments in call to 'empleado'). We have tested the procedure directly in the database and everything seems to be ok. Does anyone know where the problem can be? or even better, how can we use the stored procedures as data sources for the datawindows? We created a package and the stored proc...

How to retrieve the data from the data using the stored procedure?
 Hi All, Can any one let me know "How to retreive the data from the database using the stored procedures". Once we retrieve the data from the database using the stored procedure we need to populate the fetched data into the textboxes of the application. Please let me know "How do I acheive this". And please note this, if I try to fetch the record which does not exists in the database it should throw an exception saying that the record which you are trying to search does not exist Thanks and Regards Sandeep. Hi,sunny For Calling Stored Proc U can re...

Oracle Stored Procedure data window
Hello, I am trying to create datawindow from a stored procedure. I created a simple SP that return a value. I selected the Stored procedure option in datawindow creation and it showed the procedure i created.After selcting the procedure I got a blank datawindow painter. Menus except File,window are disabled. Can anybody exlpain the basic procedure to create a stored procedure datawindow with Oracle. I am using PB6.5 and Ora 7.1 Thanks Bal For Oracle 7.1 you need to use the pbdbms package. That version of Oracle had no way of returning result sets. The package is cr...

Data window's Data source is Oracle Procedure
Hello I created one Packed in that i created one PROCEDURE now i want to use that oracle procedure in data window as a data source. but when i created a new datawindow (PB9)in select store procedure dialog box i cant see that procedure or package in a list what i have to please tell me . regards s Patel First, the procedure has to return a ref cursor to be used as a datasource with a datawindow (natively). Second, your userid must have execute permission on the package/procedure. Third, you need to configure you PB database connection on the SYSTEM tab, there's a checkbox t...

Oracle stored procedure as source of data in datawindow
I read some articles about this functionality here and I have one question. A PL/SQL code is like this: -- type definition CREATE OR REPLACE PACKAGE fsp IS TYPE rctl IS REF CURSOR; END; -- stored procedure as source of data create or replace procedure fsc(arg1 in number, rc1 in out fsp.rctl) as begin OPEN rc1 FOR SELECT ..... FROM ... WHERE .. = arg1; end; It works fine with multiple rows selection and dynamic declaration of cursor too, but who close this cursor after retrieve in datawindow (calling fsc procedure)? Does PowerBuilder do it? Thanks. Yes, PB clo...

Using Oracle Stored Procedures as a data source
I am working with PowerBuilder 6.5 and Oracle 7.3. I created a stored procedure as suggested in the 'Connecting to your Database' manual. The stored procedure executes using Oracle Plus. When I select it as a source for a DataWindow I'm getting an Oracle error, ORA-06550. It says that "'Proc1' must be declared". Proc1 is the name of the stored procedure. I would appreciate any suggestions. George Farrance Try one of these: 1. Reset PBDBMS from the database profile setting. [PBDBMS = -> DBParm] or 2. REMOVE the instance name from in front o...

Oracle stored procedures as a datawindow data source
I've created an Oracle stored procedure that I would like to use as the data source for my datawindow. When you create a new datawindow, you get an option to select the data source and presentation style. I selected stored procedure and grid and selected OK. Then a Select Stored Procedure box came up. How do I get my stored procedure to get listed in this box? Thank you, Tonya I don't know Oracle but I do know SQL Server/SQL Anywhere. Perhaps your stored procedure needs to be created by a user that puts it into the public domain? Maybe you are not logged into...

data, data, data
Hello, I need some advice on the best method to keep a database updated in this scenario. I have a local Solomon SQL Server where we keep our inventory. I want to build an application that will be hosted at a remote hosting location which provides a SQL database. I want to build the application to allow clients to access the inventory items and make requests based on the remote SQL data. The order will be sent to our fulfillment department via email; they will fiill the request and ship.  I'm not sure the method to do this while keeping the data current on the remote and the l...

Can anyone tell me how to access data from Stored Procedures using data adapters?
 Can anyone tell me how to access data from Stored Procedures using data adapters? My task is to select a row which is valid with data particular value. Suppose i had to get all values of particular user after validating username and password. Can anyone give me some hint regarding store procedure and retriving data from stored procedure using data adapters ? How can i bind data to dropdownbox of one field in the table using datasets and data adapters? How can i insert data in database using data adapters?Can any one solve this?  for your question, dataadapter's fill and...

How do I resolve this problem of using a stored procedure with Data Window.
Hi, I have written a stored procedure in Sybase, which conssists of multiple statements (including 'Select into') and at the end it has a final 'Select' statement which gives out the output. When I use this SP in a datawindow and try to retrieve the data using - dw_sheet.SEtTransobject (sqlca) dw.sheet.Retrieve() It gives following error message - DataWindow Error Select Error : Select INTO command not allowed within multi-statement transaction. However there are other SPs written in similar manner running smoothly. Please suggest how do I resolve...

how to get data from all related tables using stored procedure
I have a situation where I want to load some entities from one table lets say the table is customers and i would like to load all the customers with first name = dummy, not only this i would like to load all the orders  and order details for these specific customers (these are two different separate tables) . I want all this within one stored procedure that return me three results for three different tables. Please tell me whether it is possible and how. You can simply have multiple SELECT's in a stored procedure. Then using a DataSet, the Tables collection will have the same num...

Oracle Stored Procedures as DataWindows' data sources
Hi there! We have been working with PowerBuilder and MS SQL Server 6.0 and now, we need to work with Oracle 7. Some of the reports the application presents were built using datawindows with Stored Procedures as their data source. Trying to convert those procedures to Oracle, we've found that in Oracle Stored Procedures we need to specify an INTO in a SELECT statement, which is not properly for us because our SELECT retrieves many rows. Do you have any ideas for solving this problem, keeping the stored procedures as the datawindows' data sources? Any help is welcome. Th...

Web resources about - Using stored procedure to get the data for data window (Oracle) - sybase.powerbuilder.datawindow

Parliamentary procedure - Wikipedia, the free encyclopedia
... of the House of Commons of the Parliament of the United Kingdom , from which it derives its name. In the United States, parliamentary procedure ...

Procedure is more a snap than a snip
A QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...

Rushed cosmetic procedures a 'recipe for disaster'
&#8203;When Chanelle O'Hare went searching online for a deal on potential cosmetic procedures, she could not have imagined that what she ended ...

Jetstar procedures under investigation after planes took off too heavy
Jetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...

Old Lady Lawyer: Uncivil Procedure
What is some of the worst behavior you've witnesses by attorneys?

NFL Announces Changes to Officiating Procedures for Playoffs - Bleacher Report
The NFL formally approved changes to its postseason officiating procedures to allow referees the opportunity to consult Vice President of Officiating ...

We need to reform the culture of law enforcement, not just the procedures
We need to reform the culture of law enforcement, not just the procedures by digby I have a new piece up at Salon this morning about police ...

Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...
Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...

TSA Updates Screening Procedure, Will Mandate Some Passengers Use Full-Body Scanners
... Imaging Technologies, or AIT, in favor of full-body pat-downs by TSA agents. Under the new mandate, not everyone can opt for the pat-down procedure. ...

'Painless' dental cavity procedure regrows tooth enamel
... "cavity" a lot of people sweat thinking about painful injections and relentless drilling. But scientists in Britain have developed a new procedure ...

Resources last updated: 1/10/2016 3:09:18 PM