Newb: What's Wrong With This Syntax? 1

[BoulderBum]

I'm trying to convert a SQL Server stored procedure to an Oracle package, but I'm running into a bit of trouble with a sub-query:

 procedure TRIM_PASSWORD_HISTORY
  (
    p_USER_ID IN VARCHAR2
  )
  is
  begin
    DELETE FROM PASSWORD_HISTORY
    WHERE USER_ID = p_USER_ID
    AND PASSWORD_HISTORY_SEQ_NO
    NOT IN
    (
	   SELECT 
		/*+ FIRST_ROWS(24) */ 
		PASSWORD_HISTORY_SEQ_NO
  	   FROM PASSWORD_HISTORY
  	   WHERE USER_ID = p_USER_ID
  	   ORDER BY INSERTED_DATE DESC
    );
  end;

I get an error that I have an extra right-parenthesis (ORA-00907), but I discovered the real heart of the issue has something to do with:

SELECT 
/*+ FIRST_ROWS(24) */ 
PASSWORD_HISTORY_SEQ_NO
FROM PASSWORD_HISTORY
WHERE USER_ID = p_USER_ID
ORDER BY INSERTED_DATE DESC

because if I just replace the above with a simpler query everything works well.

I'm sure this is a simple problem, but this is only my second day really working with Oracle and I'm still pretty clueless. What am I missing?

 

[SantaMufasa]

Boulder,

The parenthesis error relates to your attempt to tack an "ORDERY BY" onto a sub-query. Here is both the erroneous code, followed by working code:

Section 1 -- Error re-creation:

SQL> create or replace procedure TRIM_PASSWORD_HISTORY
  2    (
  3      p_USER_ID IN VARCHAR2
  4    )
  5    is
  6    begin
  7      DELETE FROM PASSWORD_HISTORY
  8      WHERE USER_ID = p_USER_ID
  9      AND PASSWORD_HISTORY_SEQ_NO
 10      NOT IN
 11      (
 12         SELECT 
 13          /*+ FIRST_ROWS(24) */ 
 14          PASSWORD_HISTORY_SEQ_NO
 15           FROM PASSWORD_HISTORY
 16           WHERE USER_ID = p_USER_ID
 17           ORDER BY INSERTED_DATE DESC
 18      );
 19    end;
 20  /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE TRIM_PASSWORD_HISTORY:
7/5      PL/SQL: SQL Statement ignored
17/10    PL/SQL: ORA-00907: missing right parenthesis

Section 2 -- Working code:

create or replace procedure TRIM_PASSWORD_HISTORY
  (
    p_USER_ID IN VARCHAR2
  )
  is
  begin
    DELETE FROM PASSWORD_HISTORY
    WHERE USER_ID = p_USER_ID
    AND PASSWORD_HISTORY_SEQ_NO
    NOT IN
    (
       SELECT 
        /*+ FIRST_ROWS(24) */ 
        PASSWORD_HISTORY_SEQ_NO
         FROM PASSWORD_HISTORY
         WHERE USER_ID = p_USER_ID
         -- ORDER BY INSERTED_DATE DESC
    );
  end;
/

Procedure created.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[BoulderBum]

Thanks, Dave.

Unfortunately the proc will bomb (as far as functionality goes) without the ORDER BY clause because I want to delete only the oldest entries from the history.

Do you know a way I can do what I want in PL/SQL?

For that matter, can anyone tell me how to execute this in SQL Plus:

SELECT PASSWORD_HISTORY_SEQ_NO
FROM PASSWORD_HISTORY

...where all I want is a sort of report of everything?

Trying using PL/SQL says I need to SELECT the result INTO something, though I haven't quite figured out what.

Sorry for the silly questions. I didn't know the implementations of the different DBMSes would be so fundamentally different (don't ask me how long it took me to figure out how to enter a date in a table properly... "09-Jan-2005", not "1/9/2005", arrggg!).

 

[BoulderBum]

PS- Nevermind the last question I guess.

Running:

SELECT PASSWORD_HISTORY_SEQ_NO
FROM PASSWORD_HISTORY

...originally produced no results, but I figured out that it was because I had extra line breaks after the statement (though if someone has an explanation for that behavior, it would be a nice fact to know).

 

[SantaMufasa]

Boulder,

Working in reverse order on your questions:

1) Line breaks: SQL*Plus does not allow blank lines in your SQL code before the end of the SQL statement. But where you said

"...I had extra line breaks after the statement...

I'm not certain if we are talking about the same "extra lines". If I am mistaken, then please post your failing code and the accompanying error message.

2) Date-input formats: You are not limited to date input formats of "09-Jan-2005". You can use any format you want, but the way you make that happen is:

INSERT INTO <table_name>
    VALUES (...,to_date('2005-04-11 16:35:33','YYYY-MM-DD HH24:MI:SS'),...);

3) Obtaining just a certain number of rows in sorted order: Can you please confirm how many sorted rows you want from your sub-query? What behaviour do you expect from

.../*+ FIRST_ROWS(24) */...

Looking forward to your response,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[BoulderBum]

Thanks again, Dave.

1) It was just extra blank lines after everything. There wasn't any error message, but it didn't do anything when I hit "execute".

2) Sweet. I'll have to study that a bit to hone the formats. Thanks!

3) Basically I'm hoping to keep the last 24 password history entries, then delete any older than that.

In T-SQL I accomplished this by checking against a collection of key entries derived by selecting the "TOP" 24 newest records (where newest is found by sorting by INSERTED_DATE). In my googling I learned that "FIRST_ROWS" is supposed to be the PL/SQL equivalent of TOP apparently so I was trying to use it.

 

[SantaMufasa]

Ah-ha! Then if that's what you want to do, try this code:

create or replace procedure TRIM_PASSWORD_HISTORY
  (
    p_USER_ID IN VARCHAR2
  )
  is
  begin
    DELETE FROM PASSWORD_HISTORY
    WHERE USER_ID = p_USER_ID
    AND PASSWORD_HISTORY_SEQ_NO
    NOT IN
    (select PASSWORD_HISTORY_SEQ_NO
       from (SELECT
        PASSWORD_HISTORY_SEQ_NO
         FROM PASSWORD_HISTORY
         WHERE USER_ID = p_USER_ID
         ORDER BY INSERTED_DATE DESC)
      where rownum <= 24
    );
  end;
/

Procedure created.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[BoulderBum]

Looks good to me!

Thanks again for all your help, man.