CREATE.. SELECT FROM PLAN_TABLE - illegal LONG datatype

[kjv1611]

Here's where I'm at with this issue...

I'd like to view the PLAN_TABLE (or some variant of it) via Access or Excel, so I can more easily view it.

When I try to import the table (ODBC) into MS Access, I get the field headers, but no data.

So, I thought I'd try Excel to see... in Excel, same ODBC connection, I get nothing... it acts like it's going to work, and then gives me no data.

So I thought, well, I'll just copy PLAN_TABLE into another table, and when I do, then I can see the data from THAT table.

Well, apparently not so... b/c now when I try something like:

[CODE]CREATE TABLE TESTPLAN AS (SELECT * FROM PLAN_TABLE WHERE 1 = 0)

Then I get this error:

ERROR at line 1:
ORA-00997: illegal use of LONG datatype

I get the same error message if I do not include the 1 - 0 portion..

Any thoughts?

 

[Turkbear]

Hi,
Since that object contains a LONG datatype a select into statement will not work.

What user account are you using to access this?

PLAN_TABLE is usually created as a PUBLIC synonym for sys.plan_table$ so any user account should work..

If not, perhaps utlxplan was not run, so try connecting as sys and using plan_table$





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[kjv1611]

Hmm, connecting as sys.... I don't know a password for that one, so I'm assuming that I am probably not supposed to. I've got login credentials for 3 logins, but not sys....

Or does sys not require a separate login password?

I've got something running right now, so perhaps I'll see if I can log in as sys just to see..

thanks.

 

[Turkbear]

Hi,
You will not be able to log in as sys unless you are the DBA for that database since it is the ultimate account ( like administrator in windows or root in *nix ) - unless the DBA has crappy security knowledge.

The PUBLIC synonym should be available to all users so maybe check with the DBA to see what's up.







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[kjv1611]

Thanks. I'll do some more digging to see what I can find. Frankly, Oracle has been handled as more or less the red-headed step-child best I know so far. So, knowing exactly whom is "the" in charge person well might take longer than figuring out another way.

But I'll post back when I get it sorted. From another parallel thread, same topic came up in another light (the Plan_Table), and it may simply be that I didn't COMMIT the data... which I still don't fully understand.