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

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

Re: pl/sql issue

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Fri, 29 Jun 2007 05:00:28 -0700
Message-ID: <1183118428.428797.292170@n60g2000hse.googlegroups.com>


On Jun 29, 10:09 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.

First of all, I'd like to remind you that Oracle is NOT SQL Server, and PL/SQL is NOT T-SQL, they are conceptually and syntactically different. As of your questions:

  1. What's the tool you're using to view the procedure source code? Looks like a feature of your tool, as if they simply skip the obvious "CREATE OR REPLACE PROCEDURE proc_name" line and add it under the hood when you compile the procedure to form complete CREATE PROCEDURE statement.
  2. CREATE TABLE is an SQL statement, not a PL/SQL statement. If you try to run it in a PL/SQL BEGIN...END; block, it will not execute and syntax error will be reported - just like in your case. Looks like your tool automatically and silently wraps PL/SQL statements into an anonymous BEGIN...END; block.
  3. Most probably that's because you already have TT_TEMP table with a different definition and it doesn't have "TOTALCOUNT" column. Oracle doesn't use the concept of dynamic local/global temporary tables so common in SQL Server. In Oracle, all tables are permanent once you create them (though content may be temporary if you create a GLOBAL TEMPORARY table,) unlike SQL Server temporary tables which are automatically dropped as soon as they go out of scope.

There are ways to simulate local temporary tables in Oracle, but you'll need to familiarize yourself with quite a few Oracle concepts by reading the documentation for your Oracle release. Start with Oracle Concepts, Oracle PL/SQL Developer's Guide and Reference, SQL Reference, and Application Developer's Guide - Fundamentals. All Oracle documentation is available at http://tahiti.oracle.com (requires free Oracle Technology Network registration.)

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Fri Jun 29 2007 - 07:00:28 CDT

Original text of this message

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