10 Replies Latest reply on Jun 8, 2017 12:05 PM by JuanM

    How to declare column type as a package.element %type?

    Ricardo Lavezzi

      Is possible to declare the type of a column table as the type of a constant (or function) inside a package?

       

      I have a global constant declared inside a package, exposed with a function, and I would like to have the type of the column inside a table, explicitly declared as the type of that constant/function.

       

      I have a package named CONSTANTES_GLOBALES, with a function named False_Funct, and I would like to have a table's column type defined as CONSTANTES_GLOBALES.False_Funct%type

       

      I would be better if the type updates automatically if I change the type of CONSTANTES_GLOBALES.False_Funct

       

      Untitled.jpg

       

      Untitled.png

        • 1. Re: How to declare column type as a package element %type?
          JuanM

          I don't undertand you requirement, but I will tag the forum where probably you can find better answers SQL & PL/SQL

          • 2. Re: How to declare column type as a package element %type?
            Ricardo Lavezzi

            I want to use a code like this, and I get this error:

            SQL> create table MyTable (MyColumn CONSTANTES_GLOBALES.False_Funct%type);

            create table MyTable (MyColumn CONSTANTES_GLOBALES.False_Funct%type)

                                                                          *

            ERROR at line 1:

            ORA-00911: invalid character

             

            I tried to take advantage of the %Type attribute.

            • 3. Re: How to declare column type as a package element %type?
              JuanM

              I tried to take advantage of the %Type attribute.

              This can be done inside PL/SQL code.

               

              Create table is a SQL DDL statement, you cannot reference a PL/SQL types in table creation. The %type does not appliy in table creation. Does not make sense putting a dependency between tables and PL/SQL code.

               

              Try to think better what you want to do.

               

               

              • 4. Re: How to declare column type as a package.element %type?
                Ricardo Lavezzi

                I'm barely doing my firsts steps in SQL. I'm at learning stage.

                 

                My objectives are:

                1. Learning
                2. Implementing a Boolean type
                3. Centralize all Boolean definitions on a single place, to avoid bug propagation

                 

                 

                I'm trying to define the Boolean type and values for true/false/null on a single place and derive any other Boolean type from that definition.

                 

                I want to avoid bugs derived from different definitions of a Boolean type and value. For example, one table may define true as a char="1", and another as a number, or a varchar="true".

                 

                 

                I'm not sure if I'm doing the right thing, because I know nothing about SQL, and do not understand how it works.

                 

                My plan is to define a global constant, and use it as the only place where true/false are defined.

                 

                As I tumbled across the documentation, I found that SQL does not supports global constants, so I stored the constant inside a package.

                As I understand, constants cannot be exposed out of the package, so I made a function for True and another for False, which expose those constants.

                 

                I plan to make a read only Boolean table enumerating the licit Boolean values, and then create an user defined Type scoped to that table.

                 

                At this point, I used char as Boolean type, but do not know if is is a good choice, and maybe is not, so I will need to maintain it in the future, and tracking the chain of definitions will be a mess, so I want to inherit the types all across the code, if possible.

                 

                That's a huge mess caused by the lack of support for Boolean types in SQL, but is also a good exercise to learn how SQL works.

                • 5. Re: How to declare column type as a package.element %type?
                  BPeaslandDBA

                  I would like to have a table's column type defined as CONSTANTES_GLOBALES.False_Funct%type

                  This is not possible. In Oracle, a column's datatype cannot be changed dynamically. A column's datatype change can only be performed explicitly.

                   

                   

                  Cheers,

                  Brian

                  • 6. Re: How to declare column type as a package.element %type?
                    Gary Graham-Oracle

                    That's a huge mess caused by the lack of support for Boolean types in SQL

                    This old Ask Tom discussion may be of interest -- it gives some historical background...

                    https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6263249199595

                    1 person found this helpful
                    • 7. Re: How to declare column type as a package.element %type?

                      Wrong forum! - As you were already told in reply #1 this forum is ONLY for questions/issues for Sql Developer and your question has NOTHING to do with Sql Developer.

                       

                      I'm barely doing my firsts steps in SQL. I'm at learning stage.

                      Then please REREAD reply #1, mark this thread ANSWERED and repost your question in the proper forum as already told.

                      I'm not sure if I'm doing the right thing, because I know nothing about SQL, and do not understand how it works.

                      I suggest you start with SQL. If you don't understand SQL then you won't understand PL/SQL.

                       

                      There are PLENTY of tutorials and books available for learning SQL.

                      • 8. Re: How to declare column type as a package.element %type?
                        Ricardo Lavezzi

                        That link is awful.The person arguing against implementing Boolean types never heard about bad coding practices, including magic numbers.

                         

                        Implementing a Boolean type is not trivial, and that's particularly bad with Oracle SQL, which do not seems very good for making user defined types.

                         

                        It forces everybody to reinvent the wheel, wasting time, and provoking bugs which other programming languages solved half a century ago.

                         

                        Forcing everybody to implement his own Boolean is much worse than preserving backwards compatibility, because everybody implements different, incompatible conventions for Boolean, and those incompatible conventions cause exactly the same backward compatibility problem, AND future incompatibilities.

                        • 9. Re: How to declare column type as a package.element %type?
                          Ricardo Lavezzi

                          rp0428 wrote:

                           

                          Wrong forum! - As you were already told in reply #1 this forum is ONLY for questions/issues for Sql Developer and your question has NOTHING to do with Sql Developer.

                          Sorry. I was thinking that SQL developer was something like a programming environment, and it had tools for creation of tables.

                           

                          I would get something from posting this on another forum? I seem to understand from your posts that what I want to do is not possible.

                          • 10. Re: How to declare column type as a package.element %type?
                            JuanM

                            >>>>Is possible to declare the type of a column table as the type of a constant (or function) inside a package?

                             

                            I told you. Define a column data type defined inside pl/sql in not possible. Is impossible.

                            This is your correct answer.

                            1 person found this helpful