Home » SQL & PL/SQL » SQL & PL/SQL » Doubt in executing PL/SQL procedure
Doubt in executing PL/SQL procedure [message #255114] Mon, 30 July 2007 12:22 Go to next message
achyutb
Messages: 2
Registered: July 2007
Junior Member
Hi,

Can anyone help me with the below error.

I get the following error when I execute a stored procedure. I'm running it from Sqlplus.

ERROR:
ORA-01756: quoted string not properly terminated

The header part of SP is
CREATE OR REPLACE PROCEDURE sp_pce_rsl_dyn_acct_results(p_i_ATLEVEL INTEGER,
p_c_TERMINATOR VARCHAR2,
p_i_EXPANDED INTEGER,
p_i_perf_ac INTEGER,
p_c_perf_ac_type VARCHAR2,
p_d_asof TIMESTAMP,
p_i_gu INTEGER,
p_i_schma INTEGER,
p_i_parent INTEGER,
p_d_pce_start TIMESTAMP,
p_d_open TIMESTAMP,
p_d_bckld TIMESTAMP,
p_i_bse_curr INTEGER,
p_i_lcl_curr IN INTEGER,
p_i_suppress IN INTEGER,
p_c_annualize VARCHAR2,
p_c_check_schma VARCHAR2,
p_c_expand_all VARCHAR2,
p_c_ins_cols VARCHAR2,
po_f_schma_change IN OUT VARCHAR2) AS


I'm execuitng it as follows.

execute sp_pce_rsl_dyn_acct_results ( p_i_ATLEVEL => 1 , p_c_TERMINATOR => '0' , p_i_EXPANDED => 0 , p_i_perf_ac => 64484 , p_c_perf_ac_type => 'PA' , p_d_asof => TO_TIMESTAMP('31-12-2006 00:00:00','DD-MM-YYYY HH24:MI:SS') , p_i_gu => 1 , p_i_schma => 1434 , p_i_parent => 0 , p_d_pce_start => TO_TIMESTAMP('30-06-1996 00:00:00','DD-MM-YYYY HH24:MI:SS') , p_d_open => TO_TIMESTAMP('31-12-9999 00:00:00','DD-MM-YYYY HH24:MI:SS') , p_d_bckld => TO_TIMESTAMP('30-06-2006 00:00:00','DD-MM-YYYY HH24:MI:SS') , p_i_bse_curr => 1 , p_i_lcl_curr => 1 , p_i_suppress => 1 , p_c_annualize => 'Y' , p_c_check_schma => 'Y' , p_c_expand_all => 'N' , p_c_ins_cols => 'BEGIN DELETE FROM PCE_GTTPB_col_RSL; INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 10001,0,''UN'',0,0,1,NULL,''12/31/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 8,0,''UN'',0,0,2,NULL,''12/31/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 10,0,''UN'',0,0,3,''11/30/2006'',''12/31/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 3,0,''UN'',0,0,4,''11/30/2006'',''12/31/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 10,0,''UN'',0,0,5,''10/31/2006'',''11/30/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 3,0,''UN'',0,0,6,''10/31/2006'',''11/30/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 10,0,''UN'',0,0,7,''09/30/2006'',''10/31/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 3,0,''UN'',0,0,8,''09/30/2006'',''10/31/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 10,0,''UN'',0,0,9,''09/30/2006'',''12/31/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 3,0,''UN'',0,0,10,''09/30/2006'',''12/31/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 10,0,''UN'',0,0,11,''12/31/2005'',''12/31/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 3,0,''UN'',0,0,12,''12/31/2005'',''12/31/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 12,0,''UN'',0,0,13,NULL,NULL,'' ''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 10052,0,''UN'',0,0,14,''09/30/2006'',''12/31/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 10052,0,''UN'',0,0,15,''12/31/2005'',''12/31/2006'',''B''); END;' );

Re: Doubt in executing PL/SQL procedure [message #255115 is a reply to message #255114] Mon, 30 July 2007 12:23 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
First read This
http://www.orafaq.com/forum/t/59966/105911/
Re: Doubt in executing PL/SQL procedure [message #255121 is a reply to message #255114] Mon, 30 July 2007 12:39 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
achyutb wrote on Mon, 30 July 2007 13:22
Hi,

I'm execuitng it as follows.

execute sp_pce_rsl_dyn_acct_results ( p_i_ATLEVEL => 1 ,
<gobbledygook>




And this one

http://www.orafaq.com/forum/t/85036/66800/
Re: Doubt in executing PL/SQL procedure [message #255139 is a reply to message #255121] Mon, 30 July 2007 14:12 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is obvious that there's nothing wrong in a way you call the procedure; isn't it?

I *guess* there's something in the procedure itself that doesn't work as you'd like it to.

Could you, just for testing purposes, change last procedure's parameter from that unreadable monster into
p_c_ins_cols => 'BEGIN NULL; END;'

I *suspect* that you use that string in the EXECUTE IMMEDIATE. Do you?

OK, post some extra info and we'll see.
Re: Doubt in executing PL/SQL procedure [message #255208 is a reply to message #255139] Tue, 31 July 2007 00:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And by the way, '12/31/2006' is not a date.
Re: Doubt in executing PL/SQL procedure [message #255794 is a reply to message #255208] Wed, 01 August 2007 14:53 Go to previous messageGo to next message
achyutb
Messages: 2
Registered: July 2007
Junior Member
Created a simple table A.

CREATE TABLE A (
A1 NUMBER,
A2 VARCHAR2 (5),
A3 TIMESTAMP(6));

I'm trying to execute the below procedure

CREATE OR REPLACE PROCEDURE sp_pce_rsl_dyn_acct_results(p_i_ATLEVEL INTEGER,
p_c_TERMINATOR VARCHAR2,
p_i_EXPANDED INTEGER,
p_i_perf_ac INTEGER,
p_c_perf_ac_type VARCHAR2,
p_d_asof TIMESTAMP,
p_i_gu INTEGER,
p_i_schma INTEGER,
p_i_parent INTEGER,
p_d_pce_start TIMESTAMP,
p_d_open TIMESTAMP,
p_d_bckld TIMESTAMP,
p_i_bse_curr INTEGER,
p_i_lcl_curr IN INTEGER,
p_i_suppress IN INTEGER,
p_c_annualize VARCHAR2,
p_c_check_schma VARCHAR2,
p_c_expand_all VARCHAR2,
p_c_ins_cols VARCHAR2,
po_f_schma_change IN OUT VARCHAR2) AS
.....
BEGIN
...
EXECUTE IMMEDIATE(p_c_ins_cols);
...
END;


I just substituted a simple value like

'BEGIN DELETE FROM A; END;' for variable p_c_ins_cols.

execute sp_pce_rsl_dyn_acct_results (
p_i_ATLEVEL => 2 ,
p_c_TERMINATOR => '10' ,
p_i_EXPANDED => 0 ,
p_i_perf_ac => 64484 ,
p_c_perf_ac_type => 'PA' ,
p_d_asof => TO_TIMESTAMP('31-12-2006 00:00:00','DD-MM-YYYY HH24:MI:SS') ,
p_i_gu => 1 ,
p_i_schma => 1434 ,
p_i_parent => 1 ,
p_d_pce_start => TO_TIMESTAMP('30-06-1996 00:00:00','DD-MM-YYYY HH24:MI:SS') ,
p_d_open => TO_TIMESTAMP('31-12-9999 00:00:00','DD-MM-YYYY HH24:MI:SS') ,
p_d_bckld => TO_TIMESTAMP('30-06-2006 00:00:00','DD-MM-YYYY HH24:MI:SS') ,
p_i_bse_curr => 1 ,
p_i_lcl_curr => 1 ,
p_i_suppress => 1 ,
p_c_annualize => 'Y' ,
p_c_check_schma => 'Y' ,
p_c_expand_all => 'N' ,
p_c_ins_cols => 'BEGIN DELETE FROM A; END;' )

But gives me the below error

ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SP_PCE_RSL_DYN_ACCT_RESULTS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


Again for testing purpose I created a simple procedure named sample

CREATE OR REPLACE PROCEDURE SAMPLE (P1 VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE (P1);
END;

when I execute this procedure from sql plus, it works fine.

execute sample('BEGIN DELETE FROM A; END;');


One more thing.

When I execute the procedure Sample by passing the below value marked in blue (sorry if its unreadable. it has delete and insert statements for table PCE_GTTPB_col_RSL)

'BEGIN DELETE FROM PCE_GTTPB_col_RSL; INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 10001,0,''UN'',0,0,1,NULL,''12/31/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 8,0,''UN'',0,0,2,NULL,''12/31/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 10,0,''UN'',0,0,3,''11/30/2006'',''12/31/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 3,0,''UN'',0,0,4,''11/30/2006'',''12/31/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 10,0,''UN'',0,0,5,''10/31/2006'',''11/30/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 3,0,''UN'',0,0,6,''10/31/2006'',''11/30/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 10,0,''UN'',0,0,7,''09/30/2006'',''10/31/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 3,0,''UN'',0,0,8,''09/30/2006'',''10/31/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 10,0,''UN'',0,0,9,''09/30/2006'',''12/31/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 3,0,''UN'',0,0,10,''09/30/2006'',''12/31/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 10,0,''UN'',0,0,11,''12/31/2005'',''12/31/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 3,0,''UN'',0,0,12,''12/31/2005'',''12/31/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 12,0,''UN'',0,0,13,NULL,NULL,'' ''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 10052,0,''UN'',0,0,14,''09/30/2006'',''12/31/2006'',''B''); INSERT INTO PCE_GTTPB_col_RSL (i_col, i_perf_obj, c_perf_ac_type, i_indx_gu, i_indx_catg, i_seq, d_start, d_end, c_risk_bse) VALUES ( 10052,0,''UN'',0,0,15,''12/31/2005'',''12/31/2006'',''B''); END;'

I get the error
ERROR:
ORA-01756: quoted string not properly terminated

But if I remove any of the 3 insert statements from the block marked in blue, the procedure runs.

I find this very strange. Could someone please help.
Re: Doubt in executing PL/SQL procedure [message #255811 is a reply to message #255794] Wed, 01 August 2007 16:26 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Your blue racer is 2862 characters long (including spaces). So - if it works as soon as you remove ANY of the INSERT statements, I'd say that you violated some "string length" rule and perhaps this blue thingy got truncated which made it incorrect.

So - could you create another parameter and pass a single BEGIN INSERT INTO ... END block using that newly created parameter?
Previous Topic: Trim time from date field
Next Topic: index on partition keys useful?
Goto Forum:
  


Current Time: Sat Dec 03 22:03:54 CST 2016

Total time taken to generate the page: 0.10319 seconds