Oracle - Crystal Variables

[David_OHS]

New to Oracle. Using Crytsal 10. Unable to create views or Stored Proc on database. Having to just use SQL code in Crystal. Need to know how to pass variables from Crystal to the SQL code that will run on Oracle DB. Also can I create a temp table and use in the SQL Code. Thank you for the help. David

[debasisdas]

What is Crytsal 10 ?

How are you connecting to Oracle DB ?

If you have privileges you can create any object in DB.

[David_OHS]

What is Crytsal 10 ?

How are you connecting to Oracle DB ?

If you have privileges you can create any object in DB.

I do not have privilege to create any object in the DB. It's hosted by a 3rd party so we just run our reports off their Oracle Data. Using Crystal Reports version 10 to create reports. We were using SQL Servers but switched to Oracle. Some of our SQL Code written for SQL server does not work with Oracle. Creating temp tables and passing variables is one of the parts that does not work now that we are using Oracle. David

[debasisdas]

You need to re design part of your code to make it work with oracle.

[David_OHS]

You need to re design part of your code to make it work with oracle.

What I don't know is the Oracle code to use Variable. In SQL Server I would Declare the variable then Set it, but how do I do the same thing for Oracle. Thanks for the help. David

[debasisdas]

You can also do the same by using & in oracle.

[David_OHS]

You can also do the same by using & in oracle.

Use the "&" in stead of the "@".

[David_OHS]

Use the "&" in stead of the "@".

Here is a sample of the code I am trying to get to work.

Declare
v_F1 number :=4;
Begin
select *
from Multi_Facility
where Facility_ID = v_F1;
End;

I just want Facility_ID = 4 records from the table. I know I have something typed in wrong.

Thanks for the help.
David

[debasisdas]

You need to use SELECT INTO in any named / anonymous block.

[David_OHS]

You need to use SELECT INTO in any named / anonymous block.

Not sure where to add in the "INTO" text. The following didn't work.

Declare
v_F1 number :=4;
Begin
select Into *
from Multi_Facility
where Facility_ID = v_F1;
End;

I just want to pass the ID to the where statment....

Thanks for the help.
David

[debasisdas]

try this sample code

Declare
v_F1 number :=4;
rec Multi_Facility%rowtype;
Begin
select * into rec
from Multi_Facility
where Facility_ID = v_F1;
End;

[David_OHS]

I ran the code and had no errors, but did not get any records returned. The system show it ran the code, but nothing erturned. David

[debasisdas]

how you handled the target variable containing the output of the query ?

[David_OHS]

Sorry, but I don't understand your question. I just want to pass in a variable that will return only one records from a table that the ID matches what the variable is. In this care the results one be one records for Facility ID = 4

[debasisdas]

The target variable (rec in my example) contains the actual output of the query . You need to process the same to get output into the front end tool.

[David_OHS]

I ran the code in Oracle SQL Developer (just as it written) and it ran but got no results.

[debasisdas]

if your query returns a sing re row try this following query

DECLARE
v_F1 NUMBER :=4;
rec Multi_Facility%ROWTYPE;
BEGIN
SELECT * INTO rec
FROM Multi_Facility
WHERE Facility_ID = v_F1;
dbms_output.put_line(rec.field1_name);
END

if the query returns multiple rows you will need a loop.