1. Anthony Mancuso
  2. PowerBuilder
  3. Monday, 20 May 2019

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

Anthony Mancuso Accepted Answer Pending Moderation
0
Votes
Undo

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.
  1. Wednesday, 22 May 2019
  2. PowerBuilder
  3. # 1
DIPANJAN DUTTA Accepted Answer Pending Moderation
0
Votes
Undo

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.
  1. Wednesday, 22 May 2019
  2. PowerBuilder
  3. # 2
DIPANJAN DUTTA Accepted Answer Pending Moderation
0
Votes
Undo

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
I did think about doing it this way but i should not have to. Powerbuilder should know better.
  1. Anthony Mancuso
  2. Tuesday, 21 May 2019
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. DIPANJAN DUTTA
  2. Tuesday, 21 May 2019
There are no comments made yet.
  1. Tuesday, 21 May 2019
  2. PowerBuilder
  3. # 3
DIPANJAN DUTTA Accepted Answer Pending Moderation
0
Votes
Undo

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
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. Anthony Mancuso
  2. Tuesday, 21 May 2019
There are no comments made yet.
  1. Tuesday, 21 May 2019
  2. PowerBuilder
  3. # 4
mike S Accepted Answer Pending Moderation
0
Votes
Undo

show us how you have the parameters in the datawindow defined

Comment
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. Anthony Mancuso
  2. Tuesday, 21 May 2019
There are no comments made yet.
  1. Monday, 20 May 2019
  2. PowerBuilder
  3. # 5
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.