Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06512 EXECUTE IMMEDIATE (merged 2 threads)
ORA-06512 EXECUTE IMMEDIATE (merged 2 threads) [message #313710] Mon, 14 April 2008 04:54 Go to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi,
I am trying to execute a query through EXECUTE IMMEDIATE.
This query runs when written as :-
SELECT MAX(TO_CHAR(f_ra_KWH1_reading_taken,v_dateformat)) INTO v_date
FROM t_rdg_abs_KWH1 WHERE f_ra_KWH1_reading_taken <= TO_DATE(p_start_date,v_dateformat) 
AND f_ra_KWH1_validated = 1 AND f_ra_KWH1_fk_mp_id = TO_NUMBER(p_meter_profile_id)';


I have to run this query on runtime putting runtime variable:-
I looked for variables data through dbms_output and they are coming correctly to the variable.
But its not working when put into strsql as a string.
strsql := 
'SELECT MAX(TO_CHAR(f_ra_KWH1_reading_taken,v_dateformat)) 
   FROM t_rdg_abs_KWH1 
  WHERE f_ra_KWH1_reading_taken <= TO_DATE  (p_start_date,v_dateformat) 
AND f_ra_KWH1_validated = 1
AND f_ra_KWH1_fk_mp_id = TO_NUMBER(p_meter_profile_id)';
Execute immediate strsql INTO v_date;


Please advice as why this is not working when taken in a string, is there some rule with execute immediate with strings sql.

Thanks,
Soni

[Updated on: Mon, 14 April 2008 04:55]

Report message to a moderator

Re: Execute immediate [message #313712 is a reply to message #313710] Mon, 14 April 2008 04:58 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
Not working is not an Oracle error.Pls post the error you encountered.


regards,
Re: Execute immediate [message #313713 is a reply to message #313710] Mon, 14 April 2008 05:00 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
But its not working when put into strsql as a string.
Is , generally speaking, not the most helpful comment. Try to be a bit more desriptive next time. Your issue lies in putting the parameter directly into the string. You have obviously read up in NDS as your code is pretty close to your requirements. Take a look at the USING clause of the execute immediate statement, it should give you an idea. any problems, post back
Re: Execute immediate [message #313739 is a reply to message #313713] Mon, 14 April 2008 06:48 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi,
I got your answer...!
I tried doing that but since the variable is fetching date value so its not able to get single quotes across dates.
The sql query is coming as below as string:
 SELECT MAX(TO_CHAR(f_ra_KWH1_reading_taken,MM/DD/YYYY HH:MI:SS AM)) FROM t_rdg_abs_KWH
 WHERE f_ra_KWH1_reading_taken <= TO_DATE(1/23/2008 3:00:00 PM,MM/DD/YYYY HH:MI:SS AM)
 AND f_ra_KWH1_validated = 1 ;


strsql := 'SELECT MAX(TO_CHAR(f_ra_'||v_dut_desc||'_reading_taken,'||v_dateformat||'))
               FROM t_rdg_abs_'||v_dut_desc||'
              WHERE f_ra_'||v_dut_desc||'_reading_taken <= TO_DATE('||p_start_date||','||v_dateformat||')
                AND f_ra_'||v_dut_desc||'_validated = 1';


I tried giving single quotes twice
before v_dateformat but then its taking it as a string again.
Please advice.
Re: Execute immediate [message #313742 is a reply to message #313710] Mon, 14 April 2008 07:14 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> Please advice.
You surely did not read pablolee's answer. Read it again.
In other words, do NOT hardcode the variable values as literals, BIND them.
You shall start with reading these links:
Using the EXECUTE IMMEDIATE Statement in PL/SQL
Improving Performance of Dynamic SQL with Bind Variables
Re: Execute immediate [message #313749 is a reply to message #313742] Mon, 14 April 2008 07:24 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi,
As per pablolee's advice I have bound the variables.
strsql := 'SELECT MAX(TO_CHAR(f_ra_'||v_dut_desc||'_reading_taken,'||v_dateformat||'))
               FROM t_rdg_abs_'||v_dut_desc||'
              WHERE f_ra_'||v_dut_desc||'_reading_taken <= TO_DATE('||p_start_date||','||v_dateformat||')
                AND f_ra_'||v_dut_desc||'_validated = 1';


I am getting correct value for other variables except for variables containing date values as they should return a value enclosed in single quotes.
SELECT MAX(TO_CHAR(f_ra_KWH1_reading_taken,MM/DD/YYYY HH:MI:SS AM)) FROM t_rdg_abs_KWH
 WHERE f_ra_KWH1_reading_taken <= TO_DATE(1/23/2008 3:00:00 PM,MM/DD/YYYY HH:MI:SS AM)
 AND f_ra_KWH1_validated = 1 ;

In the above output query, you can see that I am getting correct values for all the variables except for the date like
1/23/2008 3:00:00 PM,MM/DD/YYYY HH:MI:SS AM

These needs to be enclosed in single quotes. Please suggest me a simple way to bound them with single quotes. I am able to run the output query when I add single quotes to it but they are only missing here.

Please help me on this as I am trying on it since long and its need to be done soon.

Thanks in advance,
Soni
Re: Execute immediate [message #313750 is a reply to message #313749] Mon, 14 April 2008 07:28 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
As per pablolee's advice I have bound the variables.

Nope, you have not bound the variables. Please look up the documentation on the USING clause.
Re: Execute immediate [message #313754 is a reply to message #313750] Mon, 14 April 2008 07:44 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

I have not used Bind variables but put variables in || to get the value.

I have got correct answer for rest sqls but not getting for this as I have date variable here.
I am not worked much on bind variables...
Please give me some more hints as I have not much time left....I know its not right to say without trying myself...but please help me with little script example...I will read the links after finishing this task as I have limited time.

Plzzzz help me soon.

Thanks,
Soni
Re: Execute immediate [message #313755 is a reply to message #313749] Mon, 14 April 2008 07:49 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
sonikumari wrote on Mon, 14 April 2008 08:24

strsql := 'SELECT MAX(TO_CHAR(f_ra_'||v_dut_desc||'_reading_taken,'||v_dateformat||'))





MAX of a TO_CHAR of a DATE will give results as such:

12/01/1971 is greater than 04/14/2008.
04/14/2007 is greater than 03/31/2054.
Re: Execute immediate [message #313756 is a reply to message #313754] Mon, 14 April 2008 07:50 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Sorry, no. It will take you very little time to read up on the use of the USING clause. Your lack of time is not our concern. The link supplied will take you a matter of minutes to read through.
Re: Execute immediate [message #313765 is a reply to message #313710] Mon, 14 April 2008 08:32 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi,
I read the pages in the link given and tried in my script.
strsql := 'SELECT MAX(TO_CHAR(f_ra_kwh1_reading_taken,:1))
               FROM t_rdg_abs_kwh1
              WHERE f_ra_kwh1_reading_taken <= TO_DATE(:2,:1)
                AND f_ra_kwh1_validated = 1
                AND f_ra_kwh1_fk_mp_id = TO_NUMBER(:3)';
DBMS_OUTPUT.PUT_LINE(strsql);
      EXECUTE IMMEDIATE strsql USING v_dateformat, v_start_date, p_profile_id;


But this is again not giving the correct string rather showing :1,:2 in text form and not showing variable values.
Re: Execute immediate [message #313767 is a reply to message #313710] Mon, 14 April 2008 08:35 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
>But this is again not giving the correct string rather showing :1,:2 in text form and not showing variable values.
Then you are doing it wrong & should no be surprised with the results.
Re: Execute immediate [message #313768 is a reply to message #313765] Mon, 14 April 2008 08:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
EXECUTE IMMEDIATE strsql USING v_dateformat, v_start_date, v_dateformat, p_profile_id;

Oracle does not care the name you gave to your bind variable inside the string, you have to give as many value there are bind variables. Each one is different.

Regards
Michel
Re: Execute immediate [message #313770 is a reply to message #313765] Mon, 14 April 2008 08:39 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
sonikumari wrote on Mon, 14 April 2008 09:32
Hi,
I read the pages in the link given and tried in my script.
strsql := 'SELECT MAX(TO_CHAR(f_ra_kwh1_reading_taken,:1))




Continue to be dense and ignorant to what people tell you and you'll get nowhere in life. MAX of a TO_CHAR of a DATE shows that you do not have the skills of a programmer and after being told so, you continue to do it, which shows you also do not have the skills to comprehend responses.
TEST1 FOO>create table foo (foo datE);

Table created.

TEST1 FOO>insert into foo values (to_date('12/31/1955','mm/dd/yyyy'));

1 row created.

TEST1 FOO>insert into foo values (to_date('01/01/2008','mm/dd/yyyy'));

1 row created.

TEST1 FOO>select max(to_char(foo,'mm/dd/yyyy')) from foo;

MAX(TO_CHA
----------
12/31/1955

[Updated on: Mon, 14 April 2008 08:44]

Report message to a moderator

Re: Execute immediate [message #313772 is a reply to message #313770] Mon, 14 April 2008 08:54 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

hi Joy,
I did looked into that comment for Max and got it right.
But right now I am only working on the peace of code to solve the single quotes around date values as this is written in my whole code.

Re: Execute immediate [message #313774 is a reply to message #313770] Mon, 14 April 2008 08:56 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi Joy,
As I said I have understood the comment given for MAX date.
Currently I wanted a solution for getting dates with single quotes.
I am getting values but not with single quotes.
Please give a small example where I can get date with single quote in a variable string.

Thanks
Re: Execute immediate [message #313775 is a reply to message #313710] Mon, 14 April 2008 08:57 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
>single quotes around date values
Single quote characters ONLY enclose string datatypes; not DATE datatypes!

'this is a string, 2008-04-14; not a date'
Re: Execute immediate [message #313776 is a reply to message #313775] Mon, 14 April 2008 09:00 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

I wanted quotes to solve the query like :
select col where sdate between('12/12/2008[B]'[B],vdateformat)....

I am getting the date value in the query dynamically but I needed the single quotes too to solve the query using Execute Immediate.

CAN SOMEONE please HELP ME.
I am getting values but not single quotes only for date kind columns Sad
Re: Execute immediate [message #313779 is a reply to message #313776] Mon, 14 April 2008 09:10 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
You do NOT need to worry about putting apostrophes into your dynamic string (I would be more concerned as to why you are performing this task with NDS at all but let's leave that can of worm shut for the moment. All you need to concern yourself with is the correct structure of your EXECUTE IMMEDIATE statement (Joys point about the pointless to_char notwithstanding - You WILL have to deal with that).
Look at:
EXECUTE IMMEDIATE yourstring INTO your_variable(s) USING your_other_variable(s)


[Updated on: Mon, 14 April 2008 09:12]

Report message to a moderator

ORA-06512: EXECUTE IMMEDIATE [message #313921 is a reply to message #313710] Tue, 15 April 2008 03:17 Go to previous message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi,
I am trying to concatenate a string with variable value and then execute it using EXECUTE IMMEDIATE.
The output string is coming correct and it runs in sql when run individually.
But the procedure as a whole is giving the error :-
The result string is :
SELECT TO_CHAR(MAX(f_ra_KWH1_reading_taken),'MM/DD/YYYY HH:MI:SS AM') FROM
t_rdg_abs_KWH1 WHERE f_ra_KWH1_reading_taken <= TO_DATE('1/23/2008 3:00:00
PM','MM/DD/YYYY HH:MI:SS AM') AND f_ra_KWH1_validated = 1  AND
f_ra_KWH1_fk_mp_id = TO_NUMBER(6748);

BEGIN pk_substitution.sp_substitute('1/23/2008 3:00:00 PM','1/23/2008 4:30:00 AM',6748); END;

*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at "NOC_TEST.PK_SUBSTITUTION", line 138
ORA-06512: at "NOC_TEST.PK_SUBSTITUTION", line 55
ORA-06512: at line 1


My string is :-
strsql := 'SELECT TO_CHAR(MAX(f_ra_'||v_dut_desc||'_reading_taken),''MM/DD/YYYY HH:MI:SS AM'')'
               ||' FROM t_rdg_abs_'||v_dut_desc||
              ' WHERE f_ra_'||v_dut_desc||'_reading_taken <= '
              ||'TO_DATE('''||p_start_date||''',''MM/DD/YYYY HH:MI:SS AM'')'
               ||' AND f_ra_'||v_dut_desc||'_validated = 1 '
                ||' AND f_ra_'||v_dut_desc||'_fk_mp_id = TO_NUMBER('||p_meter_profile_id||')'||';';	
				


The dbms output for strsql runs but its giving error when I execute the procedure.
At Line 138 I have the code :-
EXECUTE IMMEDIATE strsql INTO d_start_tc;
d_start_tc is:-
d_start_tc VARCHAR2(30);

Please suggest as where I am going wrong.

Thanks,
Soni
Previous Topic: Whats the difference between Relational database and Multi dimensional databse?
Next Topic: Update Showing different results??
Goto Forum:
  


Current Time: Tue Dec 06 00:20:19 CST 2016

Total time taken to generate the page: 0.14525 seconds