Home » SQL & PL/SQL » SQL & PL/SQL » execution problem (oracle 10g)
execution problem [message #395375] |
Wed, 01 April 2009 04:37 |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
hi, i have created a procedure which has to insert values into a table based on the other table.the procedure is like this.
CREATE OR REPLACE PROCEDURE ImportData(Parameter Varchar2) AS
BeginIndex INT;
Token VARCHAR2(255);
LastIndex INT;
Test_id INT;
Test_Name VARCHAR2(255);
Test_Cdc_Code VARCHAR2(50);
Begin
BeginIndex := 1;
LastIndex := 1;
Select EZEMRXID into Test_id From EMRIDS Where Property_Name='TEST_ID';
Test_id := Test_id + 1;
DBMS_OUTPUT.PUT_LINE(Test_id);
While (LastIndex <>0) loop
Begin
LastIndex:=INSTR(Parameter,'@',BeginIndex);
IF(LastIndex>0) THEN
Token:=(SUBSTR(Parameter,BeginIndex,LastIndex-BeginIndex));
IF(Length(Token)>=1 and Token<>' ') THEN
Token:=Rtrim(Token);
END IF;
ELSE
Token:=RTRIM(SUBSTR(Parameter,BeginIndex,LENGTH(Parameter)));
END IF;
IF(LastIndex>0) THEN BeginIndex:=LastIndex+1;ELSE BeginIndex:=-1; END IF;
Test_Cdc_Code := Token;
IF (LastIndex>0) THEN
IF(LastIndex>0) THEN
LastIndex:=INSTR(Parameter,'@@',BeginIndex);
Token:=(SUBSTR(Parameter,BeginIndex,LastIndex-BeginIndex));
IF(Length(Token)>=1 and Token<>' ') THEN
Token:=Rtrim(Token);
END IF;
ELSE
Token:=RTRIM(SUBSTR(Parameter,BeginIndex,LENGTH(Parameter)));
END IF;
IF(LastIndex>0) THEN BeginIndex:=LastIndex+2;ELSE BeginIndex:=-1; END IF;
END IF;
Test_Name := RTRIM(Token);
IF (LastIndex>0) THEN
Insert Into EMRLABTESTLKUP (TEST_ID, TEST_NAME, LAB_NAME, PARENT_TEST_ID, TEST_CDC_CODE, IS_ACTIVE, IS_AOE, AOE_TEST_PRERESULT, AOE_RESULT_FILTER,
SPECIMEN_CONTAINER, SPECIMEN_STORAGE, METHODOLOGY, SPECIMEN_REQUIREMENTS, SPECIMEN_CONDITIONS, SPECIMEN_VOLUME, GROUP_ID, TEST_SPECIMEN, CATEGORY)
SELECT Test_id, Test_Name, 'OTHERS', 0, Test_Cdc_Code, 1, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, 1 from dual;
Test_id := Test_id + 1;
End IF;
End;
END LOOP;
Update EMRIDS SET EZEMRXID = Test_id Where Property_Name='TEST_ID';
End;
/
exec ImportData('70010@MYELOGRAPHY; INTERPRETATION ONLY @@70015@CISTERNOGRAPHY; INTERPRET ONLY@@70030@X-RAY EYE; DETECT FOREIGN BODY@@70100@X-RAY MANDIBLE; PARTIAL@@70110@X-RAYMANDIBLE; COMPLETE@@70120@X-RAY MASTOIDS;L3 VIEWS PER SIDE@@70130@COMPLETEX-RAY,MASTOIDS-3 VIEWS/SIDE@@70134@X-RAY INTERNAL AUDITORY MEATI@@70140@X-RAY FACIALBONES; L3 VIEWS@@70150@X-RAY FACIAL BONES; COMPLETE@@70160@X-RAY NASAL BONES;COMPLETE@@70170@DACRYOCYSTOGRAPHY; INTERPRET ONLY @@70190@X-RAY OPTICFORAMINA@@70200@X-RAY ORBITS,COMPLETE,4+ VIEWS@@70210@X-RAY SINUSES; PARANASAL; L3 VIEWS@@70220@X-RAY SINUSES; PARANASAL; COMPLETE@@70240@X-RAY SELLA TURCICA@@70250@X-RAY SKULL; LESS THAN 4 VIEWS@@70260@X-RAY SKULL; COMPLETE@@70300@X-RAY TEETH; SINGLE VIEW@@70310@X-RAY TEETH; PARTIAL EXAM@@70320@X-RAY TEETH; COMPLETE; FULL MOUTH@@70328@X-RAY TEMPOROMAN DIBULAR JNT; UNIL@@70330@ARTHROTOMOGRAPHY;TEMPOROMAND.-COMPLT@@70332@TEMPOROMAND.ARTHROGRAPHY;SUPER/INT @@70336@MRI,TEMPOROMANDIBULAR JOINT @@70350@CEPHALOGRAM; ORTHODONTIC@@70355@ORTHOPANTOGRAM@@70360@X-RAY NECK; SOFT TISSUE@@70370@X-RAY PHARYNX/LARYNX W/FLOUROSCPY @@70371@COMPLEX DYNAMIC PHARYNGEAL AND SPEECH EVALUATION BY CINE OR VIDEO RECORDING@@70373@LARYNGOGRAPHY; INTERPRET ONLY @@70380@X-RAY SALIVARY GLANDFOR CALCULUS @@70390@SIALOGRAPHY; INTERPRETATION ONLY @@70450@CAT,HEAD/BRAIN;W/OUT CONTRAST MATER. @@70460@CAT,HEAD/BRAIN;W/CONTRAST MATERIAL @@70470@CAT,HEAD/BRAIN;W/OUT-W/CONTRAST @@70480@TOMOGRAPHY;ORBIT,SELLA,POSTERIOR FOS @@70481@TOMOGRAPHY;ORBIT,ETC,WITH/CONTRAST M @@70482@CAT,ORBIT,ETC.,W/OUT-W/ CONTRAST MAT @@70486@TOMOGRAPHY;MAXILLOFACIAL W/OUT CONTR @@70487@TOMOGRAPHY;MAXILLOFAC,WITH CONTRAST @@70488@CAT;MAXILL.;W/OUT-W/ CONTRAST MATER. @@70490@CAT,SOFT TISSUE NECK;W/OUT CONTRAST @@70491@CAT.SOFT TISSUE NECK;W/ CONTRAST MAT @@70492@CAT,NECK;W/OUT-W/ CONTRAST MATERIAL @@70496@CT ANGIOGRAPHY HEAD @@70498@CT ANGIOGRAPHY NECK @@70540@MRI-ORBIT,FACE AND NECK @@70542@MR IMAGING ORBIT, FACE, AND NECK @@70543@MR IMAGING ORBIT, FACE , AND NECK@@ 70544@MR ANGIOGRAPHY HEAD @@70545@MR ANGIOGRAPHY @@70546@MR ANGIOGRAPHY NECK @@70547@MR ANGIOGRAPHY NECK; WITHOUT CONTRAS @@70548@MR ANGIOGRAPHY NECK WITH CONSTRAST @@70549@MR ANGIOGRAPHY NECK WITHOUT CONTRAS @@70551@MRI-BRAIN/INCLUDING BRAIN STEM W/O CONTRAST MATERIAL@@70552@MRI,BRAIN W/ CONTRAST MATERIAL @@70553@MAGNETIC RESONANCE (EG, PROTON) IMAG W/OUT-W/ CONTRAST MAT @@70554@MAGNETIC RESONANCE (EG, PROTON) IMAG INCL. TEST SELECTION AND ADMINISTRATIONOF REPETITIVE BODY PART MOVEMENT, NOT REQUIRING PHYSICIAN OR PSYCHOLOGIST ADM.@@70555@MAGNETIC RESONANCE (EG, PROTON) IMAG INCL. TEST SELECTION AND ADMINISTRATION OF REPETITIVE BODY PART MOVEMENT, REQUIRING PHYSICIAN OR PSYCHOLOGIST ADMINISTRATION OF ENTIRE NEUROFUNCTIONAL TESTING@@70557@MAGNETIC RESONANCE IMAGING, BRAIN, DURING OPEN INTRACRANIAL PROCEDURE;WITHOUT CONTRAST MATERIAL(S), FOLLOWED BY CONTRAST MATERIAL(S) AND FURTHER SEQUENCES@@70558@MAGNETIC RESONANCE IMAGING, BRAIN, DURING OPEN INTRACRANIAL PROCEDURE; WITH CONTRAST MATERIAL@@70559@MAGNETIC RESONANCE IMAGING, BRAIN, DURING OPEN INTRACRANIAL PROCEDURE;WITHOUT CONTRAST MATERIAL(S), FOLLOWED BY CONTRAST MATERIAL(S) AND FURTHER SEQUENCES@@71010@X-RAY CHEST; SINGLE VIEW, FRONTAL@@71015@X-RAY CHEST; STEREO;FRONTAL@@71020@X-RAY CHEST; TWO VIEWS@@71021@X-RAY CHEST; APICAL LORDOTIC@@71022@X-RAY CHEST; OBLIQUE PROJECTIONS@@71023@X-RAY CHEST,2 VIEWS,FRONT,LAT.FLUORO@@71030@X-RAY CHEST; MININUM OF 4 VIEWS@@71034@X-RAY CHEST W/FLUOROSCOPY @@71035@X-RAY CHEST; SPECIAL VIEWS@@71040@CONTRAST X-RAY OF BRONCHI @@71060@CONTRAST X-RAY OF BRONCHI @@71090@X-RAY AND PACEMAKER INSERTION@@71100@X-RAY EXAM OF RIBS@@71101@X-RAY EXAM RIBS-POSTEROANTER CHEST@@71110@X-RAY EXAM OF RIBS@@71111@X-RAY RIBS,BILAT;POSTEROANTERI CHEST@@71120@X-RAY EXAM OF BREASTBONE@@71130@X-RAY EXAM OF BREASTBONE@@71250@CAT,THORAX;W/OUT CONTRAST MATERIAL @@71260@CAT,THORAX; W/ CONTRAST MATERIAL @@71270@CAT,THORAX;W/OUT-W/ CONTRAST MATERIA@@71275@CT ANGIOGRAPHY, CHEST WITH /WITHOUT CONTRAST @@')
/
i am able to insert the above values in to the table.but with in another data base they are not able to get it.is exec with stored procedure is causing problem while excecution.is there any other way to execute the procedure.can anyone help me out.i have done this in oracle sql developer.will sql*plus won't support it,
** the error they are getting is use ; execute something which i dont know exactly
[Updated on: Wed, 01 April 2009 05:06] by Moderator Report message to a moderator
|
|
|
|
|
Re: execution problem [message #395391 is a reply to message #395382] |
Wed, 01 April 2009 05:10 |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
is there any wrong in doing so
like
Exec Sal_raise(mine, 200K); and
Exec Sal_raise(mine, 200K)
/
will it throw any exception in doing so like if we rin in oracle sql*plus and oracle sql* developer
because i have did that in second way in oracle sql developer
|
|
|
Re: execution problem [message #395394 is a reply to message #395391] |
Wed, 01 April 2009 05:14 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Every time I try to do an EXEC in Sql Developer, I get an ORA-00900 Invalid SQL statement error.
Are you certain that you are doing what you say you are?
What version of SQL Developer are you using?
|
|
|
|
|
|
|
Re: execution problem [message #395409 is a reply to message #395406] |
Wed, 01 April 2009 06:03 |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
Thank you,Thats what i am waiting for. Because upto now i have no knowlege on that.can i go with begin --- end; will it work know in toad using an anonymous block
|
|
|
|
Re: execution problem [message #395414 is a reply to message #395394] |
Wed, 01 April 2009 06:18 |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
JRowbottom wrote on Wed, 01 April 2009 11:14 | Every time I try to do an EXEC in Sql Developer, I get an ORA-00900 Invalid SQL statement error.
|
I think that if you use Run Script instead of Exectute statement it will work (I don't have access at the mo so I can't confirm)
|
|
|
|
Re: execution problem [message #395420 is a reply to message #395396] |
Wed, 01 April 2009 06:55 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Run Script works in Sql Developer with Exec, but Exectue command doesn't - Thanks for the pointer.
It's more portable to use than as the first is valid anywhere, but the second uses what was originally an SQL*Plus specific command.
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Nov 13 00:28:51 CST 2024
|