PB 8 & oracle 9i - calling SP using Declare/Execute singel return value

Hi all,

I've just begun using Oacle w/PB and am trying to get a 'feel' for the differences between PB/Oracle and PB/DB2 (last engagement) and I am having problems with the DECLARE/EXECUTE of Stored Procedures.  Any suggestions would be greatly appreciated.

In an User #Event I have the following :

string p_cont_id, p_cd_desc, p_srch_str, p_cms_uid, p_txt_found

//hard-coded values for testing purposes only
p_cont_id ='20040430001'
p_cd_desc = ''
p_srch_str = 'inspected'
p_cms_uid = 'smith8237'
p_txt_found = 'n'

 DECLARE findtext PROCEDURE FOR FIND_RMRKS_TEXT  
              (P_CONT_ID = :p_cont_id,  
         P_CD_DESC = :p_cd_desc,  
         P_SRCH_STR = :p_srch_str,  
         P_CMS_UID = :p_cms_uid,  
         P_TXT_FOUND = :p_txt_found) ;

EXECUTE findtext ;

IF SQLCA.SQLCODE<> 0 and SQLCA.SQLDBCODE <> 0 THEN
  Messagebox("Error Executing findtext"+String(SQLCA.SQLDBCODE),SQLCA.SqlErrText,Exclamation!)
END IF

when running the app I get the following message:

SQLSTATE = S1000
[Oracle][ODBC][Ora]ORA-06550: line 1, column 24:
PLS-00201: identifier 'P_CONT_ID' must be declared
ORA-06550: line 1, column 7:
PL/SQL: staement ignored

Where must it be declared?  In oracle? Is this a permissions thing?  Should I make it an External Function? If so, how?
TIA,
KAS



KAS11Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gajender_99Commented:
Hi kas

open your transaction object
got to locat external function and declare you procedure

subroutine rpc_findnext( string  P_CONT_ID,string  p_cd_desc,string p_srch_str, string p_cms_uid,string p_txt_found ) RPCFUNC ALIAS FOR "~"FIND_RMRKS_TEXT  ~""


You can use your script this way

string p_cont_id, p_cd_desc, p_srch_str, p_cms_uid, p_txt_found

//hard-coded values for testing purposes only
p_cont_id ='20040430001'
p_cd_desc = ''
p_srch_str = 'inspected'
p_cms_uid = 'smith8237'
p_txt_found = 'n'

sqlca.rpc_findnext(p_cont_id, p_cd_desc, p_srch_str,  p_cms_uid,p_txt_found) ;
IF SQLCA.SQLCODE<> 0 and SQLCA.SQLDBCODE <> 0 THEN
  Messagebox("Error Executing findtext"+String(SQLCA.SQLDBCODE),SQLCA.SqlErrText,Exclamation!)
END IF

This is the way of declaring a storeprocedure and running it.

Gajender

gajender_99Commented:
if you dont want to use the above one then i am making changes to your script

string p_cont_id, p_cd_desc, p_srch_str, p_cms_uid, p_txt_found

//hard-coded values for testing purposes only
p_cont_id ='20040430001'
p_cd_desc = ''
p_srch_str = 'inspected'
p_cms_uid = 'smith8237'
p_txt_found = 'n'

 DECLARE findtext PROCEDURE FOR FIND_RMRKS_TEXT  (:p_cont_id,   :p_cd_desc,   :p_srch_str,   :p_cms_uid,   :p_txt_found) ;

EXECUTE findtext ;

IF SQLCA.SQLCODE<> 0 and SQLCA.SQLDBCODE <> 0 THEN
  Messagebox("Error Executing findtext"+String(SQLCA.SQLDBCODE),SQLCA.SqlErrText,Exclamation!)
END IF

this will also run

Gajender
KAS11Author Commented:
Hi Gajender,

Thanks for the help.  I've already tried option #2, with the same resultant error message, that's why I was wondering if it was an Oracle persmissions issue.

Let me pay around with  option #1 and see what happens.  at least that error message doesn't come back so it appears you put me on the right path.

Cheers,
KAS
BenClarkCommented:
For Oracle, you need to change your declare to look like this:
DECLARE findtext PROCEDURE FOR FIND_RMRKS_TEXT  
             (P_CONT_ID => :p_cont_id,  
         P_CD_DESC => :p_cd_desc,  
         P_SRCH_STR => :p_srch_str,  
         P_CMS_UID => :p_cms_uid,  
         P_TXT_FOUND => :p_txt_found) ;

The parameters on your procedure must match those in the declare:
P_CONT_ID
P_CD_DESC
P_SRCH_STR
P_CMS_UID
P_TXT_FOUND

Also, if you use the SQL wizard to define your Procedure Declaration, you will be
able to see correct syntax.

Ben

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KAS11Author Commented:
Just wanted to thank you both for your assistance.  Actually, both methods worked.  The problem was a permissions thing in Oracle.

thanks again,
Ken
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Editors IDEs

From novice to tech pro — start learning today.