Re: pl/sql issue

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 01 Jul 2007 23:30:53 -0700
Message-ID: <1183357854.481471_at_bubbleator.drizzle.com>

Shishir wrote:

> On Jun 29, 9:12 pm, DA Morgan <damor..._at_psoug.org> wrote:
>> Shishir 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.

>> I just copied the code from your post and it compiled just fine
>> after commenting out the references to USRMGMTUserDetails.
>>
>> Oracle does not store the CREATE word in source$.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor..._at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org
> 
> Hi,
> But the code should refer the table USRMGMTUserDetails for extracting
> the relevant values..right??
> Can you tell me why referrring to USRMGMTUserDetails is giving
> errors??
> 
> Cheers,
> Shishir.

Because you don't have permission to access the table from PL/SQL. Grants must be explicit ... not through a role.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Mon Jul 02 2007 - 08:30:53 CEST