Hello, I am an absolute beginner to PLSQL and need some assistance.
I would like to be able to supply a table name when I run my stored
procedure. From what I gather I have to use dynamic SQl to do this. This
stored procedure works as intended when I omit the table_name parameter. I
am also looking for another set of eyes to see if I can improve this at
all. I appreciate any help anyone has to offer as I am still learning.
*This is what I would like to use to run it:*
SET SERVEROUTPUT ON
BEGIN
PHYS_IMPORT(99999,[Table_name]);
END;
*This is the stored procedure:*
create or replace
PROCEDURE PHYS_IMPORT(
VAR_FACILITY IN NUMBER, TABLE_NAME IN VARCHAR2)
AS
var_rows NUMBER :=0;
var_fac_id NUMBER := var_facility;
--Facility ID we are working with.
var_code NUMBER (10);
--Code to be inserted eg:4011104 (facility code + dict_id).
var_password VARCHAR2 (40):=
'84346135c711cc270809193d47c522030e39963'; --unsalted SHA1HASH
var_dept VARCHAR2 (32);
--Dept from import table.
var_note VARCHAR2 (255);
--Note from import table.
var_esig VARCHAR2 (255) := 0;
--Esig from import table.
var_title_id VARCHAR2 (255);
--ID from physcician_titles table.
var_specialty_id VARCHAR2 (255);
--ID from specialty table.
max_title_id physician_title.id%TYPE;
--Current highest ID in physcician_titles table.
max_specialty_id specialty.id%TYPE;
--Current highest ID in specialty table.
var_num_errors NUMBER (5) := 0;
--Number of errors found in import table.
var_inc_titles NUMBER (5) := 0;
--Used to increment ID of Titles
var_inc_spec NUMBER := 0;
--Used to increment ID of Specialties.
var_user_initials VARCHAR(5);
--User initials (To be inserted into ALTEMPUSER table).
var_uname VARCHAR2(255);
--Username (To be inserted into ALTEMPUSER table).
var_first_initial VARCHAR2(255);
--First name initial.
var_middle_initial VARCHAR2(255);
--Middle name initial.
var_last_initial VARCHAR (1);
--Last name initial.
var_last_full VARCHAR2(255);
--Full last name.
/* Loop through to check each physician to be imported from the [supply
when run] table. */
CURSOR import_check
IS
SELECT i.ROWID, i.* FROM [supply when run] i;
BEGIN
/* Get current highest Title_ID and Specialty_ID*/
SELECT MAX(t.id),
MAX(s.id)
INTO max_title_id,
max_specialty_id
FROM physician_title t,
specialty s ;
FOR rw IN import_check
LOOP
IF Regexp_like(rw.first, '^[[:alnum:][:punct:] ]{1,32}$') THEN
NULL; --dbms_output.Put_line('FIRST: '|| rw.first|| '(OK)');
ELSE
var_num_errors := var_num_errors + 1; --used to count errors
UPDATE "APEX_DBO"."[supply when run]"
SET ERROR_MESSAGE = 'FIRST Must be alpha numeric and between 1-32
characters.'
WHERE rowid =rw.rowid;
CONTINUE;
/* Next loop iteration */
END IF;
IF Regexp_like(rw.last, '^[[:alnum:][:punct:] ]{1,32}$') THEN
NULL; --dbms_output.Put_line('LAST: '|| rw.last|| '(OK)');
ELSE
var_num_errors := var_num_errors + 1; --used to count errors
UPDATE "APEX_DBO"."[supply when run]"
SET ERROR_MESSAGE = 'Last Must be alpha numeric and between 1-32
characters.'
WHERE rowid =rw.rowid;
CONTINUE;
END IF;
IF rw.middle IS NULL OR Regexp_like(rw.middle, '^[[:alpha:]]{0,1}$')
THEN
NULL; --dbms_output.Put_line('MIDDLE: '|| rw.middle|| '(OK)');
ELSE
var_num_errors := var_num_errors + 1; --used to count errors
UPDATE "APEX_DBO"."[supply when run]"
SET ERROR_MESSAGE = 'MIDDLE Must be a single alpha charachter.'
WHERE rowid =rw.rowid;
CONTINUE;
END IF;
IF rw.dict_id IS NULL OR Regexp_like(rw.dict_id, '^\d{1,16}$') THEN
NULL; -- dbms_output.Put_line('DICT_ID: '|| rw.dict_id|| '(OK)');
ELSE
var_num_errors := var_num_errors + 1; --used to count errors
UPDATE "APEX_DBO"."[supply when run]"
SET ERROR_MESSAGE = 'DICT_ID Can only contain Digits from 0-9. With a
maximum of 16 digits.'
WHERE rowid =rw.rowid;
CONTINUE;
END IF;
IF Regexp_like(rw.ext_code, '^[[:alnum:][:punct:] ]{0,16}$') THEN
NULL; --dbms_output.Put_line('EXT_CODE: '|| rw.ext_code|| '(OK)');
ELSE
var_num_errors := var_num_errors + 1; --used to count errors
UPDATE "APEX_DBO"."[supply when run]"
SET ERROR_MESSAGE = 'EXT_CODE Must be alpha numeric and between 1-16
characters.'
WHERE rowid =rw.rowid;
CONTINUE;
END IF;
IF var_note IS NULL OR Regexp_like(rw.note, '^.{0,255}$') THEN
NULL; --dbms_output.Put_line('NOTE:(OK)');
ELSE
var_num_errors := var_num_errors + 1; --used to count errors
UPDATE "APEX_DBO"."[supply when run]"
SET ERROR_MESSAGE = 'NOTE can only be max 255 characters.'
WHERE rowid =rw.rowid;
CONTINUE;
END IF;
IF rw.co_signer IS NULL OR Regexp_like(rw.co_signer, '^[10]{1}$') THEN
rw.co_signer :=0;
NULL; --dbms_output.Put_line('CO_SIGNER: '|| rw.co_signer|| '(OK)');
ELSE
var_num_errors := var_num_errors + 1; --used to count errors
UPDATE "APEX_DBO"."[supply when run]"
SET ERROR_MESSAGE = 'CO_SIGNER Can only contain 1 or 0.'
WHERE rowid =rw.rowid;
CONTINUE;
END IF;
IF rw.title IS NULL OR Regexp_like(rw.title, '^.{1,32}$') THEN
NULL;
ELSE
var_num_errors := var_num_errors + 1; --used to count errors
UPDATE "APEX_DBO"."[supply when run]"
SET ERROR_MESSAGE = 'TITLE Can only contain max 32 chars.'
WHERE rowid =rw.rowid;
CONTINUE;
END IF;
IF var_dept IS NULL OR Regexp_like(rw.dept, '^[ [:alpha:]]{1,32}$') THEN
NULL;
ELSE
var_num_errors := var_num_errors + 1; --used to count errors
UPDATE "APEX_DBO"."[supply when run]"
SET ERROR_MESSAGE = 'DEPT Must be alpha numeric and between 1-32
characters.'
WHERE rowid =rw.rowid;
CONTINUE;
END IF;
var_first_initial := Regexp_replace (rw.first, '[^[:alpha:]]');
var_middle_initial := Regexp_replace (rw.middle, '[^[:alpha:]]');
var_last_full := Regexp_replace (rw.last, '[^[[:alpha:]]', '');
var_first_initial := SUBSTR(Lower(var_first_initial), 1, 1);
var_middle_initial := SUBSTR(Lower(var_middle_initial), 1, 1);
var_last_full := SUBSTR(Lower(var_last_full),1,10);
var_last_initial := SUBSTR(Lower(var_last_full), 1, 1);
var_uname := var_first_initial ||var_last_full;
var_user_initials := var_first_initial ||var_middle_initial
||var_last_initial;
var_user_initials := Upper(var_user_initials);
BEGIN
IF Regexp_replace (rw.title, '[^[:alnum:]]') IS NULL THEN
rw.title :=' ';
END IF;
SELECT id
INTO var_title_id
FROM physician_title
WHERE NAME = rw.title;
EXCEPTION
WHEN no_data_found THEN
var_inc_titles := var_inc_titles + 1; --used to count titles for
summary
dbms_output.Put_line('New title: ' || rw.title);
max_title_id := max_title_id + 1; --Add 1 to highest title ID
dbms_output.Put_line('Inserting new title: ID: ' || max_title_id ||'
Title: ' ||rw.title);
dbms_output.Put_line('INSERT INTO physician_title (ID,NAME) VALUES ('
||max_title_id ||',' ||' ' || rw.title || ');');
var_title_id := max_title_id;
INSERT
INTO physician_title
(
ID,
NAME
)
VALUES
(
CAST (max_title_id AS NUMBER(10,0)),
CAST(rw.title AS VARCHAR2(32))
); --Insert new titles
END;
BEGIN
IF Regexp_replace (rw.dept, '[^[:alnum:]]') IS NULL THEN
rw.dept :='Unknown';
END IF;
SELECT id INTO var_specialty_id FROM specialty WHERE NAME = rw.dept;
EXCEPTION
WHEN no_data_found THEN
var_inc_spec := var_inc_spec + 1; --used to count specialties for
summary
dbms_output.Put_line('New specialty found: ' || rw.dept);
max_specialty_id := max_specialty_id + 1; --Add 1 to highest
specialty ID.
dbms_output.Put_line('Inserting new specialty: ID:' ||
max_specialty_id ||' Title: ' ||rw.dept);
dbms_output.Put_line('INSERT INTO specialty (ID,NAME) VALUES ('
||max_specialty_id ||',' ||' ' || rw.dept || ');');
var_specialty_id := max_specialty_id;
INSERT
INTO specialty
(
ID,
NAME
)
VALUES
(
CAST (max_specialty_id AS NUMBER(10,0)),
CAST(rw.dept AS VARCHAR2(32))
); --Insert new specialties
END;
BEGIN
SELECT code
INTO var_code
FROM facility
WHERE facility_id = var_fac_id;
var_code := var_code ||rw.dict_id;
END;
var_rows := var_rows + 1;
INSERT
INTO phys_import_temp
(
user_name,
last_name,
first_name,
middle_initial,
dict_id,
ext_code,
code,
title_id,
esig,
password,
initials,
specialty,
facility_id,
co_signer,
note
)
VALUES
(
CAST (var_uname AS VARCHAR2(32)),
CAST (rw.last AS VARCHAR2(32)),
CAST (rw.first AS VARCHAR2(32)),
CAST (rw.middle AS VARCHAR2(1)),
CAST (rw.dict_id AS NUMBER(10,0)),
CAST (rw.ext_code AS VARCHAR2(16)),
CAST (var_code AS VARCHAR2(16)),
CAST (var_title_id AS NUMBER(10,0)),
CAST (var_esig AS NUMBER(1,0)),
CAST (var_password AS VARCHAR2(40)),
CAST (var_user_initials AS VARCHAR2(4)),
CAST (var_specialty_id AS NUMBER(10,0)),
CAST (var_fac_id AS NUMBER(10,0)),
CAST (rw.co_signer AS NUMBER(1,0)),
CAST (rw.note AS VARCHAR2(512))
);
COMMIT;
END LOOP;
dbms_output.Put_line('------------------------------');
dbms_output.Put_line('Operation Complete.');
dbms_output.Put_line('------------------------------');
dbms_output.Put_line('Entries Processed: '|| var_rows);
dbms_output.Put_line('There were ' || var_num_errors || ' errors.' );
dbms_output.Put_line('There were ' || var_inc_titles || ' New Titles.' );
dbms_output.Put_line('There were ' || var_inc_spec || ' New Specialties.'
);
dbms_output.Put_line('------------------------------');
END PHYS_IMPORT;
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
