, ,

EXECUTE IMMEDIATE

Note:- This type of Dynamic SQL would not work in the 10.7 version Database.

For the Select statement

Note:- I have created the sample Examples code based on the EMP and DEPT table in the scott schema.

Example:-
———-

DECLARE
L_DEPTNO NUMBER DEFAULT 10;
L_SAL NUMBER; 
BEGIN 
EXECUTE IMMEDIATE ‘select max(sal) from emp 
where deptno = :l_deptno’
INTO L_SAL
USING L_DEPTNO;
DBMS_OUTPUT.PUT_LINE(L_SAL);
END;

For the Insert statement

Note:- I have created the sample Examples code based on the EMP and DEPT table in the scott schema.

Example:-
———–

DECLARE
L_ENAME VARCHAR2(20) DEFAULT ‘PHANI’;
L_EMPNO NUMBER DEFAULT 2;
L_DEPTNO NUMBER DEFAULT 10;
BEGIN
EXECUTE IMMEDIATE ‘INSERT INTO EMP(ENAME,EMPNO,DEPTNO) VALUES
(:L_ENAME,:L_EMPNO,:L_DEPTNO)’
USING L_ENAME,
L_EMPNO,
L_DEPTNO;
END;

For the Update Statement
Note:- I have created the sample Examples code based on the EMP and DEPT table in the scott schema.

Example:-
———–

DECLARE
L_ENAME VARCHAR2(20) DEFAULT ‘PHANI’;
L_EMPNO NUMBER DEFAULT 2;
L_DEPTNO NUMBER DEFAULT 10;
BEGIN
EXECUTE IMMEDIATE ‘UPDATE EMP
SET ENAME = ”RAHUL” 
WHERE ENAME = :l_ENAME’
USING L_ENAME;
END;
/by
, ,

Introduction of DYNAMIC SQL

If we have to create some PROCEDURE, FUNCTION or PACKAGE and run them, then we have to do two steps.

1) Compile the code.
2) Execute the code which you have compiled.

Errors can occur at any above steps.

At the compile Time (Step 1), system will check for the syntax of the code and also if all the objects used in the code are exisiting in database or not.

If you want to hide the objects at the compile time so that you do not get the error message at the compile time (step 1) then use DYNAMIC SQL.

Difference between DBMS_SQL and EXECUTE IMMEDIATE
———————————————————————–

EXECUTE IMMEDIATE type of Dynamic SQL would not work in the old versions of Oracle Database like 10.7 or older then this version.

DBMS_SQL type of Dynamic SQL would work in all the version of Oracle Database.
/by
, ,

DBMS_SQL

For the Select statement
Note:- I have created the sample Examples code based on the EMP and DEPT table in the scott schema.

Example:-

DECLARE
L_DEPTNO NUMBER DEFAULT 10;
L_SAL NUMBER;
L_SQL VARCHAR2( 3000 );
L_CUR NUMBER;
L_RC NUMBER;
BEGIN
— Code converted in dynamic SQL start(phani).
L_SQL := ‘select max(sal) from emp where deptno = :l_deptno’;
L_CUR := dbms_sql.OPEN_CURSOR;
dbms_sql.PARSE( L_CUR, L_SQL, dbms_sql.NATIVE );
dbms_sql.BIND_VARIABLE( L_CUR, ‘:l_deptno’, L_DEPTNO );
— describe defines
dbms_sql.DEFINE_COLUMN( L_CUR, 1, L_SAL );
— execute
L_RC := dbms_sql.EXECUTE( L_CUR );
LOOP
— fetch a row
IF dbms_sql.FETCH_ROWS( L_CUR ) > 0 THEN
— fetch columns from the row
dbms_sql.COLUMN_VALUE( L_CUR, 1, L_SAL );
ELSE
EXIT;
END IF;
END LOOP;
dbms_sql.CLOSE_CURSOR( L_CUR );
dbms_output.PUT_LINE( L_SAL );
— Code converted in dynamic SQL end (phani).
END;

For the Insert statement

Note:- I have created the sample Examples code based on the EMP and DEPT table in the scott schema.

Example:-

DECLARE
L_SQL VARCHAR2( 3000 );
L_CUR NUMBER;
L_RC NUMBER;
g_empno NUMBER := 1;
g_ename VARCHAR2(30) := ‘REDDY’;
g_deptno NUMBER := 10;
BEGIN
— Code converted in dynamic SQL start. 
l_sql:= ‘insert into emp 
(EMPNO,
ENAME,
DEPTNO)
VALUES
( :p_empno,
:p_ename,
:p_deptno)’;

l_cur := dbms_sql.open_cursor; 
dbms_sql.parse(l_cur, l_sql, dbms_sql.native);
dbms_sql.bind_variable(l_cur, ‘:p_empno’, g_empno); 
dbms_sql.bind_variable(l_cur, ‘:p_ename’, g_ename); 
dbms_sql.bind_variable(l_cur, ‘:p_deptno’,g_deptno);
— execute
l_rc := dbms_sql.execute(l_cur);
DBMS_SQL.CLOSE_CURSOR (l_cur);
END;

For the Update Statement
Note:- I have created the sample Examples code based on the EMP and DEPT table in the scott schema.

Example:-

DECLARE
L_SQL VARCHAR2( 3000 );
L_CUR NUMBER;
L_RC NUMBER;
g_new_ename VARCHAR2(20) := ‘REDDY01’;
g_old_ename VARCHAR2(20) := ‘REDDY’;
BEGIN
— Code converted in dynamic SQL start. 

l_sql:= ‘update emp 
set ename = :p_new_ename
where ename = :p_old_ename’;

l_cur := dbms_sql.open_cursor; 
dbms_sql.parse(l_cur, l_sql, dbms_sql.native);

dbms_sql.bind_variable(l_cur, ‘:p_new_ename’,g_new_ename);
dbms_sql.bind_variable(l_cur, ‘:p_old_ename’,g_old_ename);

— execute
l_rc := dbms_sql.execute(l_cur);
DBMS_SQL.CLOSE_CURSOR (l_cur);

— Code converted in dynamic SQL end. 
END;

/by
, , , ,

Database Query for Discoverer

All discoverer folders and workbooks that you create get stored in database. You can use the below queries to find the details.
Find your Discoverer Workbook and Folder Details from Database:
select  fu.user_name owner,
        doc.doc_id "Document Id",
        doc.doc_name "Document Name",
        doc.doc_developer_key,
        doc.doc_folder_id,
        doc.doc_created_by,
        doc.doc_created_date,
        doc.doc_updated_by,
        doc.doc_updated_date,
        min(qs.qs_created_date) first_acc,
        max(qs.qs_created_date) Last_Acc
from    disco_apps.eul5_documents doc,
        apps.fnd_user fu,
        disco_apps.eul5_qpp_stats qs
where   '#'||fu.user_id = doc.doc_created_by
        and qs.qs_doc_name=doc.doc_name
        and qs.qs_doc_owner=fu.user_name
        and doc.doc_created_date<qs.qs_created_date
group by fu.user_name,
        doc.doc_id,
        doc.doc_name,
        doc.doc_developer_key,
        doc.doc_folder_id,
        doc.doc_created_by,
        doc.doc_created_date,
        doc.doc_updated_by,
        doc.doc_updated_date
order by doc.doc_updated_date desc;


Find your Discoverer Workbook performance for different Business Areas:





select  ba.ba "Business Area",
        usr.user_name,
        doc.doc_name "Work Book",
        min(qs_act_elap_time) "Fastest",
        max(qs_act_elap_time) "Slowest",
        round(avg(qs_act_elap_time),2) "AVG (s)",
        round(avg(qs_act_elap_time)/60,2) "AVG (m)",
        count(*) "Often",
        doc.doc_created_date,
        min(acc.qs_created_date) "First",
        max(acc.qs_created_date) "Last Access"
from    disco_apps.eul5_documents doc,
        apps.fnd_user usr,
        disco_apps.eul5_qpp_stats acc,
        (select distinct gd_doc_id from disco_apps.eul5_access_privs ) privs,
        (
          select distinct doc.doc_id,ba.ba_name ba
          from disco_apps.eul5_documents doc
          ,disco_apps.eul5_elem_xrefs eex
          ,disco_apps.eul5_ba_obj_links bol
          ,disco_apps.eul5_objs obj
          ,disco_apps.eul5_bas ba
          WHERE doc.doc_id = eex.ex_from_id
          AND eex.ex_to_par_name = obj.obj_name
          AND obj.obj_id = bol.bol_obj_id
          AND bol.bol_ba_id = ba.ba_id
        )ba
where   '#'||usr.user_id=doc.doc_created_by
        And doc.doc_name=acc.qs_doc_name
        And privs.gd_doc_id = doc.doc_id
        And usr.user_name = upper(acc.QS_DOC_OWNER)
        And doc.doc_created_date<acc.qs_created_date
        And doc.doc_id=BA.doc_id
group by ba.ba,
        usr.user_name,
        doc.doc_name,
        doc.doc_created_date;






/by 
, , , 
Invoking SQL*Loader


Invoking SQL*Loader


On Unix systems, the command used to invoke SQL*Loader is sqlldr. On Windows systems running Oracle8i, release 8.1 or higher, the command is also sqlldr.


The command to execute SQL*Loader is as follows:


sqlldr USERID = <username>/<password> CONTROL = <control file name> LOG = <log file name>….


Issuing the sqlldr command by itself results in a list of valid command-line parameters being displayed. Command-line parameters are usually keyword/value pairs, and may be any combination of the following:


USERID = {username[/password] [@net_service_name]|/} Specifies the username and password to use when connecting to the database.


CONTROL = control_ file_name  Specifies the name, which may include the path, of the control file. The default extension is .ctl.


LOG = path_ file_name Specifies the name of the log file to generate for a load session. You may include a path as well. By default, the log file takes on the name of the control file, but with a .log extension, and is written to the same directory as the control file.


BAD = path_ file_name Specifies the name of the bad file. You may include a path as part of the name. By default, the bad file takes the name of the control file, but with a .bad extension, and is written to the same directory as the control file.




DATA = path_ file_name Specifies the name of the file containing the data to load. You may include a path as part of the name. By default, the name of the control file is used, but with the .dat extension.


DISCARD = path_ file_name Specifies the name of the discard file. You may include a path as part of the name. By default, the discard file takes the name of the control file, but it has a .dis extension.


Other Parameters are:


DISCARDMAX=logical_record_count


SKIP=logical_record_count


SKIP_INDEX_MAINTENANCE={TRUE | FALSE}


SKIP_UNUSABLE_INDEXES={TRUE | FALSE}


LOAD=logical_record_count


ERRORS=insert_error_count


ROWS=rows_in_bind_array


BINDSIZE=bytes_in_bind_array


SILENT=[(]keyword[,keyword...][)]


DIRECT={TRUE | FALSE}


PARFILE=path_file_name


PARALLEL={TRUE | FALSE}


READSIZE=bytes_in_read_buffer


FILE=database_datafile_name




/by