Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Internal error code, arguments : [17182], [185212224], [], [], [], [], [],

420827Dec 19 2006 — edited Dec 19 2006

Hello,
My oracle server has an internal error code , arguments : [17182], [185212224], [], [], [], [], [], [] when i run a decode function from an oracle client. I use an Oracle 8.1.7.0 on Windows 2000 server. I searched solutions in the net but nothing. Does anybody could help me to solve this problem.

Need your help,

Below the output of my oracle trace

Many thanks.

ksedmp: internal or fatal error
ORA-00600: code erreur interne, arguments : [17182], [185212224], [], [], [], [], [], []
Current SQL statement for this session:
SELECT ALL decode(FSQ_FORMATIONS.FRMT_TYPE,:"SYS_B_00",:"SYS_B_01",:"SYS_B_02",:"SYS_B_03",:"SYS_B_04",:"SYS_B_05",:"SYS_B_06",:"SYS_B_07",:"SYS_B_08",:"SYS_B_09",:"SYS_B_10",:"SYS_B_11",:"SYS_B_12",:"SYS_B_13",:"SYS_B_14",:"SYS_B_15" ,
:"SYS_B_16",:"SYS_B_17",:"SYS_B_18",:"SYS_B_19"),
decode (FSQ_FORMATIONS.FRMT_TYPE,:"SYS_B_20",:"SYS_B_21" ,:"SYS_B_22",:"SYS_B_23",:"SYS_B_24",:"SYS_B_25",:"SYS_B_26",:"SYS_B_27",:"SYS_B_28",:"SYS_B_29",:"SYS_B_30",:"SYS_B_31",:"SYS_B_32",:"SYS_B_33",:"SYS_B_34",:"SYS_B_35",:"SYS_B_36",:"SYS_B_37",:"SYS_B_38",:"SYS_B_39") type,
decode (FSQ_FORMATIONS.FRMT_TYPE,:"SYS_B_40",:"SYS_B_41" ,:"SYS_B_42") type_long,
FSQ_DOCUMENTS.DOC_REF,
FSQ_DOCUMENTS.DOC_INTITULE,
FSQ_FORMATIONS.SERVICE_CODE,
VFSQ_QRH_ORG_SERVICES.SERVICE_LIBELLE,
FSQ_FORMATIONS.AGENT_MATRICULE,
VFSQ_QRH_FIP_AGENTS.AGENT_NOM,
VFSQ_QRH_FIP_AGENTS.AGENT_PRENOM,
FSQ_AGENT_POSTES.POSTE_CODE,
FSQ_POSTES.POSTE_LIBELLE,
FSQ_FORMATIONS.FRMT_REELLE_DATE_DEBUT,
FSQ_ANIMATEURS.ANIM_NOM||:"SYS_B_43"|| FSQ_ANIMATEURS.ANIM_PRENOMS,
fsq_doc_indices.doc_indice doc_indice_actuel,
fsq_doc_indices.doc_indice_date doc_indice_date_actuel
FROM FSQ_DOCUMENTS,
FSQ_FORMATIONS,
VFSQ_QRH_FIP_AGENTS,
VFSQ_QRH_ORG_SERVICES,
FSQ_AGENT_POSTES,
FSQ_POSTES,FSQ_ANIMATEURS,
fsq_doc_indices
WHERE ( ( FSQ_FORMATIONS.DOC_REF = FSQ_DOCUMENTS.DOC_REF ) AND ( FSQ_FORMATIONS.SERVICE_CODE = VFSQ_QRH_ORG_SERVICES.SERVICE_CODE ) AND ( FSQ_FORMATIONS.AGENT_MATRICULE = VFSQ_QRH_FIP_AGENTS.AGENT_MATRICULE ) AND ( FSQ_FORMATIONS.AGENT_MATRICULE = FSQ_AGENT_POSTES.AGENT_MATRICULE AND FSQ_FORMATIONS.SITE_CODE = FSQ_AGENT_POSTES.SITE_CODE AND FSQ_FORMATIONS.STE_CODE = FSQ_AGENT_POSTES.STE_CODE ) AND ( FSQ_AGENT_POSTES.POSTE_CODE = FSQ_POSTES.POSTE_CODE ) ) AND FSQ_FORMATIONS.DOC_REF <> :"SYS_B_44" AND FSQ_FORMATIONS.ANIM_ID = FSQ_ANIMATEURS.ANIM_ID (+) AND FSQ_FORMATIONS.FRMT_AGENT_CONCERNE = :"SYS_B_45" AND FSQ_FORMATIONS.DOC_REF = : p_doc AND FSQ_FORMATIONS.DOC_INDICE = : p_indice AND FSQ_FORMATIONS.FRMT_REALISEE like decode ( : p_realisee , :"SYS_B_46" , :"SYS_B_47" , :"SYS_B_48" , :"SYS_B_49" , :"SYS_B_50" , :"SYS_B_51" ) and FSQ_FORMATIONS.service_code = substr ( : p_service , :"SYS_B_52" , instr ( : p_service , :"SYS_B_53" ) - :"SYS_B_54" ) and fsq_documents.doc_ref = fsq_doc_indices.doc_ref and fsq_formations.doc_ref = fsq_documents.doc_ref and fsq_doc_indices.DOC_INDICE = : p_indice GROUP BY fsq_doc_indices.doc_indice , fsq_doc_indices.doc_indice_date , FSQ_FORMATIONS.FRMT_TYPE , FSQ_FORMATIONS.SERVICE_CODE , VFSQ_QRH_ORG_SERVICES.SERVICE_LIBELLE , FSQ_FORMATIONS.AGENT_MATRICULE , VFSQ_QRH_FIP_AGENTS.AGENT_NOM , VFSQ_QRH_FIP_AGENTS.AGENT_PRENOM , FSQ_AGENT_POSTES.POSTE_CODE , FSQ_POSTES.POSTE_LIBELLE , FSQ_FORMATIONS.FRMT_REELLE_DATE_DEBUT , FSQ_ANIMATEURS.ANIM_NOM , FSQ_ANIMATEURS.ANIM_PRENOMS , FSQ_DOCUMENTS.DOC_REF , FSQ_DOCUMENTS.DOC_INTITULE ORDER BY :"SYS_B_55" ASC,:"SYS_B_56" ASC,:"SYS_B_57" ASC,:"SYS_B_58" ASC,:"SYS_B_59" ASC,:"SYS_B_60" ASC , VFSQ_QRH_ORG_SERVICES.SERVICE_LIBELLE ASC
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------

Cookiemonster76

So loop over the table. Inside the loop build up a string of the text you want in the email.

After the loop check if the string is empty, if it is assign the not exists message

After that call the procedure to send the email.

user12251389

I tried but still had the same issue

Saubhik

user12251389 wrote:

I tried but still had the same issue

What is the exact problem you are facing ?

John Stegeman

You tried and it didn't work? What does "didn't work" mean? What did you try - show us?

1 person found this helpful

user12251389

I have currently 2 records in TRACK_KPI table. When i execute this procedure i am getting 2 emails one for each record. So for example if i have 100 records now in TRACK_KPI table then i will receive 100 emails for each records which i dont want. I want all this records from TRACK_KPI table send in one email

John Stegeman

Yes, because you send the email in a loop. Don't do that. Instead, build the email content in the loop (hint: store it in a CLOB or varchar2 variable, depending on the potential size) and send the email outside the loop

user12251389

Here is what i have tried :

FOR KPI_TRACK_RUNNING_ROW IN (Select KPI_NAME FROM RATOR_MONITORING.TRACK_KPI)

LOOP

sql='SEND_MAIL_SMTP(

''support@leo'', --Sender

''r.d@leo.com'', --Recipient

''PREPROD - KPI NOT RUNNING'', --Subject

''Below KPIs are not running:''' || KPI_TRACK_RUNNING_ROW.KPI_NAME --Message

);

execute immediate l_sql;

END LOOP;

Saubhik

Answer

You need something like

CREATE OR REPLACE PROCEDURE execute_send_mail_smtp AS

v_kpi varchar2(4000) :=' '; --assuming your kpi_names are not long.

begin

FOR KPI_TRACK_ROW IN (Select KPI_NAME FROM RATOR_MONITORING.TRACK_KPI)

LOOP

v_kpi :=v_kpi||KPI_TRACK_RUNNING_ROW.KPI_NAME||chr(10);

END LOOP;

SEND_MAIL_SMTP(

'support@leo.com', --Sender

'r.d@leo.com', --Recipient

'TEST- KPI NOT RUNNING', --Subject

'Below KPIs are not running:' || v_kpi --Message

);

end execute_send_mail_smtp;

NOT TESTED.

Marked as Answer by user12251389 · Sep 27 2020

user12251389

How can i build email content outside loop ? I have procedure SEND_MAIL_SMTP also which i cant take it in string ?

John Stegeman

send_mail_smtp takes a parameter. Saubhik gave you an example.

user12251389

I tried your logic it sends email without any data and when i replace

v_kpi := v_kpi||chr(10);

v_kpi :=KPI_TRACK_ROW.KPI_NAME||chr(10);

in your logic then it sends an email with only one record but there are two records exist in my table

John Stegeman

Think about it...

Why did you replace Saubhik's code, which looked correct with yours, which is obviously not?

Saubhik

I have edited that portion, but this is not a code writing service and as a professional you should take the idea and write, debug yourself.

1 person found this helpful

user12251389

Now it works thanks but just a small question can i use IF condition in this logic ? Because if there is no records exist in the TRACK_KPI table then its not sending any emai. So if there is nor record exist then i just want to send email as 'NO KPI Tracked'

John Stegeman

and just noticed... why the heck are you using execute immediate to call a stored procedure from PL/SQL? There's no need for that

John Stegeman

You can use any PL/SQL construct you like, including "IF"

user12251389

Yes it was mistake i copy paste and didn't realize

user12251389

I have tried with IF logic and it works now. Just a small question can we send email to more than one Recipient ? When i tried to enter 2 Recipient then it gaves me error as

ora-29279 smtp permanent error 550 user unknown

John Stegeman

Talk to whomever wrote send_mail_smtp, or look at the code yourself and figure it out. Of course SMTP can send mail to more than one recipient, if you do it correctly.

1 - 19

Locked Post

New comments cannot be posted to this locked post.

Locked on Jan 16 2007

Added on Dec 19 2006

#general-database-discussions

5 comments

589 views

Oracle Database Discussions

Internal error code, arguments : [17182], [185212224], [], [], [], [], [],

Comments

Post Details