How to use bind variables in TOAD for Oracle

Toad for Oracle
1

I am a SAS Developer, I am teaching myself and doing project using TOAD for Oracle.

The bind variable is derived or created from a dataset as follow:
proc sql noprint;
select max(datepart(pay_period)) into :max_date /* Bind variable**/
from gabes.payroll;
quit; /* in this case :max_date='07OCT2023' */

how do I change the following SAS program into TOAD for Oracle?

data TG_Outputdata (drop= j k);
set TG_inputdata; /* data to be read*/
array varlist {18} prodwday rvsrs vsrs rvsrplusvsr othrs othrsperwkday
othrsperwkrperwkday rbprod totwkhrsperwkday
othrsperwkrperwkdayasp rvsr_othrsperwkrperwkdayasp
vsr_othrsperwkrperwkdayasp fdcpct x0006 rfdcount rfdwday
autorfdwday othrsaspen;

array varlist2 {3} vsravlhrsperwkday rvsravlhrsperwkday xvsravlhrsperwkday;

if week_end_date > :Max_Date then /* The place holder or bind var created 
                                                                         above*/
	do;
		do j = 1 to 18;
			varlist{j} = .;
		end;

		do k = 1 to 3;
			varlist2{k} = 0;
		end;
		dampfactor = 1;
	end;

run;
Thank you in Advance.

2

Sorry, but your question is not really about Toad the product, but about how to convert SAS code to Oracle SQL or PL/SQL. Non-Toad questions are typically outside the scope of this forum.

However, we always try to be helpful where we can. I'm personally not a SAS expert, and code conversions from one grammar to another can be tricky. Ideally, would be nice to find an experienced Oracle developer who also knows SAS.

That said, have you tried taking your question to one of the generative AI engines? I asked one of the many available genAI engines about how to convert your SAS code to Oracle (PL/SQL) and here is the output...

Will leave you to confirm/test the code for correctness and accuracy. :slight_smile:

DECLARE
  -- Declare a cursor for reading data from TG_inputdata
  CURSOR c_data IS
    SELECT *
    FROM TG_inputdata; -- Adjust this to match your table structure

  -- Declare PL/SQL records to represent the data and variable arrays
  TYPE varlist_type IS RECORD (
    prodwday NUMBER,
    rvsrs NUMBER,
    vsrs NUMBER,
    rvsrplusvsr NUMBER,
    othrs NUMBER,
    othrsperwkday NUMBER,
    othrsperwkrperwkday NUMBER,
    rbprod NUMBER,
    totwkhrsperwkday NUMBER,
    othrsperwkrperwkdayasp NUMBER,
    rvsr_othrsperwkrperwkdayasp NUMBER,
    vsr_othrsperwkrperwkdayasp NUMBER,
    fdcpct NUMBER,
    x0006 NUMBER,
    rfdcount NUMBER,
    rfdwday NUMBER,
    autorfdwday NUMBER,
    othrsaspen NUMBER
  );

  TYPE varlist2_type IS RECORD (
    vsravlhrsperwkday NUMBER,
    rvsravlhrsperwkday NUMBER,
    xvsravlhrsperwkday NUMBER
  );

  -- Declare PL/SQL variables
  data_record c_data%ROWTYPE;
  varlist varlist_type;
  varlist2 varlist2_type;
  dampfactor NUMBER;

  Max_Date DATE := :Max_Date; -- Replace :Max_Date with the actual value or parameter

BEGIN
  OPEN c_data;
  LOOP
    FETCH c_data INTO data_record;
    EXIT WHEN c_data%NOTFOUND;

    -- Check the condition and update the variables accordingly
    IF data_record.week_end_date > Max_Date THEN
      -- Initialize variables in varlist (j = 1 to 18) to NULL
      FOR j IN 1..18 LOOP
        varlist.prodwday := NULL;
        varlist.rvsrs := NULL;
        varlist.vsrs := NULL;
        varlist.rvsrplusvsr := NULL;
        varlist.othrs := NULL;
        varlist.othrsperwkday := NULL;
        varlist.othrsperwkrperwkday := NULL;
        varlist.rbprod := NULL;
        varlist.totwkhrsperwkday := NULL;
        varlist.othrsperwkrperwkdayasp := NULL;
        varlist.rvsr_othrsperwkrperwkdayasp := NULL;
        varlist.vsr_othrsperwkrperwkdayasp := NULL;
        varlist.fdcpct := NULL;
        varlist.x0006 := NULL;
        varlist.rfdcount := NULL;
        varlist.rfdwday := NULL;
        varlist.autorfdwday := NULL;
        varlist.othrsaspen := NULL;
      END LOOP;

      -- Initialize variables in varlist2 (k = 1 to 3) to 0
      FOR k IN 1..3 LOOP
        varlist2.vsravlhrsperwkday := 0;
        varlist2.rvsravlhrsperwkday := 0;
        varlist2.xvsravlhrsperwkday := 0;
      END LOOP;

      dampfactor := 1;
    END IF;

    -- Perform any additional logic or processing as needed
  END LOOP;
  CLOSE c_data;
END;
/
3

Hello Jerep,

This is beautiful, On my previous program SAS dataset, there was a variable called "TYPE". John suggested to use an other variable because "TYPE" is reserved key word in ORACLE. Now i know.

This is a good start for me. About "Generative AI Engines", Do you have a link for this group? I think they will be a good resource for me.

Regards,
Gabe

4

Can't point you to specific sites, for legal and other reasons, but you can easily browse the net for "Generative AI Engines", and you'll get lots of links to explore!