Home » SQL & PL/SQL » SQL & PL/SQL » Spilliting row (Oracle 10G)
Spilliting row [message #408645] Wed, 17 June 2009 03:48 Go to next message
someswar1
Messages: 53
Registered: January 2008
Member
Hi,
I need to pass multiple table names('emp,dept') as parameters to a PL/SQL program and check each table count.If any table count=0 then it sould return unsuccessfull otherwise its return successfull .

Eg. If two table is emp and dept.

sp pass the value as 'emp,dept' and check both emp dept tables count.

Please Help.

Regards
Someswar
Re: Spilliting row [message #408649 is a reply to message #408645] Wed, 17 June 2009 04:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Please make clear in what part you need help.
Re: Spilliting row [message #408651 is a reply to message #408649] Wed, 17 June 2009 04:13 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Ouch, this seems like a very bad design. If it is to be a stored procedure you'd want to call it multiple times. But it is your environment.

1. You have ONE input parameter: a comma separated list of tables. Is that correct?
2. If there is AT LEAST ONE table that doesn't have any records, the result is 'unsuccessful'. Is that correct?

MHE
Re: Spilliting row [message #408652 is a reply to message #408651] Wed, 17 June 2009 04:15 Go to previous messageGo to next message
someswar1
Messages: 53
Registered: January 2008
Member
Ya you are right Maaher.
Re: Spilliting row [message #408653 is a reply to message #408645] Wed, 17 June 2009 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Standard "varying inlist" question.
Please search on the board.

Regards
Michel
Re: Spilliting row [message #408657 is a reply to message #408653] Wed, 17 June 2009 04:39 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Apart from Michel's comment I think you make a basic error: You don't want to count. You 're not interested in the number of rows. You just want to know if there is at least ONE record. That's it. Counting records can be quite time consuming while fetching a single row might be a lot faster.

As for the splitting part, there are tons of examples. This is one.

MHE
Re: Spilliting row [message #408662 is a reply to message #408657] Wed, 17 June 2009 04:44 Go to previous messageGo to next message
someswar1
Messages: 53
Registered: January 2008
Member
Ya I just want to know if there is at least ONE record r exist in the table.But after spliting I am not able to check the count of at least one row.

Please help.
Re: Spilliting row [message #408664 is a reply to message #408662] Wed, 17 June 2009 04:47 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
It's not that hard:

Open a ref cursor.
Fetch one row.
Check whether the row was found (%FOUND)
Close the ref cursor

MHE
Re: Spilliting row [message #408665 is a reply to message #408664] Wed, 17 June 2009 04:50 Go to previous messageGo to next message
someswar1
Messages: 53
Registered: January 2008
Member
If u have not any prob problem please give me a example.
Re: Spilliting row [message #408667 is a reply to message #408665] Wed, 17 June 2009 04:56 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I'll give you a template:

FUNCTION check_records( p_table_list IN VARCHAR2)
...
declarations --> recur type, refcur var, local variables, ...
...
IS
...
   FOR table_rec IN ( <split sql like in my link> )
   LOOP
      OPEN refcur FOR 'SELECT rownum FROM '||table_rec.table_name;
      FETCH refcur INTO v_dummy;
      IF v_refcur%NOTFOUND;
        v_return := 'Unsuccessful'
      END IF;
      CLOSE refcur;
      
      EXIT WHEN v_return = 'Unsuccessful';
   END LOOP;
...   
   RETURN v_return;
END;

MHE
Re: Spilliting row [message #408673 is a reply to message #408667] Wed, 17 June 2009 05:12 Go to previous messageGo to next message
someswar1
Messages: 53
Registered: January 2008
Member
I have wriiten the code that way. But the code gives the error

create or replace FUNCTION check_records( p_table_list IN VARCHAR2)
return varchar2

IS
v_return varchar2(20);
v_dummy varchar2(300);

type rc is ref cursor;
c rc;

begin

FOR table_rec IN ( <split sql like in my link> )
LOOP
OPEN c FOR 'SELECT rownum FROM '||table_rec.table_name;
FETCH c INTO v_dummy;

IF v_refcur%NOTFOUND;
v_return := 'Unsuccessful'
END IF;
CLOSE c;

EXIT WHEN v_return = 'Unsuccessful';
END LOOP;

RETURN v_return;
END;
Re: Spilliting row [message #408677 is a reply to message #408673] Wed, 17 June 2009 05:23 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Rule #1: Always specify WHAT error you get.
Don't forget to add the proper SQL in the for loop. Wink

MHE
Re: Spilliting row [message #408682 is a reply to message #408677] Wed, 17 June 2009 05:28 Go to previous messageGo to next message
someswar1
Messages: 53
Registered: January 2008
Member
I have got this error..

PLS-00103: Encountered the symbol ";" when expecting one of the following:

. ( * @ % & = - + < / > at in is mod remainder not rem then
<an exponent (**)> <> or != or ~= >= <= <> and or like LIK
Re: Spilliting row [message #408685 is a reply to message #408682] Wed, 17 June 2009 05:32 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I told you: it was a template, not finished code. And now that I look at it, it was a bad template too: The IF inside the loop doesn't contain a 'THEN' and I seem to have forgotten a semicolon too somewhere. I'm pretty sure you'll figure this one out yourself.

MHE
Re: Spilliting row [message #408693 is a reply to message #408673] Wed, 17 June 2009 05:59 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: Creating dynamic table
Next Topic: insert using Case When
Goto Forum:
  


Current Time: Sat Dec 03 07:48:40 CST 2016

Total time taken to generate the page: 0.09720 seconds