Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

SQL in proc not working

Posted on 2013-10-22
11
Medium Priority
?
350 Views
Last Modified: 2013-11-06
Im trying to run a procedure but when I try to compile it gives me an error. Here is the SQL statement it seems to have a problem with. I am doing alot more than just this statement but it highlights this one on compile. It also creates the procedure? When I try to compile it has a red line through it and it says
" ORA-00900: invalid SQL statement
 (48: 0): INNER
Error at line 1
ORA-00900: invalid SQL statement


Here is the SQL (bold line is highlighted when I try to compile)
        Select TITLE, DEPARTMENT, HAYPOINTS,
        NUM_POSITIONS, SALARYMIN, SALARYMAX,
        SUPERVISOR, LOCATION,b.PayGroup_Desc,
        CLOSING_DATE, ANNOUNCE_NUM, c.PARA_TEXT JobDesc, d.PARA_TEXT JobSkill      
        INTO vTitle, vDept, nGrade,
        nNumPositions, iSalaryMin, iSalaryMax,
        vSupervisor, vLocation, vPayroll,
        dCloseDate, nAnnouncement, vJobDesc, vJobSkill        
        FROM TAL_MAIN A
        INNER JOIN TAL_PAYGROUPS b ON A.PAYGROUP_ID = B.PAYGROUP_ID
       LEFT JOIN OMNI_ENT_PARAGRAPH c ON A.JOBDESC_ID=C.PARA_ID
        LEFT JOIN OMNI_ENT_PARAGRAPH d ON A.JOBSKILL_ID=D.PARA_ID
        WHERE a.TALENT_ID = iTAL_ID;
0
Comment
Question by:jknj72
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39591146
are you using an old version of Oracle?  like 8i or lower?  Older versions don't support ANSI join syntax.

Your syntax looks valid to me provided all of the variables are valid and columns and tables names are correct.  You might want to consider prefixing all of the column references with the table aliases to make sure your references are correct.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39591150
It's also odd that your error says "Line 1"

Are you sure the error isn't somewhere else in the code?
0
 

Author Comment

by:jknj72
ID: 39591241
Im using version 11.6.0.43

I think your right, I think the problem is somewhere else just not sure where. I can run it from TOAD though with

        exec SP_TAL_USERPAGE_CREATE(104, 'HUMAN RESOURCES', 'VCSIJMK')

I attached a pic of the error. It also says something about INNERError? I know how everyone loves me posting pics about my errors but it may help
ORA-00900.jpg
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39591253
This may not be the problem, but you have to be careful with "select ...into" syntax in PL\SQL.  Implicit cursors like this may be easy to write and understand, but your join criteria then need to be tight enough (and your data needs to be clean enough!) to insure that you get exactly one row returned.  Otherwise, this may compile fine, but give a run-time error of either: "no data found" or "too_many_rows".
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39591260
11.6.0.43  is not an Oracle version.  

That looks like a Toad version from a year or so ago.

Try this...

select * from all_errors where name = 'SP_TAL_USERPAGE_CREATE'
0
 

Author Comment

by:jknj72
ID: 39591300
Like I was sayihg I can run it from TOAD and it works fine and when I run it from my application it doesnt give me an error it just doesnt insert the records?

sdstuber:
sorry your right and I ran that sql and nothing was returned?

Ill keep checking on this and if you need me to try anything let me know

Thanks all
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39591333
Perhaps you're not running or compiling the procedure in Toad correctly.

If you have an empty SQL Editor window and then put this line only

exec SP_TAL_USERPAGE_CREATE(104, 'HUMAN RESOURCES', 'VCSIJMK')

then press F9.  What happens?


Similarly, if your SQL Editor window only has the procedure's code and you press F9, what happens?
0
 

Author Comment

by:jknj72
ID: 39591416
F9 seems to run just fine for both scenarios sd? I realize this is hard for you to helpo. Would you like the entire proc. Its very l;arge. It calls 40 procs and queries but if youd llike to see it let me know..

Thanks
JK
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39591493
I don't think sending the code will be needed

to summarize results thus far...

there are no syntax errors in the code
the procedure compiles fine by itself in toad
it runs fine by itself in toad
it compiles fine in sql (I assume you mean sql*plus)
it runs fine in sql

Is that correct?

If so, then what are you doing differently in Toad when you get the error?
There are multiple ways to execute code or pieces of code within the Toad editor.

Are you using a different key or set of keys? Are you clicking buttons within the gui?
Do you have other text within the editor? If so, what is that text?
Are you highlighting anything (even just one character?)
Where do you have the cursor when you attempt to execute or compile?
0
 

Author Closing Comment

by:jknj72
ID: 39627525
Thanks
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39627554
Did you get an answer?
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

647 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question