Easy Problem I Should think, cr11 vb6 image

In a nutshell

I have a report, and a vb6 which calls it, thou the results are not the same, when called from vb6, other then just opening in cr and runing it.. The only thing which is different is that the dynamic location images, does not appear, everything else does ?

In a Sea Sure Shell

Vb does not pass anything to the report, because the report always looks at the same database (mdb) when it runs, infact i just fill the database as i want with vb, and then call the report..

Its just one record in a 6 colum type, all text fields, one date time field..
All of which show fine, apart from one, being a txt field which contains a location of a picture such as C:\Pics\blah.jpg

But this does not show on the report when called, just the other records..

I am using the new Image Location field in cr11, which i've used lots, so i know the reports right and works fine, because like i said, if i preview in cr itself, it works great..

So the problem must be when i call it, in my vb program, i have the following code

======Code=============
Dim oApp As CRAXDRT.Application
Dim oReport As CRAXDRT.Report

Set oApp = New CRAXDRT.Application
Set oReport = oApp.OpenReport("C:\offer.rpt", 1)

crvMyCRViewer.ReportSource = oReport
crvMyCRViewer.ViewReport

frmPrint.Show
======End Code==========

Which shows it on the screen, but with no pictures, i've checked the database, file path is fine

anyways come across this b4 ???

Any help on this would be great

thanks for your timewow paul, your so good looking|||stop that, this is a serious question|||look like no ones know mr paul|||i'll cry if no ones help :-(|||anyone ? i lose my job, if i dont work this out by 2morrow :(|||well thanks for your help everyone, i worked it out myself, and as i'm nice, i'll tell you how incase anyone else has this problem in the furture.. its just taken me 2 days to work this out

but because i had cr10, then 11 and both were installed it seem, i had a ref to cr viewer and active x dll's 10 and not 11, so althougth the report showed, ofcouse the advanced thing which comes with v11, does not work such as the dynamic image location.

So i got my hands on verison 11 of both dll's and it work, ta da

dynamic sql doesnt work?

I use dynamic sql to write the following PL/SQL block, but it always states that i got error on the part for opening cursor.
Actually I dunno why there's such an error.
Please help
==============================================
DECLARE
vMap_Old_Rank char(10);
vCor_New_Rank char(10);
vTable_Name Source.table_name%TYPE;
vSQLstr varchar(9999);
this_rank rank_Mapping.old_rank%TYPE;

Cursor tableCursor IS
SELECT table_name
FROM Source;

Type cur_typ is REF CURSOR;
c cur_typ;

BEGIN
OPEN tableCursor;
LOOP
FETCH tableCursor INTO vTable_Name;
EXIT WHEN tableCursor%NOTFOUND;

OPEN rankCursor FOR 'SELECT DISTINCT(rank) FROM ' || vTable_Name;

LOOP
FETCH rankCursor INTO vMap_Old_Rank;
EXIT WHEN rankCursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(vMap_Old_Rank);

/* UPDATE vTable_Name
SET rank = vCor_New_Rank
WHERE rank = vMap_Old_Rank;
*/

END LOOP;
IF rankCursor%ISOPEN THEN CLOSE rankCursor;
END IF;

END LOOP;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

EXCEPTION

WHEN OTHERS THEN
dbms_output.put_line('no actions!');
IF rankCursor%ISOPEN THEN CLOSE rankCursor;
END IF;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

END;
==============================================
ERROR at line 17:
ORA-06550: line 17, column 23:
PLS-00103: Encountered the symbol "SELECT DISTINCT(rank) FROM " when expecting
one of the following:
select
==============================================I believe it has to do with this syntax and it will not work:
OPEN rankCursor FOR 'SELECT DISTINCT(rank) FROM ' || vTable_Name;

You may not be able to specify a dynamic SQL build in an OPEN. The OPEN needs to be OPEN rankCursor FOR SELECT DISTINCT(rank) FROM tbl;

Originally posted by wakuku
I use dynamic sql to write the following PL/SQL block, but it always states that i got error on the part for opening cursor.
Actually I dunno why there's such an error.
Please help
==============================================
DECLARE
vMap_Old_Rank char(10);
vCor_New_Rank char(10);
vTable_Name Source.table_name%TYPE;
vSQLstr varchar(9999);
this_rank rank_Mapping.old_rank%TYPE;

Cursor tableCursor IS
SELECT table_name
FROM Source;

Type cur_typ is REF CURSOR;
c cur_typ;

BEGIN
OPEN tableCursor;
LOOP
FETCH tableCursor INTO vTable_Name;
EXIT WHEN tableCursor%NOTFOUND;

OPEN rankCursor FOR 'SELECT DISTINCT(rank) FROM ' || vTable_Name;

LOOP
FETCH rankCursor INTO vMap_Old_Rank;
EXIT WHEN rankCursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(vMap_Old_Rank);

/* UPDATE vTable_Name
SET rank = vCor_New_Rank
WHERE rank = vMap_Old_Rank;
*/

END LOOP;
IF rankCursor%ISOPEN THEN CLOSE rankCursor;
END IF;

END LOOP;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

EXCEPTION

WHEN OTHERS THEN
dbms_output.put_line('no actions!');
IF rankCursor%ISOPEN THEN CLOSE rankCursor;
END IF;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

END;
==============================================
ERROR at line 17:
ORA-06550: line 17, column 23:
PLS-00103: Encountered the symbol "SELECT DISTINCT(rank) FROM " when expecting
one of the following:
select
==============================================|||Originally posted by dmmac
I believe it has to do with this syntax and it will not work:
OPEN rankCursor FOR 'SELECT DISTINCT(rank) FROM ' || vTable_Name;

You may not be able to specify a dynamic SQL build in an OPEN. The OPEN needs to be OPEN rankCursor FOR SELECT DISTINCT(rank) FROM tbl;

YOU sulrely are able to specify dynamic sql in an open, look at following example:

SQL>var vcsr refcursor;
SQL>declare
2 tabname varchar2(32):='tables';
3 cols varchar2(128):='table_name,initial_extent,next_ext ent';
4 begin
5 open :vcsr for 'select '||cols
6 ||' from user_'||tabname
7 ||' where rownum <6';
8 end;
9 /

PL/SQL procedure successfully completed.

SQL>print :vcsr

TABLE_NAME INITIAL_EXTENT NEXT_EXTENT
---------- ----- ----
AEEMAIL 204800 204800
AETABLE 204800 204800
APPLICATION_CONTROL 5242880 1048576
CHAINED_ROWS 1081344 2129920
CHURNSTEP2 204800 204800

5 rows selected.

SQL>

Also, I do not see 'rankCursor' defined as a ref cursor anywhere!!

:cool:|||Sorry to post the wrong code before.

But in my coding, I did define rankCursor before as follows:
==============================================
DECLARE
vMap_Old_Rank char(10);
vCor_New_Rank char(10);
vTable_Name Source.table_name%TYPE;
vSQLstr varchar(9999);
this_rank rank_Mapping.old_rank%TYPE;

Cursor tableCursor IS
SELECT table_name
FROM Source;

Type cur_typ is REF CURSOR;
rankCursor cur_typ;

BEGIN
OPEN tableCursor;
LOOP
FETCH tableCursor INTO vTable_Name;
EXIT WHEN tableCursor%NOTFOUND;

OPEN rankCursor FOR 'SELECT DISTINCT(rank) FROM ' || vTable_Name;

LOOP
FETCH rankCursor INTO vMap_Old_Rank;
EXIT WHEN rankCursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(vMap_Old_Rank);

/* UPDATE vTable_Name
SET rank = vCor_New_Rank
WHERE rank = vMap_Old_Rank;
*/

END LOOP;
IF rankCursor%ISOPEN THEN CLOSE rankCursor;
END IF;

END LOOP;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

EXCEPTION

WHEN OTHERS THEN
dbms_output.put_line('no actions!');
IF rankCursor%ISOPEN THEN CLOSE rankCursor;
END IF;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

END;
==============================================
But the same errors still occurred.

ERROR at line 17:
ORA-06550: line 17, column 23:
PLS-00103: Encountered the symbol "SELECT DISTINCT(rank) FROM " when expecting
one of the following:
select
==============================================

I've tried LKBrwn_DBA's example. But it stated an error, "Bind variable not declared".|||Which version of Oracle are you using?

Did you try this example?:

SQL>var vcsr refcursor;

SQL>declare
2 tabname varchar2(32):='tables';
3 cols varchar2(128):= 'table_name,initial_extent,next_extent';
4 begin
5 open :vcsr for 'select '||cols
6 ||' from user_'||tabname
7 ||' where rownum <6';
8 end;
9 /|||Originally posted by LKBrwn_DBA
Which version of Oracle are you using?

Did you try this example?:

SQL>var vcsr refcursor;

SQL>declare
2 tabname varchar2(32):='tables';
3 cols varchar2(128):= 'table_name,initial_extent,next_extent';
4 begin
5 open :vcsr for 'select '||cols
6 ||' from user_'||tabname
7 ||' where rownum <6';
8 end;
9 /

I've tried, but failed again...saying
"Bind variable vscr not declared"

The one i'm using is Orcale8 Enterprise Edition Release 8.0.6.3.0|||Originally posted by wakuku
Sorry to post the wrong code before.

But in my coding, I did define rankCursor before as follows:
==============================================
DECLARE
vMap_Old_Rank char(10);
vCor_New_Rank char(10);
vTable_Name Source.table_name%TYPE;
vSQLstr varchar(9999);
this_rank rank_Mapping.old_rank%TYPE;

Cursor tableCursor IS
SELECT table_name
FROM Source;

Type cur_typ is REF CURSOR;
rankCursor cur_typ;

BEGIN
OPEN tableCursor;
LOOP
FETCH tableCursor INTO vTable_Name;
EXIT WHEN tableCursor%NOTFOUND;

OPEN rankCursor FOR 'SELECT DISTINCT(rank) FROM ' || vTable_Name;

LOOP
FETCH rankCursor INTO vMap_Old_Rank;
EXIT WHEN rankCursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(vMap_Old_Rank);

/* UPDATE vTable_Name
SET rank = vCor_New_Rank
WHERE rank = vMap_Old_Rank;
*/

END LOOP;
IF rankCursor%ISOPEN THEN CLOSE rankCursor;
END IF;

END LOOP;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

EXCEPTION

WHEN OTHERS THEN
dbms_output.put_line('no actions!');
IF rankCursor%ISOPEN THEN CLOSE rankCursor;
END IF;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

END;
==============================================
But the same errors still occurred.

ERROR at line 17:
ORA-06550: line 17, column 23:
PLS-00103: Encountered the symbol "SELECT DISTINCT(rank) FROM " when expecting
one of the following:
select
==============================================

I've tried LKBrwn_DBA's example. But it stated an error, "Bind variable not declared".
Your error suggests that you used double quotes (") not single quotes (') in your example:

SQL> declare
2 type rc is ref cursor;
3 r rc;
4 begin
5 open r for "select * from dept";
6* end;
SQL> /
declare
*
ERROR at line 1:
ORA-06550: line 5, column 14:
PLS-00201: identifier 'select * from dept' must be declared
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored

SQL> declare
2 type rc is ref cursor;
3 r rc;
4 begin
5 open r for 'select * from dept';
6* end;
SQL> /

PL/SQL procedure successfully completed.|||Originally posted by andrewst
Your error suggests that you used double quotes (") not single quotes (') in your example:

SQL> declare
2 type rc is ref cursor;
3 r rc;
4 begin
5 open r for "select * from dept";
6* end;
SQL> /
declare
*
ERROR at line 1:
ORA-06550: line 5, column 14:
PLS-00201: identifier 'select * from dept' must be declared
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored

SQL> declare
2 type rc is ref cursor;
3 r rc;
4 begin
5 open r for 'select * from dept';
6* end;
SQL> /

PL/SQL procedure successfully completed.

I've tried this, but failed again.

It seems that "OPEN sqlstatement FOR cursor" can't be run in the SQL I'm using.

I've used other method, but it works. Here's my coding...
==============================================
DECLARE
vMap_Old_Rank char(10);
vCor_New_Rank char(10);
vTable_Name Source_Table.table_name%TYPE;
vSQLstr varchar(9999);
vTotal_Rank Integer;

TYPE cur_typ is REF CURSOR;

Cursor tableCursor IS
SELECT table_name FROM Source_Table;

rankCursor integer;
exeCursor integer;
updateCursor integer;

BEGIN
OPEN tableCursor;

LOOP

FETCH tableCursor INTO vTable_Name;
EXIT WHEN tableCursor%NOTFOUND;

rankCursor := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(rankCursor, 'SELECT rank, new_rank, count(rank) FROM rank_Mapping, '
|| vTable_Name || ' WHERE ltrim(rtrim(rank)) = ltrim(rtrim(old_rank))
GROUP BY rank, new_rank', DBMS_SQL.NATIVE);

DBMS_SQL.DEFINE_COLUMN(rankCursor, 1, vMap_Old_Rank, 10);
DBMS_SQL.DEFINE_COLUMN(rankCursor, 2, vCor_New_Rank, 10);
DBMS_SQL.DEFINE_COLUMN(rankCursor, 3, vTotal_Rank);

exeCursor := DBMS_SQL.EXECUTE(rankCursor);

updateCursor := DBMS_SQL.OPEN_CURSOR;

LOOP

IF DBMS_SQL.FETCH_ROWS(rankCursor) > 0 THEN

DBMS_SQL.COLUMN_VALUE(rankCursor, 1, vMap_Old_Rank);
DBMS_SQL.COLUMN_VALUE(rankCursor, 2, vCor_New_Rank);
DBMS_SQL.COLUMN_VALUE(rankCursor, 3, vTotal_Rank);

DBMS_OUTPUT.PUT_LINE(vTable_Name || ' ' || vMap_Old_Rank ||'-'||
vCor_New_Rank || ': ' || vTotal_Rank);

DBMS_SQL.PARSE(updateCursor, 'UPDATE ' || vTable_Name ||
' SET rank = :vCor_New_Rank WHERE rank = :vMap_Old_Rank',
DBMS_SQL.NATIVE);

DBMS_SQL.BIND_VARIABLE(updateCursor, 'vMap_Old_Rank', vMap_Old_Rank);
DBMS_SQL.BIND_VARIABLE(updateCursor, 'vCor_New_Rank', vCor_New_Rank);

/* exeCursor := DBMS_SQL.EXECUTE(updateCursor); */

ELSE
EXIT;
END IF;

END LOOP;
IF DBMS_SQL.IS_OPEN(rankCursor) THEN
DBMS_SQL.CLOSE_CURSOR(rankCursor);
END IF;

IF DBMS_SQL.IS_OPEN(updateCursor) THEN
DBMS_SQL.CLOSE_CURSOR(updateCursor);
END IF;

END LOOP;
IF tableCursor%ISOPEN THEN CLOSE tableCursor;
END IF;

EXCEPTION

..............

END;
==============================================|||but it doesnt work if the ref cursor is declared with a return value.
for example, following would work
--------------
CREATE OR REPLACE
PACKAGE rcpackage IS

TYPE Recx IS RECORD
(a VARCHAR2(100));

TYPE MODULESCurType IS REF CURSOR ;

function x return rcPackage.MODULESCurType ;

END;
/

CREATE OR REPLACE
PACKAGE BODY rcpackage IS

function x return rcPackage.MODULESCurType is

t rcPackage.MODULESCurType;
s varchar2(500);

begin

s :='select * from tab';

open T for s;

return t;
end;

END;
/
----------

but not following
-----------
-- Start of DDL Script for Package RECON.RCPACKAGE
-- Generated 2-Oct-2003 22:33:06 from RECON@.CGEN

CREATE OR REPLACE
PACKAGE rcpackage IS

TYPE Recx IS RECORD
(a VARCHAR2(100));

TYPE MODULESCurType IS REF CURSOR return Recx ;

function x return rcPackage.MODULESCurType ;

END;
/

CREATE OR REPLACE
PACKAGE BODY rcpackage IS

function x return rcPackage.MODULESCurType is

t rcPackage.MODULESCurType;
s varchar2(500);

begin

s :='select * from tab';

open T for s;

return t;

end;

END;
/
-----------

Originally posted by LKBrwn_DBA
YOU sulrely are able to specify dynamic sql in an open, look at following example:

SQL>var vcsr refcursor;
SQL>declare
2 tabname varchar2(32):='tables';
3 cols varchar2(128):='table_name,initial_extent,next_ext ent';
4 begin
5 open :vcsr for 'select '||cols
6 ||' from user_'||tabname
7 ||' where rownum <6';
8 end;
9 /

PL/SQL procedure successfully completed.

SQL>print :vcsr

TABLE_NAME INITIAL_EXTENT NEXT_EXTENT
---------- ----- ----
AEEMAIL 204800 204800
AETABLE 204800 204800
APPLICATION_CONTROL 5242880 1048576
CHAINED_ROWS 1081344 2129920
CHURNSTEP2 204800 204800

5 rows selected.

SQL>

Also, I do not see 'rankCursor' defined as a ref cursor anywhere!!

:cool:|||hi actually i really wanna help in this TASK could u please help me out as soon as possible.....i kindly thanxs for that...
----------------

There are a number of subtasks to this weekly task. PL/SQL is not necessary. It will be
sufficient to use SQL. However you will need a firm grasp of the concepts of Week 7
lectures to be able to do the subtasks.
1) Create the following table
StudentTableNotNormalised
StudId Name CourseCode CourseDesc Lecturer Grade Office
S1234 Jack C224 Database Codd D 381
S1234 Jack C225 Algorithms Djikstra P 380
S2345 Jill C224 Database Codd HD 381
S2345 Jill C226 Architecture Ritchie HD 390
S4567 Jack C226 Architecture Ritchie D 390
S4567 Jack C224 Database Codd F 381
S9872 Howard Cpol Politics Marx F 380
2) Design a schema for this table that is in second normal form but not third normal
form.
Demonstrate this schema (you can if you like use views from your original table
to demonstrate the schema and populations: Hint use Distinct, , alternatively
create the tables and populations with a script).
3) Design a schema for this that is in third normal form.
Demonstrate this schema (again you can use views, alternatively create the
tables and populations with a script)
4) Functional dependencies.
Create two SQL queries on the table StudentTableNotNormalised
to ascertain whether Lecturer functionally determines Office and conversely
whether Office functionally determines Lecturerer.
(Hint Use Distinct, Group by and count)