Regarding, the cross join. you may get the result you want, but it will be
doing needless joining of records. You should look up and understand what a
cross join does, if you don't already know. If each of your tables has
1,000 records, then internally you are creating 1,000 X 1,000 rows/records.
1,000,000 is a lot of rows. There is no need to join those two tables, so
you should probably break it into two independent SELECT statements.
As for the dynamic SQL part (the cursor), can you show me what you did?
Thanks,
Mike
On Tue, Apr 24, 2012 at 11:47 AM, xX_VP_Xx <dustinjwoodr...@gmail.com>wrote:
> Hi Mike,
>
> Thanks for your reply. The cross join works fine but I am not quite sure
> how to implement the first part you suggested. When I try to use what you
> posted I get an error stating that mystr is not a cursor. I seem to be
> having an immense amount of trouble understanding this. Once again thanks
> for the help.
>
> DECLARE
> mystr VARCHAR2 (2000) := 'SELECT * FROM ' || yourtableparameter;
> BEGIN
> FOR cur1 IN mystr
> LOOP
> NULL;
> END LOOP;
> END;
>
>
>
> 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
