Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Newb: What's Wrong With This Syntax? 1

Status
Not open for further replies.

BoulderBum

Programmer
Jul 11, 2002
2,179
US
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:

Code:
 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:

Code:
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?

 
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:
Code:
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:
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.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.
 
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:

Code:
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!).
 
PS- Nevermind the last question I guess.

Running:

Code:
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).
 
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
Boulder 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:
Code:
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
Code:
.../*+ FIRST_ROWS(24) */...

Looking forward to your response,

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.
 
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.
 
Ah-ha! Then if that's what you want to do, try this code:
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.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)

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

Thanks again for all your help, man.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top