PowerBuilder can't see changed Oracle stored procedure

We have inherited a PowerBuilder 5 application using an Oracle 7.3 database;
however, the problem also occurs under PowerBuilder 7 with the same
database.

The application references a number of Oracle Stored Procedures; this works
fine. However, one procedure needed updating. If you make the change by
viewing and editing the SQL code in Oracle's Schema Manager the new version
is found. But if we make the change by running an SQL script to "CREATE OR
REPLACE" the procedure, PowerBuilder compiles OK but falls over at run time.
If we add a new stored procedure, PowerBuilder complains at compile time
that the procedure name is not found. (In all cases, the procedure can be
run from SQL*Plus).

It seems that PowerBuilder is maintaining a list of stored procedures that
contains more than the name; the name is sufficient for compilation, but
other properties don't match at runtime. However, the new procedure is not
present at all.

We have found references to "PowerBuilder Catalog Tables" on the web site,
which we presume need to be updated, but we cannot find any reference as to
how to do this update.

If this is the correct diagnosis, can anyone tell us how to force an update
of these tables? If we're barking up the wrong tree, can anyone shed any
light on the problem? We need to roll out a fix that doesn't involve
visiting every user to make the change manually through Schema Manager!

Thanks for any help offered!

Aidan Simons


0
Aidan
4/25/2000 4:03:06 PM
sybase.powerbuilder.database 9855 articles. 2 followers. Follow

5 Replies
610 Views

Similar Articles

[PageSpeed] 32

In article <Kn0IRCtr$GA.211@forums.sybase.com>, 
aidan.simons@tagroup.co.uk says...
> We have inherited a PowerBuilder 5 application using an Oracle 7.3 database;
> however, the problem also occurs under PowerBuilder 7 with the same
> database.

Aidan - 

Have you tried recompiling the packages the procedures reside in? 

Just a thought.

Mark

-- 

Mark J. Pfeifer[TeamSybase]		Corporate Technology Partners, Inc.
mpfeifer @ sprynet.com		   Emerging Technology Solutions
					         www.ctpartners.com


               Join The Team - hr@ctpartners.com


0
Mark
4/25/2000 7:25:55 PM
Mark

Thanks for the thought, but...

The procedures aren't in a package; however, we have compiled each of the
changed procedures, they show as 'valid' in the schema manager, and they run
from SQL Plus.

Aidan Simons


Mark J. Pfeifer[TeamSybase] <see_msg@sprynet.com> wrote in message
news:MPG.136f9f2ed21791a5989775@forums.sybase.com...
> In article <Kn0IRCtr$GA.211@forums.sybase.com>,
> aidan.simons@tagroup.co.uk says...
> > We have inherited a PowerBuilder 5 application using an Oracle 7.3
database;
> > however, the problem also occurs under PowerBuilder 7 with the same
> > database.
>
> Aidan -
>
> Have you tried recompiling the packages the procedures reside in?
>
> Just a thought.
>
> Mark
>
> --
>
> Mark J. Pfeifer[TeamSybase] Corporate Technology Partners, Inc.
> mpfeifer @ sprynet.com    Emerging Technology Solutions
>          www.ctpartners.com
>
>
>                Join The Team - hr@ctpartners.com
>
>


0
Aidan
4/26/2000 8:27:11 AM
In article <zAVyEo1r$GA.293@forums.sybase.com>, 
aidan.simons@tagroup.co.uk says...
> Mark
> 
> Thanks for the thought, but...
> 
> The procedures aren't in a package; however, we have compiled each of the
> changed procedures, they show as 'valid' in the schema manager, and they run
> from SQL Plus.

I take it this is not a source for a DataWindow correct - you are using 
embedded SQL. 

Maybe you can post a snippet of your code.

Mark

-- 

Mark J. Pfeifer[TeamSybase]		Corporate Technology Partners, Inc.
mpfeifer @ sprynet.com		   Emerging Technology Solutions
					         www.ctpartners.com


               Join The Team - hr@ctpartners.com

0
Mark
4/26/2000 12:53:38 PM
PowerBuilder does not maintain its own list of stored procedures.  If a
procedure is not found, it is either not found or not valid.  This problem
frequently occurs when you change an object referenced in the SP.  The
change puts the procedure in an invalid state.  If you then attempt to
execute the procedure, Oracle detects the invalid state and recompiles it on
the fly, thus fixing the problem.  PowerBuilder, however, must often
describe the procedure  before running it.  This is needed to determine the
Oracle data types  for binding the arguments.  Describes to not
automatically cause a recompile and you get the error.
Aidan Simons <aidan.simons@tagroup.co.uk> wrote in message
news:Kn0IRCtr$GA.211@forums.sybase.com...
> We have inherited a PowerBuilder 5 application using an Oracle 7.3
database;
> however, the problem also occurs under PowerBuilder 7 with the same
> database.
>
> The application references a number of Oracle Stored Procedures; this
works
> fine. However, one procedure needed updating. If you make the change by
> viewing and editing the SQL code in Oracle's Schema Manager the new
version
> is found. But if we make the change by running an SQL script to "CREATE OR
> REPLACE" the procedure, PowerBuilder compiles OK but falls over at run
time.
> If we add a new stored procedure, PowerBuilder complains at compile time
> that the procedure name is not found. (In all cases, the procedure can be
> run from SQL*Plus).
>
> It seems that PowerBuilder is maintaining a list of stored procedures that
> contains more than the name; the name is sufficient for compilation, but
> other properties don't match at runtime. However, the new procedure is not
> present at all.
>
> We have found references to "PowerBuilder Catalog Tables" on the web site,
> which we presume need to be updated, but we cannot find any reference as
to
> how to do this update.
>
> If this is the correct diagnosis, can anyone tell us how to force an
update
> of these tables? If we're barking up the wrong tree, can anyone shed any
> light on the problem? We need to roll out a fix that doesn't involve
> visiting every user to make the change manually through Schema Manager!
>
> Thanks for any help offered!
>
> Aidan Simons
>
>


0
Dan
5/1/2000 1:05:04 PM
We've seen this situation here, as well.  If you're not calling the
stored procedure from a datawindow, you could trap the Oracle exception
(existing state of package has been discarded) and attempt to call it
again.  Since Oracle will have recompiled at this point, your call
should succeed.  Code snippet attached.  Note that we've elected to
attach the stored procedure call as an external RPCFUNC to the
transaction object.

long ll_retry
for ll_retry = 1 to MAX_SP_RETRY
	// Blocking call to DB
	sqlca.your_stored_proc(an_argument)

	if SQLCA.SQLDBCode = 4068 then
		// ORA-04068: Existing state of packages has been discarded.
		// Try the call again.
		if ib_trace then this.of_log( methodName + ' Trapped ORA-04068. 
Invoking SP again.' )
		continue
	else
		// Call probably succeeded.  Drop out of the loop and handle
		// SP-specific errors in next paragraph
		exit
	end if
next

HTH

Glenn
Aidan Simons wrote:
> 
> Mark
> 
> Thanks for the thought, but...
> 
> The procedures aren't in a package; however, we have compiled each of the
> changed procedures, they show as 'valid' in the schema manager, and they run
> from SQL Plus.
> 
> Aidan Simons
> 
> Mark J. Pfeifer[TeamSybase] <see_msg@sprynet.com> wrote in message
> news:MPG.136f9f2ed21791a5989775@forums.sybase.com...
> > In article <Kn0IRCtr$GA.211@forums.sybase.com>,
> > aidan.simons@tagroup.co.uk says...
> > > We have inherited a PowerBuilder 5 application using an Oracle 7.3
> database;
> > > however, the problem also occurs under PowerBuilder 7 with the same
> > > database.
> >
> > Aidan -
> >
> > Have you tried recompiling the packages the procedures reside in?
> >
> > Just a thought.
> >
> > Mark
> >
> > --
> >
> > Mark J. Pfeifer[TeamSybase] Corporate Technology Partners, Inc.
> > mpfeifer @ sprynet.com    Emerging Technology Solutions
> >          www.ctpartners.com
> >
> >
> >                Join The Team - hr@ctpartners.com
> >
> >
0
Glenn
5/1/2000 3:34:38 PM
Reply:

Similar Artilces:

Can't base report on stored procedure--'Can't create Datawindow'
I installed IM65 today to give it a try. I made a db config to our local ASE 11.9.2 engine via Sybase' odbc driver that came with 11.9.2. I start a new report, tell it to be tabular and to get data from a SP. It asks which SP and I tell it. The result is an odbc error: Cannot create DataWindow Intersolv SQL ODBC driver: Incorrect syntax near '='. 1 execute dbo.sp_si_addressbest_;0 RETURN_VALUE = :RETURN_VALUE' I can execute the SP fine from SQL Advantage or from Crytal Reports. Thanks. -- Frank Burleigh Indiana University School of Law Bloomi...

ODBC, ASE and PowerBuilder : can't use my Stored Procedures
Hi, I'm in trouble trying to use an ASE 11.5 database via ODBC in PowerBuilder 7. With the native driver, everything run fine. When I use ODBC instead of SYC, calls to stored procedure don't run as expected. Depending of the ODBC driver I use, I get different error messages and I can't use my stored procedure. Here's the way SP is called: DECLARE p_dsu_syslog02 PROCEDURE FOR @rv = dsu_acces01 128, @co_niv_hab = :ls_buffer OUTPUT USING SQLCA; EXECUTE p_dsu_syslog02; If SQLCA.SQLCode <> 0 Then return -1 End if FETCH p_dsu_syslog02 INTO :code_retour, ...

I can't see my Procedure. Why? This is my first Stored Procedure.
Hello,I created the "MyDb" database using Microsoft SQL 2005 Server Management Studio and I added the table "dbo.Surveys".Then I right clicked on Store Procedures and created a new procedure.When I close it I am asked to save it. I save it and gave the file a name.However my stored procedure doesn't show in the Stored Procedures list.I can only access it by loading the file (File > Open > File). Why?When I open the File the Connect Window shows again.I believe the procedure is created because I used CREATE.When I execute it twice I got the message that there is already another procedur...

PowerBuilder and Oracle 10g: Invalid stored procedure parameter type 'BOOLEAN'
Hi, I am getting the following error message while executing a procedure 'update_status' in the package pkg_test. Invalid 'Stored procedure parameter type of boolean on DDD' Here is the signnature of the procedure. pkg_test.update_status(AAA varchar2, BBB number, CCC number, DDD varchar2, EEE boolean default false)=91 and the way of calling this procedure is pkg_test.update_status ('TEST',12,2345,'PLEASEWORK',TRUE) The communication between the powerbuilder 10.5 and oracle10g is through the ORACLE 10g drivers. After the execution the SQLCA.SQ...

Powerbuilder Can't find Powerbuilder NVO on Server
I have been trying to get through the PB and Jaguar document, and specifically I have been working with a Powerbuilder NVO and trying to load it onto the server, and then access it from Powerbuilder. I have constructed the Powerbuilder NVO from the oleobject. I placed one function, with a return type of (NONE) as instructed, with a string passed by reference. I use the OLE automation tool to compile the .tlb, ..reg files. I have tried this with both .PBD and .DLL with identical results. Before I load the component on the server, I can see all the packages and methods normally. A...

PowerBuilder Can't Open Database
Hi, I was using the database in PowerBuilder to connect to a SQL Anywhere 7.0 database. When I execute a SELECT statement, the program is aborted. After I re-start the PowerBuilder and click the 'database icon', the following error message box appears. PB70.exe has generated errors and will be closed by Windows. You will need to restart the program. An error log is being created. However, I found nothing on the event viewer. I also try to reinstall the PowerBuilder but the error still occurs. Configuratiion of my computer: 1. Windows 2000 Professional with S...

Can't see stored procedures
Hi,  Can anyone help me. I've created a stored procedure in sql server and I'm trying to run it from my asp.net page. On Database Explorer I can't see it, or any for that matter, however I can see tables in the same schema. Also I can't see it when I build a table adapter either. Can anybody help?  Thanks  Sam   My first suspicion is that you didn't succeed in creating the stored procedure as you think you have done.  To allay that suspicion, can you see the proc in Sql Server Management Studio? Regards Mike [MVP - ASP/ASP.NET]M...

Can't see all of stored procedure...
.... in the database painter. When I edit a stored procedure (MSS 7 or MSS 2000 or MSS 2005) in the database painter, using either PB 9, only part of the stored procedure is visible. Is there some setting that's causing this? Some buffer size or something? CANCEL this question. I'm getting mixed up between PB 11.5 and PB 9 and the various database releases. Will re-post. "Laurel" <FakeMail@Hotmail.com> wrote in message news:4a4b58f5$2@forums-3-dub.sybase.com... > ... in the database painter. > > When I edit a stored procedure (MSS 7...

Can't setfocus with Progress Database in PowerBuilder
I'm working with Progress database on SCO-UNIX in PowerBuilder 5.0.03, and when I run my application and want to insert data in datawindow, the focus does'nt appear in the first data area. But with and another database, like code is fonctionnal. Why I can't setfocus in the Progress Database. Thanks in advance Val Sigma Solutions informatiques inc. ...

Oracle Package can't call from PowerBuilder
Hi all! Are there any one know how we can call Package Stored Procedure in Oracle8.0.4 from PowerBuilder? When I called Package from PowerBuilder using the following script, I got"Errors - Database C0038: ORA-2000": date ld_stdate string ls_stid DECLARE proc00 PROCEDURE FOR dtms.rpt00_perm_detailPkg (:ls_stid, :ld_stdate) EXECUTE proc00; However, I can call any stored procedure by using the above script. When I read PowerBuilder and Oracle8 documentation, I saw it says,"You cannot call these procedures directly from PowerBuilder because some of the arguments ...

Oracle Package can't call from PowerBuilder
Hi all! Are there any one know how we can call Package Stored Procedure in Oracle8.0.4 from PowerBuilder? When I called Package from PowerBuilder using the following script, I got"Errors - Database C0038: ORA-2000": date ld_stdate string ls_stid DECLARE proc00 PROCEDURE FOR dtms.rpt00_perm_detailPkg (:ls_stid, :ld_stdate) EXECUTE proc00; However, I can call any stored procedure by using the above script. When I read PowerBuilder and Oracle8 documentation, I saw it says,"You cannot call these procedures directly from PowerBuilder because some of the arguments ...

Client can't see changes in database (Firebird)
Hi, All clients are connected to a Firebird Database using the Interbase-components in C++Builder2010. If a client do inserting or deleting, other clients can't see the changes. The changes are only in database visible if the first client reconnect to the database. What I'm doing wrong? greetings, johannes Hi, This is an InterBase newsgroup, not Firebird. > All clients are connected to a Firebird Database using the > Interbase-components in C++Builder2010. If a client do inserting or > deleting, other clients can't see the changes. The changes are ...

Oracle Package can't call from PowerBuilder #2
Hi all! Are there any one know how we call Package Stored Procedure in Oracle8.0.4 from PowerBuilder? When I called Package from PowerBuilder using the following script, I got"Errors - Database C0038: ORA-2000": date ld_stdate string ls_stid DECLARE proc00 PROCEDURE FOR mdtms.rpt00_perm_detailPkg (:ls_stid, :ld_stdate) EXECUTE proc00; However, I can call any stored procedure by using the above script. When I read PowerBuilder and Oracle8 documentation, I saw it says,"You cannot call these procedures directly from PowerBuilder because some of the arguments are...

I can't get no cache, I can't get no cache. 'Cause I try and I try and I try and, I can't get no, I can't get no cache.
I have fiddled out for days tinkering with the setting in about:config trying to get FireFox 12 to use the disk cache. I have NOT found the trick. This One Trick Pony ain't doing it like it used to up until recently. Pray tell anyone, What information can I share that will point a knowledgeable person to aid me in getting FF to disk.cache? I have also tried restarting FF with add-ons disabled, (There were NOT too many to do this to, so it was quick and easy) Here is my stab at trying to convey the information that MIGHT govern matters Using about:cache Informatio...

Web resources about - PowerBuilder can't see changed Oracle stored procedure - sybase.powerbuilder.database

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/17/2015 11:07:39 PM