opps, make that: mystr VARCHAR2 (2000) := 'SELECT i.rowid, i.* FROM ' || table_name ' i';
left out a space before the final i On Tue, Apr 24, 2012 at 1:03 PM, Michael Moore <[email protected]>wrote: > This: > > mystr VARCHAR2 (2000) := 'SELECT i.rowid, i.* FROM ' || table_name 'i'; > > should be > > mystr VARCHAR2 (2000) := 'SELECT i.rowid, i.* FROM ' || table_name || > 'i'; > > || is the string concatenation operator > > You may be new to PL/SQL, but clearly you have some programming > background. :-) > > Mike > > > On Tue, Apr 24, 2012 at 12:49 PM, xX_VP_Xx <[email protected]>wrote: > >> 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):= >> '**84346135c711cc270809193d47c522**030e39963'; >> --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):= >>> '**84346135c711cc270809193d47c522**030e39963'; >>> --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 >> > > -- 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
