Re: cast error ...

From: Rob van Wijk <rwijk72_at_gmail.com>
Date: Fri, 06 Jul 2007 08:45:30 -0000
Message-ID: <1183711530.326912.60330_at_57g2000hsv.googlegroups.com>


[Quoted] On 6 jul, 09:55, Shishir <shishir..._at_gmail.com> wrote:
> hi ,
> hey this is the sql procedure which im dealing wit . On compiling im
> getting the following errors :
> Line # = 13 Column # = 46 Error Text = PL/SQL: ORA-00906: missing left
> parenthesis
>
> CREATE OR REPLACE PROCEDURE usp_ESTMATEGetAttributes
> (
> cv_1 IN OUT SYS_REFCURSOR
> )
> AS
> BEGIN
> OPEN cv_1 FOR
> SELECT AttributeName,
> AttributeType,
> AttributeId,
> ----this line is the problem--
> CAST(AttributeID AS VARCHAR2) || ',' || AttributeType
> AttributeIDType,
> CatalogName
> FROM ESTMATEMasterAttributes ;
> END;
>
> To me it seems correct . Can someone point out wats the mistake??
>
> Thanks in advance,
> Shishir.

Shishir,

You forgot to specify the length of the varchar2 datatype.

See:

SQL> create procedure usp_estimategetattributes (p_refc in out sys_refcursor)
  2 as
  3 begin
  4 open p_refc for
  5 select empno

  6         , ename
  7         , deptno
  8         , cast(sal as varchar2) || ',' || job attributetype
  9         , hiredate
 10      from emp

 11 ;
 12 end;
 13 /

Waarschuwing: procedure is aangemaakt met compilatiefouten.

SQL> show err
Fouten voor PROCEDURE USP_ESTIMATEGETATTRIBUTES:

LINE/COL ERROR



5/3      PL/SQL: SQL Statement ignored
8/30     PL/SQL: ORA-00906: missing left parenthesis
SQL> create or replace procedure usp_estimategetattributes (p_refc in out sys_refcursor)
  2 as
  3 begin
  4 open p_refc for
  5 select empno
  6         , ename
  7         , deptno
  8         , cast(sal as varchar2(10)) || ',' || job attributetype
  9         , hiredate
 10      from emp

 11 ;
 12 end;
 13 /

Procedure is aangemaakt.

SQL> var R refcursor
SQL> exec usp_estimategetattributes(:R)

PL/SQL-procedure is geslaagd.

SQL> print :R

     EMPNO ENAME DEPTNO ATTRIBUTETYPE HIREDATE

---------- ---------- ---------- --------------------
-------------------
      7369 SMITH              20 800,CLERK            17-12-1980
00:00:00
      7499 ALLEN              30 1600,SALESMAN        20-02-1981
00:00:00
      7521 WARD               30 1250,SALESMAN        22-02-1981
00:00:00
      7566 JONES              20 2975,MANAGER         02-04-1981
00:00:00
      7654 MARTIN             30 1250,SALESMAN        28-09-1981
00:00:00
      7698 BLAKE              30 2850,MANAGER         01-05-1981
00:00:00
      7782 CLARK              10 2450,MANAGER         09-06-1981
00:00:00
      7788 SCOTT              20 3000,ANALYST         09-12-1982
00:00:00
      7839 KING               10 5000,PRESIDENT       17-11-1981
00:00:00
      7844 TURNER             30 1500,SALESMAN        08-09-1981
00:00:00
      7876 ADAMS              20 1100,CLERK           12-01-1983
00:00:00
      7900 JAMES              30 950,CLERK            03-12-1981
00:00:00
      7902 FORD               20 3000,ANALYST         03-12-1981
00:00:00
      7934 MILLER             10 1300,CLERK           23-01-1982
00:00:00

14 rijen zijn geselecteerd.

Regards,
Rob. Received on Fri Jul 06 2007 - 10:45:30 CEST

Original text of this message