Stored Procedure Help

[xX_VP_Xx]

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; 

[Michael Moore]

Yes, for this you would use Dynamic SQL like 

DECLARE

   mystr   VARCHAR2 (2000) := 'SELECT * FROM ' || yourtableparameter;

BEGIN

   FOR cur1 IN mystr

   LOOP

      NULL;

   END LOOP;

END;

Also I noticed you are doing a cross join here:

  /* 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 ;

You probably don't want to do that. I highly recommend that you use SQL92 syntax for doing all table joins. 

The equivalent would look like:

  /* 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 CROSS JOIN  specialty s ;

 Regards,

Mike

  

--
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...@googlegroups.com
To unsubscribe from this group, send email to
Oracle-PLSQL...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en

[xX_VP_Xx]

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;

[Michael Moore]

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

--

[xX_VP_Xx]

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:

[Michael Moore]

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

--

[Michael Moore]

opps, make that:

 mystr   VARCHAR2 (2000) := 'SELECT i.rowid,  i.* FROM ' || table_name ' i';

left out a space before the final i

[xX_VP_Xx]

Hi Mike, Thanks for the compliment. I highlighted what I have done, along with some questions beside the highlighted code. I think I am supposed to be using mystr in my cursor declaration, but I cannot figure out how. Again I appreciate all the help and your time.

create or replace

PROCEDURE PHYS_IMPORT_test(

    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';   -- Declared mystr here

  /* Loop through to check each physician to be imported from the table_name table. */

  --CURSOR import_check

  --IS

   -- SELECT 'i.ROWID, i.* FROM '|| table_name || ' i'; Do i leave this commented out?  Should I be using mystr in place of the select here?

BEGIN

 

  /* Get current highest Title_ID and Specialty_ID*/

SELECT MAX(id)

   INTO max_title_id

   FROM physician_title;

    

SELECT MAX(id)

  INTO  max_specialty_id

   FROM specialty ;

    

    

    

  FOR rw IN mystr   -- This gives me the error that mystr is not a cursor (PLS00456 item 'MYSTR' is not a cursor)

  LOOP

On Tuesday, April 24, 2012 11:55:05 AM UTC-4, xX_VP_Xx wrote:

On Tuesday, April 24, 2012 11:55:05 AM UTC-4, xX_VP_Xx wrote:

[Michael Moore]

sorry, I completely sent you down the wrong path. That's what I get for trying to do too much at the same time. 

I'm under a lot of pressure at the moment so I will try to get back to this later. Basically, ignore everything I said.

Also, you could post your question on ORACLE-L and probably get an immediate answer. 

Mike

--

[Michael Moore]

I do have time to tell you this. What you are trying to do is pretty advanced. PLSQL "wants" to be able to tell you if you are referencing columns that actually exist on the table, However, if it does not 'know' the table until run-time, it has no way to tell if your column references actually make any sense. 

For example you are trying to reference rw.first. But what if your table does not have a column in it named 'first'?

What you are trying to do CAN be done, but it requires the use of the dbms_sql package. 

see here http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm#autoId7

If you know all of the possible values for TABLENAME would suggest you avoid dynamic sql entirely and do something like

IF tablename = 'tab1' then

   select * from tab1 ...;

elsif tablename = 'tab2' then

    select * from tab2 ...;

else

    dbms_output.put_line('I dont know about this table');

end if;

[Andrej Hopko]

Hi, you two made me interested in this problem ( I often did whole PL/SQL block in execute immediate, only for dynamic query to loop)
    and I looked into it

so here is sample solution from which you should be able to extract the idea for your problem

SET SERVEROUTPUT ON
DECLARE
    v_rc        SYS_REFCURSOR;
    iter        user_tables%ROWTYPE;
    dynSQL      VARCHAR2(500) := 'SELECT * FROM user_tables'; -- your dynamic query
BEGIN
    OPEN v_rc FOR dynSQL;
   
    LOOP
        FETCH v_rc INTO iter;
       
        EXIT WHEN v_rc%NOTFOUND;
       
        IF iter.num_rows >= 1000000 THEN -- tables with more than 1 mil. of rows = some condition
            DBMS_OUTPUT.PUT_LINE('1. cond. match = Name:' || iter.table_name || ' |Tablespace: ' || iter.tablespace_name );
        END IF;
        IF iter.last_analyzed >= SYSDATE - 50 THEN -- tables analyzed in last 50 days = another condition
            DBMS_OUTPUT.PUT_LINE('2. cond. match = Name:' || iter.table_name || ' |Tablespace: ' || iter.tablespace_name );
        END IF;
    END LOOP;
END;
/
SET SERVEROUTPUT OFF

send reply, if this did help you or you still have some issues

regards

    hoppo

--

[Michael Moore]

Hi Hoppo,

What if your table does not have a column named 'last_analyzed'? Or maybe the OP knows in advance that all possible tables will have the same columns.

Mike

[Andrej Hopko]

Hi Mike,
    by this block of original code:

                 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 (rw.co_signer AS      NUMBER(1,0)),
                 CAST (rw.note AS           VARCHAR2(512))

    I can tell one thing for sure - OP knows for sure what columns his query will return (and what type they will be)

    I use in my example this declaration:      iter        user_tables%ROWTYPE;
        which as you know (judging by your reaction) can be replaced with OBJECT TYPE variable ( @OP: e.g.  http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/objects.htm#CIHGJHHA )

    if you are supplied name of columns along with query (so they are dynamical as well as query), then you have two ways to deal with this (whereas PL/SQL is imperative language):

    1. create whole BEGIN END block by metaprogramming (if you have no other option)

    2. redesign the stuff while you can, because something stinks with this approach
        a) edit queries to return equivalently named columns, because you are doing same one process with results in LOOP (even if you have different names for columns, you know which contains what, so you can proxy query for one specific rowtype)
        b) split the procedure, because there is done different processing of queries (so different problems are solved in one procedure) - you overgeneralized the solution to the problem

    I think this should render your objection inadequate =)

        hoppo

[Dustin Woodruff]

Yes, the columns will always have the same. I am really busy today so I may not get a chance to test any of this out, but I really appreciate the help from both of you.

[Michael Moore]

All good points hoppo. If I was in Dustin's place, I'd push hard against using dynamic sql.  I might do something like ..

DECLARE

   my_table   VARCHAR2 (80) := 'dual3';

BEGIN

   FOR cur1 IN (SELECT 'dual1' as selected_table FROM dual WHERE 'dual1' = my_table

     UNION ALL  SELECT 'dual2' as selected_table FROM dual WHERE 'dual2' = my_table

     UNION ALL  SELECT 'dual3' as selected_table FROM dual WHERE 'dual3' = my_table)

   LOOP

      dbms_output.put_line('this value was selected from '||cur1.selected_table);

   END LOOP;

END;

[Rajesh]

Hi sir,

 

           I checked all posts what you are posted in Google PL/SQL Group's , i am very requesting you to could you please help me on Oracle Cursor's , procedure and Packages with queries please 

Thank you

Siva

sivasu...@gmail.com 

India

[Michael Moore]

Do you have a specific question?

--

--
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...@googlegroups.com
To unsubscribe from this group, send email to
Oracle-PLSQL...@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 unsubscribe from this group and stop receiving emails from it, send an email to oracle-plsql...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.