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     SQL passthru in maintain

Read-Only Read-Only Topic
Go
Search
Notify
Tools
SQL passthru in maintain
 Login/Join
 
Member
posted
I'm try to use an Oracle procedure in a Mainatin SQL passthru but can't seem to get the syntax correct. Receive error message of (FOC1487) SQL SYNTAX ERROR.

For testing I am using literals instead of the parameters defined in the psql. Is the issue not being able to passthru PSQL?

/////// mainatin code ////

Case UpdatePermit

SYS_MGR.DBMS_ERRORCODE;

compute rc/I8;

rc = sys_mgr.engine("SQLORA",
"DECLARE "|
"POPKEY NUMBER; "|
"PLHU VARCHAR2(200); "|
"PFACCODE VARCHAR2(200); "|
"PFACKEY NUMBER; "|
"PNUMBER VARCHAR2(200); "|
"PDISSUE DATE; "|
"PDEXP DATE; "|
"BEGIN "|
" POPKEY := 1502;"|
" PLHU := '35';"|
" PFACCODE := 'test-01';"|
" PFACKEY := 1502;"|
" PNUMBER := '123456';"|
" PDISSUE := '01-APR-2007';"|
" PDEXP := '01-APR-2009';"|
"ATEAM.FAC_REG.ADD_PERMIT ( 1502, '35', 'test-01', 1502, '123456', '30-APR-2007', '29-APR-2008' ); "|
"COMMIT; "|
"END; ");



compute error/I3 = rc;
compute sqlerror/I5 = sys_mgr.dbms_errorcode;

EndCase


K Mann
WF 7.1.4 Win
 
Posts: 25 | Location: NY | Registered: October 16, 2003Report This Post
Master
posted Hide Post
When using the sys_mgr.engine command, the code should be sent directly to the interface. Did you try running the code outside of Maintain? That may give an answer. Could you please post the Master? I can try to repro your code.

If you do get the code working in an external procedure, you can EXEC the FOCEXEC and pass in the values. The syntax is:

EXEC proc FROM var1 var2 ... varN

In the procedure you can retrieve the variables sequentially using &1, &2, ... &N.

Mark
 
Posts: 663 | Location: New York | Registered: May 08, 2003Report This Post
Member
posted Hide Post
Mark,

I'm calling an oracle package procedure from a fex:

--psql--
procedure Add_permit(
popkey In number,
plhu In varchar,
pfaccode in varchar,
pfackey in number,
pnumber In varchar,
pdissue in varchar,
pdexp in varchar
);

--fex--
-* File permits_update.fex
ENGINE SQLORA SET DEFAULT_CONNECTION 10G_TEST
SQL SQLORA
EX ATEAM.FAC_REG.ADD_PERMIT &1,&2,&3,&4,&5,&6,&7
;

running the fex from dev studio using auto prompting works (i do have to add quotes to the character parameters), oracle executes correctly.

Calling this fex from maintain produces errors, I'm guessing there is a data type mismatch. Is there some inherent problem calling SQl passthru from maintain to WebFocus?


--maintain--
Declare opkey / I18 ;
Declare FLHU / A2 ;
Declare facCode / A10 ;
Declare fackey / I18 ;
Declare NewPermNumb / A10 ;
declare PIdt/ A20;
declare PIdt/ A20;

Case UpdatePermit

EXEC permits_update FROM opkey FLHU facCode fackey NewPermNumb PIdt PEdt ;

COMPUTE I=1;
REPEAT FocMsg.FocCount;
TYPE "Here are the messages from the server: <COMPUTE I=I+1
ENDREPEAT
commit;
EndCase

maintain runtime errors from fex exec:
Here are the messages from the server: SQLCODE IS 6550 (HEX: 00001996)
Here are the messages from the server: : ORA-06550: line 1, column 37:
Here are the messages from the server: : PLS-00103: Encountered the symbol "," when expecting one of the
Here are the messages from the server: : following:
Here are the messages from the server: : ( - + case mod new not null others
Here are the messages from the server: : avg
Here are the messages from the server: : count current exists max min prior sql stddev sum variance
Here are the messages from the server: : execute forall merge time timestamp interval date
Here are the messages from the server: :
Here are the messages from the server: : pipe


K Mann
WF 7.1.4 Win
 
Posts: 25 | Location: NY | Registered: October 16, 2003Report This Post
Master
posted Hide Post
Is there any place to insert a: -SET &ECHO=ALL;

When we do that at the beginning of Focexecs it shows all the code, including the amper variable substitution. Is it possible the values need to be in quotes before you send it?

To do this you could compute the quotes into the variable before you send it in the EXEC.

Compute Q/a1 = "'"; -* this reads double-quote, single-quote, double quote
Compute FLHU = Q || FLHU || Q;
etc...

I do not know if the alphas need quotes with your interface, but you may want to try this and see if it gives you the correct results.

Mark
 
Posts: 663 | Location: New York | Registered: May 08, 2003Report This Post
Member
posted Hide Post
Mark,

Thanks .

Adding thet quotes to the variables before sending to the EXEC worked.


K Mann
WF 7.1.4 Win
 
Posts: 25 | Location: NY | Registered: October 16, 2003Report 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     SQL passthru in maintain

Copyright © 1996-2020 Information Builders