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

Home -> Community -> Usenet -> c.d.o.tools -> pl/sql isue

pl/sql isue

From: Shishir <shishir456_at_gmail.com>
Date: Thu, 28 Jun 2007 23:10:07 -0700
Message-ID: <1183097407.376011.199080@x35g2000prf.googlegroups.com>


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
-- =============================================

Same code when viewed in the oracle database:
-- =============================================
   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;
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. Received on Fri Jun 29 2007 - 01:10:07 CDT

Original text of this message

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