Home » SQL & PL/SQL » SQL & PL/SQL » sql script issue.. Please help
sql script issue.. Please help [message #358048] Fri, 07 November 2008 23:59 Go to next message
pinki1986
Messages: 4
Registered: November 2008
Location: India
Junior Member
Hi,

I am new to SQL scripting. I wrote my script, but i dont know how to handle the below situations:

1. If my query (select * from table_a) returns no rows then.. i need to display the no of rows in table table_b (select count(*).... )

2. If the veiw/table (table_a) doesnot exist (in few databases the table doesnot exist) then i need to supress this error (ORA - 00942) only and again.... display the count of table table_c

Please help me.. Its URGENT..

Re: sql script issue.. Please help [message #358051 is a reply to message #358048] Sat, 08 November 2008 00:46 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What language are you using for the script?

Ross Leishman

P.S. We don't do urgent here. If you need help urgently, go to the Marketplace forum and hire a consultant. Offer around US$1000/dy and you should get you some urgent assistance.
Re: sql script issue.. Please help [message #358052 is a reply to message #358051] Sat, 08 November 2008 00:59 Go to previous messageGo to next message
pinki1986
Messages: 4
Registered: November 2008
Location: India
Junior Member
hi..
i am writing an sql script.. i think i have mentioned that clearly in my prev post..

( i just wanted a help {as i cant afford to pay tat much}..and i need to submit the script today...so it was jus a request.. not demand .. if you can please) reply)
Re: sql script issue.. Please help [message #358057 is a reply to message #358052] Sat, 08 November 2008 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As soon as you (completly) read OraFAQ Forum Guide, agree with it and post a test case and full version number as requested, you will have a quick and complete answer.

Regards
Michel
Re: sql script issue.. Please help [message #358058 is a reply to message #358057] Sat, 08 November 2008 02:14 Go to previous messageGo to next message
pinki1986
Messages: 4
Registered: November 2008
Location: India
Junior Member
Sir,
Please help me quickly through it..i am running short of time(for that i req for a faster reply . those who can).I had posted my question with a hope that i wil get a solution. I have been doing a google search but not finding a good answer. I have posted my full problem in my first post..is it not clear enough???


{
Please BE POLITE also, if you can please help... or else at least dont be rude or mock by posting such replies

" We don't do urgent here. If you need help urgently, go to the Marketplace forum and hire a consultant. Offer around US$1000/dy and you should get you some urgent assistance "


it may really stop some freshers from posting wat we lik to.there is nothing wrong in the way i have posted..If u want reply or else just leave it.
Re: sql script issue.. Please help [message #358063 is a reply to message #358058] Sat, 08 November 2008 03:05 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SQL is not a scripting language. A SQL is a single transaction, not a script.

Furthermore, you cannot make a SQL statement magically materialise within Oracle - you have to execute it via a client (eg. SQL*Plus), or a language (eg. PL/SQL, Java).

You are asking for something that transcends a single statement.
You call it a script.
You cannot execute SQL without some form of client or wrapper.
Don't you think it's just a little reasonable to ask which client/language you are using as it may be able to help you achieve your goal?

>>or else at least dont be rude

I was trying to convey how people who offer their expert consultancy free of charge feel when you imply that your problem is more important than whatever they are currently working on. I think I put it very politely; go to ANY I.T. forum - including this one - and run a search of the word URGENT. If I offend you, brace yourself for the replies you read and be thankful I got there first.

You cannot do what you are trying to do in a single SQL statement; it needs a language that handles multiple statements, conditional logic, and error trapping. Tell us what your front-end is and we may be able to help.

Ross Leishman
Re: sql script issue.. Please help [message #358064 is a reply to message #358058] Sat, 08 November 2008 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
[...]from posting wat we lik to.there is nothing wrong in the way i have posted..If u want [...]

What's wrong (not exhaustive list):
- you use IM speak
- you use "URGENT"
- you don't post your Oracle version
- you don't post a test case
- you don't answer to questions we asked you
...

rleishman
If I offend you, brace yourself for the replies you read and be thankful I got there first.

Yes, Ross is right, you've been lucky I was not the first one.

Regards
Michel

[Updated on: Sat, 08 November 2008 03:13]

Report message to a moderator

Re: sql script issue.. Please help [message #358071 is a reply to message #358063] Sat, 08 November 2008 03:25 Go to previous messageGo to next message
pinki1986
Messages: 4
Registered: November 2008
Location: India
Junior Member
Thank you for the reply.

I am using a .sh file and wring some sql commands in it and executing using the SQL PLUS.
Re: sql script issue.. Please help [message #358085 is a reply to message #358071] Sat, 08 November 2008 06:42 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
OK. So SQL can't do it; we've established that (and I don't want to hear from any smart-alecs with double-executing UNION / NOT-EXISTS crap either)

SQL*Plus is not so much a scripting language as a formatting language. It has no conditional structures and only the most rudimentary error handling. It's no good either.

Shell script IS a scripting language, but it deals in operating system commands, not database commands. Technically you can apply conditional logic, but only by opening a new SQL*Plus session for each statement because it has no native database connectivity. I've seen it done, but it's rubbish.

If you're using shell script, then you're on Unix; if you're on Unix, you have Perl. If you're on OraFAQ, you have Internet; if you have Internet, you have http://www.cpan.org.

Download DBI and DBD::Oracle packages from CPAN. Install them, and you have a REAL scripting language with real database connectivity.

Of course, this cannot be done quickly. And quickly is not the right way to embark on new technology directions either. So none of the above is good advice for your current problem, but next time you will know before it is too late.

Now it's time to tell your boss that you need to
a) Adapt the requirements because the current technology is inadequate, or;
b) Adapt the technology (and the timeframe) because the requirements are too sophisticated.

Ross Leishman
Re: sql script issue.. Please help [message #358124 is a reply to message #358048] Sat, 08 November 2008 16:30 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
This could be done in one SQL script executed from SQL*Plus that declares a refcursor variable, runs an anonymous PL/SQL block that opens the refcursor, then prints the resulting contents of the refcursor. You can select from the all_tables data dictionary view to determine whether a table exists or not and you can select count(*) from a table to determine whether a table contains rows or not. You can then use the results of these queries to determine which table to select from. You will need to use dynamic sql when selecting from tables that may or may not exist in order to avoid compilation errors. Please see the demonstration below.

-- contents of SQL script:
VARIABLE g_results REFCURSOR
DECLARE
  v_count NUMBER;
BEGIN
  -- check whether table_a existss:
  SELECT COUNT(*)
  INTO   v_count
  FROM   all_tables
  WHERE  table_name = 'TABLE_A';
  IF v_count > 0 THEN -- table_a exists:
    -- check whether table_a contains rows:
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM table_a' INTO v_count ;
    IF v_count > 0 THEN -- table_a contains rows:
      OPEN :g_results FOR 'SELECT * FROM table_a';
    ELSE -- table_a is empty:
      OPEN :g_results FOR SELECT COUNT(*) FROM table_b;
    END IF;
  ELSE -- table_a does not exist:
    OPEN :g_results FOR SELECT COUNT(*) FROM table_c; 
  END IF;
END;
/
PRINT g_results


-- tables for testing:
SCOTT@orcl_11g> CREATE TABLE table_a AS SELECT * FROM DUAL
  2  /

Table created.

SCOTT@orcl_11g> CREATE TABLE table_b AS SELECT * FROM DUAL CONNECT BY LEVEL <= 2
  2  /

Table created.

SCOTT@orcl_11g> CREATE TABLE table_C AS SELECT * FROM DUAL CONNECT BY LEVEL <= 3
  2  /

Table created.


-- case when table_a exists and contains rows:
SCOTT@orcl_11g> VARIABLE g_results REFCURSOR
SCOTT@orcl_11g> DECLARE
  2    v_count NUMBER;
  3  BEGIN
  4    -- check whether table_a existss:
  5    SELECT COUNT(*)
  6    INTO   v_count
  7    FROM   all_tables
  8    WHERE  table_name = 'TABLE_A';
  9    IF v_count > 0 THEN -- table_a exists:
 10  	 -- check whether table_a contains rows:
 11  	 EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM table_a' INTO v_count ;
 12  	 IF v_count > 0 THEN -- table_a contains rows:
 13  	   OPEN :g_results FOR 'SELECT * FROM table_a';
 14  	 ELSE -- table_a is empty:
 15  	   OPEN :g_results FOR SELECT COUNT(*) FROM table_b;
 16  	 END IF;
 17    ELSE -- table_a does not exist:
 18  	 OPEN :g_results FOR SELECT COUNT(*) FROM table_c;
 19    END IF;
 20  END;
 21  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> PRINT g_results

D
-
X


-- case when table_a is empty:
SCOTT@orcl_11g> DELETE FROM table_a
  2  /

1 row deleted.

SCOTT@orcl_11g> VARIABLE g_results REFCURSOR
SCOTT@orcl_11g> DECLARE
  2    v_count NUMBER;
  3  BEGIN
  4    -- check whether table_a existss:
  5    SELECT COUNT(*)
  6    INTO   v_count
  7    FROM   all_tables
  8    WHERE  table_name = 'TABLE_A';
  9    IF v_count > 0 THEN -- table_a exists:
 10  	 -- check whether table_a contains rows:
 11  	 EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM table_a' INTO v_count ;
 12  	 IF v_count > 0 THEN -- table_a contains rows:
 13  	   OPEN :g_results FOR 'SELECT * FROM table_a';
 14  	 ELSE -- table_a is empty:
 15  	   OPEN :g_results FOR SELECT COUNT(*) FROM table_b;
 16  	 END IF;
 17    ELSE -- table_a does not exist:
 18  	 OPEN :g_results FOR SELECT COUNT(*) FROM table_c;
 19    END IF;
 20  END;
 21  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> PRINT g_results

  COUNT(*)
----------
         2


-- case when table_a does not exist:
SCOTT@orcl_11g> DROP TABLE table_a
  2  /

Table dropped.

SCOTT@orcl_11g> VARIABLE g_results REFCURSOR
SCOTT@orcl_11g> DECLARE
  2    v_count NUMBER;
  3  BEGIN
  4    -- check whether table_a existss:
  5    SELECT COUNT(*)
  6    INTO   v_count
  7    FROM   all_tables
  8    WHERE  table_name = 'TABLE_A';
  9    IF v_count > 0 THEN -- table_a exists:
 10  	 -- check whether table_a contains rows:
 11  	 EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM table_a' INTO v_count ;
 12  	 IF v_count > 0 THEN -- table_a contains rows:
 13  	   OPEN :g_results FOR 'SELECT * FROM table_a';
 14  	 ELSE -- table_a is empty:
 15  	   OPEN :g_results FOR SELECT COUNT(*) FROM table_b;
 16  	 END IF;
 17    ELSE -- table_a does not exist:
 18  	 OPEN :g_results FOR SELECT COUNT(*) FROM table_c;
 19    END IF;
 20  END;
 21  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> PRINT g_results

  COUNT(*)
----------
         3

SCOTT@orcl_11g> 

[Updated on: Sat, 08 November 2008 16:34]

Report message to a moderator

Previous Topic: PL/SQL Procedures using time
Next Topic: Help with single-row subquery returns more than one row.
Goto Forum:
  


Current Time: Wed Dec 07 12:54:15 CST 2016

Total time taken to generate the page: 0.15533 seconds