Re: cast error ...
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 parenthesisSQL> 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-198200:00:00
14 rijen zijn geselecteerd.
Regards,
Rob.
Received on Fri Jul 06 2007 - 10:45:30 CEST