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.

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

On Jun 29, 5:09 am, sybrandb <sybra...@gmail.com> wrote:
> On Jun 29, 8:08 am, Shishir <shishir...@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- Hide quoted text -
>
> - Show quoted text -

Shishir, your questions show that you need to spend some time reading
the Oracle documentation especially the SQL Reference and PL/SQL
User's Guide and Reference before attempting to code in the language.

What Sybrand said about not porting your T-SQL is very true. Oracle
and SQL Server work very differently and your should really redesign
your processes to take advantage of the simplicity of Oracle.

HTH -- Mark D Powell --

On Jun 29, 10:09 am, sybrandb <sybra...@gmail.com> wrote:
>
> 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
>

Or even:

SELECT COUNT(DECODE(isregistered,1,'#')) AS "Total rows"
, COUNT(DECODE(isactive,1,'#')) AS "Active users"
, COUNT(DECODE(isregistered,0,'#')) AS "Pending registrations"
FROM usrmgmtuserdetails;

Or perhaps, if these are numeric indicator columns that can only ever
be 0 or 1:

SELECT SUM(isregistered) AS "Total rows"
, SUM(isactive) AS "Active users"
, COUNT(*) - SUM(isregistered) AS "Pending registrations"
FROM usrmgmtuserdetails;