Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2009
    Posts
    4

    Unanswered: Noob Question: SELECT from multiple tables

    I'm trying to write an ET&L process to pull from Oracle (I'm a MS SQL Server guy), and I'm trying to write a little code to help with data validation, but this has me totally dumbfounded.

    Here's what it looks like in MS SQL

    declare @tableName varchar(50),
    , @startTime datetime
    , @endTime datetime
    set @tableName = 'DUAL'
    set @startTime = 'Mar 1 2009'
    set @endTime = 'Mar 1 2009'

    select * from ALL_TABLES
    where TABLE_NAME = @storeId
    and LAST_ANALYZED between @startTime and @endTime

    select * from ALL_TAB_COLS
    where TABLE_NAME = @storeId
    and LAST_ANALYZED between @startTime and @endTime

    go


    The queries are actually more complex than this (some are over 100 lines long), but this gets the point across.

    I've tried bind variables and dynamic sql, but everything I put together either returns nothing or gives me an error. All the examples I find either don't work or are about inserting data, not fetching data.

    Another group owns the server and database, so I don't have permission to create procedures, functions, packages, etc. I just have the bare minimum to see the data.

    I just need it dumped out to the screen (well, PL/SQL Developer output) so I can save it, export it to excel, etc. Just to do data validation

    Any help is appreciated.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I just need it dumped out to the screen (well, PL/SQL Developer output) so I can save it
    While you may know exactly what "it" is, I certainly don't know.

    >export it to excel,
    Do you realize that via ODBC, EXCEL can directly access data residing in Oracle tables?

    Please realize that what I described immediately above is entirely a configuration & execution issue for EXCEL & has nothing to do directly with Oracle.
    Oracle simply processes the requests from the EXCEL client, not knowing or caring that the SQL originated from the EXCEL application.

    If you insist on dumping the Oracle data to flat file,
    I suggest you visit http://asktom.oracle.com
    & do some keyword searches.
    Many, many fine coding examples are contained in this site.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking THree cheers for SQL Developer

    If you need to query tables and "dump" the data to flat files (or excel) try using SQL Developer (free from Oracle).
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I'm a bit surprised that you are not getting syntax errors when you run that, because your example looks like a T/SQL, it is definitely not PL/SQL.

    Oracle does not use the @ character for variables:
    http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/fundamentals.htm#CIHGGIAH

    You don't use SET to assign a value to a variable:
    http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/fundamentals.htm#i16001

  5. #5
    Join Date
    Apr 2009
    Posts
    4

    Noob Question: SELECT from multiple tables

    I guess I wasn't clear.

    That SQL example is what I do in Microsoft Sql Server to retrieve a bunch of rows from different tables with a common key. Maybe this is better:

    declare @storeId int,
    , @startTime datetime
    , @endTime datetime
    set @storeId=12345
    set @startTime = 'Mar 1 2009'
    set @endTime = 'Mar 1 2009'

    select * from Sales
    where StoreId = @storeId
    and SaleDate between @startTime and @endTime

    select * from Purchases
    where StoreId = @storeId
    and PurchaseDate between @startTime and @endTime

    go

    I'm just looking for a way to do the same query in Oracle

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    repeating the question does not change the answers
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Apr 2009
    Posts
    4
    I use a tool called "PL/SQL Developer" by Allround Automations. So I can already query the database with fixed sql statements. I just want to parameterize the query, but everything I tried (for several hours now) doesn't work.

    How do I make this query work:

    DECLARE
    table_name VARCHAR2(20) NOT NULL := 'STORES';
    BEGIN
    SELECT * FROM ALL_TABLES WHERE TABLE_NAME := table_name;
    SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME := table_name;
    END;

    Thanks!

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I, for one, am not a mind reader.
    > but everything I tried (for several hours now) doesn't work.
    >How do I make this query work:

    While you may (think you) know what you want as output, I am unclear EXACTLY what your desire to be the results.

    Keep in mind "PL/SQL Developer" output only text files.
    Data types other than strings will be converted & outputted as text/strings.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    How do I make this query work
    You have the concept of variables completely wrong.

    := is used to assign a value to a variable in a PL/SQL expression

    Inside the SELECT statement you don't want to assign a value you want to compare two values.
    So you need to use the = sign instead of :=

    SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = table_name;

    And to be more precise: the SELECT statement is a SQL statement, whereas a variable assignment is a PL/SQL expression.

  10. #10
    Join Date
    Feb 2005
    Posts
    57
    This might help you get started:
    Code:
    VARIABLE tblname varchar2(50)
    
    BEGIN
      :tblname := 'DUAL';
    END;
    /
    
    SELECT *
    FROM   all_tables
    WHERE  table_name = :tblname;
    hth

  11. #11
    Join Date
    Mar 2009
    Location
    New Delhi - India
    Posts
    22
    USE DYNAMIC SQL AS FOLLOWS

    CREATE OR REPLACE PROCEDURE ALL IS (TABLE_NAME IN VARCHAR2)
    BEGIN
    EXECUTE IMMEDIATE 'SELECT * FROM ALL_TABLES WHERE TABLE_NAME = '||TABLE_NAME ;
    EXECUTE IMMEDIATE 'SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME= '||TABLE_NAME ;
    END;



    This code will parameterize your query.

  12. #12
    Join Date
    Apr 2009
    Posts
    4
    Outrider and manik019, that's what I'm looking for. Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •