Rich Text control & Oracle PLSQL procedure

[ivan.klainic@igea.hr]

Hello!

I'm working with Oracle DB and need to store content of Rich Text control via PLSQL procedure.
I found useful samples here https://support.guptatechnologies.com/su ... t+database, but need some sample with Oracle procedure call.

My case:
Our cutomer has a bunch of RTF templates wich we have to import in DB, and than enable edit in GUI.

Problem:
1. I used anonymous block wich worked OK on most of the rtf contents (it updates the table!!). But in some cases when the rtf content is slightly bigger (don't know the limit or if this is a real reason), anonymous block had delete rtf content from table and reported "OK" (?). Is it possible to get some feedback from anonymous block, like wich ORA-Error? I don't know why it didn't update table properly.
2. I couldn't call PLSQL procedure with SqlPLSQLCommand, it reports "String too long" in any case.
3. I don't use basic "update" statement, cause I have to execute other business logic in PLSQL

I have prepared one sample to demonstrate the behaviour. I hope I'm on the right track. Please help!

Thank you!

[ivan.klainic@igea.hr]

Any ideas?

[Jeff Luther]

No ideas, except to ask our developer internally what he thinks. Which I did. I cannot run your test - the BAT file causes this error:
SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12154: TNS:could not resolve the connect identifier specified

But... have you:
** found the SalGetBufferLength() value where your test goes from working to 'fails' ? That boundary value could be good to know.

** Tried assigning a simple string into your Long String to see if this makes a difference, or is this boundary value found above the same? I see on this page:
http://docs.oracle.com/cd/B28359_01/app ... limits.htm

where some PL/SQL length limits are described. Your BiggerDoc is only 20K, though that's going to be a Unicode string when assigned to your Long String var. so buffer length will be 2x that.

Meanwhile, I have asked internally to see what our router guru thinks.

[Jeff Luther]

Update and the developer says:

* There is a 32K length limit passed as a parameter to an SP in Oracle. This is not a TD limit per se, but recall that the Long String string is Unicode, 2 bytes/char.
He also provided me this link to show that info:
http://stackoverflow.com/questions/1864 ... oracle-10g

* As a side note says that a BLOB column type should be used for a RTC value, if the RTC value could have an embedded image. In that case, the image is stored in binary inside the RTC value.
ISSUE MIGHT BE: That BLOB might not be a valid PL/SQL parameter type. I'm asking about this internally.

P.S. Yes, this is verified: It is recommended you use BLOB in Oracle for storing RTC data, for the reason I mentioned above. And a corresponding binary-type LONG column column in other DB flavors.

[ivan.klainic@igea.hr]

I cannot run your test

I use Oracle 10g client and database (10.2.0.1.0). Regarding error you provided, it looks you didn't set TNS properly.
In file LOAD_RTF.bat replace existing values with your values:

Code: Select all

sqlldr your_ora_username/your_ora_password@your_ora_tns_name control = InsertRules.ctl

I'm under deadlines now, so will try your sugestions later on and report my results.
Meanwhile, I found one workaround. That is: create local RTF file from Rich text control content, copy it with FTP on Oracle directory, and call PLSQL procedure to store it. This works, but that's overhead and it is not the way it should stay.

[ivan.klainic@igea.hr]

Hello again!

I found some time and prepare new test case wich should be easier to setup and run (without sql loader).
Regarding your advice, my Oracle columns aren't CLOBs anymore, they are BLOBs now (even though I didnt use images in rtf documents).

My problem is the same: Insert/Update Rich text content through PLSQL procedure.
I tried to do it in three ways:
1. with anonymous PLSQL block (SqlOraPLSQLPrepare, SqlOraPLSQLExecute), it doesn't work!
2. with conventional call of PLSQL procedure (SqlPLSQLCommand), it doesn't work!
3. classic update statement, it works!

Acceptable solution for my problem is to call Oracle PLSQL procedure with BLOB parametar by either way 1 or way 2.

In ccod.pdf (Connecting SQLWindow Objects to Databases), in chapter describing data type maping between SQLWindows and Oracle, it says that I could map SQLWindows Long String to Oracles BLOB, wich I tried to do.
Should I somehow convert Rich text content in binary format to serve it to procedure?!

I expect you to pimp my code, so we can move this thread to SOLVED.

Please help!
Thanks

[Jeff Luther]

I don't know what "pimp my code" means but I am a bit confused. You started this thread with:

Problem:
1. I used anonymous block which worked OK on most of the rtf contents (it updates the table!!). But in some cases when the rtf content is slightly bigger (don't know the limit or if this is a real reason), anonymous block had delete rtf content from table and reported "OK" (?).

But now you write:

My problem is the same: Insert/Update Rich text content through PLSQL procedure.
I tried to do it in three ways:
1. with anonymous PLSQL block (SqlOraPLSQLPrepare, SqlOraPLSQLExecute), it doesn't work!
2. with conventional call of PLSQL procedure (SqlPLSQLCommand), it doesn't work!

It worked earlier for RTF below a certain length, but now does not work?? Or, maybe you mean, it works with setting RTF text to a string var, but not to INS/UPD the RTF value directly??

What's the chance of getting from you a self-contained test case, so I can run the APP from the IDE and all the SQL you now have in several files - ctl, dat, bat - can be done in SAL code? This will make it much easier for me to understand and run, and in the case of a defect, ditto for the developer to run. Thanks.

[ivan.klainic@igea.hr]

What's the chance of getting from you a self-contained test case, so I can run the APP from the IDE and all the SQL you now have in several files - ctl, dat, bat - can be done in SAL code? This will make it much easier for me to understand and run, and in the case of a defect, ditto for the developer to run. Thanks.

I provided self contained test case (without SQL loader).
Initially it creates table, inserts two records in table... and finaly allows you to try out updates on that table.

About the problem:
I used PLSQL anonymous block to update CLOB column with RTF content (it doesn't work for me: above some RTF size there was truncation of CLOB, maybe RTF size question, Long String size or something else? But i do not want to dig it in, because I used advice of your developer: use BLOB type instead of CLOB (in previous posts), so I moved on...

Then I tried to update BLOB:
1) plain SQL update works, but it is not the solution I need.
2) and 3) anonymous plsql block call and SqlPLSQLCommand don't work like i prepare it for you in my testcase. I dont care in wich way I'll have success, but this should be my solution.

Since than (when I changed type to BLOB), I dont manage to update BLOB column with RTF content by PLSQL procedure (before with CLOB that functionalitiy worked partialy).
How to pass Rich text control value to BLOB column through PLSQL procedure?
Note: it has to be through PLSQL procedure, regardless wich way:
call of anonymous plsql block
OR
call of SqlPLSQLCommand.

Thanks

[ivan.klainic@igea.hr]

Anyone?

[ivan.klainic@igea.hr]

Hello!

Is there anything new about this topic?

Thanks

[Jeff Luther]

I'll look at this now, Ivan...

Well, I did you at your latest BLOB attachment, and got part way there. These seem to work:
Init and Select PBs

Not working: Update1. I get -6550 errror:
"ORA-06550: line 3, column 1:
PLS-00306: wrong number or types of arguments in call to 'MYPROC2'
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored"

for this statement:
"BEGIN
myPACKAGE.myPROC2 ( :dfID, :lsHelper );
END;"

I know it is easy to forget this, but for you the test case seems easy to understand (and obvious) to run. For someone else like me, I need some 'hand holding' and if not a really simple test can be difficult to understand:
* What is the sequence of steps to run?
* What "It does not work!" mean with "Update1" PB?? -- Is what I reported above (error -6550) what you mean by 'not working'? Or do you mean something else... different error? The update does not happen? There is an update but it is not what you expect?

Sorry -- I hope I can help everyone understand -- I want to be able to understand and repro. a test case + report of a problem. But that's why I often call it a 'simple' test case & instructions. Otherwise, like here, it can be difficult to understand what the exact problem is you are reporting (is it what I reported above or different?) and how to run the test and what to do to reproduce the problem.

Thanks for your understanding, Ivan.

[ivan.klainic@igea.hr]

Yes, when I look back on my previous posts, I should have been more precise.

About my repro case... Now it's more "labelized" with useful info, which you will see when you run it.
When pressed...

Update1 button (calling anonymous PLSQL block), it throws error:

BEGIN
myPACKAGE.myPROC2 ( :dfID, :lsHelper );
END;

ORA-06550: line 3, column 1:
PLS-00306: wrong number or types of arguments in call to 'MYPROC2'
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored

Error: -6550

Update2 button (calling SqlPLSQLCommand), it throws error:

BEGIN
myPACKAGE.myPROC2 ( :dfID, :lsHelper );
END;

String too long

Error: 2214
Reason: Attempting to concatenate two strings and the resulting string
is greater than 1000 characters.
Remedy: Correct the SQL statement concatenation.

Update3 button (calling plain UPDATE statement):
That way works.

Basicly, my goal is to update record with rich text control's data via Oracle PLSQL procedure.
The history of this forum thread is quite long already, so I'll be short here. If is any other information needed, I will provide it.


Thanks.