problem with temporary table with procedure

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Wed, 11 Jan 2012 10:06 am

Hello, guys.

I have the following problem: I am creating the temporary table, but when compiling the procedure the Oracle reports that the same does not exist, insert line.
Code: Select all
create or replace procedure Carga (arquivo varchar2)
as
  V_LINHA VARCHAR2(8000) := '';
  V_ARQUIVO UTL_FILE.FILE_TYPE;
BEGIN
  EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE TEMP_TABELA ( VALORES VARCHAR(8000) ) ON COMMIT DELETE ROWS;';
  V_ARQUIVO := UTL_FILE.FOPEN('ARQUIVO', 'ENTRADA.TXT', 'R');
  LOOP
    Begin
      UTL_FILE.GET_LINE(V_ARQUIVO, V_LINHA);
      insert into TEMP_TABELA values (V_LINHA);
     Exception
     when no_data_found then
      UTL_FILE.FCLOSE(V_ARQUIVO);
      exit;
    End;
  END LOOP;
END; 
how to solve?
rsalbano
Location: Curitiba

Poston Wed, 11 Jan 2012 10:19 am

Shows the table code, can be the problem and not on Procedure.
arthae
Location: Porto Alegre - RS

GIVE ME COFFEE AND NO ONE GETS HURT!

Poston Wed, 11 Jan 2012 10:22 am

[quote = " arthae "]Shows the table code, can be the problem and not on Procedure.[/quote]
Now that I'vê seen the code right ... What a mistake he has?
arthae
Location: Porto Alegre - RS

GIVE ME COFFEE AND NO ONE GETS HURT!

Poston Wed, 11 Jan 2012 10:26 am

On line: insert into TEMP_TABELA values (V_LINHA);

Presents the following errors:-statement ignored-table or view does not exist
rsalbano
Location: Curitiba

Poston Wed, 11 Jan 2012 10:40 am

Look man I think you lack a commit to create the table.
And I would put the insert table field too, for example, insert into temp_table (values) values (blablabla)
arthae
Location: Porto Alegre - RS

GIVE ME COFFEE AND NO ONE GETS HURT!

Poston Wed, 11 Jan 2012 10:51 am

Hello, I put the commit, both in procedure (after table creation), and execute immediate command.
I also put the name of the field in the row of the insert, as you suggested.

Nothing, still with error on compiling.
rsalbano
Location: Curitiba

Poston Wed, 11 Jan 2012 1:16 pm

Hello, I put all the commands that use the temporary table within the execute immediate.
Ex: the insert: execute immediate ' insert into TEMP_TABELA values (V_LINHA); ';

Thanks.
rsalbano
Location: Curitiba

Poston Wed, 11 Jan 2012 1:20 pm

Hi, Check that the error is on line of table creation:
Code: Select all
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE TEMP_TABELA2 ( VALORES VARCHAR(8000) ) ON COMMIT DELETE ROWS';
you need the table to be created at runtime?
see the topic viewtopic.php?t=4956
RafaelFantin

Poston Fri, 13 Jan 2012 12:41 pm

Brother, Like Rafael said, your mistake is probably in "; " within the string table creation.

A tip: You will run this script more than once? Table creation in the second execution will cause error.
You cannot create this table in the definitely (as temp), once?

If you can't, make a check in your script in the data dictionary, to see if the table already exists, and only creating it if it does not exist.
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP


  • See also
    Replies
    Views
    Last Post


      Return to PL/SQL

      Who is online

      Users browsing this forum: Google [Bot] and 5 guests