Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     calling an oracle stored procedure

Read-Only Read-Only Topic
Go
Search
Notify
Tools
calling an oracle stored procedure
 Login/Join
 
<hellonitiv>
posted
Hi,
I am trying to call an oracle stored procedure with following syntax.
SQL SQLORA
EX Schemaname.Oracleprocname;
TABLE FILE SQLOUT
PRINT *
END
This procedure has only one parameter which is of IN OUT type.
But i am getting a lot of errors. I think this syntax does not work.
Please let me know the correct syntax. If it is mandatory to parameter to the proc, please let me know the correct syntax.
Please help!!!
 
Report This Post
FrankDutch
Virtuoso
posted Hide Post
is the connection to the database setup as it should be?
do you have the possibility to create a master on the database?
what errors do you get more specific?

"a lot of errors" is difficult to handle....

Would you please update your signature so we know what systems you have (adapters, WF version, platform etc...)

(take a look at others)




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
<hellonitiv>
posted
Hi,
I am using following connection settings:
ENGINE SQLORA SET CONNECTION_ATTRIBUTES /userid,password
SQL SQLORA SET SERVER
Connection to the Oracle server is working perfectly fine because i can successfully use individual SQL statement in my .fex file.
I am facing issues while trying to call an Oracle procedure.
Here is an snapshot of error:
(FOC1400) SQLCODE IS 6550 (HEX: 00001996)
: ORA-06550: line 1, column 7:
: PLS-00306: wrong number or types of arguments in call to
: 'SP_CCAM_GET_VSM_DATA'
: ORA-06550: line 1, column 7:
: PL/SQL: Statement ignored
L (FOC1405) SQL PREPARE ERROR.

My concern is the syntax to call the Oracle stored proc.
Could you please help in correcting my syntax.

I am working on WF 7.1.3 version with Oracle Database.
Thanks!
I cannot create master files on the database.
 
Report This Post
FrankDutch
Virtuoso
posted Hide Post
quote:
ORA-06550 PLS-00306


this error has in fact nothing to do with Webfocus

You are calling a stored procedure in Oracle that needs one or more parameters when calling it.
You have to ask this at your IT department who created the procedure and find out which parameters you should give.

If you google this string you will find hundreds of calls and there it is explained.

I hope this helps a bit...




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Jason K.
Master
posted Hide Post
try "CALL" or "EXECUTE" rather than "EX"

also, look into oracle's EXECUTE IMMEDIATE PL command, it's helped me out quite a bit when trying to perform DML actions via ODBC.


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report This Post
johney
Gold member
posted Hide Post
How do we trap this errors in FEX ?

I would like to show some meaningful / understandable error messages for each error codes (both webfocus related and oracle related).

Regards,
Johney.


Version 7.6.11
Webfocus installed in AIX 5.3,
desktop PC: Windows-XP based
Output: Excel, HTML, PDF
 
Posts: 83 | Registered: October 19, 2007Report This Post
johney
Gold member
posted Hide Post
Hi,

I have made the error trapping code as given below, I know this will take care any oracle errors, but can anybody suggest me a method to trap any web focus errors also ?

ENGINE SQLORA SET DEFAULT_CONNECTION &DATABASE
SQL SQLORA
EX wfpackage.P_MANAGE_AUDIT_REPORTS '&RPTID','&RPTNM';
END
-RUN
-IF &RETCODE GT 1 THEN GOTO ERRORPG ELSE SAVESUCC;
-ERRORPG
-TYPE RETCODE &RETCODE
-TYPE FOCERRNUM  &FOCERRNUM
-HTMLFORM ora_err.htm
-GOTO ENDLAST;
-SAVESUCC
-HTMLFORM dbsaveok.htm
-GOTO ENDLAST;
-ENDLAST


Regards,
Johney.


Version 7.6.11
Webfocus installed in AIX 5.3,
desktop PC: Windows-XP based
Output: Excel, HTML, PDF
 
Posts: 83 | Registered: October 19, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     calling an oracle stored procedure

Copyright © 1996-2020 Information Builders