Home » SQL & PL/SQL » SQL & PL/SQL » How can I use variables in a from_clause?
How can I use variables in a from_clause? [message #206206] Wed, 29 November 2006 03:33 Go to next message
tomboy
Messages: 23
Registered: November 2006
Location: Alessandria (Italy)
Junior Member
Hello, I need to select records from a list of tables, contained in a cursor, accessing them one by one. I add the script to better understand the matter.
________________________________________________________________
procedure ctrl_tables is
x_var varchar2(120);
v_count pls_integer;
cursor c_tables is
select ts.owner,
ft.table_name
from fnd_tables ft,
all_tables ts
where ft.table_name = ts.table_name
and ts.owner = 'AR';
begin
for record in c_tables loop
x_var := record.table_name;
-- I start with the select, but I need to insert the data in a custom table to manage them together
select owner,
table_name,
created_by,
creation_date
from table(x_var) --&&record.table_name;
--where_clause;
end loop;
end;
________________________________________________________________
I've got always the following error and I don't know how to solve it:
ORA-22905: Cannot Access Rows From a Non-Nested Table.
Thanks in advance.
Re: How can I use variables in a from_clause? [message #206234 is a reply to message #206206] Wed, 29 November 2006 04:22 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
table() can't be used in this scenario.

You need to use dynamic sql(DBMS_SQL or EXECUTE IMMEDIATE)
By
Vamsi
Re: How can I use variables in a from_clause? [message #206249 is a reply to message #206234] Wed, 29 November 2006 05:40 Go to previous messageGo to next message
tomboy
Messages: 23
Registered: November 2006
Location: Alessandria (Italy)
Junior Member
Vamsi, thanks for your answer. I tried to use "execute immediate", but I've got another error. Please see, what follows:
----------------------------------------------------------------
procedure ctrl_tables is

x_var varchar2(120);

cursor c_tables is
select ts.owner,
ft.table_name,
ft.created_by,
ft.creation_date
from fnd_tables ft,
all_tables ts
where ft.table_name = ts.table_name
and ft.table_type = 'T'
and ts.owner = 'AR';

begin
begin
select owner,
table_name,
created_by,
creation_date
from x_var
where created_by = 1055
and to_char(creation_date, 'YYYY/MM/DD HH24:MI:SS') = '2006/10/18 14.40.33';
end RUN_BT;
for record in c_tables loop
x_var := record.table_name;
-- dbms_output.put_line(to_char(x_var));
-- before select and then insert
EXECUTE IMMEDIATE 'BEGIN run_bt; END;';
end loop;
end;
----------------------------------------------------------------
The error is: "PL/SQL: ORA-00942: table or view does not exist", because it solves the table name immediately.

Thanks again, Paola
Re: How can I use variables in a from_clause? [message #206255 is a reply to message #206249] Wed, 29 November 2006 05:51 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Seems you have confused, while checking the linkEXECUTE IMMEDIATECheck the section "Demonstration Of Dynamic SQL Statement Creation"
No need to use the procedure.

By,
Vamsi
Re: How can I use variables in a from_clause? [message #206303 is a reply to message #206206] Wed, 29 November 2006 09:31 Go to previous messageGo to next message
tomboy
Messages: 23
Registered: November 2006
Location: Alessandria (Italy)
Junior Member
I've read the section "Demonstration Of Dynamic SQL Statement Creation" accurately, but I'm not able to apply it to my case. If you can, please give me an example fit for me.
I have to replicate the select for a lot of times (one for every AR table, more or less 415) changing every time the value of the table in From_clause.
Sorry for the trouble.
Thanks in advance, Paola
Re: How can I use variables in a from_clause? [message #206433 is a reply to message #206303] Thu, 30 November 2006 00:52 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Your old code
begin
for record in c_tables loop
  x_var := record.table_name;
  select owner,
         table_name,
         created_by,
         creation_date
    from table(x_var);
end loop;
end;
Here the problem is with table(x_var)
Your new code
begin
  begin
    select owner,
           table_name,
           created_by,
           creation_date
      from x_var
     where created_by = 1055
       and to_char(creation_date, 'YYYY/MM/DD HH24:MI:SS') = '2006/10/18 14.40.33';
  end RUN_BT;
  for record in c_tables loop
    x_var := record.table_name;
    EXECUTE IMMEDIATE 'BEGIN run_bt; END;';
  end loop;
end;
Here PL/SQL should contain "SELECT INTO" not the normal SELECT (if that is not a cursor).

Your code should be changed as same as the old one but use execute immediate instead of table(x_var).

EXECUTE IMMEDIATE with SELECT

By
Vamsi
Re: How can I use variables in a from_clause? [message #206491 is a reply to message #206206] Thu, 30 November 2006 05:13 Go to previous messageGo to next message
tomboy
Messages: 23
Registered: November 2006
Location: Alessandria (Italy)
Junior Member
Hello Vamsi, thanks for your help, I'm trying to apply what you suggested. I'll update with a new message as soon as possible.
Regards, Paola
Re: How can I use variables in a from_clause? [message #206552 is a reply to message #206206] Thu, 30 November 2006 08:58 Go to previous messageGo to next message
tomboy
Messages: 23
Registered: November 2006
Location: Alessandria (Italy)
Junior Member
Hello Vamsi, the situation is quite better, but the problem is not solved. Now, I can minimize the problem on row "execute immediate".
For example
"EXECUTE IMMEDIATE 'insert into mino_ctrl_tables (created_by) select 1055 from ' || x_table_name; -- || 'where created_by = ' || p_created_by;" <<-- this is OK, but it's unuseful
I did a lot (really, a lot) of tests and I discovered that I can use a constant (like 1055 in the example) but not a field_name or a variable or parameter. Is it true? Why?
Moreover, I can use only one adding part like >> ' || x_table_name; and I cannot divide the command in more than a line. Really?
Please, be patience with me... I'm going crazy!!
Thanks a lot, Paola
Re: How can I use variables in a from_clause? [message #206562 is a reply to message #206552] Thu, 30 November 2006 09:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Whatever you use instead of '1055' must be in scope inside the EXECUTE IMMEDIATE statement. In this case, it means that it either needs to be a value returned by the SELECT statement, or something passed into the EXECUTE IMMEDIATE as a bind variable.

I suspect that you want to do something like this:
EXECUTE IMMEDIATE 'insert into mino_ctrl_tables (created_by) select <some_field_name> from ' || x_table_name || 'where created_by = :b1' using <some date>;
Re: How can I use variables in a from_clause? [message #206572 is a reply to message #206206] Thu, 30 November 2006 10:20 Go to previous messageGo to next message
tomboy
Messages: 23
Registered: November 2006
Location: Alessandria (Italy)
Junior Member
Hello, you are right, I need to do what you suspect. I tried in a lot of ways but I've got an error: ORA-00904 invalid identifier.

The command line is:
EXECUTE IMMEDIATE 'insert into mino_ctrl_tables (created_by) select created_by from ' || x_table_name;
where created_by is a field of the select.

I read examples in http://www.adp-gmbh.ch/ora/plsql/exec_immediate.html and it seems to be right, but it doesn't work.

Thanks and regards, Paola
Re: How can I use variables in a from_clause? [message #206583 is a reply to message #206206] Thu, 30 November 2006 11:05 Go to previous messageGo to next message
tomboy
Messages: 23
Registered: November 2006
Location: Alessandria (Italy)
Junior Member
Sorry, I was imprecise, the exact text of invalid identifier error is "ORA-00904: "CREATED_BY": invalid indentifier.", where created_by is a field_name. It accepts constant values only.
Regards, Paola
Re: How can I use variables in a from_clause? [message #206673 is a reply to message #206583] Fri, 01 December 2006 01:14 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Cross check in which table the column created_by is not present.

As ou are getting x_table_name from the cursor and you are trying to select created_by from <x_table_name>, the column should be present in that table. Right?

By
Vamsi
Re: How can I use variables in a from_clause? [message #206692 is a reply to message #206206] Fri, 01 December 2006 02:27 Go to previous messageGo to next message
tomboy
Messages: 23
Registered: November 2006
Location: Alessandria (Italy)
Junior Member
Hello, I checked customized table and the field exists.
"Created_by" is a basic field of each table, so I think it exists in each table of x_table_name list (there are more than 400 tables, I cannot check one by one).
Moreover, I've got the error in "... select created_by ..." and not in "insert into mino_ctrl_tables (created_by) ...".
It's quite strange. Any suggestion for me?
Thanks and regards, Paola
Re: How can I use variables in a from_clause? [message #206695 is a reply to message #206692] Fri, 01 December 2006 02:43 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
"Created_by" is a basic field of each table, so I think it exists in each table of x_table_name list
Why to think?
Check that....
select ft.table_name
from fnd_tables ft,
all_tables ts
where ft.table_name = ts.table_name
and ft.table_type = 'T'
and ts.owner = 'AR'
minus
select ft.table_name
from fnd_tables ft,
all_tab_columns ts
where ft.table_name = ts.table_name
and ft.table_type = 'T'
and ts.owner = 'AR'
and ts.column_name = 'CREATED_BY';


By
Vamsi
Re: How can I use variables in a from_clause? [message #206704 is a reply to message #206206] Fri, 01 December 2006 03:15 Go to previous messageGo to next message
tomboy
Messages: 23
Registered: November 2006
Location: Alessandria (Italy)
Junior Member
Great, you're right!! There are 46 of 415 tables not having this field. It's a torment. How can I proceed? Can I join your previous suggestion to my select and check only the created_by existing tables?
Thanks, Paola
Re: How can I use variables in a from_clause? [message #206710 is a reply to message #206704] Fri, 01 December 2006 03:40 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You need to decide.
If you want to show the tables, which are having created_by as a column, they you need to do that.

Quote:
EXECUTE IMMEDIATE 'insert into mino_ctrl_tables (created_by) select created_by from ' || x_table_name;

You are inserting created_by only. What you will do with that?

By
Vamsi.
Re: How can I use variables in a from_clause? [message #206743 is a reply to message #206206] Fri, 01 December 2006 05:37 Go to previous messageGo to next message
tomboy
Messages: 23
Registered: November 2006
Location: Alessandria (Italy)
Junior Member
Hello, I restricted the select choosing only the tables with "created_by" field. Now, I'm testing the other parts of the command line to insert all the necessary fields and the where_clause. I'll update as soon as possible.
Regards, Paola
Re: How can I use variables in a from_clause? [message #206747 is a reply to message #206206] Fri, 01 December 2006 05:59 Go to previous messageGo to next message
tomboy
Messages: 23
Registered: November 2006
Location: Alessandria (Italy)
Junior Member
Hello, I'm here again to ask another suggestion. The new issue is about x_table_name. I need to store the table_name in my table. I tried in two different ways, but I've got an error (please, see below:
EXECUTE IMMEDIATE 'insert into mino_ctrl_tables (owner, table_name, created_by, creation_date) select to_char(x_owner), to_char(' || x_table_name || '), created_by, creation_date from ' || x_table_name || ' where created_by = 1055';
--ORA-00904: "AR_ACTION_NOTIFICATIONS": invalid identifier where AR_ACTION_NOTIFICATIONS is the first table of the selection
EXECUTE IMMEDIATE 'insert into mino_ctrl_tables (owner, table_name, created_by, creation_date) select to_char(x_owner), to_char(x_table_name), created_by, creation_date from ' || x_table_name || ' where created_by = 1055'; -- || p_created_by;
--ORA-00904: "X_TABLE_NAME": invalid identifier.
Thanks for your help, Paola
Re: How can I use variables in a from_clause? [message #206753 is a reply to message #206747] Fri, 01 December 2006 06:18 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
To Cross check this, you need to display them and see.
select 'insert into mino_ctrl_tables (owner, table_name, created_by, creation_date) 
    select to_char(:x_owner ), to_char(' || :x_table_name || '), created_by, creation_date 
      from ' || :x_table_name || ' 
     where created_by = 1055' 
from dual;

The out put is when you pass x_table_name as AR_ACTION_NOTIFICATIONS.

insert into mino_ctrl_tables (owner, table_name, created_by, creation_date) 
    select to_char(:x_owner ), to_char(AR_ACTION_NOTIFICATIONS), created_by, creation_date 
      from AR_ACTION_NOTIFICATIONS 
     where created_by = 1055
x_owner is not there in AR_ACTION_NOTIFICATIONS. You need to pass that. So, you need to concat that. Also single quotes are missing when you write to_char.
select 'insert into mino_ctrl_tables (owner, table_name, created_by, creation_date) 
     select to_char('''|| :x_owner ||'''), to_char(''' || :x_table_name || '''), created_by, creation_date 
       from ' || :x_table_name || ' 
      where created_by = 1055' 
from dual;

The out put is when you pass x_table_name as AR_ACTION_NOTIFICATIONS and x_owner as AR.

insert into mino_ctrl_tables (owner, table_name, created_by, creation_date) 
     select to_char('AR'), to_char('AR_ACTION_NOTIFICATIONS'), created_by, creation_date 
       from AR_ACTION_NOTIFICATIONS 
      where created_by = 1055
But why it is giving X_TABLE_NAME as invalid. Have you assigned that with the table name from the cursor? In your old code you are using x_var. Check that out?

By
Vamsi

[Updated on: Fri, 01 December 2006 06:21]

Report message to a moderator

Re: How can I use variables in a from_clause? [message #206782 is a reply to message #206206] Fri, 01 December 2006 07:44 Go to previous messageGo to next message
tomboy
Messages: 23
Registered: November 2006
Location: Alessandria (Italy)
Junior Member
Hello, x_table_name is the same of x_var, I added x_owner and so I changed even the other one to be clearer.
This is the new code:
procedure ctrl_tables (p_owner in varchar2,
p_created_by in number,
--p_creation_date in date,
errbuf out varchar2,
retcode out varchar2) is

x_owner varchar2(40);
x_table_name varchar2(30);

cursor c_tables is
select ts.owner,
ft.table_name,
ft.created_by,
ft.creation_date
from fnd_tables ft,
all_tables ts,
all_tab_columns tc
where ft.table_name = ts.table_name
and ts.table_name = tc.table_name
and ts.owner = p_owner
and tc.column_name = 'CREATED_BY';

begin

for record in c_tables loop
x_owner := record.owner;
x_table_name := record.table_name;
dbms_output.put_line(to_char(x_table_name));
EXECUTE IMMEDIATE 'insert into mino_ctrl_tables (owner, created_by, creation_date) select to_char(x_owner), created_by, creation_date from ' || x_table_name || ' where created_by = 1055'; -- || p_created_by;
-- prec. >> and to_char(creation_date, 'YYYY/MM/DD HH24:MI:SS') = '2006/10/18 14.40.33';';
end loop;
end;
----------------------------------------------------------------
I need to insert 4 fields (2 vars + 2 fields from x_table_name) in my custom table with a where_cluade based on 2 parameters.

Thanks and regards, Paola
Re: How can I use variables in a from_clause? [message #206784 is a reply to message #206782] Fri, 01 December 2006 07:59 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Have you read my previous post clearly?
Ignore the following from that.
Quote:
But why it is giving X_TABLE_NAME as invalid. Have you assigned that with the table name from the cursor? In your old code you are using x_var. Check that out?
Check the differences between the queries I have given.
You have used to_char(x_owner). It needs to be changed to to_char(''' || x_owner || ''')
You want to build your query as to_char('AR') not to_char(x_owner).
Quote:
x_owner is not there in AR_ACTION_NOTIFICATIONS. You need to pass that. So, you need to concat that. Also single quotes are missing when you write to_char.
Why don't you just remove colon ( : ) from the code and check?
select 'insert into mino_ctrl_tables (owner, table_name, created_by, creation_date) 
     select to_char('''|| :x_owner ||'''), to_char(''' || :x_table_name || '''), created_by, creation_date 
       from ' || :x_table_name || ' 
      where created_by = 1055' 
from dual;
By
Vamsi
Re: How can I use variables in a from_clause? [message #206799 is a reply to message #206206] Fri, 01 December 2006 09:55 Go to previous messageGo to next message
tomboy
Messages: 23
Registered: November 2006
Location: Alessandria (Italy)
Junior Member
Hello, step by step I'm going on... now the last one, the date format of the second part of where_clause.
The code is:
EXECUTE IMMEDIATE 'insert into mino_ctrl_tables (owner, table_name, created_by, creation_date)' ||
'select to_char('''|| x_owner ||'''), to_char(''' || x_table_name || '''), created_by, creation_date ' ||
'from ' || x_table_name || ' where created_by = ' || p_created_by || ' and trunc(creation_date) = ' || p_creation_date;

----------------------------------------------------------------
The issue is according the format date of the db (generally is 18-OTT-2006 where OTT is the italian shortname of October) with the format of the parameter (generally is 18/10/2006).
The error message now is about OTT: invalid identifier.
Thanks a lot for your help, Paola
Re: How can I use variables in a from_clause? [message #206811 is a reply to message #206206] Fri, 01 December 2006 11:03 Go to previous messageGo to next message
tomboy
Messages: 23
Registered: November 2006
Location: Alessandria (Italy)
Junior Member
Hello, other information for you... I suspected the date issue was due to a language problem and so I tested the code using a month, whose shortname was the same in both language (example: NOV). The result is not good: the same ORA-00904: "NOV": invalid identifier. I added a "dbms_output.put_line(to_char(x_table_name) || ' - ' || to_char(trunc(record.creation_date)) || ' - ' || to_char(p_creation_date));" line code to examine the values before the first table and so before the error message.
The output is "AR_ACTION_NOTIFICATIONS - 02-MAR-00 - 18-NOV-06" , so the both date are similar and comparable. Any idea?
Thanks and regards, Paola
Re: How can I use variables in a from_clause? [message #206837 is a reply to message #206811] Fri, 01 December 2006 13:00 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"ORA-00904: Invalid identifier" means that you didn't spell column name correctly. Check the table and its columns.

One more suggestion: you are talking about dates and parameter value which looks like 18/10/2006. Do not rely on possible correct date format. On some databases it will run OK, but on others will fail because of invalid date format. Use appropriate TO_DATE('18/10/2006', 'dd/mm/yyyy') function to have it under your control.
Re: How can I use variables in a from_clause? [message #206906 is a reply to message #206206] Sat, 02 December 2006 02:37 Go to previous messageGo to next message
tomboy
Messages: 23
Registered: November 2006
Location: Alessandria (Italy)
Junior Member
Hello, the problem is due exclusively to the date format of the where clause last condition. If I take it off, the code run correctly. I'll try to use a different format.
Regards, Paola
Re: How can I use variables in a from_clause? [message #207820 is a reply to message #206206] Thu, 07 December 2006 02:44 Go to previous messageGo to next message
tomboy
Messages: 23
Registered: November 2006
Location: Alessandria (Italy)
Junior Member
Hello, I tried to change the code last part in a lot of ways to avoid the error message, but I wasn't able to solve it.
Below, I copied the code last part and the relative errors. Probably, the 1st and 2nd test contain formal error due to ' or ||, but the last one seems to be ok.
So, the error could be due to:
- different language, because OTT is the italian of OCT
- different date format.

I tried to force date in format with numeric month, in order to avoid language issue. I need to use a parameter, but I tried with a constant to simplify it at the beginning.

Any idea? Thanks, Paola


CODE:
----------------------------------------------------------------
'and to_char(creation_date, ' || 'YYYY/MM/DD HH24:MI:SS' || ''') = ''' || p_creation_date;
'and to_char(creation_date, ' || 'YYYY/MM/DD HH24:MI:SS' || ') = ''' || p_creation_date;
'ORA-00907: missing right parenthesis'

'and to_char(creation_date, ''' || 'DD/MM/YYYY' || ''') = ' || trunc(p_creation_date);
'ORA-06502: numeric or value error'

'and to_char(creation_date, ''' || 'DD/MM/YYYY' || ''') = ' || to_date('18/10/2006', 'DD/MM/YYYY'); --trunc(p_creation_date);
'ORA-00904: "OTT": invalid identifier'
Re: How can I use variables in a from_clause? [message #207824 is a reply to message #207820] Thu, 07 December 2006 02:53 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You have lot of confusion with single quotes.

To understand it better, try to display that using "select --- from dual"
select 'and to_char(creation_date, ''' || 'DD/MM/YYYY' || ''') = ' || to_date('18/10/2006', 'DD/MM/YYYY') from dual;
Try for others also. You will come to know.

Also use to_date both sides of the equal sign.

by
Vamsi

[Updated on: Thu, 07 December 2006 02:53]

Report message to a moderator

Re: How can I use variables in a from_clause? [message #207863 is a reply to message #206206] Thu, 07 December 2006 03:59 Go to previous messageGo to next message
tomboy
Messages: 23
Registered: November 2006
Location: Alessandria (Italy)
Junior Member
Hello Vamsi, I already tried using the "select ... from dual" and the both date are in the same format and with the same value --> '18/10/2006' with any 'OTT', so I can only think that during the execute immediate running there is another automatic date conversion.
Regards, Paola
Re: How can I use variables in a from_clause? [message #207879 is a reply to message #207863] Thu, 07 December 2006 04:19 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Try this
EXECUTE IMMEDIATE 'insert into mino_ctrl_tables (owner, table_name, created_by, creation_date) ' ||
'select '''|| x_owner ||''', ''' || x_table_name || ''', created_by, creation_date ' ||
'from ' || x_table_name || ' where created_by = ' || p_created_by || ' and trunc(creation_date) = trunc(''' ||p_creation_date || ''')';


By taking into consideration of the following variables/parameters/datatypes
p_owner in varchar2
p_created_by in number
p_creation_date in date

x_owner varchar2(40);
x_table_name varchar2(30);

By
Vamsi
Re: How can I use variables in a from_clause? [message #207947 is a reply to message #206206] Thu, 07 December 2006 08:26 Go to previous messageGo to next message
tomboy
Messages: 23
Registered: November 2006
Location: Alessandria (Italy)
Junior Member
Hello Vamsi, I tried what you suggested but I've got a new error message: ORA00932 inconsistent datatype... number instead of date.
Regards, Paola
Re: How can I use variables in a from_clause? [message #207957 is a reply to message #206206] Thu, 07 December 2006 08:47 Go to previous messageGo to next message
tomboy
Messages: 23
Registered: November 2006
Location: Alessandria (Italy)
Junior Member
Hello, I reran the code using my one (in red) mixing with the last line of what you posted (in green).
So the new code is:
EXECUTE IMMEDIATE 'insert into mino_ctrl_tables (owner, table_name, created_by, creation_date)' ||
'select to_char('''|| x_owner ||'''), to_char(''' || x_table_name || '''), created_by, creation_date ' ||
'from ' || x_table_name || ' ' ||
'where created_by = ' || p_created_by || ' ' ||

'and trunc(creation_date) = trunc(''' ||p_creation_date || ''')';
The error is always the same "ORA-00904: OTT: invalid identifier". So the previous error is due to a misunderstanding.
Regards, Paola
Re: How can I use variables in a from_clause? [message #207959 is a reply to message #207947] Thu, 07 December 2006 08:47 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Oops! I missed to_date
EXECUTE IMMEDIATE 'insert into mino_ctrl_tables (owner, table_name, created_by, creation_date) ' ||
'select '''|| x_owner ||''', ''' || x_table_name || ''', created_by, creation_date ' ||
'from ' || x_table_name || ' where created_by = ' || p_created_by || ' and trunc(creation_date) = trunc(to_date(''' ||p_creation_date || '''))';
By
Vamsi
Re: How can I use variables in a from_clause? [message #207964 is a reply to message #206206] Thu, 07 December 2006 09:13 Go to previous messageGo to next message
tomboy
Messages: 23
Registered: November 2006
Location: Alessandria (Italy)
Junior Member
Hello Vamsi, thanks for your patience!!
I modified the last part as per your suggestion and run the code again... another error: ORA-01843: invalid month.
Regards, Paola
Re: How can I use variables in a from_clause? [message #207987 is a reply to message #207964] Thu, 07 December 2006 12:11 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
You'll need a format mask with the TO_DATE.
Re: How can I use variables in a from_clause? [message #208010 is a reply to message #207964] Thu, 07 December 2006 16:50 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Very nice example, how not binding may lead into very complicated code. Even after adding format to the date I am afraid that p_created_by is a varchar2 variable, so you should put additional quotes around it.

I would use binding instead:

EXECUTE IMMEDIATE 'insert into mino_ctrl_tables (owner, table_name, created_by, creation_date) ' ||
'select '''|| x_owner ||''', ''' || x_table_name || ''', created_by, creation_date from ' || x_table_name || 
' where created_by = :1 and trunc(creation_date) = trunc(:2)'
using p_created_by, p_creation_date;


See what asktom says about it, search for "bind".

Keep in mind, that if index is created on creation_date column, it will not be used in this query. If you want to use its advantages, either create a function-based index on trunc(creation_date), or change the where condition in this way:

'<...> and creation_date >= :m and creation_date < :n'
using <...>, trunc(p_creation_date), trunc(p_creation_date)+1;


I would prefer the second variant.

[Update: Overbinding on the select part corrected.]

[Updated on: Fri, 08 December 2006 00:02]

Report message to a moderator

Re: How can I use variables in a from_clause? [message #208559 is a reply to message #206206] Mon, 11 December 2006 05:53 Go to previous messageGo to next message
tomboy
Messages: 23
Registered: November 2006
Location: Alessandria (Italy)
Junior Member
Hello, I tried your last suggestion and executed what follows:

EXECUTE IMMEDIATE 'insert into mino_ctrl_tables (owner, table_name, created_by, creation_date) ' ||
'select to_char('''|| x_owner ||'''), to_char(''' || x_table_name || '''), created_by, creation_date ' ||
'from ' || x_table_name || ' ' ||
--'where created_by = :1 and trunc(creation_date) = trunc(:2)'
--using p_created_by, trunc(p_creation_date);

'where created_by = :1 and creation_date >= :m and creation_date < :n'
using p_created_by, p_creation_date, p_creation_date+1;

I've got an error: ORA00932 inconsistent datatype... number instead of date.
Regards, Paola
Re: How can I use variables in a from_clause? [message #208560 is a reply to message #206206] Mon, 11 December 2006 05:55 Go to previous messageGo to next message
tomboy
Messages: 23
Registered: November 2006
Location: Alessandria (Italy)
Junior Member
Hello, I tried different combinations of the code... with and without trunc function, but the error is always the same.
Any idea?
Paola
Re: How can I use variables in a from_clause? [message #208594 is a reply to message #208560] Mon, 11 December 2006 08:24 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hello,
if the error is in the execute immediate statement, then the column types of mino_ctrl_tables and source tables do not match.
Try
select ts.owner, tc.column_name, tc.data_type
from all_tables ts, all_tab_columns tc
where ts.table_name = tc.table_name
and ts.table_name = 'MINO_CTRL_TABLES'
and tc.column_name in ( 'CREATED_BY', 'CREATION_DATE' );

select ts.owner, tc.column_name, tc.data_type, count(1)
from fnd_tables ft, all_tables ts, all_tab_columns tc
where ft.table_name = ts.table_name
and ts.table_name = tc.table_name
and ts.owner = '<your_owner>'
and tc.column_name in ( 'CREATED_BY', 'CREATION_DATE' )
group by ts.owner, tc.column_name, tc.data_type;

Replace <your_owner> with upper case value of your p_owner parameter.
Both queries should return the same values (except the last column of the second query - number of source tables).
You will get the wrong tables by:
select ts.owner, ft.table_name
from fnd_tables ft, all_tables ts, all_tab_columns tc
where ft.table_name = ts.table_name
and ts.table_name = tc.table_name
and ts.owner = '<your_owner>'
and tc.column_name = '<wrong_column_name>'
and tc.data_type = '<wrong_data_type>';

P.S. If the p_creation_date is not truncated to days, use
 using p_created_by, trunc(p_creation_date), trunc(p_creation_date)+1;

for the second variant.
icon14.gif  Re: How can I use variables in a from_clause? [message #208644 is a reply to message #206206] Mon, 11 December 2006 12:11 Go to previous message
tomboy
Messages: 23
Registered: November 2006
Location: Alessandria (Italy)
Junior Member
Hello, I solved the issue and now the code is OK. The date problem was on the parameter... after a thousand of tests I had lost control of p_creation_date that was wrongly a varchar instead of date type.

Thanks a lot and regards, Paola
Previous Topic: urgent:Error in trigger .
Next Topic: what's difference between admin and developer
Goto Forum:
  


Current Time: Sat Dec 10 03:29:04 CST 2016

Total time taken to generate the page: 0.12047 seconds