Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic SQL Question
icon11.gif  Dynamic SQL Question [message #194269] Thu, 21 September 2006 06:22 Go to next message
pw1975
Messages: 24
Registered: June 2006
Junior Member
Hi Folks,

i need some help regarding the following statement (as an excerpt from a stored proc on a ora 9.2 enterprise)


....

l_statement := 'INSERT INTO T_R_AGE_STAT (
                      SELECT 
                               T_STAMP
                               , UNC_PATH
                               , Get_Day_Cluster(:1) DAY_CLUSTER
                               , SUM(FILESIZE) FILESIZE_AGGR
                               , COUNT(FILENAME) FILECOUNT_AGGR
                               , '':1'' FILEATTR
                      FROM
                              ( SELECT
                                  T_STAMP
                                  , UNC_PATH
                                  , :1
                                  , FILESIZE
                                  , FILENAME
                                FROM
                                 :2
                                WHERE 
                                      UNC_PATH = :3
                                      AND T_STAMP = TRUNC(TO_TIMESTAMP(:4,''DD.MM.YY HH24:MI:SS''),''MI'')
                                      AND FILENAME IS NOT NULL) 
                                GROUP BY 
                                  T_STAMP
                                  , UNC_PATH
                                  , Get_Day_Cluster(:1)
                              )
                      )';
  EXECUTE IMMEDIATE l_statement USING 'ACCESSED', p_import, p_unc_path, p_t_stamp;

....


'ACCESSED' should be a sort of string constant.
p_import is a Tablename, p_unc_path is a normal UNC-Path like \\server\share and the timestamp has a Oracle Timestamp format.

For the parsed statment should (in order to work) look like:

INSERT INTO T_R_AGE_STAT (
                      SELECT 
                               T_STAMP
                               , UNC_PATH
                               , Get_Day_Cluster(ACCESSED) DAY_CLUSTER
                               , SUM(FILESIZE) FILESIZE_AGGR
                               , COUNT(FILENAME) FILECOUNT_AGGR
                               , 'ACCESSED' FILEATTR
                      FROM
                              ( SELECT
                                  T_STAMP
                                  , UNC_PATH
                                  , ACCESSED
                                  , FILESIZE
                                  , FILENAME
                                FROM
                                 T_IMPORT_T_132
                                WHERE 
                                      UNC_PATH = '\\SERVER\NAME'
                                      AND T_STAMP = TRUNC(TO_TIMESTAMP('21.09.06 13:54:20,000000000','DD.MM.YY HH24:MI:SS'),'MI')
                                      AND FILENAME IS NOT NULL) 
                                GROUP BY 
                                  T_STAMP
                                  , UNC_PATH
                                  , Get_Day_Cluster(ACCESSED)
                              )
                      );


When i try to execute the stored proc, it always says:
Connecting to the database DWH_INT.
ORA-00903: Invalid tablename
ORA-06512: in "HSSM_LOAD.P_HSSM_AGE_STAT", Line 31
ORA-06512: in Line 10
Process exited.


Can anyone help?

Thanks a lot,
cheers,
ph

[Updated on: Thu, 21 September 2006 06:49]

Report message to a moderator

Re: Dynamic SQL Question [message #194278 is a reply to message #194269] Thu, 21 September 2006 06:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ok, the ORA-00903 is caused becuase the value that's getting used in the FROM clause doesn't meet Oracles criteria for being a valid table name.

This is because you've got the bind variables wrong.
Oracle passes bind variables into NDS statement by position. The first variable after the USING is used for the first bind variable in the string. Even if the second bind variable in the string has the same name as the first, it needs a seperate vairable in the list.

So, given that you reference bind variables
:b1,:b1,:b1,:b2,:b3,:b4,:b1
your list of variables should be:
USING 'ACCESSED','ACCESSED','ACCESSED', p_import, p_unc_path, p_t_stamp,'ACCESSED';
Re: Dynamic SQL Question [message #194282 is a reply to message #194278] Thu, 21 September 2006 06:59 Go to previous messageGo to next message
pw1975
Messages: 24
Registered: June 2006
Junior Member
Hi,

thanks for your reply.

i now tested:

  l_statement := 'INSERT INTO T_R_AGE_STAT (
                      SELECT 
                               T_STAMP
                               , UNC_PATH
                               , Get_Day_Cluster(:b1) DAY_CLUSTER
                               , SUM(FILESIZE) FILESIZE_AGGR
                               , COUNT(FILENAME) FILECOUNT_AGGR
                               , '':b2'' FILEATTR
                      FROM
                              ( SELECT
                                  T_STAMP
                                  , UNC_PATH
                                  , :b3
                                  , FILESIZE
                                  , FILENAME
                                FROM
                                 :b4
                                WHERE 
                                      UNC_PATH = :b5
                                      AND T_STAMP = TRUNC(TO_TIMESTAMP(:b6,''DD.MM.YY HH24:MI:SS''),''MI'')
                                      AND FILENAME IS NOT NULL) 
                                GROUP BY 
                                  T_STAMP
                                  , UNC_PATH
                                  , Get_Day_Cluster(:b7)
                              )
                      )';
  EXECUTE IMMEDIATE l_statement USING 'ACCESSED', 'ACCESSED', 'ACCESSED', p_import, p_unc_path, p_t_stamp,'ACCESSED';
  EXECUTE IMMEDIATE l_statement USING 'CREATED', 'CREATED', 'CREATED', p_import, p_unc_path, p_t_stamp, 'CREATED';
  EXECUTE IMMEDIATE l_statement USING 'MODIFIED', 'MODIFIED', 'MODIFIED', p_import, p_unc_path, p_t_stamp, 'MODIFIED';
  COMMIT;



Same error.... what am i missing? (except some brain, i think Wink)

[Updated on: Thu, 21 September 2006 07:01]

Report message to a moderator

Re: Dynamic SQL Question [message #194301 is a reply to message #194282] Thu, 21 September 2006 07:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to have a look at the generated SQL instead of executing it.
Can you post it up here?
Re: Dynamic SQL Question [message #194302 is a reply to message #194269] Thu, 21 September 2006 07:46 Go to previous messageGo to next message
pw1975
Messages: 24
Registered: June 2006
Junior Member
How can i do that, DMBS .. PUTLINE exceeds buffer length of 256 chars... is there any other way to get the parsed statement?
Re: Dynamic SQL Question [message #194304 is a reply to message #194302] Thu, 21 September 2006 07:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Either create a temp table and insert it into there, or do something like this:
  for i in 1..ceil(length(v_String)/255) loop
    dbms_output.put_line(substr(v_string,1+(i-1)*255,255));
  end loop;
to split the code over multiple lines.
Re: Dynamic SQL Question [message #194328 is a reply to message #194304] Thu, 21 September 2006 09:23 Go to previous messageGo to next message
pw1975
Messages: 24
Registered: June 2006
Junior Member
Thanks for the code. But how can i get to the parsed statement.
It is "executed immediately" and this throws the error. I can't print out the parsed statement Sad

I just read, that it is not possible to bind variables for tablenames. The doc says, i have to use string concatenation.


l_statement := 'INSERT INTO T_R_AGE_STAT (
                      SELECT 
                               T_STAMP
                               , UNC_PATH
                               , Get_Day_Cluster(ACCESSED) DAY_CLUSTER
                               , SUM(FILESIZE) FILESIZE_AGGR
                               , COUNT(FILENAME) FILECOUNT_AGGR
                               , ''ACCESSED'' FILEATTR
                      FROM
                              ( SELECT
                                  T_STAMP
                                  , UNC_PATH
                                  , ACCESSED
                                  , FILESIZE
                                  , FILENAME
                                FROM
                                 ' || p_import || '
                                WHERE 
                                      UNC_PATH = ' || p_unc_path || '
                                      AND T_STAMP = TRUNC(TO_TIMESTAMP(' || p_t_stamp || ',''DD.MM.YY HH24:MI:SS''),''MI'')
                                      AND FILENAME IS NOT NULL
                              ) 
                        GROUP BY 
                          T_STAMP
                          , UNC_PATH
                          , Get_Day_Cluster(ACCESSED)
                      )';
  
  EXECUTE IMMEDIATE l_statement;



But even here, it says ORA-0911 , invalid character...

[Updated on: Thu, 21 September 2006 09:31]

Report message to a moderator

Re: Dynamic SQL Question [message #194330 is a reply to message #194269] Thu, 21 September 2006 09:39 Go to previous messageGo to next message
pw1975
Messages: 24
Registered: June 2006
Junior Member
ok i fixed it! Thanks a lot for your help!
Re: Dynamic SQL Question [message #194335 is a reply to message #194328] Thu, 21 September 2006 09:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
D'oh!
I forgot that.
Re: Dynamic SQL Question [message #194336 is a reply to message #194335] Thu, 21 September 2006 09:59 Go to previous message
pw1975
Messages: 24
Registered: June 2006
Junior Member
nevermind - i learnd a lot last 2 hours Wink
Previous Topic: What is the criteria for creating an index on a column
Next Topic: executing a stored procedure from batch file
Goto Forum:
  


Current Time: Sat Dec 10 18:16:40 CST 2016

Total time taken to generate the page: 0.09550 seconds