Variable Size:: Help Urgent

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Tue, 21 Jun 2005 11:39 am

Good day I have a select that do the following: check and product_id < 9999 Where brings me only products with 04 houses.

I would like to know how to compare type: and product_id tabela.id <% type product _ namely, get to know the size of the field in the table and make this dynamic comparison.

Would like?

Grateful,
Trevisolli
Location: Araraquara - SP

Poston Tue, 21 Jun 2005 11:52 am

Follow the reasoning and see where you get that information:
Code: Select all
SQL> DESC EMP
Name                            Null?    Type
------------------------------- -------- ----
EMPNO                           NOT NULL NUMBER(4)
ENAME                                    VARCHAR2(10)
JOB                                      VARCHAR2(9)
MGR                                      NUMBER(4)
HIREDATE                                 DATE
SAL                                      NUMBER(7,2)   ===> ESSA que eu quero!
COMM                                     NUMBER(7,2)
DEPTNO                                   NUMBER(2)

SQL>
SQL> select DATA_PRECISION - DATA_SCALE CASAS_DECIMAIS_DA_COLUNA_SAL
  2  from user_tab_columns
  3  where table_name  ='EMP'
  4    AND COLUMN_NAME ='SAL'
  5  /

CASAS_DECIMAIS_DA_COLUNA_SAL
----------------------------
                           5

1 row selected.

SQL>
SQL> select
  2    DATA_PRECISION-DATA_SCALE             CASAS_DECIMAIS_DA_COLUNA_SAL
  3  , POWER(10, DATA_PRECISION-DATA_SCALE ) VALOR_MAXIMO
  4  from user_tab_columns
  5  where table_name  ='EMP'
  6    AND COLUMN_NAME ='SAL'
  7 
SQL> /

CASAS_DECIMAIS_DA_COLUNA_SAL VALOR_MAXIMO
---------------------------- ------------
                           5       100000

1 row selected.
In summary, you get the maximum value of the column at the data dictionary through these tables up!!! Now, it's just adapt it to its context.

:-o
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Wed, 22 Jun 2005 9:30 am

First, thank you very much for the reply.
The site is first!

I just have one more question: how much of my DATA_PRECISION is 04.
I would like to take this value and replicate the number 9, four times ... as data_precision.

Would do that? I don't know if exists REPLACE or REPLICATE in Oracle.

Thank you very much.

Hug, Cheezburger Network
Trevisolli
Location: Araraquara - SP

Poston Wed, 22 Jun 2005 11:51 am

Why don't you do the following:
Code: Select all
POWER(10, DATA_PRECISION-DATA_SCALE ) - 1
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered


  • See also
    Replies
    Views
    Last Post


    Return to PL/SQL

    Who is online

    Users browsing this forum: Google [Bot] and 5 guests