Home » SQL & PL/SQL » SQL & PL/SQL » Procedure error [merged]
Procedure error [merged] [message #398620] Thu, 16 April 2009 23:29 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,

I have created a procedure. but it's giving error while executin .please look into this.

create or replace PROCEDURE PR_LOAD_SMS
  IS
  BEGIN
     EXECUTE IMMEDIATE 'TRUNCATE TABLE DSS.FORT_HZM';
     EXECUTE IMMEDIATE '
     INSERT INTO DSS.FORT_HZM (CO_ID,FU_CODE,FU_DESC,SCODE,S_DES,AT_DT,SDN,CODELANG)
     SELECT
       CONTR_SERVICES.CO_ID                                         CO_ID,
       FU_PROFILE.FUCODE                                            FU_CODE,
       FU_PROFILE.FU_DESC                                           FU_DESC,
       CONTR_SERVICES.SCODE                                        SCODE,
       MPUSNTAB.DES                                                 S_DES,
       TO_DATE(SUBSTR(CONTR_SERVICES.CS_STAT_CHNG,-7,6),'YYMMDD') ACT_DT,
       CONTRACT_PHONENR.NUM                                         SDN,
       CCONTACT_ALL.CCLANGUAGE                                      CODELANG
     FROM
       SCS.CONTR_SERVICES        CONTR_SERVICES,
       SCS.FU_PROFILE            FU_PROFILE,
       SCS.MPUSNTAB              MPUSNTAB,
       DSS.CONTRACT_PHONENR       CONTRACT_PHONENR,
       SCS.CONTRACT_ALL          CONTRACT_ALL,
       SCS.CCONTACT_ALL          CCONTACT_ALL,
       SCS.FULKSN                FULKSN
     WHERE
       FULKSN.FUCODE = FU_PROFILE.FUCODE
       AND FULKSN.SCODE = CONTR_SERVICES.SCODE
       AND MPUSNTAB.SCODE = CONTR_SERVICES.SCODE
       AND CCONTACT_ALL.customer_id = CONTRACT_ALL.customer_id
       AND CONTRACT_ALL.co_id = CONTRACT_PHONENR.co_id
       AND CONTRACT_ALL.co_id = CONTR_SERVICES.co_id
       AND CONTRACT_PHONENR.co_id = CONTR_SERVICES.co_id
       AND CCONTACT_ALL.CCBILL = 'X'
       AND CONTR_SERVICES.CS_STAT_CHNG LIKE ('%a')
       AND FU_PROFILE.HZ_FLAG IS NOT NULL;
       AND SUBSTR (NVL (CONTR_SERVICES.CS_STAT_CHNG, 'a'), -1, 1) != 'd'
       AND CONTR_SERVICES.SCODE <> 4520 ;
     COMMIT;
  END PR_LOAD_SMS;


Error:
LINE/COL ERROR
-------- -----------------------------------------------------------------
13/58    PLS-00103: Encountered the symbol "YYMMDD" when expecting one of
         the following:
         * & = - + ; < / > at in is mod remainder not rem return
         returning <an exponent (**)> <> or != or ~= >= <= <> and or
         like LIKE2_ LIKE4_ LIKEC_ between into using || multiset bulk
         member SUBMULTISET_


Thank you
Re: Procedure error [message #398621 is a reply to message #398620] Thu, 16 April 2009 23:31 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Too bad we do not know which line generates the error.

Good Luck solving YOUR mystery!
Re: Procedure error [message #398622 is a reply to message #398621] Thu, 16 April 2009 23:34 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

TO_DATE(SUBSTR(CONTR_SERVICES.CS_STAT_CHNG,-7,6),'YYMMDD') ACT_DT,
Re: Procedure error [message #398630 is a reply to message #398622] Fri, 17 April 2009 00:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How do we include quotes in strings again?

And why do you use dynamic sql for this?
Oh, and your procedure is not errorring out during execution, but during compilation.

[Updated on: Fri, 17 April 2009 00:03]

Report message to a moderator

Re: Procedure error [message #398635 is a reply to message #398630] Fri, 17 April 2009 00:51 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
Hi user,

Below example might be helpfull for you.
Make the changes in your code as required.

SQL> select * from t1;

no rows selected

SQL>  create or replace procedure test_pro as
  2   begin
  3    execute immediate ' insert into t1 select 10,20,''H'',to_date(''02-MAR-2009'',''DD-MON-YYYY''
       ) from dual';
  4    end;
  5   /

Procedure created.

SQL> exec test_pro ;

PL/SQL procedure successfully completed.


SQL> select * from t1;

         I          J K                    DD
---------- ---------- -------------------- ---------
        10         20 H                    02-MAR-09

[Updated on: Fri, 17 April 2009 00:53]

Report message to a moderator

Copy data from one environment to anotehr environment [message #398664 is a reply to message #398620] Fri, 17 April 2009 01:53 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,

I am using 2 environments.
1. Development
2. Intergration.

if I execute " SELECT * from EMP" statement in INTEGRATION I am getting data.
But I am not getting the data in DEVELOPMENT. So now I want to copy the data from INTEGRATION to DEVELOPMENT and insert those data to some table .
Pleas let me know the syntax for this requirement.I have already created table called "EMPSS" in development.

Thank you,
Re: Copy data from one environment to anotehr environment [message #398669 is a reply to message #398664] Fri, 17 April 2009 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
exp/imp

Regards
Michel
Re: Copy data from one environment to anotehr environment [message #398676 is a reply to message #398669] Fri, 17 April 2009 02:05 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

I gave the folllowing command.

copy from usr/pwd@<integration host>
     to usr/pwd@<dev host>
Insert into <table_name>(column list)
using
select statement;


can you please correct this.


Thank you
Re: Copy data from one environment to anotehr environment [message #398678 is a reply to message #398676] Fri, 17 April 2009 02:13 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
You can create database link like this http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_5005.htm

and then
Insert into <table_name>(column list)
select (column list) from table@dblinkname;
Re: Copy data from one environment to anotehr environment [message #398682 is a reply to message #398678] Fri, 17 April 2009 02:25 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member


create or replace PROCEDURE PR_LOAD_FORT_SMS
  IS
  BEGIN

     EXECUTE IMMEDIATE 'TRUNCATE TABLE DSS.FORT_HZ';

 --     EXECUTE IMMEDIATE '
     INSERT INTO DSS.FORT_HZ (CO_ID,FU_CODE,FU_DESC,SNCODE,SN_DES,ACT_DT,MSISDN,CODELANG)
     SELECT
       CONTR_SERVICES.CO_ID                                         CO_ID,
       FU_PROFILE.FUCODE                                            FU_CODE,
       FU_PROFILE.FU_DESC                                           FU_DESC,
       CONTR_SERVICES.SNCODE                                        SNCODE,
       MPUSNTAB.DES                                                 SN_DES,
       TO_DATE(SUBSTR(CONTR_SERVICES.CS_STAT_CHNG,-7,6),''YYMMDD'') ACT_DT,
       CONTRACT_PHONENR.NUM                                         MSISDN,
       CCONTACT_ALL.CCLANGUAGE                                      CODELANG
     FROM
       SCS.CONTR_SERVICES        CONTR_SERVICES,
       SCS.FU_PROFILE            FU_PROFILE,
       SCS.MPUSNTAB              MPUSNTAB,
       DSS.CONTRACT_PHONENR       CONTRACT_PHONENR,
       SCS.CONTRACT_ALL          CONTRACT_ALL,
       SCS.CCONTACT_ALL          CCONTACT_ALL,
       SCS.FULKSN                FULKSN
     WHERE
       FULKSN.FUCODE = FU_PROFILE.FUCODE
       AND FULKSN.SNCODE = CONTR_SERVICES.SNCODE
       AND MPUSNTAB.SNCODE = CONTR_SERVICES.SNCODE
       AND CCONTACT_ALL.customer_id = CONTRACT_ALL.customer_id
       AND CONTRACT_ALL.co_id = CONTRACT_PHONENR.co_id
       AND CONTRACT_ALL.co_id = CONTR_SERVICES.co_id
       AND CONTRACT_PHONENR.co_id = CONTR_SERVICES.co_id
       AND CCONTACT_ALL.CCBILL = ''X''
       AND CONTR_SERVICES.CS_STAT_CHNG LIKE (''%a'')
       AND FU_PROFILE.HZ_FLAG IS NOT NULL
       AND SUBSTR (NVL (CONTR_SERVICES.CS_STAT_CHNG, ''a''), -1, 1) != ''d''
       AND CONTR_SERVICES.SNCODE <> 2530;

     COMMIT;

  END PR_LOAD_FORT_SMS;


Error :

[1]: Statement processed in 0.08 sec with warnings
[1]: (Warning) PL/SQL: ORA-00907: missing right parenthesis
[1]: (Warning) PL/SQL: SQL Statement ignored


can you please correct this.

Thank you

[Updated on: Fri, 17 April 2009 02:29]

Report message to a moderator

Re: Copy data from one environment to anotehr environment [message #398684 is a reply to message #398682] Fri, 17 April 2009 02:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you unable to read what you wrote, or do you just don't understand it?

Regards
Michel
Re: Copy data from one environment to anotehr environment [message #398685 is a reply to message #398684] Fri, 17 April 2009 02:30 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

I have written.... but I am not able to find where I am getting exactly.... please correct me where I am doing mistake...

Thank you
Re: Copy data from one environment to anotehr environment [message #398687 is a reply to message #398685] Fri, 17 April 2009 02:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you wrote it you would know why you doubled the quote.

Regards
Michel

[Updated on: Fri, 17 April 2009 02:33]

Report message to a moderator

Re: Copy data from one environment to anotehr environment [message #398694 is a reply to message #398687] Fri, 17 April 2009 02:40 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Michel,

I should use Dunamic SQL stateent. But for testing purpose I have commneted Execute Immediate statement. Now I have delete double quotes and it compiled successfully.
while uncommneting Dynamic sql statement I am getting error again.
So can you please guide me to use execute immediate in this.

Thank you
Re: Copy data from one environment to anotehr environment [message #398697 is a reply to message #398694] Fri, 17 April 2009 02:45 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:
I should use Dunamic SQL stateent


Why are you forced to use dynamic Sql statement when static sql works?

and

Can't you identify issue yourself when you yourself did following?

1. Commented Execute immediate but had 2 single quotes and it was not compiling
2. Then you removed 2 double quote and just had 1 single quotes and it worked
3. Now you removed commented part of execute immediate it is again not compiling.

[Updated on: Fri, 17 April 2009 03:05]

Report message to a moderator

Re: Procedure error [merged] [message #398799 is a reply to message #398620] Fri, 17 April 2009 11:51 Go to previous messageGo to next message
yugi.adapala
Messages: 10
Registered: April 2009
Junior Member
create or replace PROCEDURE PR_LOAD_SMS
  IS
  BEGIN
     EXECUTE IMMEDIATE 'TRUNCATE TABLE DSS.FORT_HZM';
     EXECUTE IMMEDIATE '
     INSERT INTO DSS.FORT_HZM (CO_ID,FU_CODE,FU_DESC,SCODE,S_DES,AT_DT,SDN,CODELANG)
     SELECT
       CONTR_SERVICES.CO_ID                                         CO_ID,
       FU_PROFILE.FUCODE                                            FU_CODE,
       FU_PROFILE.FU_DESC                                           FU_DESC,
       CONTR_SERVICES.SCODE                                        SCODE,
       MPUSNTAB.DES                                                 S_DES,
       TO_DATE(SUBSTR(CONTR_SERVICES.CS_STAT_CHNG,-7,6),''YYMMDD'') ACT_DT,
       CONTRACT_PHONENR.NUM                                         SDN,
       CCONTACT_ALL.CCLANGUAGE                                      CODELANG
     FROM
       SCS.CONTR_SERVICES        CONTR_SERVICES,
       SCS.FU_PROFILE            FU_PROFILE,
       SCS.MPUSNTAB              MPUSNTAB,
       DSS.CONTRACT_PHONENR       CONTRACT_PHONENR,
       SCS.CONTRACT_ALL          CONTRACT_ALL,
       SCS.CCONTACT_ALL          CCONTACT_ALL,
       SCS.FULKSN                FULKSN
     WHERE
       FULKSN.FUCODE = FU_PROFILE.FUCODE
       AND FULKSN.SCODE = CONTR_SERVICES.SCODE
       AND MPUSNTAB.SCODE = CONTR_SERVICES.SCODE
       AND CCONTACT_ALL.customer_id = CONTRACT_ALL.customer_id
       AND CONTRACT_ALL.co_id = CONTRACT_PHONENR.co_id
       AND CONTRACT_ALL.co_id = CONTR_SERVICES.co_id
       AND CONTRACT_PHONENR.co_id = CONTR_SERVICES.co_id
       AND CCONTACT_ALL.CCBILL = ''X''
       AND CONTR_SERVICES.CS_STAT_CHNG LIKE (''%a'')
       AND FU_PROFILE.HZ_FLAG IS NOT NULL;
       AND SUBSTR (NVL (CONTR_SERVICES.CS_STAT_CHNG, ''a''), -1, 1) != ''d''
       AND CONTR_SERVICES.SCODE <> 4520 ;'
     COMMIT;
  END PR_LOAD_SMS;

[Updated on: Fri, 17 April 2009 11:52]

Report message to a moderator

Re: Procedure error [merged] [message #398802 is a reply to message #398799] Fri, 17 April 2009 12:00 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read the replies before posting it may prevent you from posting something less than optimal:
bonker wrote on Fri, 17 April 2009 09:45
Quote:
I should use Dunamic SQL stateent


Why are you forced to use dynamic Sql statement when static sql works?
...

Regards
Michel
Previous Topic: COM Object or Command Line Triggers
Next Topic: Convert Relational table into Hierarchical table
Goto Forum:
  


Current Time: Sun Dec 04 14:57:13 CST 2016

Total time taken to generate the page: 0.27058 seconds