Home » SQL & PL/SQL » SQL & PL/SQL » Variable stors the value of another variable (oracle 10g)
Variable stors the value of another variable [message #416355] Sat, 01 August 2009 00:32 Go to next message
bond007
Messages: 58
Registered: March 2009
Member
Hi friend ,
I need your help for fixing the following problem.
please gothrugh the code , where I have mentiioned the problem
create or replace procedure  parm_proc is
cursor c1 is 
select s.*,
m.priority,m.map_demnd 
from sort_sequence s , map_sort_entity m
where s.entity_type=m.entity_type
and s.is_default='N' 
order by m.priority;


cursor c2(v_entity_type in char) is 
select entity_type,
  (SELECT ATTR_TYPE || ' ' || SORT_ORDR FROM map_sort_seq_attributes 
   where ATTR_CD= attribute1) as srt_order1,
   (SELECT ATTR_TYPE || ' ' || SORT_ORDR FROM map_sort_seq_attributes 
   where ATTR_CD= attribute2) as srt_order2,
   (SELECT ATTR_TYPE || ' ' || SORT_ORDR FROM map_sort_seq_attributes 
   where ATTR_CD= attribute3) as srt_order3,
   (SELECT ATTR_TYPE || ' ' || SORT_ORDR FROM map_sort_seq_attributes 
   where ATTR_CD= attribute4) as srt_order4,
   (SELECT ATTR_TYPE || ' ' || SORT_ORDR FROM map_sort_seq_attributes 
   where ATTR_CD= attribute5) as srt_order5,
   (SELECT ATTR_TYPE || ' ' || SORT_ORDR FROM map_sort_seq_attributes 
   where ATTR_CD= attribute6) as srt_order6,
   (SELECT ATTR_TYPE || ' ' || SORT_ORDR FROM map_sort_seq_attributes 
   where ATTR_CD= attribute7) as srt_order7
   from sort_seq_attributes
    where entity_type =v_entity_type;

--v_out varchar2(100);
v_srt1 varchar2(100);
v_srt2 varchar2(100);
v_srt3 varchar2(100);
v_srt4 varchar2(100); 
v_srt5 varchar2(100); 
v_srt6 varchar2(100);
v_srt7 varchar2(100);
v_is_default varchar2(100);
v_entity_type varchar2(100);
v_priority varchar2(100);
v_map_demand varchar2(100);
v_srt varchar2(30);
v_sql_stmnt varchar2(1300);


i number(3);
TYPE cur_typ IS REF CURSOR;
c                  cur_typ;
BEGIN 
EXECUTE IMMEDIATE 'TRUNCATE TABLE parm_search'; 
 FOR r_c1 in c1 LOOP
 begin
   
   for r_c2 in c2(r_c1.entity_type) loop

 --insert into parm_search values
 select decode (r_c1.srt_order1,1,r_c2.srt_order1,2,r_c2.srt_order2,3,r_c2.srt_order3,4,r_c2.srt_order4,5,r_c2.srt_order5,
                              6,r_c2.srt_order6,7,r_c2.srt_order7) ,
       decode (r_c1.srt_order2,1,r_c2.srt_order1,2,r_c2.srt_order2,3,r_c2.srt_order3,4,r_c2.srt_order4,5,r_c2.srt_order5,
                              6,r_c2.srt_order6,7,r_c2.srt_order7) ,
       decode (r_c1.srt_order3,1,r_c2.srt_order1,2,r_c2.srt_order2,3,r_c2.srt_order3,4,r_c2.srt_order4,5,r_c2.srt_order5,
                              6,r_c2.srt_order6,7,r_c2.srt_order7) ,
       decode (r_c1.srt_order4,1,r_c2.srt_order1,2,r_c2.srt_order2,3,r_c2.srt_order3,4,r_c2.srt_order4,5,r_c2.srt_order5,
                              6,r_c2.srt_order6,7,r_c2.srt_order7) ,
       decode (r_c1.srt_order5,1,r_c2.srt_order1,2,r_c2.srt_order2,3,r_c2.srt_order3,4,r_c2.srt_order4,5,r_c2.srt_order5,
                              6,r_c2.srt_order6,7,r_c2.srt_order7) ,
  decode (r_c1.srt_order6,1,r_c2.srt_order1,2,r_c2.srt_order2,3,r_c2.srt_order3,4,r_c2.srt_order4,5,r_c2.srt_order5,
                              6,r_c2.srt_order6,7,r_c2.srt_order7) ,
  decode (r_c1.srt_order7,1,r_c2.srt_order1,2,r_c2.srt_order2,3,r_c2.srt_order3,4,r_c2.srt_order4,5,r_c2.srt_order5,
                              6,r_c2.srt_order6,7,r_c2.srt_order7), r_c1.IS_DEFAULT ,r_c1.ENTITY_TYPE,r_c1.PRIORITY,r_c1.MAP_DEMND 
 into v_srt1 , v_srt2, v_srt3, v_srt4,v_srt5 , v_srt6, v_srt7 ,v_is_default,v_entity_type,v_priority,v_map_demand
from dual;

    
 
 --------------
   --  I found problem in this step
  
IF substr(v_srt7,1,2) like '%so%' THEN
       dbms_output.put_line ('YES');
      END IF ;

    
       FOR i IN 1..7   LOOP
         --v_srt:='v_srt'||i;
         --dbms_output.put_line ('v_srt'||i);
        select substr('v_srt'||i,1,12) into v_srt from dual;
        dbms_output.put_line (v_srt);
       END LOOP; 

       
      --Here dbms_output.put_line shows  v_srt1 , v_srt2, v_srt3, v_srt4,v_srt5 , v_srt6, v_srt7 itertively.
      --But My requirement is to get the value stores in v_srt1 , v_srt2, v_srt3, v_srt4,v_srt5 , v_srt6, v_srt7 

 --------------
     

 insert into parm_search values (r_c1.entity_name,v_srt1, v_srt2, v_srt3, v_srt4 ,v_srt5, v_srt6, v_srt7,
v_is_default,v_entity_type,v_priority,v_map_demand);
commit;



----------------------------
 
   end loop;
  


 --exception
 end;
 END LOOP;

--EXCEPTION
END;
/
show errors
Re: Variable stors the value of another variable [message #416357 is a reply to message #416355] Sat, 01 August 2009 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not clear what works, what does not work, what you want and so on. As I can see you have the values in the variables.

Get rid of actual code, build a SIMPLE BUT COMPLETE example of what you want.

Regards
Michel
Re: Variable stors the value of another variable [message #416390 is a reply to message #416357] Sat, 01 August 2009 07:11 Go to previous messageGo to next message
bond007
Messages: 58
Registered: March 2009
Member

       FOR i IN 1..7   LOOP
         --v_srt:='v_srt'||i;
         --dbms_output.put_line ( dbms_output.put_line (v_srt);
||i);
        select substr('v_srt'||i,1,12) into v_srt from dual;
        dbms_output.put_line (v_srt);
       END LOOP; 



I am expecting
dbms_output.put_line (v_srt); should show the content of

v_srt1
v_srt2
v_srt3
v_srt4
v_srt5
v_srt6
v_srt7
the above variable gets data from the below statement
select decode (r_c1.srt_order1,1,r_c2.srt_order1,2,r_c2.srt_order2,3,r_c2.srt_order3,4,r_c2.srt_order4,5,r_c2.srt_order5,
                              6,r_c2.srt_order6,7,r_c2.srt_order7) ,
       decode (r_c1.srt_order2,1,r_c2.srt_order1,2,r_c2.srt_order2,3,r_c2.srt_order3,4,r_c2.srt_order4,5,r_c2.srt_order5,
                              6,r_c2.srt_order6,7,r_c2.srt_order7) ,
       decode (r_c1.srt_order3,1,r_c2.srt_order1,2,r_c2.srt_order2,3,r_c2.srt_order3,4,r_c2.srt_order4,5,r_c2.srt_order5,
                              6,r_c2.srt_order6,7,r_c2.srt_order7) ,
       decode (r_c1.srt_order4,1,r_c2.srt_order1,2,r_c2.srt_order2,3,r_c2.srt_order3,4,r_c2.srt_order4,5,r_c2.srt_order5,
                              6,r_c2.srt_order6,7,r_c2.srt_order7) ,
       decode (r_c1.srt_order5,1,r_c2.srt_order1,2,r_c2.srt_order2,3,r_c2.srt_order3,4,r_c2.srt_order4,5,r_c2.srt_order5,
                              6,r_c2.srt_order6,7,r_c2.srt_order7) ,
  decode (r_c1.srt_order6,1,r_c2.srt_order1,2,r_c2.srt_order2,3,r_c2.srt_order3,4,r_c2.srt_order4,5,r_c2.srt_order5,
                              6,r_c2.srt_order6,7,r_c2.srt_order7) ,
  decode (r_c1.srt_order7,1,r_c2.srt_order1,2,r_c2.srt_order2,3,r_c2.srt_order3,4,r_c2.srt_order4,5,r_c2.srt_order5,
                              6,r_c2.srt_order6,7,r_c2.srt_order7), r_c1.IS_DEFAULT ,r_c1.ENTITY_TYPE,r_c1.PRIORITY,r_c1.MAP_DEMND 
 into v_srt1 , v_srt2, v_srt3, v_srt4,v_srt5 , v_srt6, v_srt7 ,v_is_default,v_entity_type,v_priority,v_map_demand
from dual;




But it is showing
v_srt1
v_srt2
v_srt3
v_srt4
v_srt5
v_srt6
v_srt7


Re: Variable stors the value of another variable [message #416391 is a reply to message #416390] Sat, 01 August 2009 07:15 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Of course it does. Because that IS what you store in the variable with

 select substr('v_srt'||i,1,12) into v_srt from dual;


The usual advice would be :

Don. Use. Dynamic. SQL.

It is complicated, slow, unreadable and un-maintainable. As your example again shows.
Re: Variable stors the value of another variable [message #416392 is a reply to message #416391] Sat, 01 August 2009 07:40 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
And while I haven't figured out exactly what you are trying to do, is it something like this?#

SQL> set serverout on
SQL> -- Create Parameter Table
SQL> CREATE TABLE param (num NUMBER(12), val VARCHAR2(20));

Table created.

SQL>
SQL> -- Insert Values
SQL> INSERT INTO param VALUES (1,'One');
1 row created.
SQL> INSERT INTO param VALUES (2,'Two');
1 row created.
SQL> INSERT INTO param VALUES (3,'Three');
1 row created.
SQL> INSERT INTO param VALUES (4,'Four');
1 row created.
SQL> INSERT INTO param VALUES (5,'Five');
1 row created.
SQL> INSERT INTO param VALUES (6,'Six');
1 row created.
SQL> INSERT INTO param VALUES (7,'Seven');
1 row created.
SQL> INSERT INTO param VALUES (8,'Eight');
1 row created.
SQL>
SQL>
SQL> -- Example Script
SQL> DECLARE
  2
  3    TYPE t_par_array IS VARRAY(100) OF VARCHAR2(20);
  4    v_par_array t_par_array;
  5
  6  BEGIN
  7
  8    -- Store Parameters in Array.
  9    v_par_array := NEW t_par_array();
 10    v_par_array.extend(100);
 11    FOR l IN (
 12       SELECT * FROM param
 13    ) LOOP
 14       v_par_array(l.num) := l.val;
 15    END LOOP;
 16
 17    -- Loop Through values in The array
 18    FOR i IN 1..8 LOOP
 19        Dbms_Output.put_line('Value ' || i || ' is ' || v_par_array(i));
 20    END LOOP;
 21
 22  END;
 23  /
Value 1 is One
Value 2 is Two
Value 3 is Three
Value 4 is Four
Value 5 is Five
Value 6 is Six
Value 7 is Seven
Value 8 is Eight
PL/SQL procedure successfully completed.
SQL>
SQL> -- Cleanup
SQL> DROP TABLE param;
Table dropped.
SQL>
Re: Variable stores the value of another variable [message #416413 is a reply to message #416392] Sat, 01 August 2009 14:31 Go to previous messageGo to next message
bond007
Messages: 58
Registered: March 2009
Member
Thanks Thomas .. your example gave me the exact information that I want.

Now I changed the code and it works fine.

Re: Variable stors the value of another variable [message #416442 is a reply to message #416355] Sun, 02 August 2009 13:39 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Now I changed the code and it works fine.

It might "work fine" in single user mode


EXECUTE IMMEDIATE 'TRUNCATE TABLE parm_search';


What happens when 2nd user issues command above & eliminates data for 1st user which just loaded this table?
Previous Topic: Loop through table user_tab_cols and execute dynamic SQL for each table
Next Topic: sequence in Dynamic sql
Goto Forum:
  


Current Time: Sat Dec 10 06:58:01 CST 2016

Total time taken to generate the page: 0.24431 seconds