11 Replies Latest reply on May 16, 2012 4:59 AM by Ayham

    How to declare varaibles in PL/SQL (Dynamically)

    Ayham
      Hi ,

      Please inform me how can i declare varaibles in PL/SQL dynamically ( I want to used it in other project). i tried by the follwoing code but this error appears

      SQL> /
      declare
      *
      ERROR at line 1:
      ORA-06550: line 1, column 74:
      PL/SQL: ORA-00933: SQL command not properly ended
      ORA-06550: line 1, column 35:
      PL/SQL: SQL Statement ignored
      ORA-06512: at line 10

      -----------------------------------------
      This is the code .
      declare
      v_temp varchar2(300);
      x varchar2(20);
      y varchar2(20);

      begin
      bbb:=' X varchar2(20);';
      y:='1';

      execute immediate ' declare '
      || v_temp
      || ' begin '
      || ' Select name '
      || ' from ali where id=:yy '
      || ' into x using '||y
      || ' ; '
      || ' end; ';
      end;

      regards
      WAel
        • 1. Re: How to declare varaibles in PL/SQL (Dynamically)
          Arun Kumar Gupta
          You can write a dynamic PLSQL block but not the variables only.

          BTW could you explain why do you want to declare the variables dynamically.


          Regards
          Arun
          • 2. Re: How to declare varaibles in PL/SQL (Dynamically)
            HuaMin Chen
            Your syntax above is incorrect!

            Many Thanks & Best Regards,
            HuaMin

            Edited by: HuaMin on May 14, 2012 5:41 PM
            • 3. Re: How to declare varaibles in PL/SQL (Dynamically)
              indra budiantho
              As far as i know, there's no way. Just create function in a package, then you just can copy /call it for your other project.
              • 4. Re: How to declare varaibles in PL/SQL (Dynamically)
                BluShadow
                user3098640 wrote:
                Hi ,

                Please inform me how can i declare varaibles in PL/SQL dynamically ( I want to used it in other project). i tried by the follwoing code but this error appears
                What are you actually trying to achieve?

                Dynamic SQL is bad enough, but dynamic PL/SQL is even worse.

                Not only is it non-scalable, it can also lead to many run time bugs that you won't pick up straight away, and potential security issues.

                The Oracle database is extremely powerful with SQL and PL/SQL as it is, so why on Earth would you need to dynamically generate code on the fly? Step back... explain what you are trying to achieve and maybe we can explain a better way of doing it. 99.999% of the time, people who try and do things dynamically are doing it for the wrong reasons.
                • 5. Re: How to declare varaibles in PL/SQL (Dynamically)
                  Himanshu Binjola
                  Its just your syntax is not correct.

                  Below is only for learning purpose, try not to make so complex code with dynamic variable
                  DECLARE
                  v_x VARCHAR2(20) := 'x VARCHAR2(20);';
                  v_y VARCHAR2(20) := 'X';
                  
                  BEGIN
                  
                  EXECUTE IMMEDIATE   
                  'DECLARE'                  || chr(10) ||
                  v_x                        || chr(10) ||
                  'BEGIN'                    || chr(10) ||
                  '  SELECT ''Hello World''' || chr(10) ||
                  '  INTO x'                 || chr(10) ||
                  '  FROM DUAL'              || chr(10) ||
                  '  WHERE dummy = :y;'       || chr(10) ||
                  ''                         || chr(10) ||
                  '  DBMS_OUTPUT.PUT_LINE(''X = '' || x );' || chr(10) ||
                  'END;' USING v_y;
                  
                  END;
                  
                  -- Output
                  X = Hello World
                  
                  {code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                  • 6. Re: How to declare varaibles in PL/SQL (Dynamically)
                    Ayham
                    Hi , many thanks for all . Ok i will show you i want to achive. So please help me
                    see the following table it consist from four column and three row.
                    I want only to show the column that has value ZERO : as the following query

                    Select REC1 from IMAGE_VALUE where corr='x' and rec1='1';
                    in that case is easy but in my case the columns maybe more that 100 columns (it's will create automatically- already done the number of columns is not fixed but it start by REC1, REC2,REC3,REC4 ... etc)
                    the question is how can i SHOW the Columns that only contains '1' for specfic 'corr' is X,Y or Z in PL-SQL


                    SQL> column REC1 format a10
                    SQL> column REC2 format a10
                    SQL> column REC3 format a10
                    SQL> column CORR format a10
                    SQL> SELECT * FROM IMAGE_VALUE;

                    CORR REC1 REC2 REC3

                    X 1 0 0
                    Y 0 1 1
                    Z 1 0 1




                    This is the code is you want to test in your PC.
                    SQL> CREATE TABLE IMAGE_VALUE (CORR VARCHAR2(20), REC1 VARCHAR2(20), REC2 VARCHA
                    R2(20), REC3 VARCHAR2(20));

                    Table created.



                    SQL> INSERT INTO IMAGE_VALUE VALUES('X','1','0','0');

                    1 row created.

                    SQL> INSERT INTO IMAGE_VALUE VALUES('Y','0','1','1');

                    1 row created.

                    SQL> INSERT INTO IMAGE_VALUE VALUES('Z','1','0','1');

                    1 row created.

                    SQL> COMMIT;

                    Commit complete.

                    Edited by: user3098640 on May 14, 2012 9:51 AM

                    Edited by: user3098640 on May 14, 2012 9:59 AM
                    • 8. Re: How to declare varaibles in PL/SQL (Dynamically)
                      BluShadow
                      user3098640 wrote:
                      Hi , many thanks for all . Ok i will show you i want to achive. So please help me
                      see the following table it consist from four column and three row.
                      I want only to show the column that has value ZERO : as the following query

                      Select REC1 from IMAGE_VALUE where corr='x' and rec1='1';
                      Do you want ZERO or "1" ? You contradict yourself.

                      Good to see you're requirements are clear.
                      in that case is easy but in my case the columns maybe more that 100 columns (it's will create automatically- already done the number of columns is not fixed but it start by REC1, REC2,REC3,REC4 ... etc)
                      the question is how can i SHOW the Columns that only contains '1' for specfic 'corr' is X,Y or Z in PL-SQL
                      Why are you having a table that is creating automatically with an unknown number of columns?
                      This is very bad database design and completely throws the basic prinicples of design out of the window.

                      When you say "Show" what does that mean? SQL and PL/SQL code does not "Show" anything, as it has no user interface. It simply processes data, and if that data is not in a known structure at the time the application is designed, then all the code has to be dynamic to try and deal with it. This is just wrong in so many ways.

                      A better structure would be something like this...
                      SQL> create table image_value (corr varchar2(20), rec varchar2(10), rec_val varchar2(20));
                      
                      Table created.
                      
                      SQL>
                      SQL> insert into image_value (corr, rec, rec_val) values ('X','REC1','1');
                      
                      1 row created.
                      
                      SQL> insert into image_value (corr, rec, rec_val) values ('X','REC2','0');
                      
                      1 row created.
                      
                      SQL> insert into image_value (corr, rec, rec_val) values ('X','REC3','0');
                      
                      1 row created.
                      
                      SQL> insert into image_value (corr, rec, rec_val) values ('Y','REC1','0');
                      
                      1 row created.
                      
                      SQL> insert into image_value (corr, rec, rec_val) values ('Y','REC2','1');
                      
                      1 row created.
                      
                      SQL> insert into image_value (corr, rec, rec_val) values ('Y','REC3','1');
                      
                      1 row created.
                      
                      SQL> insert into image_value (corr, rec, rec_val) values ('Z','REC1','1');
                      
                      1 row created.
                      
                      SQL> insert into image_value (corr, rec, rec_val) values ('Z','REC2','0');
                      
                      1 row created.
                      
                      SQL> insert into image_value (corr, rec, rec_val) values ('Z','REC3','1');
                      
                      1 row created.
                      
                      SQL>
                      SQL> commit;
                      
                      Commit complete.
                      
                      SQL>
                      SQL> with r as (select '&Required_Corr' as req_corr from dual)
                        2  --
                        3  -- end of input
                        4  --
                        5  select corr
                        6        ,max(decode(rn,1,rec)) as c1
                        7        ,max(decode(rn,2,rec)) as c2
                        8        ,max(decode(rn,3,rec)) as c3
                        9        ,max(decode(rn,4,rec)) as c4
                       10        ,max(decode(rn,5,rec)) as c5
                       11        ,max(decode(rn,6,rec)) as c6
                       12        ,max(decode(rn,7,rec)) as c7
                       13        ,max(decode(rn,8,rec)) as c8
                       14        ,max(decode(rn,9,rec)) as c9
                       15        ,max(decode(rn,10,rec)) as c10
                       16  from (
                       17        select corr, rec, row_number() over (partition by corr order by rec) as rn
                       18        from   image_value, r
                       19        where  corr = req_corr
                       20        and    rec_val = '1'
                       21       )
                       22  group by corr;
                      Enter value for required_corr: X
                      old   1: with r as (select '&Required_Corr' as req_corr from dual)
                      new   1: with r as (select 'X' as req_corr from dual)
                      
                      CORR                 C1         C2         C3         C4         C5         C6         C7         C8         C9         C10
                      -------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
                      X                    REC1
                      
                      SQL> /
                      Enter value for required_corr: Y
                      old   1: with r as (select '&Required_Corr' as req_corr from dual)
                      new   1: with r as (select 'Y' as req_corr from dual)
                      
                      CORR                 C1         C2         C3         C4         C5         C6         C7         C8         C9         C10
                      -------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
                      Y                    REC2       REC3
                      
                      SQL> /
                      Enter value for required_corr: Z
                      old   1: with r as (select '&Required_Corr' as req_corr from dual)
                      new   1: with r as (select 'Z' as req_corr from dual)
                      
                      CORR                 C1         C2         C3         C4         C5         C6         C7         C8         C9         C10
                      -------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
                      Z                    REC1       REC3
                      
                      SQL>
                      And you cater for the maximum number of REC values you expect.

                      Other than that, what you are really talking about is a reporting requirement which requires the data to be read once before it is then processed and only the required data is queried with a second query. That can be done in PL/SQL using DBMS_SQL (or poor versions using EXECUTE IMMEDIATE), but really that should be done by reporting tools that are designed to query data and then format it and lay it out based on the contents of the data.

                      Again, you haven't really explained the reason behind why you are trying to do this. It's alright saying you are trying to achieve it, but that doesn't justify it as being the right way to do things. From what you've shown us so far, this is not how to go about designing a database or application code.
                      • 9. Re: How to declare varaibles in PL/SQL (Dynamically)
                        Ayham
                        Thank you very much. But i am design this table for temporary data for Image Clustering . the Corr columns can be not only X,Y, and z also can be unlimited for x,y,z,a,b,c,h,j,k,...etc. and the number of of column can be from REC1 to REC100++ . For the Show i know the SQL can be retrive data i mean by show is Retrive data.
                        Anyway, many thanks i will check the code when i back to office.

                        (My target only is cluster the columns that content One's. without any information about how columns and how many raws.)

                        Regards
                        Wael
                        • 10. Re: How to declare varaibles in PL/SQL (Dynamically)
                          BluShadow
                          user3098640 wrote:
                          Thank you very much. But i am design this table for temporary data for Image Clustering . the Corr columns can be not only X,Y, and z also can be unlimited for x,y,z,a,b,c,h,j,k,...etc. and the number of of column can be from REC1 to REC100++ .
                          All the more reasons to store it in a structure like I showed and not to have an unknown number of columns. That structure will deal with any number of CORR values and any number of REC values.
                          For the Show i know the SQL can be retrive data i mean by show is Retrive data.
                          Having an unknown number of columns means ALL your code will have to be dynamic. This is NOT GOOD DESIGN, it is NOT scalable, will perform poorly and will undoubtably have many bugs that you may not know about until the application is already in use.
                          • 11. Re: How to declare varaibles in PL/SQL (Dynamically)
                            Ayham
                            Many thanks , i undersatnd what you many i change my table strcture.