Re: pl/sql issue

On Jun 29, 8:08 am, Shishir <shishir..._at_gmail.com> 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.

You don't need the temporary table. Actually you don't need the procedure, as it can be done by one (1!) SQL-statement. This actually demonstrates again t-sql is a piece of crap, and you shouldn't 'port' it.

Your statement is
SELECT 'Total count', COUNT(*)

    FROM USRMGMTUserDetails
    WHERE IsRegistered = 1
union
 SELECT 'Active users', COUNT(*)
 FROM USRMGMTUserDetails
  WHERE IsActive = 1
UNION
  SELECT 'Pending registrations', COUNT(*)   FROM USRMGMTUserDetails
  WHERE IsRegistered = 0
/

and that is all.

As to your original question:
The first snippet of code is NOT a *single* procedure, it is a SQL- script issuing a CREATE TABLE statement followed by a CREATE PROCEDURE statement. This is why you get errors when you run the entire text through a procedure editor.

--
Sybrand Bakker
Senior Oracle DBA