Re: PL/SQL question

From: <G.Plivna_at_itsystems.lv>
Date: Fri, 16 Nov 2001 05:33:42 -0800
Message-ID: <F001.003C7383.20011116045518@fatcity.com>

To make such things you need dynamic SQL either execute immediate
(8.1.something and above) or dbms_sql (more clumsy)

Here is example using execute immediate

qaqa is table of one column col1, max (col1) = 17 qaqa_seq is sequence

gints@> create table qaqa (col1 number);

Table created.

gints@> insert into qaqa values (1);

1 row created.

gints@> insert into qaqa values (17);

1 row created.

gints@> create sequence qaqa_seq;

Sequence created.

gints@> create table matching_table (table_owner varchar2(40), table_name varchar2(40), table_column varchar2(40)
  2 , sequence_owner varchar2(40), sequence_name varchar2(40));

Table created.

gints@> insert into matching_table values ('GINTS', 'QAQA', 'COL1', 'GINTS', 'QAQA_SEQ'); 1 row created.

gints@> commit;

DECLARE

  v_table_owner varchar2(40);
  v_table_name  varchar2(40);
  v_table_column varchar2(40);
  v_select_statement VARCHAR2(1000);
  v_seq_statement VARCHAR2(1000);
  v_max_result number;
  v_seq_result number;

BEGIN
  FOR i IN (SELECT sequence_name, sequence_owner

            FROM dba_sequences
            WHERE sequence_name = 'QAQA_SEQ'
              AND sequence_owner = 'GINTS')

  LOOP
    BEGIN

      SELECT table_owner, table_name, table_column
      INTO v_table_owner, v_table_name, v_table_column
      FROM matching_table a
      WHERE i.sequence_owner = a.sequence_owner
        AND i.sequence_name = a.sequence_name ;

    EXCEPTION WHEN OTHERS
    THEN
      NULL;
    END;
    v_select_statement := 'SELECT max(' || v_table_column || ') ' ||

                   'FROM ' || v_table_owner || '.' || v_table_name;

    EXECUTE IMMEDIATE v_select_statement INTO v_max_result;     dbms_output.put_line('SELECT statement is: ' || v_select_statement);     dbms_output.put_line('MAX number of ' || v_table_owner || '.' || v_table_name || '.' || v_table_column || ' is ' || v_max_result);

    v_seq_statement := 'SELECT ' || i.sequence_owner || '.' || i.sequence_name || '.nextval FROM dual';

    EXECUTE IMMEDIATE v_seq_statement INTO v_seq_result;     dbms_output.put_line('Select sequence nextval stetement is: ' || v_seq_statement);

    dbms_output.put_line('Next sequence value is: ' || v_seq_result);   END LOOP;
END;
/

output result is following
SELECT statement is: SELECT max(COL1) FROM GINTS.QAQA MAX number of GINTS.QAQA.COL1 is 17
Select sequence nextval stetement is: SELECT GINTS.QAQA_SEQ.nextval FROM dual
Next sequence value is: 1

How to increment sequence appropriate times I'll leave to you as an excersise ;))))))

Gints Plivna
IT Sistēmas, Merķeļa 13, LV1050 Rīga
http://www.itsystems.lv/gints/

                                                                                       
                            
                    "Hagedorn,                                                         
                            
                    Linda"               To:     Multiple recipients of list ORACLE-L 
<ORACLE-L_at_fatcity.com>       
                    <lindah_at_epocra       cc:                                           
                            
                    tes.com>             Subject:     PL/SQL question                  
                            
                    Sent by:                                                           
                            
                    root_at_fatcity.c                                                     
                            
                    om                                                                 
                            
                                                                                       
                            
                                                                                       
                            
                    2001.11.15                                                         
                            
                    23:30                                                              
                            
                    Please respond                                                     
                            
                    to ORACLE-L                                                        
                            
                                                                                       
                            
                                                                                       
                            

Can anyone can tell me how to use PL/SQL declared variables in a select statement where a . has to be between the owner and table name, and the owner and table name are variables, I'd be most appreciative. The answer is probably obvious... Feel free to point it out.

On import, we occasionally have sequences that are out of sync with the data. To remedy this, I'm creating a master table that will match owner/table/column to owner/sequence, and a PL/SQL procedure that will increment sequences which are found to be lower than the max value in the associated owner/table/column.

Matching_Table:

Table_owner
Table_name
Table_column

Sequence_owner
Sequence_name
Create_dt
Last_mod_dt

Pseudo code:

Declarations variables, output report file, counters. Read dba_sequences in cursor
Select table_owner, table_name, table_column into v_table_owner, v_table_name, v_table_column from matching_table a where sequence_owner = a.sequence_owner and sequence_name = a.sequence_name ; (sequence_owner is from loop, reading dba_sequences)

if row is found then
  Select max(v_table_column) from v_table_owner.v_table_name ;

For the life of me I can't get this syntax right. The parser is complaining because v_table_owner.v_table_name isn't declared. I've tried || (concatenation), commas, single quotes, double quotes, colon, etc.

If you can see the error, I'd be very happy for a reply.

Thanks, Linda
echo
'[q]sa[ln0=aln256%Pln256/snlbx]sb3135071790101768542287578439snlbxq'|dc

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: G.Plivna_at_itsystems.lv

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Fri Nov 16 2001 - 07:33:42 CST