I am still not quite sure how I managed to get this to work. I started
learning as I wrote this. I will definitely change the cross join to
2 separate selects as I just need the highest id number. This is what I did
for the cursor, and looking at it I can see that I totally removed my
cursor (import_check) and am lost as to how to implement your suggestion
into the cursor.
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.
mystr VARCHAR2 (2000) := 'SELECT i.rowid, i.* FROM ' || table_name 'i';
-- CURSOR import_check
-- IS
--SELECT i.ROWID, i.* FROM [supply when run] i;
BEGIN
FOR rw IN mystr
LOOP
On Tuesday, April 24, 2012 11:55:05 AM UTC-4, xX_VP_Xx wrote:
>
> 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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
