Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved Oracle Stored Procedure Retrieving Data

  1. How-to
0
Votes
Undo
  1. Mike Kolenda
    Mike Kolenda
  2. PowerBuilder
  3. Tuesday, 17 January 2023 22:09 PM UTC

I've been attempting to utilize an Oracle Stored Procedure in a DataWindow to retrieve data, however upon retrieval I receive the following error.

ORA-06550 - Identifier '{indentifier}' must be declared.

The issue can be created in the DW painter.

 

I'm using Oracle ODBC drivers (doesn't require the client) and I'm not sure if that is the cause at this point. 

I have no issues with MSSQL stored procedures. Just the ORA ones are not playing nice.

I've provided the DW (dtb_sp_simple_select_char) and PL/SQL (TEST_SP_ex_simple_select.txt) for the procedure.

Any guidance would be appreciated.

- Mike

Attachments (1)
test_objects.zip
  1. DataWindow
  2. Oracle
  3. Stored Procedure
Rich Text Control - Zoom?
Resize service behavior question
Responses (4)
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 19 January 2023 19:33 PM UTC
  2. PowerBuilder
  3. # 1

Hi,

It seems that your best shot is to go with the native driver (sqlca.dbms = ORA), that's what we use and it works great!

https://stackoverflow.com/questions/18518049/parameter-direction-output-sys-refcursor-in-odbc 

regards.

Comment
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 19 January 2023 19:24 PM UTC
  2. PowerBuilder
  3. # 2

Hi,

I've compared what we do (with Oracle client Native driver) and the only difference I see is that 

outData IN OUT SYS_REFCURSOR would be declared without the "IN" keyword:

outData OUT SYS_REFCURSOR.

Give it a try?

When I have a working connection to my work pc, I'll look at the dw with the parameters. There's a "0" in yours which I don't understand why it's there. (Maybe in our dw it's there too!).

regards.

 

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Thursday, 19 January 2023 19:43 PM UTC
I've checked on our own DW and indeed, that weird "0" is also there in the arguments:

1 execute CREDIT.SP_DW_TRANS_LIST3;0 AS_ITEM_CUSTOMER = :AS_ITEM_CUSTOMER, .......

So that's not the problem.

  1. Helpful
There are no comments made yet.
Mike Kolenda
Mike Kolenda Accepted Answer Pending Moderation
  1. Thursday, 19 January 2023 17:04 PM UTC
  2. PowerBuilder
  3. # 3

Thanks, Chris.

I will get that a try.  By any chance is there a good tutorial/example on Oracle DB Objects and DataWindows somewhere you can point me to? Most the ones I have found have been more general.

- Mike

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Thursday, 19 January 2023 21:49 PM UTC
Not in the PB documentation realm but there should be "tons" out there in the various Oracle forums. Even PB related material as PB has been interacting with Oracle DBMS versions since the early days of PowerSoft PB versions.
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 18 January 2023 18:23 PM UTC
  2. PowerBuilder
  3. # 4

Hi Mike;

  ODBC has challenges for BLOB data stream processing and must be configured exactly for this to work.

   Have you tried the native "ORA" DB Client driver instead?

Regards .. Chris

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.