• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • How to Source a Stored procedure with Date input parms in a create of a datawindow.

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

Resolved How to Source a Stored procedure with Date input parms in a create of a datawindow.

  1. How-to
0
Votes
Undo
  1. Anthony Mancuso
    Anthony Mancuso
  2. PowerBuilder
  3. Monday, 20 May 2019 13:42 PM UTC

I am trying to create a new data window by sourcing an Oracle Stored procedure. The stored procedure has compiled and been tested using Oracle SQL Developer. It runs fine. However when i try to create a data window it throws a Date Error before i can even see the created data window to enter retrieval arguments.

We are using Oracle 12c and PB 2017-2

create or replace
PROCEDURE effective_date_report(from_eff_date in date,
thru_eff_date in date,
v_cursor out sys_refcursor)
as

BEGIN

OPEN v_cursor FOR
select a.spa_nbr,
a.spa_type_cd,
a.spa_sub_type_cd,
a.spa_title,
a.spa_eff_date,
a.spa_qtr,
a.spa_year,
f.spa_status_cd as "spa_status" ,
d.spa_status_date,
f.spa_location_cd,
e.spa_cont_form,
c.spa_fed_dols as "spa_fi_gross_sfy_dols",
b.prem_contact_name as "spa_prog_contact_name",
b.pcvg_contact_name as "spa_cms_contact_name",
b.spau_contact_name as "spa_spau_contact_name"
from state_plan.spa_hdr a,
state_plan.spa_contact_leads b,
state_plan.spa_sum_fi_fed_dols c,
state_plan.spa_max_status_date d,
state_plan.spa_upl_cont e,
state_plan.max_status_data f
where a.spa_eff_date >= from_eff_date
and a.spa_eff_date <= thru_eff_date
and a.spa_nbr not in(select spa_nbr from state_plan.finalized_spas)
and a.spa_nbr not in(select spa_nbr from state_plan.spas_on_hold)
and a.spa_nbr = b.spa_nbr
and a.spa_nbr = c.spa_nbr
and a.spa_nbr = d.spa_nbr
and a.spa_nbr = e.spa_nbr
and a.spa_nbr = f.spa_nbr
and d.spa_status_date = f.spa_status_date;

RETURN;

EXCEPTION WHEN OTHERS THEN utils.handleerror(SQLCODE,SQLERRM);
END;

 

 

Can anyone give me a clue? Thanks

  1. DataWindow
  2. Oracle
  3. Stored Procedure
Select an opened sheet in Dockable Windows
PB Runtime Packager fails
Responses (5)
Anthony Mancuso
Anthony Mancuso Accepted Answer Pending Moderation
  1. Wednesday, 22 May 2019 11:10 AM UTC
  2. PowerBuilder
  3. # 1

I figured it out. On the Connection Profile, I had specified the Date Format, Date Time Format, Time format.  By removing what I specified, PB went to the Oracle Database and used its date format and my datawindow created without the wizard aborting. I tested it and it worked fine.

Thanks for all your help everyone.

Comment
There are no comments made yet.
DIPANJAN DUTTA
DIPANJAN DUTTA Accepted Answer Pending Moderation
  1. Wednesday, 22 May 2019 04:58 AM UTC
  2. PowerBuilder
  3. # 2

Check NLS Date format at your system

 

And pass the date from PB as per NLS_DATE_FORMAT....in my case it format is 'DD-MON-RR'....and I have passed 20-MAY-19..in date format and it work fine.

Comment
There are no comments made yet.
DIPANJAN DUTTA
DIPANJAN DUTTA Accepted Answer Pending Moderation
  1. Tuesday, 21 May 2019 06:30 AM UTC
  2. PowerBuilder
  3. # 3

I have tried this and its working fine for me

  1. Change Procedure input parameter from Date to varchar2
  2. Incorporate TO_DATE function in where clause to convert varchar2 to date
  3. I am using “dd/mm/yyyy” format, you can change it as per your requirement

Here is the sample procedure

Create Or Replace Procedure sp_bookissuelist(as_issue_date varchar2, refcur_booklist OUT sys_refcursor)

 IS

 Begin 

    OPEN refcur_booklist For

        Select    

                S.SUBJECT_NAME subject_name, 

                B.TITLE book_title,

                A.FIRST_NAME || ' ' || A.LAST_NAME author_name 

        From

                AUTHORS A,

                BOOKS B,

                SUBJECT S,

                BookIssue I

        Where       

                A.ID = B.AUTHOR_ID

        And         

                B.SUBJECT_ID = S.SUBJECT_ID

        And

                I.BOOK_ID =   B.ID

        And    

                ISSUE_DATE >= to_date(as_issue_date, 'dd/mm/yyyy');

END sp_bookissuelist;

 

Following is the sample code to Call it from PB

Date       ldt_BookIssueDate

ldt_BookIssueDate = Date('20/05/2019')

MessageBox("String", String(ldt_BookIssueDate))

ls_proc = "DataWindow.Table.Procedure=" +'"' + "1 execute sp_bookissuelist; " + &

                                                          "@ad_issue_date = " + "'" + String(ldt_BookIssueDate) + "'" + '"'

MessageBox("Modify OK", dw_bookdetails.Modify (ls_proc))

dw_bookdetails.SetTransObject( g_connect)

dw_bookdetails.Retrieve()

Comment
Anthony Mancuso
  1. Anthony Mancuso
  2. Tuesday, 21 May 2019 11:06 AM UTC
I did think about doing it this way but i should not have to. Powerbuilder should know better.
  1. Helpful
DIPANJAN DUTTA
  1. DIPANJAN DUTTA
  2. Tuesday, 21 May 2019 11:56 AM UTC
Just pass String type variable in retrial argument instead of date ........change DW retrieval argument type date / datetime to string........also change your proc. input argument type and where clause as per your date formatting.....It will work.
  1. Helpful
There are no comments made yet.
DIPANJAN DUTTA
DIPANJAN DUTTA Accepted Answer Pending Moderation
  1. Tuesday, 21 May 2019 05:08 AM UTC
  2. PowerBuilder
  3. # 4

Hi Anthony

I thing you are using Oracle, please first check your default data format using Syadate in DB.

Then Send the same format when you call it from PB

Comment
Anthony Mancuso
  1. Anthony Mancuso
  2. Tuesday, 21 May 2019 11:05 AM UTC
I did:

select sysdate from from dual

I got

5/21/2019 07:03:11



The data window wizard errors as shown above before i can specify any retrieval arguments.

  1. Helpful
There are no comments made yet.
mike S
mike S Accepted Answer Pending Moderation
  1. Monday, 20 May 2019 17:03 PM UTC
  2. PowerBuilder
  3. # 5

show us how you have the parameters in the datawindow defined

Comment
Anthony Mancuso
  1. Anthony Mancuso
  2. Tuesday, 21 May 2019 11:02 AM UTC
Hi Mike thanks for responding. I can't show you these because i don't get that far in the create data window process to even specify them. The datawindow wizard blows up with the error message before that.
  1. Helpful
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.