Results 1 to 3 of 3

Thread: Data Type Error

  1. #1
    Join Date
    Jun 2003
    Posts
    294

    Lightbulb Unanswered: Data Type Error

    Hello, I'm Reciving the next data type error and I don't know how to solve It, cause I have to use BULK clause on a nested table that is base on a type or record or view.

    See Below:

    SQL> DECLARE
    2
    3 TYPE movimientos IS TABLE OF vieMovimientos%ROWTYPE;
    4 TMP_MOVIMIENTOS movimientos := movimientos();
    5
    6 TYPE miCursor IS REF CURSOR;
    7 lcuMovimientos miCursor;
    8
    9 lnuMovimiento NUMERIC;
    10
    11 BEGIN
    12
    13 SELECT M.NMSEC_MOVIMIENTO, CR.SNFACULTATIVO, M.NMSEC_TIPO_MVTO BULK COLLECT INTO TMP_MOVIMIENTOS
    14 FROM TCRW_MOVIMIENTOS M, TCRW_CUENTAS_REASEG CR
    15 WHERE M.NMSEC_CUENTAS_REASEG = CR.NMSEC_CUENTAS_REASEG;
    16
    17 OPEN lcuMovimientos FOR
    18 SELECT MOV.NMSEC_MOVIMIENTO
    19 FROM TCRW_OPERACIONES O, TCRW_OPER_X_TIP_MVTO OTM, TCRW_TIPO_MOVTOS TM,
    20 TABLE(CAST(TMP_MOVIMIENTOS AS movimientos)) MOV
    21 WHERE O.NMSEC_OPERACION = OTM.NMSEC_OPERACION
    22 AND OTM.NMSEC_TIPO_MVTO = TM.NMSEC_TIPO_MVTO
    23 AND TM.NMSEC_TIPO_MVTO = MOV.NMSEC_TIPO_MVTO
    24 AND OTM.SNFACULTATIVO = MOV.SNFACULTATIVO;
    25
    26 FETCH lcuMovimientos INTO lnuMovimiento;
    27
    28 WHILE lcuMovimientos%FOUND LOOP
    29 DBMS_OUTPUT.put_line(TO_CHAR(lnuMovimiento));
    30 FETCH lcuMovimientos INTO lnuMovimiento;
    31 END LOOP;
    32
    33 CLOSE lcuMovimientos;
    34
    35 EXCEPTION
    36 WHEN OTHERS THEN
    37 CLOSE lcuMovimientos;
    38
    39 END;
    40 /
    TABLE(CAST(TMP_MOVIMIENTOS AS movimientos)) MOV
    *
    ERROR at line 20:
    ORA-06550: line 20, column 35:
    PL/SQL: ORA-00902: invalid datatype
    ORA-06550: line 18, column 5:
    PL/SQL: SQL Statement ignored


    Pd. This is the view I used on the PLSQL blobk

    CREATE VIEW vieMovimientos as SELECT M.NMSEC_MOVIMIENTO, CR.SNFACULTATIVO, M.NMSEC_TIPO_MVTO
    FROM TCRW_MOVIMIENTOS M, TCRW_CUENTAS_REASEG CR
    WHERE M.NMSEC_CUENTAS_REASEG = CR.NMSEC_CUENTAS_REASEG;

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Unfortunately SQL does not recognise types defined in PL/SQL. For a type to be used in SQL it needs to be created in SQL with CREATE TYPE.

  3. #3
    Join Date
    Jun 2003
    Posts
    294
    Thank You William !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •