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     [CLOSED] WF-ORACLE: procedure in package

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] WF-ORACLE: procedure in package
 Login/Join
 
Member
posted
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.

Frowner

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

Thats! all right!! Good One

What can I do to make it right?


Thanks in advance!

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


7.6.8, Linux
HTML,Excel,PDF,...
 
Posts: 13 | Registered: January 07, 2010Report This Post
<JG>
posted
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
 
Report This Post
Virtuoso
posted Hide Post
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 Frowner 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,



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Member
posted Hide Post
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...


7.6.8, Linux
HTML,Excel,PDF,...
 
Posts: 13 | Registered: January 07, 2010Report This Post
Virtuoso
posted Hide Post
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,



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Member
posted Hide Post
Perfect! If someday you come to my country, you have all the free beer! Smiler


7.6.8, Linux
HTML,Excel,PDF,...
 
Posts: 13 | Registered: January 07, 2010Report This Post
Virtuoso
posted Hide Post
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 Wink

Best,
Neftali.

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



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Member
posted Hide Post
This is a very useful post, I like it.
Thank you !


7.7.0.3 - WebFocus
Unix, Oracle
 
Posts: 1 | Location: Toronto | Registered: May 15, 2015Report 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     [CLOSED] WF-ORACLE: procedure in package

Copyright © 1996-2020 Information Builders