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.

On 6 jul, 09:55, Shishir <shishir... (AT) gmail (DOT) com> wrote:
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.

On Jul 6, 1:45 pm, Rob van Wijk <rwij... (AT) gmail (DOT) com> wrote:
thank you very much sir..it works fine wit tat modification..

Cheers,
shishir.

On Fri, 06 Jul 2007 02:43:16 -0700, Shishir <shishir456 (AT) gmail (DOT) com>
wrote:

Actually you don't need the CAST at all.
If you would have RTFM (which you consistently refuse to do) you would
have learned about the TO_CHAR *conversion function*.



You are recommended to do your homework, and stop abusing this forum
as substitute for not doing your work, which is R E A D T H E M A
N U A L!!

--
Sybrand Bakker
Senior Oracle DBA

Whats the difference between using Cast(number_field as VARCHAR2) and
TO_CHAR(number_field) ?

Are there performance differences, or are you commenting on your own
personal preference to use TO_CHAR?

Matt T wrote:
The test:

DECLARE
v VARCHAR2(10);
BEGIN
FOR i IN 1 .. 100000 LOOP
SELECT CAST(3.14 AS VARCHAR2(10))
INTO v
FROM dual;
END LOOP;
END;
/

DECLARE
v VARCHAR2(10);
BEGIN
FOR i IN 1 .. 100000 LOOP
SELECT TO_CHAR(3.14)
INTO v
FROM dual;
END LOOP;
END;
/

The results:

CAST TO_CHAR
Run 1 3.90 3.90
Run 2 3.87 3.87
Run 3 3.90 3.89

The differences, if any, look inconsequential.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org