How to pass table name as an input in dynamic SQL?

Question

I'm trying to create a dynamic SQL procedure to get the first name given an ID.

CREATE OR REPLACE PROCEDURE SELECT_12 
(
  MIN_NBR NUMBER  
, BORR_FST_NM VARCHAR2  
, FIELD_NAME VARCHAR2
) IS 
TYPE cur_type IS REF CURSOR;
C_1 CUR_TYPE;
QUERY_STR VARCHAR2(1000);
FIRST_NAME VARCHAR(1000);
BEGIN
QUERY_STR:= 'SELECT BORR_FST_NM from MON_DD_DDS.' || field_name ||'
 WHERE   MIN_NBR = :MINNBR';
   OPEN C_1 FOR QUERY_STR USING MIN_NBR;
LOOP
 FETCH C_1 INTO FIRST_NAME;
 EXIT WHEN C_1%NOTFOUND;
 DBMS_OUTPUT.PUT_LINE(FIRST_NAME);
END LOOP;
NULL;
END SELECT_12;

I have different databases with the type MON_DD_DMS.STAGE_MRE_() where the bracket contains may different names like student, professor, scholar etc. I would like the user to define the name of the table like MON_DD_DMS.STAGE_MRE_STUDENT and MON_DD_DMS.STAGE_MRE_PROFESSOR etc.

When I try to run the procedure:

DECLARE
  MIN_NBR NUMBER;
  BORR_FST_NM VARCHAR2(200);
  FIELD_NAME VARCHAR2(200);
BEGIN
  FIELD_NAME := &FIELD_NAME;
  MIN_NBR := &MIN_NBR;
  BORR_FST_NM := NULL;

  SELECT_12( MIN_NBR => MIN_NBR, BORR_FST_NM => BORR_FST_NM,
    FIELD_NAME => FIELD_NAME );
END;

it says that STAGE_MRE_student must be declared.

So is there something wrong I'm doing? Any help would be appreciated.

DECLARE MIN_NBR NUMBER; BORR_FST_NM VARCHAR2(200); FIELD_NAME VARCHAR2(200); BEGIN FIELD_NAME := &FIELD_NAME; MIN_NBR := &MIN_NBR; BORR_FST_NM := NULL; SELECT_12( MIN_NBR => MIN_NBR, BORR_FST_NM => BORR_FST_NM, FIELD_NAME => FIELD_NAME ); END; — abominable_bride
– abominable_bride, Commented Jun 20, 2016 at 17:00
That's the procedure I'm running and I get the following error: "Identifier student must be declared" — abominable_bride
– abominable_bride, Commented Jun 20, 2016 at 17:01
Please edit the question to add extra information, particularly code, which needs to be formatted. — Alex Poole
– Alex Poole, Commented Jun 20, 2016 at 17:16

Alex Poole · Accepted Answer · 2016-06-20 17:21:27Z

1

The error seems to be coming from your call, not the procedure. You're doing:

FIELD_NAME := &FIELD_NAME;

but you're assigning a string, so you need to have single quotes around the substitution variable:

FIELD_NAME := '&FIELD_NAME';

Unless you intend to add an OUT parameter you don't really need the local variables; you can do:

BEGIN
  SELECT_12( MIN_NBR => &MIN_NBR, BORR_FST_NM => NULL,
    FIELD_NAME => '&FIELD_NAME' );
END;

(and if you can get it on one line, or use a continuation character, you could use execute in SQL*Plus or SQL Developer as a shorthand for that anonymous block)

edited Jun 20, 2016 at 17:21

answered Jun 20, 2016 at 17:15

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

abominable_bride Over a year ago

Thanks. I've implemented both the suggestions and it is working now.

Collectives™ on Stack Overflow

How to pass table name as an input in dynamic SQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related