Re: cast error ...

On Jul 6, 1:45 pm, Rob van Wijk <rwij..._at_gmail.com> wrote:
> 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.

[Quoted] thank you very much sir..it works fine wit tat modification..

Cheers,
shishir. Received on Fri Jul 06 2007 - 11:43:16 CEST