Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: pl/sql issue

Re: pl/sql issue

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Fri, 29 Jun 2007 13:04:56 -0700
Message-ID: <1183147496.800154.222560@w5g2000hsg.googlegroups.com>


On Jun 29, 5:09 am, sybrandb <sybra..._at_gmail.com> wrote:
> 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- 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 -- Received on Fri Jun 29 2007 - 15:04:56 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US