Hello,

I have this procedure:

PROCEDURE xxx (ALFA10 IN OUT VARCHAR2)

In WF i execute this code:

-SET &ALFA10 = 'HOLA';

ENGINE SQLORA
EX schema.package.procedure('&ALFA10');
END

and Eureka!!....

(FOC1400) SQLCODE IS 6550 (HEX: 00001996)
: ORA-06550: línea 1, columna 27:
: PLS-00363: expression 'HOLA' cannot be used as an assignment target
: ORA-06550: línea 1, columna 7:
: PL/SQL: Statement ignored
L (FOC1405) SQL PREPARE ERROR.



But if the parameter's procedure is PROCEDURE xxx (ALFA10 IN VARCHAR2)

Thats! all right!!

What can I do to make it right?


Thanks in advance!

This message has been edited. Last edited by: Kerry,

Looks like an error in the assignment, take a look at this post that might help

http://oraclequirks.blogspot.c...nnot-be-used-as.html

Hello Ben,

As far as I know when attempting to invoke stored PL/SQL procedures from WebFOCUS, the Oracle PL/SQL unit must have a REF CURSOR defined as the first parameter followed by the actual parameters you want to use there (well, at least that's the way it works in WF 5.3). When you later "call" your procedure from WebFOCUS using SQL passthrough you DO NOT specify such a parameter; that is done internally by iWay because the REF CURSOR is used to fetch whatever results you want to send out from Oracle.

Detailed documentation about that feature as well as examples can be found here: http://documentation.informati...adm768/dbsqlora8.htm

A list with more documentation resources about the same topic can be found here: http://techsupport.information...om/sps/12582097.html

Please pay special attention to the syntax used when calling stored procedure from WebFOCUS as it differs slightly from the one you'd regularly use with any other language or tool; for example, parenthesis cannot be used!

You would do something like this:

SQL SQLORA
EX schema.package.procedure par1, par2, par3;
END

I can see from the documentation that version 7.6 WF no longer requires the use of REF CURSOR as a first parameter but I haven't used such a feature so I'm unable to help there There are some examples in there you may want to analyze and adjust according to your needs.

Regards,
Neftali.

This message has been edited. Last edited by: njsden,

thanks! I've looked at the documentation and fairly well, but I can not retrieve OUT.

SQL SQLORA
EX schema.pkgname.procedurename ?OUT;
END

If I do this, open the File Save window.

If add TABLE FILE...

SQL SQLORA
EX schema.pkgname.procedurename ?OUT;
TABLE FILE SQLOUT
END
.
.
I get an error (FOC)1487...

Hello Ben,

I'm afraid I'm not familiarized with the syntax you are using which is probably one of the new ways to do it in WF 7.6.

I'll provide a little example of I've done with WF 5.3.4, that is, using a REF CURSOR as a first parameter in the procedure as required in that version. I don't have a fully functioning development environment at home right now so I'm unable to test this but hopefully it'll give you a baseline for what you need.

1. Definition of a PL/SQL package with a procedure that receives a user name as a parameter and "returns" a greeting as a record.

CREATE OR REPLACE PACKAGE wf_test_pkg IS
-- Cursor type used to "return" data to WebFOCUS
   type ty_greeting is record (
      greeting   varchar2(10),
      user_name  varchar2(128),
      current_dt date
   );

   type tyrc_greeting is ref cursor return ty_greeting;

   procedure wf_hello
     ( orc_greeting    in out tyrc_greeting, 
       p_user_name  in     varchar2
     );
end; -- package spec
/

CREATE OR REPLACE PACKAGE BODY wf_test_pkg IS
-- Return one record with a "Hello" message in a REF CURSOR.
-- WebFOCUS will fetch the record from there.
   procedure wf_hello
     ( orc_greeting in out tyrc_greeting,
       p_user_name  in  varchar2
     )
   is
   begin
      -- Open ref cursor and add a record to it including:
      -- greeting, user name and current date/time

      open orc_greeting for 
           select 'Hello', p_user_name, sysdate from dual;

   end wf_hello;
end;
/

2. Call the procedure and produce a silly report with the data received.

-DEFAULT &VUSERNAME='Ben';

SQL SQLORA
EX your_schema.wf_test_pkg.wf_hello '&VUSERNAME';
TABLE FILE SQLOUT
PRINT GREETING
      USER_NAME
      CURRENT_DT
END

The only parameter we need to pass to the Oracle procedure is the one corresponding to p_user_name and no parenthesis can be used. WF will read the REF CURSOR (accessible through SQLOUT) and will fetch the information from there.

Hopefully this will give you some extra ideas for what you need to achieve.

Regards,
Neftali.

This message has been edited. Last edited by: njsden,

Perfect! If someday you come to my country, you have all the free beer!

I look forward to it! ... er ... out of curiosity, which country is that? I need to know so I can go and get my free beer

Best,
Neftali.

This message has been edited. Last edited by: njsden,

This is a very useful post, I like it.
Thank you !