Re: pl/sql issue

Shishir wrote:
> hi this is the code generated by the sql developer for one of the
> stored procedures in pl/sql:
>
> By using the translation scratch editor;
> ---------------------------------------------------------
> /* Translation Extracted DDL For Required Objects */
> CREATE TABLE tt_temp (
> Summary VARCHAR2(30) ,
> TotalCount NUMBER(10,0) );
>
>
>
> CREATE OR REPLACE PROCEDURE usp_USRMGMTGetUserSummary
> -- =============================================
> -- Author: <Author,,Name>
> -- Create date: <Create Date,,>
> -- Description: <Description,,>
> -- =============================================
> (
> cv_1 IN OUT SYS_REFCURSOR
> )
> AS
> v_TotalCount NUMBER(10,0);
> v_ActiveUsers NUMBER(10,0);
> v_PendingReg NUMBER(10,0);
> -- Add the parameters for the stored procedure here
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> NULL/*TODO:SET NOCOUNT ON*/;
> SELECT COUNT(*)
> INTO v_TotalCount
> FROM USRMGMTUserDetails
> WHERE IsRegistered = 1;
> SELECT COUNT(*)
> INTO v_ActiveUsers
> FROM USRMGMTUserDetails
> WHERE IsActive = 1;
> SELECT COUNT(*)
> INTO v_PendingReg
> FROM USRMGMTUserDetails
> WHERE IsRegistered = 0;
> -- Insert statements for procedure here
> INSERT INTO tt_temp
> ( Summary, TotalCount )
> VALUES ( 'Total Count', v_TotalCount );
> INSERT INTO tt_temp
> ( Summary, TotalCount )
> VALUES ( 'Active Users', v_ActiveUsers );
> INSERT INTO tt_temp
> ( Summary, TotalCount )
> VALUES ( 'Pending Registrations', v_PendingReg );
> OPEN cv_1 FOR
> SELECT *
> FROM tt_temp ;
> END;
>
> Same code when viewed in the oracle database:
> -- =============================================
> -- Author: <Author,,Name>
> -- Create date: <Create Date,,>
> -- Description: <Description,,>
> -- =============================================
> (
> cv_1 IN OUT SYS_REFCURSOR
> )
> AS
> v_TotalCount NUMBER(10,0);
> v_ActiveUsers NUMBER(10,0);
> v_PendingReg NUMBER(10,0);
> -- Add the parameters for the stored procedure here
> BEGIN
>
>
>
>
> SELECT COUNT(*)
> INTO v_TotalCount
> FROM USRMGMTUserDetails
> WHERE IsRegistered = 1;
> SELECT COUNT(*)
> INTO v_ActiveUsers
> FROM USRMGMTUserDetails
> WHERE IsActive = 1;
> SELECT COUNT(*)
> INTO v_PendingReg
> FROM USRMGMTUserDetails
> WHERE IsRegistered = 0;
> -- Insert statements for procedure here
> INSERT INTO tt_temp
> ( Summary, TotalCount )
> VALUES ( 'Total Count', v_TotalCount );
> INSERT INTO tt_temp
> ( Summary, TotalCount )
> VALUES ( 'Active Users', v_ActiveUsers );
> INSERT INTO tt_temp
> ( Summary, TotalCount )
> VALUES ( 'Pending Registrations', v_PendingReg );
> OPEN cv_1 FOR
> SELECT *
> FROM tt_temp ;
> END;
>
> On compilation the errors generated are:
> Line # = 35 Column # = 4 Error Text = PL/SQL: SQL Statement ignored
> Line # = 36 Column # = 17 Error Text = PL/SQL: ORA-00904:
> "TOTALCOUNT": invalid identifier
> Line # = 38 Column # = 4 Error Text = PL/SQL: SQL Statement ignored
> Line # = 39 Column # = 17 Error Text = PL/SQL: ORA-00904:
> "TOTALCOUNT": invalid identifier
> Line # = 41 Column # = 4 Error Text = PL/SQL: SQL Statement ignored
> Line # = 42 Column # = 17 Error Text = PL/SQL: ORA-00904:
> "TOTALCOUNT": invalid identifier
>
> 1) My first doubt is why is the oracle db version of the stored
> procedure not using "CREATE.." statement to create procedure.
> 2)why is the temporary table not being created in the oracle db
> version of the procedure.
> if i try to create a table using the following code,it gives errors
> like :
> CREATE TABLE tt_temp (
> Summary VARCHAR2(30) ,
> TotalCount NUMBER(10,0) );
>
> Line # = 34 Column # = 4 Error Text = PLS-00103: Encountered the
> symbol "CREATE" when expecting one of the following:
>
> begin case declare end exception exit for goto if loop mod
> null pragma raise return select update while with
> <an identifier> <a double-quoted delimited-identifier>
> <a bind variable> << close current delete fetch lock insert
> open rollback savepoint set sql execute commit forall merge
> pipe
>
> 3)why im getting this errors at all:
> Line # = 35 Column # = 4 Error Text = PL/SQL: SQL Statement ignored
> Line # = 36 Column # = 17 Error Text = PL/SQL: ORA-00904:
> "TOTALCOUNT": invalid identifier
>
>
> Im new to pl/sql and have a task of urgently converting t-sql to pl/
> sql code. Any help in this direction will be appreciated.
>
>
> Cheers,
> Shishir.

I just copied the code from your post and it compiled just fine after commenting out the references to USRMGMTUserDetails.

Oracle does not store the CREATE word in source$.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org