1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Temporary table in pl/sql sqldeveloper

Discussion in 'SQL PL/SQL' started by Girishr369, Feb 15, 2017.

  1. Girishr369

    Girishr369 Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    hyderabad
    Hi Team,

    can any one suggest on TEMP table concept. I have below requirement and I tried the below but I am getting error.

    select partyno into #tmptbl
    from partym
    --------- when I executed this I got ORA-00911: invalid character error.

    my requirement is :
    I need to execute 3 queries one by one and hold the result of the first query and second query to feed as input to 3rd query. at the end of the job I need to delete first query and second query result.


    Thanks,
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,533
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I am wondering where you found that command since it's invalid in Oracle if you don't already have a temp table named #tmptbl. Also such special characters need to be quoted:

    select partyno into "#tmptbl" from ...

    to allow Oracle to process such special characters. What you need to do is this:

    create table tmptbl as select partyno from partym;

    Such "select into" syntax is only good for variables you create within the PL/SQL block:

    declare
    v_tmptbl number;
    begin
    select partyno into v_tmptbl from partym where ...;
    ...

    Otherwise you'd use a for loop to process the results:

    begin
    for tmptbl in (select partyno from partym) loop
    [do processing here]
    end loop;
    end;
    /

    SQL Server syntax, as mentioned before, does not work with Oracle.
     
  3. Girishr369

    Girishr369 Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    hyderabad
     
  4. Girishr369

    Girishr369 Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    hyderabad

    Thank you David Fitzjarrell, i will try and let you know
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    734
    Likes Received:
    144
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.
    This is the T-SQL syntax for MS-SQL.
    Oracle not supported T-SQL.
    What do you want execute ?A heterogeneous query ?
    You can use : DBLINK,DBMS_HS_PASSTHROUGH.
    Please,specify ,what are the three query and on which database?
     
  6. Girishr369

    Girishr369 Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    hyderabad

    hi ,


    earlier we used SEQUEL tool to build reports from AS/400.data base is DB2.
    There we wrote the individual queries in SEQUELVIEWS and execute one by one and transfer the last view output to XLSX.
    ex :if i need to execute 5 views , i will hold 4 views result in TEMP tables(which will gone once the session closed) and generate the final output in the 5th view .
    we are able to pass the parameters also there.

    Now my application s migrated to Oracle and we need to execute those queries in ORACLE.(need pass parameters where ever it is required).
    so we are in the process of identify the option in oracle to hold the result in TEMP table and used in the further queries.

    Thanks