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 <dustinjwoodr...@gmail.com>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 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
>
--
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
