Home » SQL & PL/SQL » SQL & PL/SQL » execution problem (oracle 10g)
execution problem [message #395375] Wed, 01 April 2009 04:37 Go to next message
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 #395380 is a reply to message #395375] Wed, 01 April 2009 04:53 Go to previous messageGo to next message
cookiemonster
Messages: 13952
Registered: September 2008
Location: Rainy Manchester
Senior Member
YOu need to tell us what the actual error message is.

There is no reason why exec procedure wouldn't work in sqlplus.
Re: execution problem [message #395382 is a reply to message #395375] Wed, 01 April 2009 04:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The actual Error message they're getting would be really useful....
Re: execution problem [message #395391 is a reply to message #395382] Wed, 01 April 2009 05:10 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #395396 is a reply to message #395394] Wed, 01 April 2009 05:19 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
yes i am sure about my thing.

version----1.0.0.15.57
Re: execution problem [message #395399 is a reply to message #395396] Wed, 01 April 2009 05:33 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
will oracle sql developer supports exec for latest versions only.am i correct?
Re: execution problem [message #395403 is a reply to message #395399] Wed, 01 April 2009 05:54 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hai will the above one work with toad? because client were using the toad and i have to execute through anonymous block like
begin
----
end;?
Re: execution problem [message #395406 is a reply to message #395375] Wed, 01 April 2009 05:59 Go to previous messageGo to next message
cookiemonster
Messages: 13952
Registered: September 2008
Location: Rainy Manchester
Senior Member
As far as I'm aware exec will not work in toad.
It's a sqlplus command, it's not an oracle command.
Re: execution problem [message #395409 is a reply to message #395406] Wed, 01 April 2009 06:03 Go to previous messageGo to next message
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 #395412 is a reply to message #395375] Wed, 01 April 2009 06:12 Go to previous messageGo to next message
cookiemonster
Messages: 13952
Registered: September 2008
Location: Rainy Manchester
Senior Member
pretty much all database tools can run annoymous PL/SQL blocks - they wouldn't be much use otherwise.
Re: execution problem [message #395414 is a reply to message #395394] Wed, 01 April 2009 06:18 Go to previous messageGo to next message
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 #395416 is a reply to message #395412] Wed, 01 April 2009 06:19 Go to previous messageGo to next message
trivendra
Messages: 211
Registered: October 2007
Location: Phoenix
Senior Member
EXEC or EXECUTE both work on TOAD.
Re: execution problem [message #395420 is a reply to message #395396] Wed, 01 April 2009 06:55 Go to previous messageGo to next message
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
BEGIN
  test_proc;
END;
/
than
exec test_proc;
as the first is valid anywhere, but the second uses what was originally an SQL*Plus specific command.
icon7.gif  Re: execution problem [message #395422 is a reply to message #395420] Wed, 01 April 2009 07:12 Go to previous messageGo to next message
trivendra
Messages: 211
Registered: October 2007
Location: Phoenix
Senior Member
@JRowbottom

Thanks for the update on SQL Developer.


Thanks
Trivendra




Re: execution problem [message #395448 is a reply to message #395420] Wed, 01 April 2009 08:23 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Quote:
It's more portable to use

Agreed
Re: execution problem [message #395474 is a reply to message #395422] Wed, 01 April 2009 10:21 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It was @PabloLee who mentioned it - I just checked it worked.
Previous Topic: Building a list of dates
Next Topic: Doubt in SQL query
Goto Forum:
  


Current Time: Wed Nov 13 00:28:51 CST 2024