Home » SQL & PL/SQL » SQL & PL/SQL » Passing parameter from Crystal to Oracle (Oracle 9i)
Passing parameter from Crystal to Oracle [message #343621] Wed, 27 August 2008 12:00 Go to next message
mwin
Messages: 7
Registered: August 2008
Junior Member
First post so I apologize for any errors.

First I am trying to recreate a view in oracle that was previously created in ms access and not by me.

The code: (slimmed down)
SELECT la.site_id,...
FROM (lease_actuals la ...
WHERE la.BAT_DATE=[Forms]![invoice_details_param]![batch_date_frm]
GROUP BY la.site_id, ...

"WHERE la.BAT_DATE=[Forms]![invoice_details_param]![batch_date_frm]" is a form created in access and the date that is passed from Crystal.

I am needing to pass this date from crystal into Oracle but from my understanding you cannot pass a parameter into a View, so you have to create it as a stored proc.

Stored Proc code:
CREATE OR REPLACE PROCEDURE LEASE_HEADER (BATCH_DATE IN DATE)
AS
la_site_id la.site_id%TYPE;
la_site_name la.site_name%TYPE;
la_payee_id la.payee_id%TYPE;
la_payee_name la.payee_name%TYPE;
la_address1 la.address1%TYPE;
la_address2 la.address2%TYPE;
la_city la.city%TYPE;
la_state la.state%TYPE;
la_country la.country%TYPE;
la_zip_code la.zip_code%TYPE;
la_lease_id la.lease_id%TYPE;
la_lease_name la.lease_name%TYPE;
la_TENANT_ID la.TENANT_ID%TYPE;
la_site la.site%TYPE;
pd_primarydivision pd.primarydivision%TYPE;
po_po_number po.po_number%TYPE;
la_BAT_DATE la.BAT_DATE%TYPE;

BEGIN
SELECT la.site_id,
la.site_name,
la.payee_id,
la.payee_name,
la.address1,
la.address2,
la.city,
la.state,
la.country,
la.zip_code,
la.lease_id,
la.lease_name,
la.TENANT_ID,
la.site,
pd.primarydivision,
CASE WHEN po.po_number is null THEN 'null'
ELSE po.po_number
END AS po_num,
la.BAT_DATE
Into la_site_id,
la_site_name,
la_payee_id,
la_payee_name,
la_address1,
la_address2,
la_city,
la_state,
la_country,
la_zip_code,
la_lease_id,
la_lease_name,
la_TENANT_ID,
la_site,
pd_primarydivision,
po_po_number,
la_BAT_DATE
FROM lease_actuals la LEFT JOIN primary_division pd
ON la.site = pd.site
LEFT JOIN purchase_order po
ON la.charge_seq = po.charge_sequence
AND la.lease_id = po.leasse_id
AND la.TENANT_ID = po.tenant_id
WHERE la.BAT_DATE= batch_date
GROUP BY la.site_id, la.site_name, la.payee_id,
la.payee_name, la.address1, la.address2,
la.city, la.state, la.country, la.zip_code,
la.lease_id, la.lease_name, la.TENANT_ID,
la.site, pd.primarydivision, la.BAT_DATE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END LEASE_HEADER;
/

I have created a stored proc and when compiling I receive an error PLS-00201: identifier 'LA.SITE_ID' must be declared and for each one below this. However, they appear in my declarations list.

If you have a better way to do this then creating a stored proc then please do tell, but if not a little help with this would be greatful.
Re: Passing parameter from Crystal to Oracle [message #343622 is a reply to message #343621] Wed, 27 August 2008 12:02 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Passing parameter from Crystal to Oracle [message #343630 is a reply to message #343621] Wed, 27 August 2008 12:31 Go to previous messageGo to next message
tyler_durden
Messages: 14
Registered: August 2008
Location: http://tinyurl.com/63fmwx
Junior Member
Quote:

I have created a stored proc and when compiling I receive an error PLS-00201: identifier 'LA.SITE_ID' must be declared and for each one below this. However, they appear in my declarations list.



You will need to specify the table name instead of the table alias in the declarations section.

tyler_durden

Re: Passing parameter from Crystal to Oracle [message #343633 is a reply to message #343630] Wed, 27 August 2008 12:37 Go to previous messageGo to next message
mwin
Messages: 7
Registered: August 2008
Junior Member
Ahhh. Thank you. That worked.
Re: Passing parameter from Crystal to Oracle [message #343634 is a reply to message #343621] Wed, 27 August 2008 12:42 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
CREATE OR REPLACE PROCEDURE LEASE_HEADER
     (BATCH_DATE  IN DATE)
AS
  LA_SITE_ID          LA.SITE_ID%TYPE;
  LA_SITE_NAME        LA.SITE_NAME%TYPE;
  LA_PAYEE_ID         LA.PAYEE_ID%TYPE;
  LA_PAYEE_NAME       LA.PAYEE_NAME%TYPE;
  LA_ADDRESS1         LA.ADDRESS1%TYPE;
  LA_ADDRESS2         LA.ADDRESS2%TYPE;
  LA_CITY             LA.CITY%TYPE;
  LA_STATE            LA.STATE%TYPE;
  LA_COUNTRY          LA.COUNTRY%TYPE;
  LA_ZIP_CODE         LA.ZIP_CODE%TYPE;
  LA_LEASE_ID         LA.LEASE_ID%TYPE;
  LA_LEASE_NAME       LA.LEASE_NAME%TYPE;
  LA_TENANT_ID        LA.TENANT_ID%TYPE;
  LA_SITE             LA.SITE%TYPE;
  PD_PRIMARYDIVISION  PD.PRIMARYDIVISION%TYPE;
  PO_PO_NUMBER        PO.PO_NUMBER%TYPE;
  LA_BAT_DATE         LA.BAT_DATE%TYPE;
BEGIN
  SELECT   LA.SITE_ID,
           LA.SITE_NAME,
           LA.PAYEE_ID,
           LA.PAYEE_NAME,
           LA.ADDRESS1,
           LA.ADDRESS2,
           LA.CITY,
           LA.STATE,
           LA.COUNTRY,
           LA.ZIP_CODE,
           LA.LEASE_ID,
           LA.LEASE_NAME,
           LA.TENANT_ID,
           LA.SITE,
           PD.PRIMARYDIVISION,
           CASE 
             WHEN PO.PO_NUMBER IS NULL  THEN 'null'
             ELSE PO.PO_NUMBER
           END AS PO_NUM,
           LA.BAT_DATE
  INTO     LA_SITE_ID,
           LA_SITE_NAME,
           LA_PAYEE_ID,
           LA_PAYEE_NAME,
           LA_ADDRESS1,
           LA_ADDRESS2,
           LA_CITY,
           LA_STATE,
           LA_COUNTRY,
           LA_ZIP_CODE,
           LA_LEASE_ID,
           LA_LEASE_NAME,
           LA_TENANT_ID,
           LA_SITE,
           PD_PRIMARYDIVISION,
           PO_PO_NUMBER,
           LA_BAT_DATE
  FROM     LEASE_ACTUALS LA
           LEFT JOIN PRIMARY_DIVISION PD
             ON LA.SITE = PD.SITE
           LEFT JOIN PURCHASE_ORDER PO
             ON LA.CHARGE_SEQ = PO.CHARGE_SEQUENCE
                AND LA.LEASE_ID = PO.LEASSE_ID
                AND LA.TENANT_ID = PO.TENANT_ID
  WHERE    LA.BAT_DATE = BATCH_DATE
  GROUP BY LA.SITE_ID,
           LA.SITE_NAME,
           LA.PAYEE_ID,
           LA.PAYEE_NAME,
           LA.ADDRESS1,
           LA.ADDRESS2,
           LA.CITY,
           LA.STATE,
           LA.COUNTRY,
           LA.ZIP_CODE,
           LA.LEASE_ID,
           LA.LEASE_NAME,
           LA.TENANT_ID,
           LA.SITE,
           PD.PRIMARYDIVISION,
           LA.BAT_DATE;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
END LEASE_HEADER;
/


which is easier to read & understand?

[Updated on: Wed, 27 August 2008 12:43] by Moderator

Report message to a moderator

Re: Passing parameter from Crystal to Oracle [message #343651 is a reply to message #343621] Wed, 27 August 2008 14:21 Go to previous messageGo to next message
mwin
Messages: 7
Registered: August 2008
Junior Member
OK. So after creating this stored proc, I am now trying to run it.

When I execute it, I need to pass a date parameter to it and it return the results.

I put: call lease_header ('18-APR-08') and it returns an error:

ORA-01422: exact fetch returns more than requested number of rows

Do I need to use a cursor here and store the result of the query, without the date range, in a separate table and then call for it from the cursor. If I do this how do I pass the date through the cursor?

[Updated on: Wed, 27 August 2008 14:37]

Report message to a moderator

Re: Passing parameter from Crystal to Oracle [message #343665 is a reply to message #343621] Wed, 27 August 2008 15:11 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

With Oracle characters between single quote marks are STRINGS!
'this is a string 2008-08-08 not a date'

When you desire a DATE datatype, use TO_DATE() function.

The INTO clause is valid when only a single row gets returned.

http://asktom.oracle.com contains many fine coding examples.
Re: Passing parameter from Crystal to Oracle [message #343669 is a reply to message #343621] Wed, 27 August 2008 15:35 Go to previous messageGo to next message
mwin
Messages: 7
Registered: August 2008
Junior Member
OK.
If you are going to keep pointing me to the guidelines then at least explain what guidelines I am not following!

If you want to belittle me and tell me where I can go to lookup queries and how to properly right queries, then send me to one that will help with the actual question I ask.

If you see that I am asking the question incorrectly then ask a question to explain what I mean and again not point out the obvious.

If you see I am trying to pass a date into a stored proc as a string then instead of saying what you did in bold letters, explain that what I am trying to do is wrong and at least offer an explanation of how I can pass a date into a stored proc. Again, answering the question I ask.

I originally posted the problem I am having, what I want to achieve, and questions if anyone knows a better way to do it. If you would go back and read what I posted about if there is a way to pass a date from crystal into oracle and if anyone knows how to do this other than a stored proc, instead of sending me a link to the guidelines, I could have already been done, but I am instead here having to rebuttal your impolite responses, which are clearly posted at the very top of the guidelines you keep sending me.

I know Oracle characters between single quote marks are STRINGS!
Re: Passing parameter from Crystal to Oracle [message #343672 is a reply to message #343669] Wed, 27 August 2008 16:01 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

I know Oracle characters between single quote marks are STRINGS!



Then why did you still use the incorrect way of handling a date?

But, don't worry, If you are not satisfied with the answers here you are entitled to a complete refund.

Also, the main point in the guidelines asks user to create some sort of reproducible test-case. Or at least post some sort of SQL session level copy and past of what actually happened.

Anacedent pointed out the THREE main problems that your procedure seems to have. And since you didn't give the necessary information (like table structures and what the actual resultset should be, AS MENTIONED IN THE GUIDELINES, your are your own.)

Re: Passing parameter from Crystal to Oracle [message #343676 is a reply to message #343672] Wed, 27 August 2008 16:25 Go to previous messageGo to next message
mwin
Messages: 7
Registered: August 2008
Junior Member
I have no problems with adhering to the guidelines, but I do not know which guidelines I broke.

My main question was how do I pass a parameter into oracle from crystal. I cannot give you a complete test on this.

The original question I posted stated that someone wrote a query in access with a call to a form and I needed to duplicate this in oracle, but did not know how to do so from crystal.
The queries I am creating are being created as views in oracle from what I know you cannot pass a parameter from crystal into a view.
My question was how can I do this?

It is not a matter of wanting a refund or anything like that. I came here because someone recommended this site and I needed help. The tone in the responses I received from Anacedent did not adhere to the guidelines themselves with the bolding and the links to asktom about how to write queries. Be polite was the first thing in the introduction of the guidelines, and with the tone and bolding and '!' marks that is not polite. It would be the same with email etiquette. I would make the changes to my postings if I would have only been told. I do still need help with my original question, and if you able to then please help. If you feel I am no longer going to get any help with this, then tell me now and I will seek help elsewhere, but would like to continue using this site for help. If you notice the posting from tyler_durden, he quoted my question or problem and then answered it. I did not get this from Anacedent and that is obvious in the postings. I do not normally post to websites and if I am doing this completely wrong then educate me, but if i do not know what I am doing wrong I cannot correct it.

[Updated on: Wed, 27 August 2008 16:26]

Report message to a moderator

Re: Passing parameter from Crystal to Oracle [message #343687 is a reply to message #343676] Wed, 27 August 2008 16:53 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
me
since you didn't give the necessary information (like table structures and what the actual resultset should be, AS MENTIONED IN THE GUIDELINES



mwin
... but I do not know which guidelines I broke.


I just told you. The way you wrote the procedure it looks like you expect ONE row.

The "ORA-01422: exact fetch returns more than requested number of rows" seems to indicate that there are multiple rows in the result set.

Which one of the two actually is it?

Re: Passing parameter from Crystal to Oracle [message #343689 is a reply to message #343687] Wed, 27 August 2008 17:18 Go to previous messageGo to next message
mwin
Messages: 7
Registered: August 2008
Junior Member
You did and I apologize. A little frustrated and for that I am sorry.

I actually have a few other different questions that might make this a moot point. So please bare with me.

Currently the report is in crystal report that is called from an asp page. On the asp page it has a prompt for a date. You enter the date and press run report and presto! From crystal it calls for the access query and in the access query it has a where clause with the call to a form. This is what I am needing to duplicate in oracle 9i. Can I create a similar form in oracle? I have seen this in some postings, but it all points to having oracle developer. I do not have oracle developer.

If I cannot do this then is the only way for me to duplicate, by creating a stored proc to have a parameter passed into a query that retrieves data based on the date passed in from Crystal? Am I going in the correct direction with all of this?

I can give you table structures, but really I do not know where to begin with which tables to give because the query calls 6 different other queries. The resultset should be that of the query in the stored proc below, but with the date that is passed from crystal.

I hope I have adhered to the guidelines with these questions and have you given you enough information of what I am trying to accomplish.
Re: Passing parameter from Crystal to Oracle [message #343695 is a reply to message #343689] Wed, 27 August 2008 17:51 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, still no definite answer if the is ONE row returned or MULTIPLE. So I'm going to assume it's multiple rows.

One possible way:

A view is just a stored SQL statement. Oracle doesn't have much performance loss when multiple views are "stacked".

So you could create a view without the where-clause which includes the batch-date, and then add the where clause when you query the view from crystal.


Another possible way:

Return a REF CURSOR from the procedure.

Using Cursor Variables (REF CURSORs)

Using REF CURSORS in Crystal Reports (PDF)

Re: Passing parameter from Crystal to Oracle [message #343698 is a reply to message #343695] Wed, 27 August 2008 18:08 Go to previous message
mwin
Messages: 7
Registered: August 2008
Junior Member
Ahh sorry. It is multiple rows.

I was looking at the cursor and thought that might work, but what I was looking at did not really explain how I would write it so the parameter would be passed. I will look at the links you gave and see if I can find anything with that specific piece, and will also consider the other approach. In the end the other approach with just writing the view and having the date be pushed from a where clause in crystal, may be the best solution here.

Thank you for you time and patience.
Previous Topic: ORA-14451 , creating index for a temporary table
Next Topic: using forall and bulk collect together
Goto Forum:
  


Current Time: Sat Dec 03 08:23:16 CST 2016

Total time taken to generate the page: 0.15764 seconds