SQL passthru in maintain

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, 2003

Maintain Wizard

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, 2003

Ignored post by Maintain Wizard posted

Show Post

K Mann

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, 2003

Ignored post by K Mann posted

Show Post

Maintain Wizard

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, 2003

Ignored post by Maintain Wizard posted

Show Post

K Mann

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, 2003

Ignored post by K Mann posted

Show Post