How to use variable substitution in a procedure

[mrguru]

In the procedure below myItc will return a single value of a dblink which is stored in a table. I want to then use it to dynamically query a table using the link(as in the lines that are commented out). How do I do it through a procedure?


CREATE OR REPLACE PROCEDURE getWorkOrderDetail (i_dispatchId IN dispatch_interaction.confirmation_number%TYPE,
o_WorkOrderDetailCur OUT NOCOPY RefCursorType) IS
myItc VARCHAR2 := ‘@’ || getMyItcLink(i_corp);
BEGIN
OPEN o_workOrderDetailCur FOR
SELECT (hh.first_name||' '||hh.last_name) custName,
(stnum||' '||street_prefix||' '||stname||' '||street_suffix||':'||city_town) address,
itc_house house,
itc_cust cust,
.
.
.
FROM dispatch_interaction di,
.
.
-- wipmaster@itcplay.world wm,
-- wipordcomms@itcplay.world wc
wipmaster"&myItc" wm,
wipordcomms"&myItc" wc
WHERE
.
.
.
EXCEPTION
WHEN OTHERS THEN
RAISE;
END getWorkOrderDetail;

 

[carp]

I think the following would work:

CREATE OR REPLACE PROCEDURE getWorkOrderDetail (i_dispatchId IN dispatch_interaction.confirmation_number%TYPE,
                                 o_WorkOrderDetailCur OUT NOCOPY RefCursorType) IS
    myItc VARCHAR2 :=  ‘@’ || getMyItcLink(i_corp);
    l_cmd VARCHAR2(4000) := 'SELECT   
                       (hh.first_name||'' ''||hh.last_name)
                          custName,
                       (stnum||'' ''||
                        street_prefix||'' ''||
                        stname||'' ''||
                        street_suffix||'':''||
                        city_town) address,
                        itc_house house,
                        itc_cust cust,
                        .
                        .
                        .
                   FROM   dispatch_interaction di,
                        .
                        .
                        wipmaster'||myItc||' wm,
                        wipordcomms'||myItc||' wc
                   WHERE
                        .
                        .
                        .';

    BEGIN
          OPEN o_workOrderDetailCur FOR l_cmd
     EXCEPTION
     WHEN OTHERS THEN
       RAISE;
     END getWorkOrderDetail;

You may have to play around with the ticks to get this to work right, but this should be fairly close.

 

[mrguru]

When I compile it I get the following error

PLS - 00103: Encountered the Symbol "Exception" when expecting on of the following....

 

[SantaMufasa]

You are missing a ";" on the line prior to "Exception".

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[mrguru]

It will not compile successfully. I am using sql developer and there is a red squiggly line under "RefCursorType) IS" from the following:

CREATE OR REPLACE PROCEDURE zgetWorkOrderDetail (i_dispatchId IN dispatch_interaction.confirmation_number%TYPE,
o_WorkOrderDetailCur OUT NOCOPY RefCursorType) IS
myItc VARCHAR2 := '@' || getMyItcLink(i_corp);
l_cmd VARCHAR2 := 'SELECT (hh.first_name||'' ''||hh.last_name) custName,
(stnum||'' ''||street_prefix||'' ''||stname||'' ''||street_suffix||'':''||city_town) address,
itc_house house,
itc_cust cust,
itc_corp corp,
city_town city,
province state,
postal_code zipCode,
home_phone_nbr homePhone,
other_phone_nbr altPhone,
country,
(NVL(TO_CHAR(eta,''HH24MI''),''0000'')) eta,
(NVL(TO_CHAR(arrive_time,''HH24MI''), ''0000'')) arriveTime,
(NVL(TO_CHAR(departure_time,''HH24MI''), ''0000'')) departTime,
commt wipComment,
dseq jobSequence,
ad.mkis_location_key mkisKey,
di.household_id houseHoldId,
appointment_desc jobTime,
(itc_house || ''-'' || itc_cust) account,
purpose_desc jobDesc,
(NVL(itc_checkin,''N'')) jobStatus,
plant_type plantType,
plant_id_desc plantIdDesc,
(NVL(TO_CHAR(wbdate,''MM/DD/YYYY''), ''NO DATE'')) billDate,
wstat wipStatus,
DECODE (SIGN(TO_DATE(NVL(wm.wbdate,SYSDATE))-TO_DATE(SYSDATE)),
''-1'',''2'',
''0'',''1'',
''1'',''3'') billStatus
FROM dispatch_interaction di,
dispatch_appointment da,
dispatch_purpose dp,
household hh,
address ad,
plant_id_lookup pl,
wipmaster'|| myItc ||' wm,
wipordcomms'|| myItc ||' wc
WHERE di.confirmation_number = i_dispatchId
AND di.household_id = hh.household_id
AND hh.mkis_location_key = ad.mkis_location_key
AND da.appointment_id = di.appointment_id
AND dp.purpose_id = di.purpose_id
AND pl.plant_id = ad.plant_id
AND wm.house = hh.itc_house
AND wm.cust = hh.itc_cust
AND wm.corp = hh.itc_corp
AND wm.wpcddate = di.interaction_date_time
AND wm.house = wc.house(+)
AND wm.cust = wc.cust(+)
AND wm.wpcnt = wc.wpcomcnt(+)
AND wm.corp = wc.corp(+)
ORDER BY wm.house, wm.cust, wm.wpcnt, wm.corp;';
BEGIN
OPEN o_workOrderDetailCur FOR l_cmd;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END zgetWorkOrderDetail;

 

[carp]

Have you defined a TYPE of refCursor yet?
If not, then you need to create it before you can use it:

CREATE TYPE refCursor IS REF CURSOR;

THEN try compiling.

If still in doubt, I would recommend the Oracle PL/SQL Users Guide and Reference.