Home » SQL & PL/SQL » SQL & PL/SQL » Oracle SQL questions (Oracle 10g)
icon5.gif  Oracle SQL questions [message #385725] Tue, 10 February 2009 17:25 Go to next message
Icebergue
Messages: 2
Registered: February 2009
Junior Member
Hello,

I've been making some functions and procedures in PL /SQL lately and since most of my background is with T-SQL I noticed some differences in which I need help.

1 - The first thing I noticed is that returning no rows is not the same as returning null as in SQL Server.

This raises a problem when I am inserting data into a variable because it triggers a "no data found" error, that from what I read can be caught using an exception block like this:

exception
when no_data_found then
do something;

So lets imagine that I have 10 variables in which i want to insert a result of a query, and they are executed in a sequence like:

Select <table column> INTO variable_1
From <table name>;

Select <table column> INTO variable_2
From <table name>;

.
.
Select <table column> INTO variable_10
From <table name>;

What is the most reliable option to do so that all the queries are executed even if there is no data found in one of the queries, so I can reach the end of the procedure without any error message?

2 - Since the select statement is very similar is there any way that I can generate dynamic SQL in a cycle so that the variable name can be created in execution time? Check this example:

FOR index IN 1..10
LOOP
SELECT <table column> into variable_ || index -- I try here to dynamically insert into variable_x , where x is the index
FROM <table name>
WHERE id = index;
END LOOP;

Get the idea? Smile

Thank you in advance,

Regards
Re: Oracle SQL questions [message #385726 is a reply to message #385725] Tue, 10 February 2009 17:28 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Oracle SQL questions [message #385727 is a reply to message #385726] Tue, 10 February 2009 17:56 Go to previous messageGo to next message
Icebergue
Messages: 2
Registered: February 2009
Junior Member
-- Create table
CREATE TABLE person(
ID NUMBER NOT NULL,
NAME VARCHAR2(100) NOT NULL,
NATIONALITY VARCHAR2(20) NOT NULL);

-- Inserting some data
INSERT INTO person (ID, NAME, NATIONALITY)
VALUES( 1, 'Anna', 'Spanish');

INSERT INTO person (ID, NAME, NATIONALITY)
VALUES( 2, 'Pierre', 'French');

INSERT INTO person (ID, NAME, NATIONALITY)
VALUES( 3, 'Michael', 'German');

commit;

-- Procedure for example
CREATE OR REPLACE FUNCTION GET_NATIONALITIES
RETURN SYS_REFCURSOR
IS
v_cursor sys_refcursor;
nationality_1 varchar2(50);
nationality_2 varchar2(50);
nationality_3 varchar2(50);
BEGIN

FOR counter IN 1..3
LOOP
-- I want to create the query dinamically so that I insert in each iteration in the variable X where X is the counter of the loop
-- I also want that the procedure reaches the end without giving the message no data found that could occur if I deleted one record from "person" table

SELECT nationality INTO nationality_ || counter
FROM person
where ID = counter;
END LOOP;

OPEN v_cursor FOR

SELECT nationality_1 AS N1,
nationality_2 AS N2,
nationality_3 AS N3
FROM DUAL;

RETURN v_cursor;

END GET_NATIONALITIES;
Re: Oracle SQL questions [message #385730 is a reply to message #385725] Tue, 10 February 2009 19:06 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>SELECT nationality_1 AS N1, nationality_2 AS N2, nationality_3 S N3 FROM DUAL;

What is this broken SQL supposed to do?

What problem are you really trying to solve?




Re: Oracle SQL questions [message #385785 is a reply to message #385725] Wed, 11 February 2009 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select col1, col2, col3 into v1, v2, v3 from mytable

Regards
Michel
Re: Oracle SQL questions [message #385789 is a reply to message #385725] Wed, 11 February 2009 01:05 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
To the questions:
1. You shall enclose each SELECT statement into BEGIN EXCEPTION END block to handle NO_DATA_FOUND exception separately.

2. Not possible with local variables.
Possible with variables declared in package specification.
Possible with local collection variables (TABLE OF, VARRAY).

To the example:
There is a big problem there - as ID is not unique (regarding the CREATE TABLE statement), what do you expect to return for multiple rows with the same ID?
I do not know, what T-SQL would do in this case, but Oracle would raise TOO_MANY_ROWS exception.

In case of its uniqueness, there is no need for any variables, it is a simple pivot query (thank you for the testcase):
SQL> SELECT MAX(CASE WHEN id=1 THEN nationality END) AS n1,
  2         MAX(CASE WHEN id=2 THEN nationality END) AS n2,
  3         MAX(CASE WHEN id=3 THEN nationality END) AS n3
  4  FROM person
  5  WHERE id in (1, 2, 3);

N1                   N2                   N3
-------------------- -------------------- --------------------
Spanish              French               German

SQL> 


[Edit: Added remark about TOO_MANY_ROWS]

[Updated on: Wed, 11 February 2009 01:17]

Report message to a moderator

Re: Oracle SQL questions [message #385949 is a reply to message #385725] Thu, 12 February 2009 00:02 Go to previous messageGo to next message
simi_ghy
Messages: 18
Registered: July 2005
Junior Member
you can either use separate exception blocks for each select. Or use NVL to have a value stored in the variable so that no_date_found exception is not raised at all...
Re: Oracle SQL questions [message #385983 is a reply to message #385949] Thu, 12 February 2009 02:31 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
simi_ghy wrote on Thu, 12 February 2009 07:02
you can either use separate exception blocks for each select. Or use NVL to have a value stored in the variable so that no_date_found exception is not raised at all...

Maybe I did not understand you, but NVL alone does not avoid raising exception. Did you try that?
SQL> declare
  2    nat1 varchar2(50);
  3  begin
  4    select nvl( nationality, 0 ) into nat1
  5    from person
  6    where id = 4;
  7    dbms_output.put_line( 'nat1 = <'||nat1||'>' );
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

Or you meant using subquery in SELECT statement? However you do not need NVL for this:
SQL> declare
  2    nat1 varchar2(50);
  3  begin
  4    select (select nationality
  5            from person
  6            where id = 4) into nat1
  7    from dual;
  8    dbms_output.put_line( 'nat1 = <'||nat1||'>' );
  9  end;
 10  /
nat1 = <>

PL/SQL procedure successfully completed.

I find the second approach less handsome than handling NO_DATA_FOUND exception. Also, there may be performance issues in large SELECT statements.
Re: Oracle SQL questions [message #385987 is a reply to message #385983] Thu, 12 February 2009 02:49 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Another option would be: use an NVL / Aggregate function construct when you can't be sure that there is one and only one value returned.

SQL> DECLARE
  2  v_var VARCHAR2(10);
  3  BEGIN
  4  SELECT dummy
  5    INTO v_var
  6    FROM dual WHERE 1 = 2;
  7  END;
  8  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4


SQL>
SQL>
SQL>
SQL> DECLARE
  2  v_var VARCHAR2(10);
  3  BEGIN
  4  SELECT Nvl(Max(dummy),'-')
  5    INTO v_var
  6    FROM dual WHERE 1 = 2;
  7  END;
  8  /

PL/SQL procedure successfully completed.


That way you also avoid any possible "ORA-01422: exact fetch returns more than requested number of rows" errors.
Re: Oracle SQL questions [message #386000 is a reply to message #385987] Thu, 12 February 2009 03:24 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
ThomasG wrote on Thu, 12 February 2009 09:49
Another option would be: use an NVL / Aggregate function construct when you can't be sure that there is one and only one value returned.

I do not see any reason to use NVL as the query returns NULL without it. And, if I understood this note from the very first post:
Quote:
The first thing I noticed is that returning no rows is not the same as returning null as in SQL Server
correctly, then the query is expected to return NULL.
Re: Oracle SQL questions [message #386003 is a reply to message #386000] Thu, 12 February 2009 03:39 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Something no-one's mentioned is using explicit cursors.
Now, I know that they're deeply unfashionable, and soooo Oracle 7.3.4, but they are a very simple solution to the problem of fetching a single row that might not exist:
DECLARE
  cursor c_demo (p_value  in  number)is
    SELECT column_1,column_2
    FROM   table
    WHERE  column_3 = p_value;

  v_value_1  table.column_1%type;
  v_value_2  table.column_2%type;
BEGIN
  OPEN c_demo(stuff);
  FETCH c_demo INTO v_value_1,v_value_2;
  <Check c_demo%found if you want to see if a row was fetched>
  CLOSE c_demo;
END;


More details can be found here, and from the links at the bottom of the page
Previous Topic: Maximum jobs that can be submitted
Next Topic: Help me Please
Goto Forum:
  


Current Time: Wed Dec 07 08:47:29 CST 2016

Total time taken to generate the page: 0.09896 seconds