Executing oracle stored procedure in powerbuilder

Hi all

We have a thin-client application written in powerbuilder with all the
processing (add,update,delete etc) in Sybase stored procedures. We are
currently porting this app to Oracle 8.1.6. We use the following syntax to
execute stored procs for sybase

Declare instance variables:

 DECLARE ptKitAdd PROCEDURE FOR
         @RC = p_KitAdd_p
         @OrgId = :OrgId,
         @ProductId = :ProductId,
         @KitID = :is_kitid,
         @Note = :is_notename,
         @Letter = :is_letter,
         @paper = :is_paper,
         @inTS = :id_TS  ;

In script for add button:

 Execute ptKitAdd ;

 If SQLCA.sqlcode = -1 Then
     messagebox("SQL Error", SQLCA.sqlerrtext)
     Rollback using SQLCA;
     Close ptKitAdd ;
     Return
Else
    Fetch ptKitAdd Into :RC;
    Close ptKitAdd ;

   If RC <> errconstant Then
       Event ge_errorcheck()
       RC = 0
       Return
    End If
    Commit Using SQLCA;
    If SQLCA.sqlcode = -1 Then
        Rollback using SQLCA ;
        Return
    End If
End If

My question is what would the instance variable declaration for the oracle
equivalent stored procedure look like because i get a "wrong type or number
of aruments" message if i execute the script against the oracle database.
The procedure works however if i execute it manually

Thanks
Jaco Grobler



0
Jaco
8/30/2000 8:14:18 AM
sybase.powerscript 7704 articles. 0 followers. Follow

3 Replies
1159 Views

Similar Articles

[PageSpeed] 56

Jaco,

The PB Help says:

DECLARE logical_procedure_name PROCEDURE  FOR
Oracle_procedure_name(:InParam1,:InParam2, ...)
{USING transaction_object};

I think you declare it like this:

 DECLARE ptKitAdd PROCEDURE FOR
oracle_sp_name(
         :p_KitAdd_p,
         :OrgId,
         :ProductId,
         :is_kitid,
         :is_notename,
         :is_letter,
         :is_paper,
         :id_TS ) ;



Jaco Grobler wrote:

> Hi all
>
> We have a thin-client application written in powerbuilder with all the
> processing (add,update,delete etc) in Sybase stored procedures. We are
> currently porting this app to Oracle 8.1.6. We use the following syntax to
> execute stored procs for sybase
>
> Declare instance variables:
>
>  DECLARE ptKitAdd PROCEDURE FOR
>          @RC = p_KitAdd_p
>          @OrgId = :OrgId,
>          @ProductId = :ProductId,
>          @KitID = :is_kitid,
>          @Note = :is_notename,
>          @Letter = :is_letter,
>          @paper = :is_paper,
>          @inTS = :id_TS  ;
>
> In script for add button:
>
>  Execute ptKitAdd ;
>
>  If SQLCA.sqlcode = -1 Then
>      messagebox("SQL Error", SQLCA.sqlerrtext)
>      Rollback using SQLCA;
>      Close ptKitAdd ;
>      Return
> Else
>     Fetch ptKitAdd Into :RC;
>     Close ptKitAdd ;
>
>    If RC <> errconstant Then
>        Event ge_errorcheck()
>        RC = 0
>        Return
>     End If
>     Commit Using SQLCA;
>     If SQLCA.sqlcode = -1 Then
>         Rollback using SQLCA ;
>         Return
>     End If
> End If
>
> My question is what would the instance variable declaration for the oracle
> equivalent stored procedure look like because i get a "wrong type or number
> of aruments" message if i execute the script against the oracle database.
> The procedure works however if i execute it manually
>
> Thanks
> Jaco Grobler

--
Tom Mackin
tmackin@LNC.com
219.455.1466

pbm_iwannabelikeroy


0
Tom
8/30/2000 3:07:58 PM
Easiest way I found was to create a transaction object.  In the 'Declare
Local External Functions' pane, right mouse click and select 'Paste Special'
and then 'SQL'.  There will be a selection 'Remote Stored Procedures' which
will define the Stored Procedure as a function to the trans object.  Then
just call by coding

transobject.storedprocedurename(arg1,arg2,...)

Hope this helps.

--
Thanks
David Warner
Tom Mackin <tmackin@lnc.com> wrote in message
news:39AD234E.BA500DB9@lnc.com...
> Jaco,
>
> The PB Help says:
>
> DECLARE logical_procedure_name PROCEDURE  FOR
> Oracle_procedure_name(:InParam1,:InParam2, ...)
> {USING transaction_object};
>
> I think you declare it like this:
>
>  DECLARE ptKitAdd PROCEDURE FOR
> oracle_sp_name(
>          :p_KitAdd_p,
>          :OrgId,
>          :ProductId,
>          :is_kitid,
>          :is_notename,
>          :is_letter,
>          :is_paper,
>          :id_TS ) ;
>
>
>
> Jaco Grobler wrote:
>
> > Hi all
> >
> > We have a thin-client application written in powerbuilder with all the
> > processing (add,update,delete etc) in Sybase stored procedures. We are
> > currently porting this app to Oracle 8.1.6. We use the following syntax
to
> > execute stored procs for sybase
> >
> > Declare instance variables:
> >
> >  DECLARE ptKitAdd PROCEDURE FOR
> >          @RC = p_KitAdd_p
> >          @OrgId = :OrgId,
> >          @ProductId = :ProductId,
> >          @KitID = :is_kitid,
> >          @Note = :is_notename,
> >          @Letter = :is_letter,
> >          @paper = :is_paper,
> >          @inTS = :id_TS  ;
> >
> > In script for add button:
> >
> >  Execute ptKitAdd ;
> >
> >  If SQLCA.sqlcode = -1 Then
> >      messagebox("SQL Error", SQLCA.sqlerrtext)
> >      Rollback using SQLCA;
> >      Close ptKitAdd ;
> >      Return
> > Else
> >     Fetch ptKitAdd Into :RC;
> >     Close ptKitAdd ;
> >
> >    If RC <> errconstant Then
> >        Event ge_errorcheck()
> >        RC = 0
> >        Return
> >     End If
> >     Commit Using SQLCA;
> >     If SQLCA.sqlcode = -1 Then
> >         Rollback using SQLCA ;
> >         Return
> >     End If
> > End If
> >
> > My question is what would the instance variable declaration for the
oracle
> > equivalent stored procedure look like because i get a "wrong type or
number
> > of aruments" message if i execute the script against the oracle
database.
> > The procedure works however if i execute it manually
> >
> > Thanks
> > Jaco Grobler
>
> --
> Tom Mackin
> tmackin@LNC.com
> 219.455.1466
>
> pbm_iwannabelikeroy
>
>


0
David
8/31/2000 6:41:33 PM
Sorry
    This was for PB 7.0

--
Thanks
David Warner
David Warner <zz7942@dfa.state.ny.us> wrote in message
news:JGanGy3EAHA.317@forums.sybase.com...
> Easiest way I found was to create a transaction object.  In the 'Declare
> Local External Functions' pane, right mouse click and select 'Paste
Special'
> and then 'SQL'.  There will be a selection 'Remote Stored Procedures'
which
> will define the Stored Procedure as a function to the trans object.  Then
> just call by coding
>
> transobject.storedprocedurename(arg1,arg2,...)
>
> Hope this helps.
>
> --
> Thanks
> David Warner
> Tom Mackin <tmackin@lnc.com> wrote in message
> news:39AD234E.BA500DB9@lnc.com...
> > Jaco,
> >
> > The PB Help says:
> >
> > DECLARE logical_procedure_name PROCEDURE  FOR
> > Oracle_procedure_name(:InParam1,:InParam2, ...)
> > {USING transaction_object};
> >
> > I think you declare it like this:
> >
> >  DECLARE ptKitAdd PROCEDURE FOR
> > oracle_sp_name(
> >          :p_KitAdd_p,
> >          :OrgId,
> >          :ProductId,
> >          :is_kitid,
> >          :is_notename,
> >          :is_letter,
> >          :is_paper,
> >          :id_TS ) ;
> >
> >
> >
> > Jaco Grobler wrote:
> >
> > > Hi all
> > >
> > > We have a thin-client application written in powerbuilder with all the
> > > processing (add,update,delete etc) in Sybase stored procedures. We are
> > > currently porting this app to Oracle 8.1.6. We use the following
syntax
> to
> > > execute stored procs for sybase
> > >
> > > Declare instance variables:
> > >
> > >  DECLARE ptKitAdd PROCEDURE FOR
> > >          @RC = p_KitAdd_p
> > >          @OrgId = :OrgId,
> > >          @ProductId = :ProductId,
> > >          @KitID = :is_kitid,
> > >          @Note = :is_notename,
> > >          @Letter = :is_letter,
> > >          @paper = :is_paper,
> > >          @inTS = :id_TS  ;
> > >
> > > In script for add button:
> > >
> > >  Execute ptKitAdd ;
> > >
> > >  If SQLCA.sqlcode = -1 Then
> > >      messagebox("SQL Error", SQLCA.sqlerrtext)
> > >      Rollback using SQLCA;
> > >      Close ptKitAdd ;
> > >      Return
> > > Else
> > >     Fetch ptKitAdd Into :RC;
> > >     Close ptKitAdd ;
> > >
> > >    If RC <> errconstant Then
> > >        Event ge_errorcheck()
> > >        RC = 0
> > >        Return
> > >     End If
> > >     Commit Using SQLCA;
> > >     If SQLCA.sqlcode = -1 Then
> > >         Rollback using SQLCA ;
> > >         Return
> > >     End If
> > > End If
> > >
> > > My question is what would the instance variable declaration for the
> oracle
> > > equivalent stored procedure look like because i get a "wrong type or
> number
> > > of aruments" message if i execute the script against the oracle
> database.
> > > The procedure works however if i execute it manually
> > >
> > > Thanks
> > > Jaco Grobler
> >
> > --
> > Tom Mackin
> > tmackin@LNC.com
> > 219.455.1466
> >
> > pbm_iwannabelikeroy
> >
> >
>
>


0
David
8/31/2000 6:45:13 PM
Reply:

Similar Artilces:

Executing oracle stored procedures from powerbuilder
Hi all We have a thin-client application written in powerbuilder with all the processing (add,update,delete etc) in Sybase stored procedures. We are currently porting this app to Oracle 8.1.6. We use the following syntax to execute stored procs for sybase Declare instance variables: DECLARE ptKitAdd PROCEDURE FOR @RC = p_KitAdd_p @OrgId = :OrgId, @ProductId = :ProductId, @KitID = :is_kitid, @Note = :is_notename, @Letter = :is_letter, @paper = :is_paper, @inTS = :id_TS ; In script for add button: ...

Executing oracle stored procedure from powerbuilder
Hi all We have a thin-client application written in powerbuilder with all the processing (add,update,delete etc) in Sybase stored procedures. We are currently porting this app to Oracle 8.1.6. We use the following syntax to execute stored procs for sybase Declare instance variables: DECLARE ptKitAdd PROCEDURE FOR @RC = p_KitAdd_p @OrgId = :OrgId, @ProductId = :ProductId, @KitID = :is_kitid, @Note = :is_notename, @Letter = :is_letter, @paper = :is_paper, @inTS = :id_TS ; In script for add button: ...

Execute stored procedure from stored procedure
Hello,i need to execute a stored procedure from another stored procedure. For example:StoredProcedure1: Returns a table with columns "year" and "value".StoredProcedure2: Also returns a table with columns "year" and "value".In Stored Procedure3: I would like to use the results from StoredProcedure1 and StoredProcedure2 and join them. Can somebody give me an example how to execute a stored procedure from another one and use the result table for a new command?Kind regards and thanks,carsaw hi,first off all let me clarify here - you cannot return ta...

execute oracle stored procedure
Hi all, i would like to execute a oracle store procedure by simply using a command with commandType -- >Text. Somthing like this: Shared.OracleStringConnection)) { conn.Open(); using (OracleCommand cmd = new OracleCommand("execute nomeSP ('par1','par2');", conn)) { cmd.ExecuteNonQuery(); } conn.Close(); } But i get an exception like sql syntax valide...Does anyone know why? (I wold like use text command not storedprocedure) Thanks     useCMD.CommandText = "your proc name";CMD.CommandType = CommandType.StoredProcedure;execute this....

Execute an Oracle stored procedure
Environment: NT40, PB70, ORA8.1.5 I am trying to execute a very simple oracle SP from PB with the following powerscript code: DECLARE sp_1 dynamic procedure for sqlsa; PREPARE sqlsa FROM "EXECUTE jzua.kk1" USING sqlca2; EXECUTE DYNAMIC sp_1; But it returns the error : ORA-24372 Invalid object for describe This "Dynamic SQL Format 3" syntax is for using it with input variables unknow in design-time. In your case you don't have them. Al you need to do is EXECUTE IMMEDIATE "EXECUTE jzua.kk1" USING sqlca2; or EXECUTE IMMEDIATE &quo...

ORACLE Stored Procedures in PowerBuilder
I am trying to pass a dynamic procedure name and dynamic number of arguments to a DECLARE and EXECUTE (procedurename) statement within Powerbuilder 6.5. The name and number of arguments are stored on a table and they are syntactically extracted into variables. Any suggestions? ...

Execute Oracle Stored Procedures.
I am working with Power++ that is connected to an Oracle 7.3 database with a Native driver Pbo72050.dll I can not find a way to execute a stored procedure that is without a result set ( just to execute the stored procedure). I understood that the Query object can be used just throw ODBC - I tried it and it run OK but I need urgent to work just throw the Native driver. I also try working with DataWindow but it would work just with SP that should return a result set and it is not my case. Is any one have an idea. Lili Even wrote <34E69460.FCAA4217@vocaltec.com>... > I a...

Executing Oracle stored procedures
All, I've got Apache v2.x, Oracle 9i, running on Red Hat linux 9, mod_perl, and DBI installed and working. I'd like to be able to execute Oracle stored procedures. Is anyone doing this without the mod_plsql plugin? If so, would you be wlling to share tips for making this work. I've already got stored procedures and am connecting to the database but am seeing the following errors. DBD::Oracle::db do failed ORA-06502.: PL/SQL: numeric or value error ORA-06502: at "SYS.OWA_UTIL", line 323 I really need to know if there is something Apche needs to dis...

Oracle store procedure problem using Powerbuilder and Oracle 8.0.4.0 Net
Powerbuilder version 6.0.00 / 6.5 Build 444 Oracle Net 8.0.4.0 I found there is problem with Oracle store procedure having more than 6 in parameters. I got GPF error on Window 95 and Dr. Watson error on NT. Also, I found it works find in Oracle Net 8.0.3.0. Is this a bug? Is there any new patch for this if it is a bug. Any recommendation about What version Oracle Net or SQL Net should I used for Oracle server version 8.0.4.0 (I don't think I am using any Oracle 8 new features)? Thanks in advance, Sharon Li If you use PB 6.5, you can try the new Oracle 8 driver: ftp...

How to convert Sql Server Stored Procedures into Oracle Stored Procedures
Hi All, I am migrating from sql server2000 to oracle. I have moved all table into oracle manually. Now I need to move stored procedure. I don't know how to convert sql server stored procedure into oracle stored procedure form. Is there any tool which will convert directly. Please some one help me. Thank you.    Hi there,Please use the tool bellow, which does the job you want. http://www.swissql.com/products/sqlserver-to-oracle/sql-server-to-oracle.html thanks sharp guy...

Using Oracle Stored Procedures in Powerbuilder
I need to populate a datawindow using an Oracle stored procedure. I went through various faxline documents and found that the PBDBMS.put_line option was not useful in my case, as I was going to return multiple result sets based on cursor processing. I took the suggested option of creating a package which had a stored procedure which was returning various output variables of type table. The output variables correspond to each column of the datawindow. I will have to define the procedure as an RPC in the transaction object. I am having a problem with this. When I go into Local external f...

Executing a Packaged Oracle Stored Procedure
I am having trouble executing an Oracle stored procedure from PowerBuilder. The stored procedure is packaged. I have tried several different approaches without success: public synonyms, wrapper procedures, etc. without success. In each case I was able to execute the stored procedure at the SQLPLUS commandline, but PowerBuilder either failed to find the procedure or crashed when it did find the procedure. I am using PowerBuilder 9.2 and Oracle 9i on Windows XP. I will assume the user you logged in as from PB has EXECUTE privilege on the package. Please post the code used to invoke ...

problem in executing the stored procedure from powerbuilder
i have a stored procedure in sybase as follows. create procedure p_tes_update_open_seats @class_id int, @enroll_cnt int, @ret_value int output as Declare @capacity int, @open_seats int Select @capacity = capacity From classes Where class_id = @class_id Select @open_seats = @capacity - @enroll_cnt Update classes Set open_seats = @open_seats Where class_id = @class_id if @@error !=0 Begin Select @ret_value = -1 End else Begin Select @ret_value = 1 End i wanted to execute the above procedure, so i wrote like the code as follows in powerbuilde...

Powerbuilder-Oracle dynamic procedure execution (In OUT)
How do I execute an Oracle 8i stored procedure which has IN OUT arguments in powerbuilder 7.0. I am using Declare, Prepare , Execute etc, since my requirement demands dynamic execution. It looks like PB considers IN OUT argument as just an OUT argument. If the IN OUT arg is at the end of the stored procedure. e.g. Proc1 ( Arg1 , Arg2 IN OUT). I am able to execute the procedure as "execute Proc1 (?)". If the IN OUT argument occurs in the middle like Proc1 (arg1, arg2 IN OUT , arg2 ), I couldn't find a way to execute this in PB. "execute Proc1(?,?)", gives ...

Web resources about - Executing oracle stored procedure in powerbuilder - sybase.powerscript

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

Sybase PowerBuilder tool readied for Microsoft's .Net
... Win32 or newer style .Net development After several years of work, Sybase is ready to deliver on the final step in its plan to move PowerBuilder ...

Jim O'Neil (@jimoneil) on Twitter
Sign in Sign up To bring you Twitter, we and our partners use cookies on our and other websites. Cookies help personalize Twitter content, tailor ...

Open Directory - Computers: Programming: Languages
about dmoz - dmoz blog - report abuse/spam - help the entire directory only in Programming/Languages Description Top : Computers : Programming ...

The HP Booth Staff (Goons) Owe Me an Apology
... when you might need to use it. For instance, when I was in the WinRunner world there were plugins for driving terminal emulators and powerbuilder ...

Contact Us - Sybase Inc
Thanks for visiting the 主页 section of Sybase.com. Here you will find information about Contact Us. For more information about Business Intelligence, ...

热门搜索 - 我的异常网
... 我的异常网 » 热门搜索 sdpnet2 移动【A111】其它用户原因鉴权失败,是什么意思啊 http:// 192.168.1.102:22578 本网站服务器位于美国,受美国法律保护 MiniUI js 破解 http://192.168.1.103:8080/ powerbuilder ...

Mobile app security: Always keep the back door locked
The best way to keep mobile apps safe is to secure the services they connect to.

10 Tech Skills That Will Instantly Net You A $100,000+ Salary
... that let's developers run Java applications. Java is highly popular language for writing web apps and custom enterprise apps. No. 8: PowerBuilder ...

Top Ranked Articles
Top Ranked Articles - Free source code and tutorials for Software developers and Architects.; Updated: 23 Feb 2013

Resources last updated: 12/3/2015 11:12:47 PM